Bug #69707 Adding "_ibfk_" foreign key "Table XXX already exists"

Description:
We're seeing an error that suggests hidden name collisions in foreign keys. I saw this with MySQL 5.5.32 but not 5.5.24. The samples represent scripts that exist as part of our legacy DB upgrade tasks, and this is the first time they've failed.

I suspect this issue is related to http://bugs.mysql.com/bug.php?id=69699 and http://bugs.mysql.com/bug.php?id=69693

I ran all of the examples from the command line client to a local instance.

How to repeat:
Here's the basic order of operations:

DROP DATABASE IF EXISTS my_test_db;
CREATE DATABASE my_test_db;
USE my_test_db;
CREATE TABLE IF NOT EXISTS `door` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `window` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE `house` (
  `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `door` int(10) unsigned DEFAULT NULL,
  `window` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`key`),
  KEY `my_arbitrarily_named_constraint_ibfk_1` (`door`),
  KEY `other_arbitrarily_named_constraint_ibfk_1` (`window`),
  CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON UPDATE CASCADE,
  CONSTRAINT `house_ibfk_1` FOREIGN KEY (`window`) REFERENCES `window` (`key`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_ibfk_1`;
ALTER TABLE house ADD CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;

And here's the error from the last command:

ERROR 1050 (42S01): Table '.\my_test_db\house' already exists

I can eliminate the problem in a couple of ways...

===== WORKAROUND 1: Use a non-default name for the parallel constraint =====

If I setup the scenario so that the parallel constraint has a name "house_blah_ibfk_1" instead of "house_ibfk_1" (or even "house_ibfk_2"), we don't see an error.

DROP DATABASE IF EXISTS my_test_db;
CREATE DATABASE my_test_db;
USE my_test_db;
CREATE TABLE IF NOT EXISTS `door` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `window` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE `house` (
  `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `door` int(10) unsigned DEFAULT NULL,
  `window` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`key`),
  KEY `my_arbitrarily_named_constraint_ibfk_1` (`door`),
  KEY `other_arbitrarily_named_constraint_ibfk_1` (`window`),
  CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON UPDATE CASCADE,
  CONSTRAINT `house_blah_ibfk_1` FOREIGN KEY (`window`) REFERENCES `window` (`key`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_ibfk_1`;

===== WORKAROUND 2: Don't use "_ibfk_" in my arbitrarily named constraint =====

Changing my arbitrarily named constraint from "my_arbitrarily_named_constraint_ibfk_1" to "my_arbitrarily_named_constraint_blah_1" prevents the error.

DROP DATABASE IF EXISTS my_test_db;
CREATE DATABASE my_test_db;
USE my_test_db;
CREATE TABLE IF NOT EXISTS `door` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `window` (
	`key` int(10) unsigned NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(`key`)
) ENGINE=INNODB;
CREATE TABLE `house` (
  `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `door` int(10) unsigned DEFAULT NULL,
  `window` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`key`),
  KEY `my_arbitrarily_named_constraint_blah_1` (`door`),
  KEY `other_arbitrarily_named_constraint_ibfk_1` (`window`),
  CONSTRAINT `my_arbitrarily_named_constraint_blah_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON UPDATE CASCADE,
  CONSTRAINT `house_ibfk_1` FOREIGN KEY (`window`) REFERENCES `window` (`key`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_blah_1`;
ALTER TABLE house ADD CONSTRAINT `my_arbitrarily_named_constraint_blah_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;

Hello Ryan,

Thank you for the bug report. 
Verified as described on 5.5.32 version.

Thanks,
Umesh

// 5.5.32 - affected

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

node1 [localhost] {msandbox} ((none)) > DROP DATABASE IF EXISTS my_test_db;
Query OK, 0 rows affected, 1 warning (0.00 sec)

node1 [localhost] {msandbox} ((none)) > CREATE DATABASE my_test_db;
Query OK, 1 row affected (0.00 sec)

node1 [localhost] {msandbox} ((none)) > USE my_test_db;
Database changed
node1 [localhost] {msandbox} (my_test_db) > CREATE TABLE IF NOT EXISTS `door` (
    -> `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(`key`)
    -> ) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `window` (
        `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(`key`)
) ENGINE=INNODB;Query OK, 0 rows affected (0.10 sec)

node1 [localhost] {msandbox} (my_test_db) > CREATE TABLE IF NOT EXISTS `window` (
    -> `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(`key`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.25 sec)

node1 [localhost] {msandbox} (my_test_db) > CREATE TABLE `house` (
    ->   `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `door` int(10) unsigned DEFAULT NULL,
    ->   `window` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`key`),
    ->   KEY `my_arbitrarily_named_constraint_ibfk_1` (`door`),
    ->   KEY `other_arbitrarily_named_constraint_ibfk_1` (`window`),
    ->   CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON UPDATE CASCADE,
    ->   CONSTRAINT `house_ibfk_1` FOREIGN KEY (`window`) REFERENCES `window` (`key`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_ibfk_1`;
ALTER TABLE house ADD CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;Query OK, 0 rows affected (0.23 sec)

node1 [localhost] {msandbox} (my_test_db) > ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_ibfk_1`;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (my_test_db) > ALTER TABLE house ADD CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1050 (42S01): Table './my_test_db/house' already exists
node1 [localhost] {msandbox} (my_test_db) >

// 5.6.12 not affected

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

mysql> DROP DATABASE IF EXISTS my_test_db;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE my_test_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE my_test_db;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `door` (
    -> `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(`key`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.33 sec)

mysql> CREATE TABLE IF NOT EXISTS `window` (
    -> `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(`key`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `house` (
    ->   `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `door` int(10) unsigned DEFAULT NULL,
    ->   `window` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`key`),
    ->   KEY `my_arbitrarily_named_constraint_ibfk_1` (`door`),
    ->   KEY `other_arbitrarily_named_constraint_ibfk_1` (`window`),
    ->   CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON UPDATE CASCADE,
    ->   CONSTRAINT `house_ibfk_1` FOREIGN KEY (`window`) REFERENCES `window` (`key`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)

mysql> ALTER TABLE house DROP FOREIGN KEY `my_arbitrarily_named_constraint_ibfk_1`;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE house ADD CONSTRAINT `my_arbitrarily_named_constraint_ibfk_1` FOREIGN KEY (`door`) REFERENCES `door` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

Bug #69699 marked as duplicate of this one.

fixed in 5.6.12

Here's the workaround I ended up applying to our software so that it can be installed on a server running 5.5.32 without incident. The gist is that I forced all foreign keys to use the standard naming convention (e.g. "<table name>_ibfk_<index>"). When you do this, 5.5.32 works just like previous versions.

It's not completely obvious from my example above, but some of the odd key naming was a result of earlier scripts doing wonky things due to an issue similar to #69693.

In the end my solution was to:
* Modify all historical scripts to use standardized foreign key names
* Add DB tasks that drop/re-add any non-standard foreign keys

Queries to the following tables let us identify the state of things, but I didn't figure out how to query these tables and then apply the fixes using pure sql, so I ended up using jdbc to solve the problem, which is fine for our system.
* INFORMATION_SCHEMA.TABLE_CONSTRAINTS
* INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Added this bug number to the existing changelog entry for Bug #17076737. 

Thank you for the bug report.

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.