pt-online-schema-change doesn't apply underscores to foreign keys individually
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Invalid
|
High
|
Carlos Salguero |
Bug Description
pt-osc version: version 2.2.15
MySQL version: 5.6.19a (but should affect other versions too)
Summary:
The pt-online-
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:/
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-
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-
Enter MySQL password:
No slaves found. See --recursion-method if host PeterDolbergMac
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_
Altering `pt_osc_
Creating new table...
`pt_osc_
Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new' [for Statement "CREATE TABLE `pt_osc_
`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-
Changed in percona-toolkit: | |
status: | New → In Progress |
importance: | Undecided → High |
assignee: | nobody → Frank Cizmich (frank-cizmich) |
milestone: | none → 2.3.1 |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
tags: | added: pt-online-schema-change |
tags: | added: i64127 |
tags: | added: i64713 |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
tags: | added: i67446 |
Changed in percona-toolkit: | |
status: | Fix Released → Triaged |
assignee: | Frank Cizmich (frank-cizmich) → Carlos Salguero (carlos-salguero) |
milestone: | 2.2.17 → 2.2.21 |
Indeed. Verified.
Thank you Peter.