Bug #60633 Error too generic when using a fk name which exists
Submitted: 25 Mar 2011 10:31 Modified: 14 Apr 2011 9:38
Reporter: Cyril SCETBON Email Updates:
Status: Verified 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