Adding _ to constraints can create issues with constraint name length

[Jon Middleton]

When using pt-online-schema-change, if a table has a foreign key, it will add a "_" on the name of the constraint to make it unique. However, if you do multiple schema changes, you'll end up with "_" stacking up which will eventually cause an error like the following.

Error creating new table: DBD::mysql::db do failed: Identifier name '_____FK_SALES_FLAT_ORDER_ITEM_ORDER_ID_SALES_FLAT_ORDER_ENTITY_ID' is too long [for Statement "CREATE TABLE `magdb`.`_sales_flat_order_item_new` (

Obviously the FK name is borderline silly in length, but when using software frameworks, you don't have much say. However, even on reasonably named constraints, you'll still have a hard limit on how many times you can use pt-online-schema-change on any given table. In short, the continual addition of underscores to the name, if the tool is utilized multiple times on a table, you can hit the 64 character limit and be stuck with no way to work around it given the options available in the tool.

Proposed change, toggle between adding and removing underscores to keep the constraint name length fluctuation to one character. On line 9560, change to something like this which will look for leading underscores and strip them out if they exist, otherwise add them:

if ($sql =~ m/^ CONSTRAINT `_+/gm) {
   $sql =~ s/^ CONSTRAINT `_+/ CONSTRAINT `/gm;
} else {
   $sql =~ s/^ CONSTRAINT `/ CONSTRAINT `_/gm;
}

This would potentially cause issues if no-swap-tables is yes and the old table isn't dropped as it wouldn't be able to get a unique name space.. but could additionally put in a checks for the uniqueness of the constraint and fall back onto the default prepending underscores to the constraint name.

==

Please account for the situation where the current length of the CONSTRAINT
name is exactly 64 characters. Adding a 65th character will flag an error
an the max name length is 64 characters.

Troy.
#

[Francois Swanepoel (swanepoel)]

Hi there

Has any decision or progress been made with regards to this this feature request? I really need this for my project.

In my production environment we need to OPTIMIZE a table quite often to ensure we do not run out of hard drive space. (Simply deleting unwanted/old entries from the table does not stop the table file to grow indefinitely.) The pt-online-schema-change tool works great, until I hit the FK constraint name length limit after optimizing a number of times.

[Francois Swanepoel (swanepoel)]

Good day

Is there any feedback on my previous request?

Regards
Francois

[Frank Cizmich (frank-cizmich)]

Hi Francois,

Sorry for the delay in addressing this issue. We'll update as soon as we fix it.

[Frank Cizmich (frank-cizmich)]

Hello Francois,

I forgot to ask you what version of pt-online-schema-change are you using.

run:
pt-online-schema-change --version

I prepared a patch assuming it's 2.2.6

It basically limits the constraint name prefix to 2 underscores. Further runs just alternately remove or add one accordingly.
It also makes sure the new name is unique.

[Francois Swanepoel (swanepoel)]

Morning Frank

Thank you very much for attending to this problem I am having.

The version of pt-online-schema-change I am currently using is 2.2.7. I downloaded this Debian installer from Percona's website. Will your patch for 2.2.6 also work with the latest version?

Regards
Francois

[Frank Cizmich (frank-cizmich)]

Francois,

The patch will work for 2.2.7

[Francois Swanepoel (swanepoel)]

Good day Frank

I applied your patch and after it initially looked like it solved the issue, I realised a related problem still exists.

There could be two types of foreign key constraints related to a table that needs to be altered using pt-online-schema-change:
 1) A FK on this table referencing another table.
 2) A FK on another table referencing the table to be altered.

The patch provided by yourself addresses and fixes one of these scenarios, the second one. The FK in scenario 1 has a name that gets prepended with underscores all the time until the name gets too long and breaks. This of course is a problem.

The following example demonstrates this behaviour:

// ----- Create two test tables with FKs for scenario 1 and 2: -----
drop database if exists test_db;
CREATE DATABASE test_db;
USE test_db;

CREATE TABLE IF NOT EXISTS `Table1` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `T2ID` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `tagIndex` (`T2ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=335;

CREATE TABLE `Table2` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=335;

ALTER TABLE `Table1`
  ADD CONSTRAINT `fkey1` FOREIGN KEY (`T2ID`) REFERENCES `Table2` (`ID`) ON DELETE NO ACTION;

ALTER TABLE `Table2`
  ADD CONSTRAINT `fkey2` FOREIGN KEY (`ID`) REFERENCES `Table1` (`T2ID`) ON DELETE NO ACTION;

// ----- Check the constraint names. -----
SELECT CONSTRAINT_NAME, TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema='test_db' and (table_name='Table1' OR table_name='Table2') and CONSTRAINT_NAME LIKE '%fkey%';

Here you will see the names of both FKs being:
 1) 'fkey1'
 2) 'fkey2'.

So far so good.

// ---- Now alter the first table 'Table1' using pt-online-schema-change which had been patched: -----
./pt-online-schema-change-2.2.6-PATCHED --alter-foreign-keys-method=rebuild_constraints --execute -u 'username' --password='password' D='test_db',t='Table1' --alter='ENGINE=InnoDB'

After this alter had been run 5 times, the constraint names are:
 1) '_____fkey1'
 2) '_fkey2'

The name of the second FK is managed correctly where the underscores are added and removed from run to run.

However, the name of the first FK is prepended with underscores every run and eventually will break when it gets too long.

I tested this behaviour with your patch applied to versions 2.2.6, 2.2.7 and 2.2.8 of the Debian installers available online and all have the same behaviour in this regard.

Looking forward to your feedback.

Regards
Francois

[Frank Cizmich (frank-cizmich)]

Hello Francois,

You are right. Nice catch!

I'm attaching an updated fix. I've simplified it a bit.

Thank you for providing a clear test case.

Regards,
Frank

[Francois Swanepoel (swanepoel)]

Hello Frank

A belated thank you very much for this fix.

Regards
Francois

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-623