Delete table Crashed Innodb

Question

I cannot delete / delete a broken 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): Unable to create table accounts (errno: -1)

This happens on my server after an accidental power failure.

Hi

Answer 1

I also found this problem here http://www.randombugs.com/linux/crash-innodb-table.html and it seems that just deleting the ibdata file and restarting mysql can solve this problem. In any case, this is not really a solution if you do not have a backup.

Answer 2

Disabling external key constraints before resetting the lookup table?

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

There is an error report that details something similar, but I don’t understand if this is the same problem:

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

If not, you can try mysqlcheck :

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

You will need to check the documents for the most appropriate parameters for your database. Be sure to pay attention to the comments in the first paragraph of documents about locks that are placed in tables during the execution of this command.

Answer 3

I assume InnoDB is not even loaded (check SHOW ENGINES), so you won’t be able to DROP it until you fix this problem.

You can usually start InnoDB in recovery mode 3, drop everything you need, and then turn off and delete the recovery mode setting:

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

Answer 4

A simple solution that worked for me.

• Try deleting the table.

  dropOne table table

You will see an error message:

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

2. Copy the create statement from this 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) 

• Drop the table

   drop table tableOne; 

Successfully

  Query OK, 0 rows affected (0.01 sec)