Comment 3 for bug 1393961

Similar problem happens when trying to alter table with self-referential FK which already exists.

mysql> CREATE TABLE fk1 (
    -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> `a` int(11) unsigned DEFAULT NULL,
    -> `b` int(11) unsigned NOT NULL,
    -> `id2` int(11) unsigned DEFAULT NULL,
    -> PRIMARY KEY (`id`),
    -> CONSTRAINT `id_id2` FOREIGN KEY (`id2`) REFERENCES `fk1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

# ./pt-online-schema-change --version
pt-online-schema-change 2.2.19

# ./pt-online-schema-change --alter "ADD COLUMN c INT(11)" D=test,t=fk1 --alter-foreign-keys-method auto --execute
No slaves found. See --recursion-method if host przemek1 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:
  `test`.`fk1` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Altering `test`.`fk1`...
Creating new table...
Created new table test._fk1_new OK.
Altering new table...
Altered `test`.`_fk1_new` OK.
2016-11-18T14:19:53 Creating triggers...
2016-11-18T14:19:53 Created triggers OK.
2016-11-18T14:19:53 Copying approximately 1 rows...
2016-11-18T14:19:53 Copied rows OK.
2016-11-18T14:19:53 Max rows for the rebuild_constraints method: 4000
Determining the method to update foreign keys...
2016-11-18T14:19:53 `test`.`fk1`: 1 rows; can use rebuild_constraints
2016-11-18T14:19:53 Analyzing new table...
2016-11-18T14:19:53 Swapping tables...
2016-11-18T14:19:53 Swapped original and new tables OK.
2016-11-18T14:19:53 Rebuilding foreign key constraints...
2016-11-18T14:19:53 Dropping triggers...
2016-11-18T14:19:53 Dropped triggers OK.
Altered `test`.`fk1` but there were errors or warnings.
Error updating foreign key constraints: 2016-11-18T14:19:53 DBD::mysql::db do failed: Can't write; duplicate key in table '#sql-1fa0_5274b' [for Statement "ALTER TABLE `test`.`fk1` DROP FOREIGN KEY `_id_id2`, ADD CONSTRAINT `id_id2` FOREIGN KEY (`id2`) REFERENCES `test`.`fk1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE"] at ./pt-online-schema-change line 10500.

mysql> show create table fk1\G
*************************** 1. row ***************************
       Table: fk1
Create Table: CREATE TABLE `fk1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  `b` int(11) unsigned NOT NULL,
  `id2` int(11) unsigned DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_id2` (`id2`),
  CONSTRAINT `_id_id2` FOREIGN KEY (`id2`) REFERENCES `_fk1_old` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table _fk1_old\G
*************************** 1. row ***************************
       Table: _fk1_old
Create Table: CREATE TABLE `_fk1_old` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  `b` int(11) unsigned NOT NULL,
  `id2` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_id2` (`id2`),
  CONSTRAINT `id_id2` FOREIGN KEY (`id2`) REFERENCES `_fk1_old` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The --alter-foreign-keys-method drop_swap works better, but obviously it not as safe.