MySQL Bugs: #60633: Error too generic when using a fk name which exists

Bug #60633	Error too generic when using a fk name which exists
Submitted:	25 Mar 2011 10:31	Modified:	4 Sep 2019 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

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

Thank you for the report.

Verified as described. Can be considered as feature request though.

I don't really think it can be considered as a feature request. It should be part of the error handling code

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.

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.