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.

Answer 3

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

Answer 4

Simple solution that worked for me.

1. Try to delete the table.

   drop table tableOne;

You will see error:

ERROR 1051 (42S02): Unknown table 'tableOne'

2. Copy the create statement of that table from another Database or write it.

   CREATE TABLE tableOne ( ID int(11) NOT NULL, LOCKED tinyint(1) NOT NULL) ENGINE=InnoDB;

Successful

Query OK, 0 rows affected (0.03 sec)

1. Drop the table

   drop table tableOne;  
   

Successful

    Query OK, 0 rows affected (0.01 sec)