Delete a Crashed Innodb table

Question

I cannot delete/drop a crashed Innodb table. I get the following error:

ERROR 1051 (42S02): Unknown table ‘accounts’

And if I want to create it I get the following error:

ERROR 1005 (HY000): Can’t create table ‘accounts’ (errno: -1)

This happens on my server after an accidental power failure.

Regards

Answer 1

I also found this problem here http://www.randombugs.com/linux/crash-innodb-table.html and it seems just deleting ibdata file and restarting mysql can solve this. Anyway this is not truly a solution if you don't have any backup.

Answer 2

Does turning off foreign key constraints prior to dropping the table help?

set foreign_key_checks=0;
drop table <table>;
set foreign_key_checks=1;

There is a bug report which details something similar, but I'm not clear if it is the same issue:

http://bugs.mysql.com/bug.php?id=5784

If not, you could try mysqlcheck:

mysqlcheck -u root -p <dbname> --auto-repair --check --optimize --databases

You'll have to check the docs for the most appropriate options for your database. Be sure to note the comments in the first paragraph of the docs about the locks that are placed on tables while this command is running.