MySQL Bugs: #21119: Parser seems to push foreign key modifications in the wrong order to InnoDB.

Bug #21119	Parser seems to push foreign key modifications in the wrong order to InnoDB.
Submitted:	18 Jul 2006 17:44	Modified:	12 Sep 2006 12:19
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	Latest official	OS:	Windows (Windows)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any
Tags:	Object Editors

Description:
Dropping and recreating a foreign key in the same statement will under some conditions fail if you do it in the same ALTER TABLE statement - saying that the foreign key that you drop already exists if you recreate it.

How to repeat:
flupps@localhost:test> CREATE TABLE t1 ( a INT UNIQUE, b INT, FOREIGN KEY (b) REFERENCES t1 (a)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

flupps@localhost:test> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) default NULL,
  `b` int(11) default NULL,
  UNIQUE KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

flupps@localhost:test> ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1, ADD CONSTRAINT t1_ibfk_1 FOREIGN KEY (a) REFERENCES t1 (b) ON UPDATE CASCADE;
ERROR 1025 (HY000): Error on rename of './test/t1' to './test/#sql2-ca-251' (errno: 152)

flupps@localhost:test> show innodb status \G

(snip)

------------------------
LATEST FOREIGN KEY ERROR
------------------------
060718 10:26:29 Error in foreign key constraint creation for table `test/#sql-ca_2a8`.
A foreign key constraint of name `test/t1_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Note that this works:

flupps@localhost:test> ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1, ADD FOREIGN KEY t1_ibfk_1 (a) REFERENCES t1 (b) ON DELETE CASCADE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verified on Linux using last 5.0 and 5.1 BK sources as described with one exception - different error message:
mysql> ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1, ADD CONSTRAINT
    -> t1_ibfk_1 FOREIGN KEY (a) REFERENCES t1 (b) ON UPDATE CASCADE;
ERROR 1005 (HY000): Can't create table './test/#sql-36a6_1.frm' (errno: 121)

Tobias,

actually this is not legal syntax for InnoDB. If you look at the InnoDB manual, this syntax of simultaneously dropping and creating foreign keys is not listed there.

InnoDB should give an error of the wrong syntax.

Regards,

Heikki

That's fine, Heikki.

I'm changing this now to a Query Browser bug, because if this is not valid syntax, the Query Browser should not try to execute queries with this syntax.

It's easy to reproduce, just create a FK on a table, then modify it and add something like ON DELETE CASCADE and it will try this syntax.