MySQL Bugs: #62894: InnoDB Data Dictionary is out of sync (FK constraint for deleted child table)

Bug #62894	InnoDB Data Dictionary is out of sync (FK constraint for deleted child table)
Submitted:	23 Oct 2011 15:27	Modified:	6 Jun 2013 12:12
Reporter:	Анатолий Кирсанов	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.17	OS:	Windows (Vista SP2 Home Basic Russian)
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
I had two tables (parent and child) with foreign key constraint. 

Now child table is absent in database directory (there is no frm file) and in internal InnoDB data dictionary (I verified it using InnoDB table monitor - http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html#innodb-table-monitor). 

But a foreign key constraint was somehow preserved in internal dictionary. So I can't delete parent table (because foreign key constraint is still active), and can't re-create child table (because InnoDB is sure that this table still exists). 

I have standard installation with one data file for all tables.

How to repeat:
I am not sure how child table was dropped. I used synchronization with database in MySQL Workbench. There was some index for child table that was deleted during synchronization. I can't reproduce exact steps. So I have to write about the fact - child table is absent, parent table and foreign key constrain are still in database.

Suggested fix:
1.) I already done steps mentioned in 'Problem with CREATE TABLE' paragraph of 'Troubleshooting InnoDB Data Dictionary Operations' article. Nothing helps.

2.) I can delete parent table using 'set foreign_key_checks=0;'. But I cant create child table all the same.

3.) I happily have backups for all databases on my server. So, I can reinstall MySQL and import all databases. It should resolve the problem. But this is bad variant. It would be hard process to repeat every time when this error happens.

4.) I hope that there is some steps like in 'Troubleshooting InnoDB Data Dictionary Operations' (http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html) for my situation.

I recovered scenario resulted in child table deletion:

1.) My child table

CREATE TABLE `mydb`.`child` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PARENT_ID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ID`,`PARENT_ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `FK_Parent` (`PARENT_ID`),
  CONSTRAINT `FK_Parent` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

2.) Command executed during synchronization
ALTER TABLE `mydb`.`child` DROP INDEX `FK_Parent`;

After this command table disappeared too. 

I have another table with the same structure. But all is ok with it. So the exact reason is unknown.

I found a workaround:

Just replace column positions in primary key. Then foreign key index does not created and does not deleted during next synchronization.

CREATE TABLE `mydb`.`child` (
  `PARENT_ID` int(11) unsigned NOT NULL,
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`PARENT_ID`, `ID`),
  UNIQUE KEY `ID` (`ID`),
  CONSTRAINT `FK_Parent` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Please, send the entire error log (usually it is <hostname>.err file in the datadir) and the results of:

show engine innodb status\G

from your MySQL server.

InnoDB status

Attachment: innodbstatus.txt (text/plain), 3.65 KiB.

This looks very similar to bug #62100. Need to check when bug #62100 is fixed.

Please check with latest version. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".