CODEWITHSUNDEEP
mysqlcrashinnodbrecovery
Sacx
Sacx

Reputation: 6392

Delete a Crashed Innodb table

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

Upvotes: 5

Views: 19821

Answers (4)

biniam
biniam

Reputation: 8199

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'

  1. 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)

Upvotes: 1

Sacx
Sacx

Reputation: 6392

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.

Upvotes: 4

Morgan Tocker
Morgan Tocker

Reputation: 3438

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

Upvotes: 1

Mike
Mike

Reputation: 21659

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.

Upvotes: 3

Related Questions