pt-osc version: version 2.2.15
MySQL version: 5.6.19a (but should affect other versions too)
Summary:
The pt-online-schema-change tool (version 2.2.15) fails with an error when the table being altered has foreign key constraints where some start with an underscore and some don't.
The error for a table named "child":
Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new'
Root cause and fix:
This is caused because there is an error in the code which toggles constraint names between starting with underscores and removing the underscores. The problem is that the code checks to see if any foreign key starts with an underscore and then assumes that all of them start with an underscore, which may be incorrect. Because of that possibly incorrect assumption, the code then applies the same regex replacement to all foreign keys, even though it needs to apply the check to each foreign key individually.
Here is the current code:
# If it has a leading underscore, we remove one, otherwise we add one
# This is in contrast to previous behavior were we added underscores
# indefinitely, sometimes exceeding the allowed name limit
# https://bugs.launchpad.net/percona-toolkit/+bug/1215587
if ( $sql =~ /CONSTRAINT `_/ ) {
$sql =~ s/^ CONSTRAINT `_/ CONSTRAINT `/gm;
} else {
$sql =~ s/^ CONSTRAINT `/ CONSTRAINT `_/gm;
}
Here is what it can be changed to in order to resolve this issue:
# If it has a leading underscore, we remove one, otherwise we add one
# This is in contrast to previous behavior were we added underscores
# indefinitely, sometimes exceeding the allowed name limit
$sql =~ s/^ CONSTRAINT `(_?)/' CONSTRAINT `'.($1 eq '' ? '_' : '')/gme;
Reproducing the problem:
1) run in MySQL:
create database pt_osc_test;
use pt_osc_test;
create table parent1 (
parent1_id int auto_increment not null,
PRIMARY KEY (parent1_id)
) engine=InnoDB;
create table child (
child_id int auto_increment not null,
value int not null default 0,
parent1_id int not null,
PRIMARY KEY (child_id),
CONSTRAINT `parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
) engine=InnoDB;
create table parent2 (
parent2_id int auto_increment not null,
PRIMARY KEY (parent2_id)
) engine=InnoDB;
2) Run pt-osc, successfully:
./pt-online-schema-change -u root -h 127.0.0.1 --ask-pass --alter 'ADD `parent2_id` int default null, ADD CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`)' --execute --print D=pt_osc_test,t=child;
3) View the child table and notice that it has one constraint with an underscore and one constraint without:
mysql> show create table child;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`child_id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL DEFAULT '0',
`parent1_id` int(11) NOT NULL,
`parent2_id` int(11) DEFAULT NULL,
PRIMARY KEY (`child_id`),
KEY `parent1_fk` (`parent1_id`),
KEY `parent2_fk` (`parent2_id`),
CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`),
CONSTRAINT `_parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4) Run pt-osc, performing ANY change, which fails when creating the new table:
./pt-online-schema-change -u root -h 127.0.0.1 --ask-pass --alter 'alter column value set default 1' --execute --print D=pt_osc_test,t=child;
Enter MySQL password:
No slaves found. See --recursion-method if host PeterDolbergMac.local has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `pt_osc_test`.`child`...
Creating new table...
`pt_osc_test`.`child` was not altered.
Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new' [for Statement "CREATE TABLE `pt_osc_test`.`_child_new` (
`child_id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL DEFAULT '0',
`parent1_id` int(11) NOT NULL,
`parent2_id` int(11) DEFAULT NULL,
PRIMARY KEY (`child_id`),
KEY `parent1_fk` (`parent1_id`),
KEY `parent2_fk` (`parent2_id`),
CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`),
CONSTRAINT `parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"] at ./pt-online-schema-change line 9893.
Indeed. Verified.
Thank you Peter.