MySQL Bugs: #13741: cannot re-add constraint

Bug #13741	cannot re-add constraint
Submitted:	4 Oct 2005 12:53	Modified:	13 May 2010 16:04
Reporter:	Vladimir Kolesnikov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.13-rc-nt, 5.0.15-rc BK	OS:	Linux (Linux, win xp sp2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I get the following error when try to re-add a constrains via alter table:
ERROR 1005 (HY000): Can't create table '.\test\#sql-42cc_194.frm' (errno: 121)

How to repeat:
(all table should be innodb, logged in as root)

1. create table t1 (id integer primary key);
2. create table t2 (id integer primary key);
3. alter table t2 add constraint c foreign key c (id) references t1 (id);
4. alter table t1 drop foreign key c, add constraint c foreign key c (id) references t1 (id);

here i get the error:
ERROR 1005 (HY000): Can't create table '.\test\#sql-42cc_194.frm' (errno: 121)

Suggested fix:
no idea

I hope, you mean the following sequence of actions (changed last statement)?

mysql> create table t1 (id integer primary key);
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 (id integer primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t2 add constraint c foreign key c (id) references t1 (id);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 drop foreign key c, add constraint c foreign key c (id)
    -> references t1 (id);
ERROR 1005 (HY000): Can't create table '.\test\#sql-160_1.frm' (errno: 121)
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)

Please, look at http://bugs.mysql.com/bug.php?id=10260 also - I seems to me this report is about the same problem, really. It is just put into the incorrect category. Please, confirm my findings.

I didn't really understood what was changed in the last statement, but anyway it's ok as long as you can reproduce the bug. 

I reproduced this bug from a cmd line. I think #10260 is a consequence of this one. Actually I was working on a similar one for MA (#13519) and found out that it's caused by the described behaviour of the server.

You had t1 in your last statement, not t2... 

Let me check on the latest Linux -BK build also.

Ah, yes - you're right - modifying t2
thanks

mysql> create table t1 (id integer primary key) engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> create table t2 (id integer primary key) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t2 add constraint c foreign key c (id) references t1 (id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 drop foreign key c, add constraint c foreign key c (id) re
ferences t1 (id);
ERROR 1005 (HY000): Can't create table './test/#sql-5f34_2.frm' (errno: 121)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0.00 sec)

Please note that the InnoDB section in the manual contains the description what kind of ALTER do InnoDB foreign key contraints support. The syntax for dropping and adding in the same ALTER is not supported.

I agree that such syntax is desirable in the future.

Regards,

Heikki

> The syntax for dropping and adding in the same ALTER is not supported.

It is possible to drop and add a constratint in the same ALTER, if you use different names for the contraints. Did you mean dropping and adding of the a constraint with the same name?

If the dropping and adding a foreign key is not supported in the same ALTER statement then the table editor in MySQL Query Browser shouldn't be attempting it.  I'm using MySQL Query Browser 1.1.17 (Windows) to change a foreign key constraint 'On Delete' property to 'Set Null' versus 'Restrict'.  Query Browser attempts the following command:

ALTER TABLE `lifebase21`.`contact` DROP FOREIGN KEY `0_222`,
 ADD CONSTRAINT `0_222` FOREIGN KEY `0_222` (`CLIENT_NAME`)
    REFERENCES `client` (`CLIENT_NAME`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

And results in the error:

MySQL Error Number 1005
Can't create table '.\lifebase21\#sql-b8_1b7frm' (errno: 121)

Since my complaint is more MySQL Query Browser related let me know if I should file a seperate bug.  (With better code and better instructions to reproduce the error.)

See bug: http://bugs.mysql.com/bug.php?id=58215.