Home » Databases, Debian, Featured, Linux, Mysql, Recovery

Innodb table crash

6 May 2010 6 Comments

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?


6 Comments »

  • Sarah said:

    Thanks for the information! I can always count on you to help with my questions… since I usually have no idea what I am doing! Keep the information coming! 🙂

  • Janis said:

    Thanks for your experience. For me, the removal of ibd file worked. My scenario was an InnoDB table for which drop was requested, but the command was aborted unfinished.

  • Christina said:

    Thanks! I was getting “ERROR 2006 (HY000): MySQL server has gone away” when I tried “show tables” on a particular database and “ERROR 2013 (HY000): Lost connection to MySQL server during query” when I tried to drop the database or do a mysql_upgrade. Removing the files fixed it.

  • Julien said:

    You just saved my day !

    It fixed my problem, I’ll add my error logs, so people who search for it can find the solution here :

    InnoDB: Assertion failure in thread *** in file dict0dict.c line 2783
    InnoDB: Failing assertion: for_table || ref_table

    (Using MAMP on Mac OS)

  • Phongveth said:

    Great , Thanks and save my day

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.