Bug #50384 InnoDB should check whether a foreign key exists before attempting to drop it
Submitted: 15 Jan 2010 21:59 Modified: 18 Jan 2010 17:21
Reporter: Ben Krug Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.90-bzr, 5.1.43-bzr OS:Any
Assigned to: CPU Architecture:Any

[15 Jan 2010 21:59] Ben Krug
InnoDB, in 5.0, will attempt to drop a non-existent foreign key rather than checking for existence first.  On large tables, this can lead to the table being unavailable for a long time, as it is copied over, etc.
In 5.1, this is not so (probably due to fast alter and the plugin.

How to repeat:
Create a large InnoDB table, try to drop a non-existent foreign key.

Suggested fix:
check for existence first
[16 Jan 2010 15:27] Valeriy Kravchuk
Verified just as described with both recent 5.0.90 and 5.1.43 from bzr. For any large enough table server spends many seconds (if not minutes or hours) making temporary copy of the table before returning error message like:

mysql> alter table ti drop foreign key fk1;
ERROR 1025 (HY000): Error on rename of './test/ti' to './test/#sql2-1822f-1' (errno: 152)

if you try to drop non-existing foreign key.
[18 Jan 2010 12:27] MySQL Verification Team
i had a similar request in bug #46281
[18 Jan 2010 17:06] Mikhail Izioumtchenko
this is better done in MySQL, not SE
[18 Jan 2010 17:21] Valeriy Kravchuk
So, this is a duplicate of bug #46281.
[26 May 2011 17:31] Valeriy Kravchuk
Bug #61310 looks like a duplicate of this one (but for 5.5.x).