Delete a Crashed Innodb table

Question

Hi,

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

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.

Answer 2

My guess is that InnoDB isn't even loaded (check SHOW ENGINES), so you're not going to be able to DROP it until you fix that problem first.

Usually you can start InnoDB up in recovery mode 3, drop whatever you need, and then shutdown and remove the recovery mode setting:

http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html