MySQL Bugs: #12852: If .ibd file is missing and a table is opened, it cannot be dropped

Bug #12852	If .ibd file is missing and a table is opened, it cannot be dropped
Submitted:	29 Aug 2005 17:33	Modified:	1 Sep 2005 1:50
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1, 5.0	OS:	Any (All)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
InnoDB normally allows one to drop a table if there is the .frm file but the .ibd file for the table is missing.

But if one first opens such table, for example, because the mysql client does not have the no-auto-rehash option in my.cnf, then InnoDB erroneously increments the open handle count to the table, and refuses to drop the table.

Workaround: put no-auto-rehash to the [mysql] section of my.cnf before you try DROP TABLE.

How to repeat:
See above.

Suggested fix:
Decrement the count in ::open() if we returned ib_table != NULL but we in the second 'if' return an error to MySQL.

Verified on recent 4.1 BK, with:

mysqld_safe --innodb-file-per-table &

mysql -e 'create table drop_me (id int unsigned not null primary key) engine=innodb' test

mysqladmin shutdown

mysqld_safe --innodb-file-per-table &

rm data/test/drop_me.ibd

mysql -e 'drop table drop_me; create table drop_me (id int unsigned not null primary key) engine=innodb'

The last create table fails with:

mysql> create table drop_me (id int unsigned not null primary key) engine=innodb;
ERROR 1005 (HY000): Can't create table './test/drop_me.frm' (errno: 121)

This shows that it was not properly dropped.

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/internals/29093

Fixed in 4.1.15 and 5.0.13.

Noted in 4.1.15, 5.0.13 changelogs.