**Reported in Launchpad by Agustín last update 11-04-2017 03:08:05

If there is a table with more than one FK, and there is a mix of them starting with and without underscore, pt-osc will fail with:

Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table ...

How to reproduce:

DROP SCHEMA IF EXISTS pt_osc_fk_drop;
CREATE SCHEMA IF NOT EXISTS pt_osc_fk_drop;
USE pt_osc_fk_drop;

CREATE TABLE `parent` (
`field_1` INT NOT NULL,
`field_2` INT NOT NULL,
KEY `key_field_1` (`field_1`),
KEY `key_field_2` (`field_2`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`field_1_parent` INT NOT NULL,
`field_2_parent` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_field_1_parent` (`field_1_parent`),
KEY `fk_field_2_parent` (`field_2_parent`),
CONSTRAINT `_fk_field_1_parent` FOREIGN KEY (`field_1_parent`) REFERENCES `parent` (`field_1`),
CONSTRAINT `fk_field_2_parent` FOREIGN KEY (`field_2_parent`) REFERENCES `parent` (`field_2`)
) ENGINE=InnoDB;

pt-online-schema-change --execute --print --alter="\ ADD COLUMN field_3 INT NOT NULL DEFAULT 1 AFTER field_2_parent" \ D="pt_osc_fk_drop",h="127.0.0.1",u="root",p="msandbox",P=5633,t="child"

As we can see in the example above, there are two FKs:

CONSTRAINT `_fk_field_1_parent` FOREIGN KEY (`field_1_parent`) REFERENCES `parent` (`field_1`),
CONSTRAINT `fk_field_2_parent` FOREIGN KEY (`field_2_parent`) REFERENCES `parent` (`field_2`)

one starting with underscore, and the other without. The tool tries to create the new table with the following structure:

Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new' [for Statement "CREATE TABLE `pt_osc_fk_drop`.`_child_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field_1_parent` int(11) NOT NULL,
`field_2_parent` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_field_1_parent` (`field_1_parent`),
KEY `fk_field_2_parent` (`field_2_parent`),
CONSTRAINT `fk_field_1_parent` FOREIGN KEY (`field_1_parent`) REFERENCES `parent` (`field_1`),
CONSTRAINT `fk_field_2_parent` FOREIGN KEY (`field_2_parent`) REFERENCES `parent` (`field_2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"] at /usr/bin/pt-online-schema-change line 10106.

which makes the fk_field_2_parent error out due to duplicate FK name. Attached goes a file with full tests and outputs, to show that this is an issue only when there is a mix. Note that due to how the tool currently works, if one wants to DROP a FK that starts with underscore, the pt-osc command should not have it, and vice versa (I'm saying this because the commands in the tests use this).

How to fix:

Check FK names for all fields before defining the new table structure, to avoid collisions in names, or change how names are chosen for new FKs (maybe having a random, fixed-length string at the end?).

Note that this is a clear issue when pt-osc is used for creating FKs, since the table will be left in a state where, unless one chooses the name correctly, some FKs will have underscore, and others will not. To be clear in what I'm saying, if the table has all FKs without underscore, pt-osc should be used with a name with underscore for any new FKs, and the other way around.

Agustn.