Activity log for bug #1215587

Date Who What changed Old value New value Message
2013-08-22 18:58:14 Jon Middleton bug added bug
2013-10-30 17:32:30 Daniel Nichter tags foreign-keys pt-online-schema-change
2013-10-30 17:32:37 Daniel Nichter percona-toolkit: status New Triaged
2014-05-05 18:57:06 Francois Swanepoel bug added subscriber Francois Swanepoel
2014-05-14 18:04:04 Frank Cizmich attachment added constraint_name_length_issue_1215587_2.2.6.diff https://bugs.launchpad.net/percona-toolkit/+bug/1215587/+attachment/4112540/+files/constraint_name_length_issue_1215587_2.2.6.diff
2014-05-14 18:04:19 Frank Cizmich percona-toolkit: assignee Frank Cizmich (frank-cizmich)
2014-06-13 18:30:47 Frank Cizmich attachment added constraint_name_length_issue_1215587_updated.diff https://bugs.launchpad.net/bugs/1215587/+attachment/4131131/+files/constraint_name_length_issue_1215587_updated.diff
2014-06-13 18:31:37 Frank Cizmich attachment added constraint_name_length_issue_1215587_updated.diff https://bugs.launchpad.net/percona-toolkit/+bug/1215587/+attachment/4131132/+files/constraint_name_length_issue_1215587_updated.diff
2015-07-08 21:25:13 troy frericks bug added subscriber troy frericks
2015-07-08 21:29:42 troy frericks 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. 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. #
2015-07-30 19:51:05 Frank Cizmich percona-toolkit: importance Undecided Medium
2015-07-30 19:51:08 Frank Cizmich percona-toolkit: milestone 2.2.15
2015-07-30 21:55:50 Frank Cizmich percona-toolkit: status Triaged Fix Committed
2015-07-31 13:30:17 troy frericks 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. # 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. #
2015-08-28 12:42:27 Hrvoje Matijakovic percona-toolkit: status Fix Committed Fix Released