pt-online-schema-change generates double foreign key constraints
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Invalid
|
Undecided
|
Unassigned |
Bug Description
Example:
root@myhost [mydb]> show create table sysctl_cnf\G
*******
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`
KEY `item_id` (`item_
KEY `value_id` (`value_
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-
[root@myhost ~]# rpm -q MySQL-server
MySQL-server-
I use a wrapper but this conveniently tells me how pt-online schema change is called:
[root@myhost ~]# our_company_
Sep 20 12:07:46 myhost our_company_
pt-online-
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `mydb`.
Creating new table...
Created new table mydb._sysctl_
Altering new table...
Altered `mydb`.
Creating triggers...
Created triggers OK.
Copying approximately 10239680 rows...
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copying `mydb`.
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `mydb`.
Dropping triggers...
Dropped triggers OK.
Successfully altered `mydb`.
Sep 20 12:15:42 myhost our_company_
[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
*******
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`
KEY `item_id` (`item_
KEY `value_id` (`value_id`),
CONSTRAINT `_sysctl_
CONSTRAINT `_sysctl_
CONSTRAINT `_sysctl_
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.
tags: | added: foreign-keys pt-online-schema-change risk |
tags: | added: mysql-5.6 |
Changed in percona-toolkit: | |
status: | New → Invalid |
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.