Comment 0 for bug 1215587

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.