pt-online-schema-change fails with self-referential foreign key

[Ian Dallas]

I have an existing table with the following schema:

CREATE TABLE `ConfigData` (
  `primaryKey` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(36) DEFAULT NULL,
  `version` int(11) NOT NULL,
  `createdBy` varchar(36) DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  `lastModifiedBy` varchar(36) DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `tenantId` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`primaryKey`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I run the follwing ALTER tables throught pt-online-schema-change:
 - ADD COLUMN `parentEntity_primaryKey` bigint(20) DEFAULT NULL
 - ADD KEY `parentEntityPrimaryKey` (`parentEntity_primaryKey`)
 - ADD CONSTRAINT `parentEntityFK` FOREIGN KEY (`parentEntity_primaryKey`) REFERENCES `ConfigData` (`primaryKey`)

pt-online-schema-change fails on the last one where we add the self-referential foreign key. When the tool exits there are two tables: ConfigData and _ConfigData_old and ConfigData has the following schema:

CREATE TABLE `ConfigData` (
  `primaryKey` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(36) DEFAULT NULL,
  `version` int(11) NOT NULL,
  `createdBy` varchar(36) DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  `lastModifiedBy` varchar(36) DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `tenantId` varchar(36) DEFAULT NULL,
  `parentEntity_primaryKey` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`primaryKey`),
  UNIQUE KEY `id` (`id`),
  KEY `parentEntityPrimaryKey` (`parentEntity_primaryKey`),
  CONSTRAINT `parentEntityFK` FOREIGN KEY (`parentEntity_primaryKey`) REFERENCES `_ConfigData_old` (`primaryKey`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

As you can see the problem with the parentEntityFK which references _ConfigData_old. The tool fails when trying to drop this table due to foreign key problems.

Tool/Server Versions:
 - 5.6.19-0ubuntu0.14.04.1 (Ubuntu)
 - pt-online-schema-change 2.2.11

DEBUG output for ADD CONSTRAINT `parentEntityFK` FOREIGN KEY is attached.

[Peiran Song (peiran-song)]

Error is verified and reproducible with the latest version 2.2.12.

Test
------------
CREATE TABLE `ConfigData` (
  `primaryKey` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(36) DEFAULT NULL,
  `parentEntity_primaryKey` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`primaryKey`),
  KEY `parentEntityPrimaryKey` (`parentEntity_primaryKey`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

# ./pt-online-schema-change --execute --alter "ADD CONSTRAINT parentEntityFK FOREIGN KEY (parentEntity_primaryKey) REFERENCES ConfigData (primaryKey)" D=test,t=ConfigData
...
2014-11-21T17:01:02 Error dropping the old table: DBD::mysql::db do failed: Cannot delete or update a parent row: a foreign key constraint fails [for Statement "DROP TABLE IF EXISTS `test`.`_ConfigData_old`"] at ./pt-online-schema-change line 9328.

Workaround
------------------------
When the tool runs, it creates a new table named _<table_name>_new and run the alter on the new table. Thus the referential constraint should really be created on this new table instead of the original one. The following command works:

# ./pt-online-schema-change --execute --alter "ADD CONSTRAINT parentEntityFK FOREIGN KEY (parentEntity_primaryKey) REFERENCES _ConfigData_new (primaryKey)" D=test,t=ConfigData

Suggested Fix
------------------------
Replace table name in self-referential FK reference to the new table name.

[Przemek (pmalkowski)]

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,
...

[Carlos Salguero (carlos-salguero)]

See https://bugs.launchpad.net/percona-toolkit/+bug/1632522.
It is part of PT-3.0

[Shahriyar Rzayev (rzayev-sehriyar)]

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