How to repair, or drop/create a corrupted table in mysql?

mysql database recover repair
Musa Haidari picture Musa Haidari · Mar 31, 2014 · Viewed 8k times · Source

Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP (CodeIgniter).

Problem: Last night as usual I stopped working on the project and shutdown the windows normally. Today in the morning I got error table MY_TALBE does not exists in the browser. show tables on console shows the table name, but desc MY_TABLE and select * from MY_TABLE says unknown table. I tried create table MY_TABLE(...) but I had error as table MY_TABLE already exists, so I tried drop table MY_TABLE but it said Unknown table MY_TABLE! I tried mysqlcheck.exe for repair and check, they failed both, but after that, show tables does not show the table name any longer. I need to mention I did not change/move/delete/rename any of mysql files. Also I can not drop the database: 

mysql> drop database MY_DB;
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

Question: Is it possible to recover/repair MY_TABLE, or at least how to drop and create it again (without dropping the database and re-crteating it again)?

Answer

Musa Haidari picture Musa Haidari · Apr 7, 2014

After asking the same question on dba.stackexchnage.com, I found my solution as follows:

The corrupted table is not repairable nor recoverable. The only way is to healthy backup the databases, and the healthy table of the database with corrupted table and then clean the infrastructure of MySQL from the database files, i.e. delete the files from disk, and then start fresh, i.e. restore all the databases and tables and also re-create the corrupted table. Also the linked article mentions that it is possible to restore some data of the corrupted table.

Also some tools like Percona is available to restore corrupted InnoDB tables, but it is not for windows.

Refer the above link for full details.

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More