MySQL Bugs: #69693: Duplicate constraints displayed when name includes "_ibfk

Bug #69693	Duplicate constraints displayed when name includes "_ibfk_"
Submitted:	8 Jul 2013 17:14	Modified:	30 Jul 2013 11:51
Reporter:	Robert Lineweaver	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.32, 5.6.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ibfk

Description:
This issue occurs with version 5.5.32, but not 5.5.30.  Based on the changelog, it likely appeared in 5.5.31.

When adding a foreign key constraint and specifying a constraint name that includes the string "_ibfk_", viewing the table structure using "show create table" results in two constraints being displayed.  The second has an autogenerated constraint name that appears to be the table name prepended before "_ibfk_XXX".

How to repeat:
Ensure that InnoDB is the default table type.

Execute the following:

use test;
drop table if exists t2;
drop table if exists t1;
create table t1 (id int(11) primary key);
create table t2 (t1_id int(11));
alter table t2 add constraint `_ibfk_` foreign key (t1_id) references t1 (id);
show create table t2;

Observe that the output displays two constraints, as below:

CREATE TABLE `t2` (
  `t1_id` int(11) DEFAULT NULL,
  KEY `_ibfk_` (`t1_id`),
  CONSTRAINT `_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`),
  CONSTRAINT `t2_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Suggested fix:
Do not create/display a second constraint when only one was intended.

Adding 5.6.12 to the list of affected versions.

Hello Robert,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh

I couldn't reproduce it on 5.6.12.

// 5.5.32

node1 [localhost] {msandbox} (my_test_db) > select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (my_test_db) > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
node1 [localhost] {msandbox} (test) > drop table if exists t2;
Query OK, 0 rows affected (0.05 sec)

node1 [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (0.03 sec)

node1 [localhost] {msandbox} (test) > create table t1 (id int(11) primary key);
Query OK, 0 rows affected (0.16 sec)

node1 [localhost] {msandbox} (test) > create table t2 (t1_id int(11));
Query OK, 0 rows affected (0.10 sec)

node1 [localhost] {msandbox} (test) > alter table t2 add constraint `_ibfk_` foreign key (t1_id) references t1 (id);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (test) > show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `t1_id` int(11) DEFAULT NULL,
  KEY `_ibfk_` (`t1_id`),
  CONSTRAINT `_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`),
  CONSTRAINT `t2_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

// 5.6.12 - Not affected

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.12-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 (id int(11) primary key);
Query OK, 0 rows affected (0.20 sec)

mysql> create table t2 (t1_id int(11));
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t2 add constraint `_ibfk_` foreign key (t1_id) references t1 (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `t1_id` int(11) DEFAULT NULL,
  KEY `_ibfk_` (`t1_id`),
  CONSTRAINT `_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//

Added changelog entry for 5.1.72, 5.5.32, 5.6.14, 5.7.2:

"InnoDB" would rename a user-defined foreign key constraint containing the
string "_ibfk_" in its name, resulting in a duplicate constraint.

Reproduced on 5.6.12  

mysqld --version
mysqld  Ver 5.6.12 for Linux on x86_64 (MySQL Community Server (GPL))

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)

mysql> create table t1 (id int(11) primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (t1_id int(11));
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t2 add constraint `_ibfk_` foreign key (t1_id) references t1 (id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `t1_id` int(11) DEFAULT NULL,
  KEY `_ibfk_` (`t1_id`),
  CONSTRAINT `_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`),
  CONSTRAINT `t2_ibfk_` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

5.1$ bzr log -r 4029
------------------------------------------------------------
revno: 4029
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.1
timestamp: Thu 2013-07-25 14:53:23 +0530
message:
  Bug #17076737 DUPLICATE CONSTRAINTS DISPLAYED WHEN NAME INCLUDES "_IBFK_"
  
  Problem:
  
  When the user specified foreign key name contains "_ibfk_", InnoDB wrongly
  tries to rename it. 
  
  Solution:
  
  When a table is renamed, all its associated foreign keys will also be renamed,
  only if the foreign key names are automatically generated.  If the foreign key
  names are given by the user, even if it has _ibfk_ in it, it must not be
  renamed.
  
  rb#2935 approved by Jimmy, Krunal and Satya

Posted by developer:
 
The changelog text was revised as follows:

"Adding a foreign key with a constraint name that included the string
_ibfk_ caused InnoDB to create a duplicate constraint with a generated
internal name. The generated internal name could also collide with an
existing user-defined constraint of the same name, causing a duplicate key
error."

Posted by developer:
 
The following related updates were made to the MySQL reference manual:

https://dev.mysql.com/doc/refman/5.7/en/rename-table.html

RENAME TABLE changes internally generated foreign key constraint names and user-defined foreign key constraint names that contain the string “tbl_name_ibfk_” to reflect the new table name. InnoDB interprets foreign key constraint names that contain the string “tbl_name_ibfk_” as  internally generated names. 

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that contain the string “tbl_name_ibfk_” to reflect the new table name. InnoDB interprets foreign key constraint names that contain the string “tbl_name_ibfk_” as internally generated names.