MySQL Bugs: #18942: DROP DATABASE does not drop an orphan FOREIGN KEY constraint

Bug #18942	DROP DATABASE does not drop an orphan FOREIGN KEY constraint
Submitted:	10 Apr 2006 13:03	Modified:	19 Jun 2010 18:03
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.14, 5.0, 5.1, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fixed in pre-v6, v6

Description:
In this Forum posting, a user has somehow managed to create an orphan foreign key constraint:

http://forums.mysql.com/read.php?22,81537

There are two bugs here:

1) how did InnoDB manage to create an orphan constraint;

2) InnoDB should drop the orphan constraint in DROP DATABASE.

How to repeat:
Do not know.

Suggested fix:
Write an InnoDB stored procedure that drops constraints with name:

databasename/...

in row_drop_database_for_mysql() in row0mysql.c

This forum posting may be assoaciated with this bug:

http://forums.mysql.com/read.php?22,95361

I have experienced this same problem with MySQL 5.0.22. I was using MySQL Administrator to edit some tables and ended up with an orphan foreign key that doesn't go away when I drop and recreate the database.

I was using only InnoDB tables as far as I know but shortly before I discovered the orphan relationship I did rename the table in question to something else and then changed its name back to the original name.

There exists now a patch by Vasil. It makes DROP DATABASE to drop also orphan constraints. The mechanism how some users have been able to create orphan constraints is still a mystery.

As a corollary to this, I can't get the ALTER TABLE ... DROP CONSTRAINT to work.  Also, I haven't found anywhere in the documentation how to query a list of constraints.

I have code that can replicate this, but it is for my training materials and I cannot not put it up because of the disclaimer below that says all postings become the property of MySQL Inc.

I can be contacted by email for further information.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41009

ChangeSet@1.2649, 2008-01-14 22:55:50-07:00, tsmith@ramayana.hindu.god +7 -0
  Apply innodb-5.1-ss2146 and innodb-5.1-ss2178 snapshots.
  
  Fixes:
  
  Bug #18942: DROP DATABASE does not drop an orphan FOREIGN KEY constraint
    Fix Bug#18942 by dropping all foreign key constraints at the end of
    DROP DATABASE. Usually, by then, there are no foreign constraints
    left because all of them are dropped when the relevant tables are
    dropped. This code is to ensure that any orphaned FKs are wiped too.
  
  Bug #29157: UPDATE, changed rows incorrect
    Return HA_ERR_RECORD_IS_THE_SAME from ha_innobase::update_row() if no
    columns were updated.
  
  Bug #32440: InnoDB free space info does not appear in SHOW TABLE STATUS or I_S
    Put information about the free space in a tablespace in
    INFORMATION_SCHEMA.TABLES.DATA_FREE. This information was previously
    available in INFORMATION_SCHEMA.TABLES.TABLE_COMMENT, but MySQL has
    removed it from there recently.
    The stored value is in kilobytes.
    This can be considered as a permanent workaround to
    http://bugs.mysql.com/32440. "Workaround" becasue that bug is about the
    data missing from TABLE_COMMENT and this is actually not solved.

Patch queued to 5.1-build.  NOT yet in 6.0 (5.1-snapshot is null merged into 6.0).  Please return to "Patch approved" after documenting until 6.0 snapshot is available.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

DROP DATABASE did not drop orphaned FOREIGN KEY constraints.

Correction: Not yet fixed in 6.0.5.

Resetting to Patch Pending waiting for push of fix to 6.0.x.

>The mechanism how some users have been able to create orphan constraints is still a mystery.

It seems that create a FK defined with a wrong reference table name (casse error) create this kind of orphan constraints.

I had the problem:

ex:
ALTER TABLE `table_1` ADD CONSTRAINT `FK123` FOREIGN KEY ( `column_1` ) REFERENCES `tableName` ( `column_2` );
If tablename is used instead tableName, this give you a message "can't create table". Then if you correct the sql statement, the FK is created. Drop the all database and try to create it again and you have the error message describe is this issue.

Is there any workaround to this problem (other then waiting a release). I have the problem on a 5.0.32-Debian_7etch1-log version. All my MySQL installation (in fact, all my InnoDB database) are corrupted and I'm not able to create any other tables.

Thanks

Tim, have you now pushed this to 6.0.x?
Regards, Heikki

I retract the question: we have not shipped any InnoDB-6.0 snapshot yet!
--Heikki

Please forget also my comment (27th feb).
My problem was due to a specific bug http://bugs.mysql.com/bug.php?id=25596

Fix also noted for 5.1.23-ndb-6.3.11.

Merged into 6.0.6-alpha, according to Tim.

Noted in 6.0.6 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)