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 17:21	Modified:	7 Nov 2005 15:31
Reporter:	Jonathan Miller
Status:	To be fixed later
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Heikki Tuuri	Target Version:

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.