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
Triage: Triaged: D5 (Feature request)

[4 Oct 2005 12:53] Vladimir Kolesnikov
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
[4 Oct 2005 13:24] Valeriy Kravchuk
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.
[4 Oct 2005 13:46] Vladimir Kolesnikov
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.
[4 Oct 2005 13:50] Valeriy Kravchuk
You had t1 in your last statement, not t2... 

Let me check on the latest Linux -BK build also.
[4 Oct 2005 13:53] Vladimir Kolesnikov
Ah, yes - you're right - modifying t2
thanks
[4 Oct 2005 15:25] Valeriy Kravchuk
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)
[5 Oct 2005 17:12] Heikki Tuuri
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
[7 Oct 2005 7:48] Vladimir Kolesnikov
> 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?
[24 Oct 2005 16:41] Jon Lindbloom
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.)
[16 Nov 2010 0:06] Miguel Solorzano
See bug: http://bugs.mysql.com/bug.php?id=58215.