pt-online-schema-change doesn't apply underscores to foreign keys individually

Bug #1498128 reported by Peter Dolberg
44
This bug affects 9 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Invalid
High
Carlos Salguero

Bug Description

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:
      # If it has a leading underscore, we remove one, otherwise we add one
      # This is in contrast to previous behavior were we added underscores
      # indefinitely, sometimes exceeding the allowed name limit
      # 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:
      # If it has a leading underscore, we remove one, otherwise we add one
      # This is in contrast to previous behavior were we added underscores
      # 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.

Changed in percona-toolkit:
status: New → In Progress
importance: Undecided → High
assignee: nobody → Frank Cizmich (frank-cizmich)
milestone: none → 2.3.1
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Indeed. Verified.

Thank you Peter.

Changed in percona-toolkit:
status: In Progress → Fix Committed
tags: added: pt-online-schema-change
Revision history for this message
Ian Dallas (idallas456) wrote :

How until 2.3.1 is released? This is a pretty serious bug with pt-online-schema-change since it basically can't be used anymore with something more complex than just unrelated tables. There are some new features that were added that are now unusable until a fix is released.

Is there any possibility we could get a hotfix for 2.2.x?

Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Hi Ian
We're considering putting out a hotfix.
Meanwhile I'm attaching the latest version fixed as suggested by Peter Dolberg, which works fine. (Thanks Peter)

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

This bug was introduced in 2.2.15 by https://bugs.launchpad.net/percona-toolkit/+bug/1215587. In addition to using the patched version attached in the previous comment, one can also use v2.2.14.

Given those two alternatives, I want to leave this for a future release because I think neither solution is ideal: adding _ leads to max length problems, but the toggling approach introduced in 2.2.15 lead to this problem and, in general, strikes me as not a good thing to do because it doesn't guarantee uniqueness and it's a bit clobbering, e.g. if a constraint's name is originally "_foo", then we clobber that by making it "foo" whereas adding a _ leaves the original name intact. I think a better solution might be something like a magical suffix like "_vN" where N is an integer that pt-osc increments each time it changes the constraint. So even if the original name is "_foo_v2", we'll get "_foo_v2_v1" on first run, "_foo_v2_v2" on 2nd run, etc. This has a bit of a length problem as well, but it's far more bounded than simply adding _, and the tool should do the right thing: verify that it can successfully rename all constraints before doing any real work. -- All this is just ideas so far.

tags: added: i64127
Przemek (pmalkowski)
tags: added: i64713
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Jaime Sicam (jssicam) wrote :
Download full text (4.5 KiB)

Looks like there's an issue if the existing constraints have both underscore and no underscore:

[user@sandbox employees_db]$ mysql --port=5625 employees -e "show create table employees"
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `emp_no_2` int(11) NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `fk2` (`emp_no_2`),
  CONSTRAINT `_fk1` FOREIGN KEY (`emp_no`) REFERENCES `employees2` (`emp_no`),
  CONSTRAINT `fk2` FOREIGN KEY (`emp_no_2`) REFERENCES `employees2` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[user@sandbox employees_db]$ pt-online-schema-change --execute --alter "engine=innodb" h=127.0.0.1,P=5625,D=employees,t=employees --alter-foreign-keys-method=rebuild_constraints
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
 LANGUAGE = (unset),
 LC_ALL = (unset),
 LC_CTYPE = "UTF-8",
 LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: F...

Read more...

Jaime Sicam (jssicam)
tags: added: i67446
Revision history for this message
igroene (igroene) wrote :

I still see this in 2.2.17 even though it says 'Fix released'

Revision history for this message
Christopher N (chroder) wrote :

I'm also still seeing this in 2.2.17.

Revision history for this message
Christopher N (chroder) wrote :

Worth saying that if I apply Peter's suggested fix to 2.2.17, that works.

Revision history for this message
Craig Glendenning (c-glendenning) wrote :

I noticed that here: https://www.percona.com/doc/percona-toolkit/2.2/release_notes.html 2.2.18 was released on 6/24/2016, but there is no reference to bug 1498128 in the 2.2.18 notes, only 2.2.17, which did not actually fix the bug. Was this bug actually fixed in 2.2.18?

Revision history for this message
Kevin (kevin-t-cook) wrote :

I ran into this issue on 2.2.18, applied Peter's patch and resolved the issue

Revision history for this message
Kevin (kevin-t-cook) wrote :
Revision history for this message
Daniël van Eeden (dveeden) wrote :

This bug seems not to be fixed in 2.2.19.
So it looks like the 'Fix Released' status is wrong.
So please change the status and/or post the commit which fixes this bug

Revision history for this message
Pontus (pontusfa) wrote :

$ pt-online-schema-change --version
pt-online-schema-change 2.2.19

...

Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table
...
CONSTRAINT `_FK_x` FOREIGN KEY (x) REFERENCES x (x) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_y`
...

High prio, marked as fixed when it in fact isn't and people have said so for months?

Changed in percona-toolkit:
status: Fix Released → Triaged
assignee: Frank Cizmich (frank-cizmich) → Carlos Salguero (carlos-salguero)
milestone: 2.2.17 → 2.2.21
Revision history for this message
Matthew Kent (mkent) wrote :

We were bitten by this last week on 2.2.19, rolling in the patch fixed the issue.

Big +1 to get this fixed upstream.

Revision history for this message
Carlos Salguero (carlos-salguero) wrote :

Cannot reproduce with pt-online-schema-change v 3.0.4

Changed in percona-toolkit:
status: Triaged → Invalid
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.