Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1153

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

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist