pt-online-schema-change generates double foreign key constraints

[Simon J Mudd]

Example:

root@myhost [mydb]> show create table sysctl_cnf\G
*************************** 1. row ***************************
       Table: sysctl_cnf
Create Table: CREATE TABLE `sysctl_cnf` (
  `set_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `value_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`set_id`,`item_id`,`value_id`),
  KEY `item_id` (`item_id`,`value_id`),
  KEY `value_id` (`value_id`,`item_id`),
  CONSTRAINT `sysctl_cnf_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `sysctl_set` (`id`),
  CONSTRAINT `sysctl_cnf_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `sysctl_ci` (`id`),
  CONSTRAINT `sysctl_cnf_ibfk_3` FOREIGN KEY (`value_id`) REFERENCES `sysctl_cv` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

My environment:

[root@myhost ~]# rpm -q percona-toolkit
percona-toolkit-2.2.3-1.noarch
[root@myhost ~]# rpm -q MySQL-server
MySQL-server-5.6.13-1.el6.x86_64

I use a wrapper but this conveniently tells me how pt-online schema change is called:

[root@myhost ~]# our_company_wrapper_for_osc -T 0.1 -M 0.1 -l 10 -L 15 -d mydb -t sysctl_cnf -a "drop key value_id, add key ( value_id )" -c "my-slave" -e
Sep 20 12:07:46 myhost our_company_wrapper_for_osc[2474]: Starting run...
pt-online-schema-change --critical-load="Threads_running:17" --max-load "Threads_running:12" --recurse=0 F=/path/to/.my.cnf,D=mydb,t=sysctl_cnf --alter "drop key value_id, add key ( value_id )" --check-slave-lag h=my-slave,u=myuser,p='mypass' --max-lag=0.1 --chunk-time=0.1 --execute
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `mydb`.`sysctl_cnf`...
Creating new table...
Created new table mydb._sysctl_cnf_new OK.
Altering new table...
Altered `mydb`.`_sysctl_cnf_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 10239680 rows...
Copying `mydb`.`sysctl_cnf`: 6% 07:22 remain
Copying `mydb`.`sysctl_cnf`: 12% 06:51 remain
Copying `mydb`.`sysctl_cnf`: 19% 06:18 remain
Copying `mydb`.`sysctl_cnf`: 25% 05:47 remain
Copying `mydb`.`sysctl_cnf`: 32% 05:16 remain
Copying `mydb`.`sysctl_cnf`: 38% 04:46 remain
Copying `mydb`.`sysctl_cnf`: 44% 04:17 remain
Copying `mydb`.`sysctl_cnf`: 51% 03:47 remain
Copying `mydb`.`sysctl_cnf`: 57% 03:20 remain
Copying `mydb`.`sysctl_cnf`: 65% 02:39 remain
Copying `mydb`.`sysctl_cnf`: 71% 02:08 remain
Copying `mydb`.`sysctl_cnf`: 78% 01:40 remain
Copying `mydb`.`sysctl_cnf`: 84% 01:11 remain
Copying `mydb`.`sysctl_cnf`: 90% 00:42 remain
Copying `mydb`.`sysctl_cnf`: 97% 00:13 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `mydb`.`_sysctl_cnf_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `mydb`.`sysctl_cnf`.

Sep 20 12:15:42 myhost our_company_wrapper_for_osc[2474]: OSC change on mydb.sysctl_cnf took 477 seconds
[root@myhost ~]# mysql mydb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3337606
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@myhost [mydb]> show create table sysctl_cnf\G
*************************** 1. row ***************************
       Table: sysctl_cnf
Create Table: CREATE TABLE `sysctl_cnf` (
  `set_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `value_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`set_id`,`item_id`,`value_id`),
  KEY `item_id` (`item_id`,`value_id`),
  KEY `value_id` (`value_id`),
  CONSTRAINT `_sysctl_cnf_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `sysctl_set` (`id`),
  CONSTRAINT `_sysctl_cnf_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `sysctl_ci` (`id`),
  CONSTRAINT `_sysctl_cnf_ibfk_3` FOREIGN KEY (`value_id`) REFERENCES `sysctl_cv` (`id`),
  CONSTRAINT `sysctl_cnf_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `sysctl_set` (`id`),
  CONSTRAINT `sysctl_cnf_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `sysctl_ci` (`id`),
  CONSTRAINT `sysctl_cnf_ibfk_3` FOREIGN KEY (`value_id`) REFERENCES `sysctl_cv` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@myhost [mydb]> exit
Bye

Notice the double InnoDB foreign key constraints which should not be there.

[Simon J Mudd (sjmudd)]

Work around: manually drop the extra foreign key constraints, which is immediate, so not a big problem. I'm not sure if this generates extra work for InnoDB when checking constraints and would guess that it does.

[Simon J Mudd (sjmudd)]

Note: I have just seen that MySQL 5.6.14 is out and this comment in the changelog:

  * InnoDB: InnoDB would rename a user-defined foreign key
      constraint containing the string "_ibfk_" in its name,
      resulting in a duplicate constraint. (Bug #17076737, Bug
      #69693, Bug #17076718, Bug #69707)

This seems to be similar, so I'm not sure yet if this issue is due to 5.6.X and therefore not a bug in percona-toolkit.
I need to find a different version to try on to see.

[Simon J Mudd (sjmudd)]

Just checked on 5.6.14 and indeed there is no issue.
Sorry for the noise.

[Simon J Mudd (sjmudd)]

Hm. Not sure how to close this, so I guess someone else will have to.

[Shahriyar Rzayev (rzayev-sehriyar)]

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