Adding _ to constraints can create issues with constraint name length

Bug #1215587 reported by Jon Middleton on 2013-08-22
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Frank Cizmich

Bug Description

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.

==

Note, also, once the underscore has been added to the beginning of the CONSTRAINT name, the name should be truncated to 64 characters as that is the max object name length in MySQL. This is to account for the case where the original CONSTRAINT name is exactly 64 characters and adding an underscore would make it 65 characters in length, which causes errors.
Troy.
#

tags: added: foreign-keys pt-online-schema-change
Changed in percona-toolkit:
status: New → Triaged
Francois Swanepoel (swanepoel) wrote :

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) wrote :

Good day

Is there any feedback on my previous request?

Regards
Francois

Frank Cizmich (frank-cizmich) wrote :

Hi Francois,

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

Frank Cizmich (frank-cizmich) wrote :

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.

Changed in percona-toolkit:
assignee: nobody → Frank Cizmich (frank-cizmich)
Francois Swanepoel (swanepoel) wrote :

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) wrote :

Francois,

The patch will work for 2.2.7

Francois Swanepoel (swanepoel) wrote :

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) wrote :

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) wrote :

Hello Frank

A belated thank you very much for this fix.

Regards
Francois

description: updated
Changed in percona-toolkit:
importance: Undecided → Medium
milestone: none → 2.2.15
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers