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 |
|