Innodb table crash

I searched over internet, on several tens of posts, to find how to delete a crashed Innodb table. Short story: I tried to import a big table but it crashed in the middle of the process (power failure). The table was not very important so I didn’t care about the table, I just want to delete it and start from the scratch, but surprise! The table cannot be deleted! I use the latest MySQL Innodb plugin from Debian backports repository (is coming with Debian 5.1.xx) with file per table option enabled ( innodb_file_per_table=1 ). When I tried to drop the table, Mysql respond with

mysql> drop table testtable;
ERROR 1051 (42S02): Unknown table ‘testtable’

Until now everything was ok, but when I tried to create a table I get:

mysql> create table testtable (id int(11));
ERROR 1005 (HY000): Can’t create table ‘testdatabase.testtable’ (errno: -1)

Looking in logs i get the following error message:

May 5 14:23:44 docstorage mysqld: 100505 14:23:44 InnoDB: Error creating file ‘./testdatabase/testtable.ibd’.
May 5 14:23:44 docstorage mysqld: 100505 14:23:44 InnoDB: Operating system error number 17 in a file operation.
May 5 14:23:44 docstorage mysqld: InnoDB: Error number 17 means ‘File exists’.
May 5 14:23:44 docstorage mysqld: InnoDB: Some operating system error numbers are described at
May 5 14:23:44 docstorage mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
May 5 14:23:44 docstorage mysqld: InnoDB: The file already exists though the corresponding table did not
May 5 14:23:44 docstorage mysqld: InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
May 5 14:23:44 docstorage mysqld: InnoDB: .ibd files around without using the SQL commands
May 5 14:23:44 docstorage mysqld: InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
May 5 14:23:44 docstorage mysqld: InnoDB: mysqld crash in the middle of CREATE TABLE? You can
May 5 14:23:44 docstorage mysqld: InnoDB: resolve the problem by removing the file ‘./testdatabase/testtable.ibd’
May 5 14:23:44 docstorage mysqld: InnoDB: under the ‘datadir’ of MySQL.

When I see the error message I was happy … I just should remove the ibd file and everything should work, but after removing the ibd file I get:

mysql> create table borderou (id int(11));
ERROR 1050 (42S01): Table ‘`testdatabase`.`testtable`’ already exists
mysql> create table borderou (id int(11));
ERROR 1005 (HY000): Can’t create table ‘testdatabase.testtable’ (errno: -1)

And the ibd file appear again. I tried a lot of solutions found on internet (including innodb_force_recovery) without any success.

Until the end I don’t find a clear solution to this problem … but if you are on a testing environment you can just delete ibdata1 and ib_logfile0, ib_logfile1 and restart the server. This worked in my case.

Anyway here are several links which can be helpful for you:

Recovering a Schema From InnoDB .frm Files
*.frm files and reducing ibdata file
Recovering an InnoDB table from only an .ibd file.
Error 1005 with errno -1 on CREATE TEMPORARY TABLE
MySQL InnoDB tables corrupt — how to fix?