Pt-online-schema failure due to duplicated constraint names and from multiple foreign keys

Polyglot Projects Percona Toolkit
,
1

(I can’t seem to log this as a possible bug on perconadev.atlassian.net because it requires login, and login seems to be down or unavailable to me. If there’s a way to report without login, please let me know and sorry if this post is just noise.)

I seem to be running into an issue similar to PT-1418. pt-online-schema-change is failing due to duplicated constraint names when it attempts to make the table copy for altering.

Here’s a simple example:

I have a table with these three databases (MySQL):

CREATE TABLE foos (
  id BIGINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET = utf8mb4;

CREATE TABLE bars (
  id BIGINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET = utf8mb4;

CREATE TABLE bazs (
  id BIGINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET = utf8mb4;

Next I use pt-online-schema-change to add two foreign keys to the bars table:

./pt-online-schema-change --alter "ADD COLUMN foo_id BIGINT, ADD FOREIGN KEY (foo_id) REFERENCES foos (id);" D=pt_test,t=bars --execute
./pt-online-schema-change --alter "ADD COLUMN baz_id BIGINT, ADD FOREIGN KEY (baz_id) REFERENCES bazs (id);" D=pt_test,t=bars --execute

Here now are the constraints that have been added to bars:

mysql> show create table bars\G
*************************** 1. row ***************************
       Table: bars
Create Table: CREATE TABLE `bars` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `foo_id` bigint DEFAULT NULL,
  `baz_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`foo_id`),
  KEY `baz_id` (`baz_id`),
/* ---> */  CONSTRAINT `_bars_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`), 
/* ---> */  CONSTRAINT `bars_ibfk_1` FOREIGN KEY (`baz_id`) REFERENCES `bazs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

I think perhaps a key detail here is that the constraints have the same name other than the leading prefix.

Now, if I attempt to add another column to bars, I get the error:

pt-online-schema-change --alter "ADD COLUMN name VARCHAR(512);" D=pt_test,t=bars --execute

pt-online-schema-change --alter "ADD COLUMN name VARCHAR(512);" D=pt_test,t=bars --execute
No slaves found.  See --recursion-method if host ty.dev.lucidpressvpc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `pt_test`.`bars`...
Creating new table...
Error creating new table: DBD::mysql::db do failed: Duplicate foreign key constraint name '_bars_ibfk_1' [for Statement "CREATE TABLE `pt_test`.`_bars_new` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `foo_id` bigint DEFAULT NULL,
  `baz_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`foo_id`),
  KEY `baz_id` (`baz_id`),
    CONSTRAINT `__bars_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`),
    CONSTRAINT `_bars_ibfk_1` FOREIGN KEY (`baz_id`) REFERENCES `bazs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"] at ./pt-online-schema-change line 10919.
`pt_test`.`bars` was not altered.
        (in cleanup) DBD::mysql::db do failed: Duplicate foreign key constraint name '_bars_ibfk_1' [for Statement "CREATE TABLE `pt_test`.`_bars_new` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `foo_id` bigint DEFAULT NULL,
  `baz_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`foo_id`),
  KEY `baz_id` (`baz_id`),
    CONSTRAINT `__bars_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`),
    CONSTRAINT `_bars_ibfk_1` FOREIGN KEY (`baz_id`) REFERENCES `bazs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"] at ./pt-online-schema-change line 10919.

Is this expected behavior? User error?

For context I tested this with the v3.5.7 which seems to be latest version as of today. Also I’m using MySQL version 8.0.36-0ubuntu0.20.04.1.

2

Hi, pt-osc adds a leading underscore to constraint names on the new table, so the problem here is that your current constraint names are overlapping with these. I think the best to avoid further issues would be to drop and add back the existing _bars_ibfk_1 constraint with another name.

3

It seems to be a bug on MySQL, which wasn’t there before. I’ve used pt-osc many many times, with many rewrites so the handling of (empty) / _ / __ is okay.

CREATE TABLE statement is okay (I’m using pt-osc 3.4.0), but MySQL considers that keys are the same…

Fun thing: changing my keys’ name (here it would be bars => foos) works…

@Ty_Lewis if you still need to make it work with no downtime, you may add a temporary line in pt-online-schema-change around line 10919 (as per your dump).

You should see something like:

      my %search_dict = (
        'CONSTRAINT `__' => 'CONSTRAINT `',
        'CONSTRAINT `_' => 'CONSTRAINT `__',
        'CONSTRAINT `' => 'CONSTRAINT `_'
      );
      my $constraint_pattern = qr((CONSTRAINT `__|CONSTRAINT `_|CONSTRAINT `));

change it to:

      my %search_dict = (
        'bars_ibfk_' => 'foos_ibfk_',
        'CONSTRAINT `__' => 'CONSTRAINT `',
        'CONSTRAINT `_' => 'CONSTRAINT `__',
        'CONSTRAINT `' => 'CONSTRAINT `_'
      );
      my $constraint_pattern = qr((bars_ibfk|CONSTRAINT `__|CONSTRAINT `_|CONSTRAINT `));

Use at your own risk :sweat_smile:

Once you’ve succeeded, remove the 2 changes.