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

[28 Sep 2004 11:49] Egor Egorov
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'
[2 Oct 2004 22:55] MySQL Verification Team
Tested against latest BK source on Linux and Windows.
[12 Oct 2004 15:18] Ingo Strüwing
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.
[12 Oct 2004 15:20] Egor Egorov
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.
[12 Oct 2004 15:50] Ingo Strüwing
Fixed and pushed in 4.0.22 by Heikki Tuuri.