MySQL Bugs: #5784: Unable to drop table, error 1051

Bug #5784	Unable to drop table, error 1051
Submitted:	28 Sep 2004 11:49	Modified:	12 Oct 2004 15:50
Reporter:	Egor Egorov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.21	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
In certain circumstances a DROP TABLE produces that error: 

#1051 - Unknown table 'produktkategorie_typ'

User thinks it's because of the constraint, but then the error message is missldeadi
ng.

So basically he would need to disable the foreign_key_checks every time he drop a
table which has constraints..

More in "how to repeat". 

How to repeat:
Here is a small example:

set foreign_key_checks=0;

CREATE TABLE `produkt_kategorie` (
  `pk_id` int(10) unsigned NOT NULL auto_increment,
  `pk_pt_id_typ` int(10) unsigned NOT NULL default '0',
  `pk_kategorie` varchar(255) default NULL,
  `pk_sortid` int(10) unsigned default NULL,
  PRIMARY KEY  (`pk_id`),
  KEY `produk_kategorie_FKIndex1` (`pk_pt_id_typ`),
  CONSTRAINT `produkt_kategorie_ibfk_1` FOREIGN KEY (`pk_pt_id_typ`) REFERENCES
`produktkategorie_typ` (`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) TYPE=InnoDB;

CREATE TABLE `produktkategorie_typ` (
  `pt_id` int(10) unsigned NOT NULL auto_increment,
  `pt_name` varchar(255) default NULL,
  `pt_sortid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`pt_id`)
) TYPE=InnoDB;

set foreign_key_checks=1;

When I now try to drop the table produktkategorie_typ I get the error:

#1051 - Unknown table 'produktkategorie_typ'

Tested against latest BK source on Linux and Windows.

The innodb table handler converts the innobase error code DB_CANNOT_DROP_CONSTRAINT to the MySQL error code HA_ERR_CANNOT_ADD_FOREIGN. This code is not handled in the DROP code and mapped to ER_BAD_TABLE_ERROR.

Changing the handler conversion to HA_ERR_ROW_IS_REFERENCED makes the DROP code noticing the constraint. But the eror message is now: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails. This is better, but may still be confusing. I need feedback on this from the bug reporter.

In my personal opinion, if this error gives gives a clue on what to look at (and it does!) - it is okay. 

Here it is clear that the problem is in constraints. The error messages directs user to look at them.

Fixed and pushed in 4.0.22 by Heikki Tuuri.