Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues

Select view

Select search mode

 
21 of 21

LP #1228078: pt-online-schema-change generates double foreign key constraints

Not a Bug

Description

**Reported in Launchpad by Simon J Mudd last update 30-10-2013 18:13:21

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.

Environment

None

Smart Checklist

Details

Assignee

Reporter

Priority

Labels

Smart Checklist

Created January 24, 2018 at 7:47 PM
Updated February 4, 2018 at 2:04 AM
Resolved January 24, 2018 at 7:47 PM

Activity

Show:

lpjirasyncJanuary 24, 2018 at 7:47 PM

**Comment from Launchpad by: Simon J Mudd on: 21-09-2013 07:34:23

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

lpjirasyncJanuary 24, 2018 at 7:47 PM

**Comment from Launchpad by: Simon J Mudd on: 21-09-2013 07:25:05

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

lpjirasyncJanuary 24, 2018 at 7:47 PM

**Comment from Launchpad by: Simon J Mudd on: 20-09-2013 16:16:50

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.

lpjirasyncJanuary 24, 2018 at 7:47 PM

**Comment from Launchpad by: Simon J Mudd on: 20-09-2013 10:33:49

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.