pt-online-schema-change fails with duplicate key in table for self-referencing FK

[junkfactory]

When altering a table with a self-refencing foreign key, pt-osc fails with a duplicate key in table.

pt-osc version = 2.2.19+
MySQL version = 5.6.31

Steps to reproduce.

# Setup database and test tables with self referencing FK

drop database if exists fk_test;
create database fk_test;
use fk_test;

CREATE TABLE `person` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `refId` bigint(20) DEFAULT NULL,
  `person` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_person` (`person`),
  KEY `fk_refId` (`refId`),
  CONSTRAINT `fk_person` FOREIGN KEY (`person`) REFERENCES `person` (`id`),
  CONSTRAINT `fk_refId` FOREIGN KEY (`refId`) REFERENCES `test_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Add a new column

./liquibase/bin/pt-online-schema-change --alter="add column testColumn bigint(20)" --alter-foreign-keys-method=auto --host=localhost --port=3306 --user=root --execute D=fk_test,t=test_table
No slaves found. See --recursion-method if host C02MM3DMFD57 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `fk_test`.`test_table` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Altering `fk_test`.`test_table`...
Creating new table...
Created new table fk_test._test_table_new OK.
Altering new table...
Altered `fk_test`.`_test_table_new` OK.
2016-10-11T17:18:50 Creating triggers...
2016-10-11T17:18:50 Created triggers OK.
2016-10-11T17:18:50 Copying approximately 1 rows...
2016-10-11T17:18:50 Copied rows OK.
2016-10-11T17:18:50 Max rows for the rebuild_constraints method: 4000
Determining the method to update foreign keys...
2016-10-11T17:18:50 `fk_test`.`test_table`: 1 rows; can use rebuild_constraints
2016-10-11T17:18:50 Analyzing new table...
2016-10-11T17:18:50 Swapping tables...
2016-10-11T17:18:50 Swapped original and new tables OK.
2016-10-11T17:18:50 Rebuilding foreign key constraints...
2016-10-11T17:18:50 Dropping triggers...
2016-10-11T17:18:50 Dropped triggers OK.
Altered `fk_test`.`test_table` but there were errors or warnings.
Error updating foreign key constraints: 2016-10-11T17:18:50 DBD::mysql::db do failed: Can't write; duplicate key in table '#sql-7db0_9' [for Statement "ALTER TABLE `fk_test`.`test_table` DROP FOREIGN KEY `_fk_refId`, ADD CONSTRAINT `fk_refId` FOREIGN KEY (`refId`) REFERENCES `fk_test`.`test_table` (`id`)"] at ./liquibase/bin/pt-online-schema-change line 10501.

[junkfactory (junkfactory)]

Suggested fix: https://github.com/percona/percona-toolkit/pull/137

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-710