Bug #60633 Error too generic when using a fk name which exists
Submitted: 25 Mar 2011 10:31 Modified: 4 Sep 18:06
Reporter: Cyril SCETBON Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.9 OS:Linux (ubuntu lucid x86)
Assigned to: CPU Architecture:Any
Tags: fk, innodb
Triage: Needs Triage: D3 (Medium)

[25 Mar 2011 10:31] Cyril SCETBON
Description:
When I try to create a table a fk constraint name which already exist in the database I get the following error :

ERROR 1005 (HY000): Can't create table 'db1.t1' (errno: 121)

The error message should be less generic as we can spend a lot of time (as I did) searching for the reason which can be just provided like :

ERROR XXX (HY000): Can't create table 'db1.t1' with a fk constraint name which already exist in the database
 (errno: YYY)

How to repeat:
mysql> create table t1(id int unsigned auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t2(id int unsigned not null primary key,CONSTRAINT `fk_t1_id` FOREIGN KEY (id) REFERENCES t1 (id) ON DELETE CASCADE ON UPDATE CASCADE) engine=innodb;Query OK, 0 rows affected (0.02 sec)

mysql> create table t3(id int unsigned not null primary key,CONSTRAINT `fk_t1_id` FOREIGN KEY (id) REFERENCES t1 (id) ON DELETE CASCADE ON UPDATE CASCADE) engine=innodb;
ERROR 1005 (HY000): Can't create table 'db1.t3' (errno: 121)

Suggested fix:
change the error message
[25 Mar 2011 12:15] Sveta Smirnova
Thank you for the report.

Verified as described. Can be considered as feature request though.
[14 Apr 2011 9:38] Cyril SCETBON
I don't really think it can be considered as a feature request. It should be part of the error handling code
[4 Sep 11:20] Dmitry Lenev
Posted by developer:
 
Hello!

Situation with error code and message reported when one tries to create
foreign key with an existing name has been somewhat improved in 5.6 and 5.7
versions of server.

Depending on situation errors like:
1823: Failed to add the foreign key constraint 'test/c' to system tables
1022: Can't write; duplicate key in table 't3'
1826: Duplicate foreign key constraint name 'test/c'
are reported in them.

In MySQL 8.0 we store information about foreign keys in SQL-layer
data-dictionary and do much of foreign key handling in DDL statements
on SQL-layer as well.

So we now consistently use:
1826 : Duplicate foreign key constraint name 'c'
error message in such situations.

Taking into account the above I am closing these request as fulfilled
in 8.0.3. Moving it to Documenting state to let Documentation Team
to decide if anything needs to be updated in our manual.
[4 Sep 18:06] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.3 release, and here's the changelog entry:

The error messages reported when attempting to use an existing foreign
key constraint name were inconsistent and did not always provide
sufficient information.