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

[29 Aug 2005 17:33] Heikki Tuuri
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.
[29 Aug 2005 22:56] Timothy Smith
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.
[31 Aug 2005 11:00] Bugs System
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
[31 Aug 2005 11:18] Heikki Tuuri
Fixed in 4.1.15 and 5.0.13.
[1 Sep 2005 1:50] Paul DuBois
Noted in 4.1.15, 5.0.13 changelogs.