| Bug #71616 | auto-naming of foreign keys seems broken when multi-operation ALTER is used | ||
|---|---|---|---|
| Submitted: | 7 Feb 2014 0:53 | Modified: | 16 Apr 2019 20:50 |
| Reporter: | Marcos Albe (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.14, 5.6.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[7 Feb 2014 4:00]
MySQL Verification Team
Hello Marcos, Thank you for the bug report and test case. Verified as described on latest builds. Thanks, Umesh
[7 Feb 2014 4:08]
MySQL Verification Team
// 5.6.15 -- affected
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)
mysql> create table parent (id int not null auto_increment primary key, c char(32)) engine innodb;
insert into parent values (), ();
create table uncle (id int not null auto_increment primary key, c char(32)) engine innodb;
insert into uncle values (), ();
create table child (id int not null auto_increment primary key, c char(32)) engine innodb;
insert into child values (), ();
set foreign_key_checks=OFF;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into parent values (), ();
alter table child add column parent_id int, add constraint foreign key (parent_id) references parent (id), LOCK=NONE;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table uncle (id int not null auto_increment primary key, c char(32)) engine innodb;
alter table child add column uncle_id int, add constraint foreign key (uncle_id) references uncle (id), LOCK=NONE;Query OK, 0 rows affected (0.04 sec)
mysql> insert into uncle values (), ();
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table child (id int not null auto_increment primary key, c char(32)) engine innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into child values (), ();
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table child add column parent_id int, add constraint foreign key (parent_id) references parent (id), LOCK=NONE;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table child add column uncle_id int, add constraint foreign key (uncle_id) references uncle (id), LOCK=NONE;
ERROR 1823 (HY000): Failed to add the foreign key constraint 'test/child_ibfk_1' to system tables
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` char(32) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table child add column uncle_id int, LOCK=NONE;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table child add constraint foreign key (uncle_id) references uncle (id), LOCK=NONE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` char(32) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`uncle_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `uncle_id` (`uncle_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`),
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`uncle_id`) REFERENCES `uncle` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
[7 Feb 2014 4:11]
MySQL Verification Team
// 5.5.36 - Not affected
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.36-log |
+------------+
1 row in set (0.00 sec)
mysql> create table parent (id int not null auto_increment primary key, c char(32)) engine innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into parent values (), ();
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table uncle (id int not null auto_increment primary key, c char(32)) engine innodb;
insert into uncle values (), ();
Query OK, 0 rows affected (0.00 sec)
mysql> insert into uncle values (), ();
create table child (id int not null auto_increment primary key, c char(32)) engine innodb;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table child (id int not null auto_increment primary key, c char(32)) engine innodb;
insert into child values (), ();
Query OK, 0 rows affected (0.00 sec)
mysql> insert into child values (), ();
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table child add column parent_id int, add constraint foreign key (parent_id) references parent (id);
alter table child add column uncle_id int, add constraint foreign key (uncle_id) references uncle (id);
show create table child\GQuery OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> alter table child add column uncle_id int, add constraint foreign key (uncle_id) references uncle (id);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` char(32) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`uncle_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `uncle_id` (`uncle_id`),
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`uncle_id`) REFERENCES `uncle` (`id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[9 Apr 2019 7:41]
Dmitry Lenev
Posted by developer: The problem is still repeatable in version 5.7.27-git. Somewhat simplified test case is: create table parent (id int not null auto_increment primary key, c char(32)); create table uncle (id int not null auto_increment primary key, c char(32)); create table child (parent_id int, c char (32), foreign key (parent_id) references parent (id)); set foreign_key_checks=OFF; alter table child add column uncle_id int, drop column c, add constraint foreign key (uncle_id) references uncle (id), algorithm=inplace; # Fails: 1823: Failed to add the foreign key constraint 'test/child_ibfk_1' to system tables Quick analysis shows that code in innobase_update_foreign_try() when it is called from commit_try_rebuild() (i.e. when ALTER TABLE is executed using in-place algorithm but still rebuilds the table internally) doesn't take into account existing foreign key on table when calculating name for newly created foreign key. In 8.0.17-git problem is not repeatable: reate table parent (id int not null auto_increment primary key, c char(32)); create table uncle (id int not null auto_increment primary key, c char(32)); create table child (parent_id int, c char(32), foreign key (parent_id) references parent (id)); set foreign_key_checks=OFF; # The below ALTER succeeds! alter table child add column uncle_id int, drop column c, add constraint foreign key (uncle_id) references uncle (id), algorithm=inplace; # And new FK is there with the correct name! show create table child; # Table Create Table # child CREATE TABLE `child` ( # `parent_id` int(11) DEFAULT NULL, # `uncle_id` int(11) DEFAULT NULL, # KEY `parent_id` (`parent_id`), # KEY `uncle_id` (`uncle_id`), # CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`), # CONSTRAINT `child_ibfk_2` FOREIGN KEY (`uncle_id`) REFERENCES `uncle` (`id`) #) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci This is because foreign key metadata is now managed and persisted in the New data-dictionary by SQL-layer, which also correctly generates foreign key names in this scenario.
[9 Apr 2019 10:11]
Dmitry Lenev
Posted by developer: Taking into account above I am closing this bug as fixed in 8.0.3. Moving it to Documenting state to let Documentation team to decide if it makes sense to mention it in release notes.
[16 Apr 2019 20:50]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.3 release, and here's the changelog entry: An in-place ALTER TABLE operation that rebuilt the table and added a foreign key without specifying a foreign key constraint name failed due to a duplicate constraint name. InnoDB did not account for existing foreign key constraint names. This issue was addressed in MySQL 8.0.3, when foreign key metadata was moved to the data dictionary, and foreign key constraint name generation was moved to the SQL layer.
Description: When running an ALTER with multiple parts, one of the parts being addition of foreign key, the foreign key addition will fail due to duplicate name: How to repeat: mysql [localhost] {msandbox} (test) > create table parent (id int not null auto_increment primary key, c char(32)) engine innodb; Query OK, 0 rows affected (0.02 sec) mysql [localhost] {msandbox} (test) > insert into parent values (), (); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > create table uncle (id int not null auto_increment primary key, c char(32)) engine innodb; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into uncle values (), (); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > create table child (id int not null auto_increment primary key, c char(32)) engine innodb; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into child values (), (); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > set foreign_key_checks=OFF; Query OK, 0 rows affected (0.00 sec) -- first time it goes through, because "child_ibfk1" is not being used. mysql [localhost] {msandbox} (test) > alter table child add column parent_id int, add constraint foreign key (parent_id) references parent (id), LOCK=NONE; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 -- but second fk addition fails: mysql [localhost] {msandbox} (test) > alter table child add column uncle_id int, add constraint foreign key (uncle_id) references uncle (id), LOCK=NONE; ERROR 1823 (HY000): Failed to add the foreign key constraint 'test/child_ibfk_1' to system tables -- here's confirmation that's due to dupe name: mysql [localhost] {msandbox} (test) > show engine innodb status\G .... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2014-02-06 22:27:13 7fb6527b6700 Error in foreign key constraint creation for table `test`.`child`. A foreign key constraint of name `test`.`child_ibfk_1` already exists. (Note that internally InnoDB adds 'databasename' in front of the user-defined constraint name.) Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names. -- but if we do using individual ALTERs for each operation it works: mysql [localhost] {msandbox} (test) > alter table child add column uncle_id int, LOCK=NONE; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > alter table child add constraint foreign key (uncle_id) references uncle (id), LOCK=NONE; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Suggested fix: use same auto-naming routine when adding foreign keys from within multi-operation ALTER statement.