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

LP #1498128: pt-online-schema-change doesn't apply underscores to foreign keys individually

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by Peter Dolberg last update 30-08-2017 13:35:41

      pt-osc version: version 2.2.15
      MySQL version: 5.6.19a (but should affect other versions too)

      Summary:
      The pt-online-schema-change tool (version 2.2.15) fails with an error when the table being altered has foreign key constraints where some start with an underscore and some don't.

      The error for a table named "child":
      Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new'

      Root cause and fix:
      This is caused because there is an error in the code which toggles constraint names between starting with underscores and removing the underscores. The problem is that the code checks to see if any foreign key starts with an underscore and then assumes that all of them start with an underscore, which may be incorrect. Because of that possibly incorrect assumption, the code then applies the same regex replacement to all foreign keys, even though it needs to apply the check to each foreign key individually.

      Here is the current code:

      1. If it has a leading underscore, we remove one, otherwise we add one
      2. This is in contrast to previous behavior were we added underscores
      3. indefinitely, sometimes exceeding the allowed name limit
      4. https://bugs.launchpad.net/percona-toolkit/+bug/1215587
        if ( $sql =~ /CONSTRAINT `_/ ) { $sql =~ s/^ CONSTRAINT `_/ CONSTRAINT `/gm; }

        else

        { $sql =~ s/^ CONSTRAINT `/ CONSTRAINT `_/gm; }

      Here is what it can be changed to in order to resolve this issue:

      1. If it has a leading underscore, we remove one, otherwise we add one
      2. This is in contrast to previous behavior were we added underscores
      3. indefinitely, sometimes exceeding the allowed name limit
        $sql =~ s/^ CONSTRAINT `(?)/' CONSTRAINT `'.($1 eq '' ? '' : '')/gme;

      Reproducing the problem:

      1) run in MySQL:
      create database pt_osc_test;
      use pt_osc_test;

      create table parent1 (
      parent1_id int auto_increment not null,
      PRIMARY KEY (parent1_id)
      ) engine=InnoDB;

      create table child (
      child_id int auto_increment not null,
      value int not null default 0,
      parent1_id int not null,
      PRIMARY KEY (child_id),
      CONSTRAINT `parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
      ) engine=InnoDB;

      create table parent2 (
      parent2_id int auto_increment not null,
      PRIMARY KEY (parent2_id)
      ) engine=InnoDB;

      2) Run pt-osc, successfully:
      ./pt-online-schema-change -u root -h 127.0.0.1 --ask-pass --alter 'ADD `parent2_id` int default null, ADD CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`)' --execute --print D=pt_osc_test,t=child;

      3) View the child table and notice that it has one constraint with an underscore and one constraint without:
      mysql> show create table child;
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Table Create Table

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      child CREATE TABLE `child` (
      `child_id` int(11) NOT NULL AUTO_INCREMENT,
      `value` int(11) NOT NULL DEFAULT '0',
      `parent1_id` int(11) NOT NULL,
      `parent2_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`child_id`),
      KEY `parent1_fk` (`parent1_id`),
      KEY `parent2_fk` (`parent2_id`),
      CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`),
      CONSTRAINT `_parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      4) Run pt-osc, performing ANY change, which fails when creating the new table:
      ./pt-online-schema-change -u root -h 127.0.0.1 --ask-pass --alter 'alter column value set default 1' --execute --print D=pt_osc_test,t=child;

      Enter MySQL password:
      No slaves found. See --recursion-method if host PeterDolbergMac.local has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      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 `pt_osc_test`.`child`...
      Creating new table...
      `pt_osc_test`.`child` was not altered.
      Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_child_new' [for Statement "CREATE TABLE `pt_osc_test`.`_child_new` (
      `child_id` int(11) NOT NULL AUTO_INCREMENT,
      `value` int(11) NOT NULL DEFAULT '0',
      `parent1_id` int(11) NOT NULL,
      `parent2_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`child_id`),
      KEY `parent1_fk` (`parent1_id`),
      KEY `parent2_fk` (`parent2_id`),
      CONSTRAINT `parent2_fk` FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`parent2_id`),
      CONSTRAINT `parent1_fk` FOREIGN KEY (`parent1_id`) REFERENCES `parent1` (`parent1_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"] at ./pt-online-schema-change line 9893.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              carlos.salguero Carlos Salguero
              Reporter:
              lpjirasync lpjirasync (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: