MySQL Bugs: #13431: Duplicate FOREIGN KEY error does not make sense

Bug #13431	Duplicate FOREIGN KEY error does not make sense
Submitted:	23 Sep 2005 15:21	Modified:	16 Jan 2014 2:56
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t1;

CREATE TABLE test.t1 (c1 INT PRIMARY KEY, c2 INT)ENGINE=INNODB;
CREATE TABLE test.t2 (c1 INT, c2 INT)ENGINE=INNODB;

ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1);
ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1);

DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t1;
-------------------------------------------- results -----------------------------------------------------------
mysqltest: At line 20: query 'ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1)' failed: 1005: Can't create table './test/#sql-2eb8_1.frm' (errno: 121)
-----------------------------------------------------------------------------------------------------------------------------
But this error message does not make sense for this. Should say that FOREIGN KEY already exists. 

How to repeat:
See above

Suggested fix:
Should say that FOREIGN KEY already exists.

Notice that the correct error code 121 is displayed and verified
with perror, however how is presented the description error
has confused some users. So left Heikki to decide  if better
descriptive error can be used here.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS test.t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS test.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE test.t1 (c1 INT PRIMARY KEY, c2 INT)ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE test.t2 (c1 INT, c2 INT)ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES
    -> test.t1(c1);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES
    -> test.t1(c1);
ERROR 1005 (HY000): Can't create table './test/#sql-38c3_3.frm' (errno: 121)
mysql> 
mysql> DROP TABLE IF EXISTS test.t2;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS test.t1;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/perror 121
OS error code 121:  Remote I/O error
MySQL error code 121: Duplicate key on write or update
miguel@hegel:~/dbs/5.0>

Hi!

121 is the handler error code for a duplicate key, I think. You have a duplicate constraint name.

---

About the other topic: blocking the creation of identical foreign key constraints at least would have the benefit that users would not be confused by them. The performance impact is minimal.

Assigning this low-priority bug report to Osku. Osku has been improving error messages when he fixed http://bugs.mysql.com/bug.php?id=3443

Regards,

Heikki

> You have a duplicate constraint name.

I know that I do, I did it on purpose. That is why I said the error message needed to be fixed.

Thanks
JBM

This isn't so easy to fix as it looks, and very low-priority, so unassigning myself.

The error message has been updated to:

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2452_1'

Which conveys enough information.