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

Question

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)?

check if you have a backup folder exist at your mysql location it might have auto backup — Divya, Mar 31 '14 at 06:32
This URL might be helpful: http://www.thegeekstuff.com/2011/12/mysqlcheck/ — ursitesion, Mar 31 '14 at 06:35
@A5l-lE5 There is a backup folder, but no files for my database. — Musa Haidari, Mar 31 '14 at 06:38
@ursitesion As mentioned in the question, I have already tested them with failure — Musa Haidari, Mar 31 '14 at 06:40
http://geek.co.il/2013/04/27/recovering-innodb-tables-in-mysql-5 have you chekd for hidden files too? — Divya, Mar 31 '14 at 06:44
@A5l-lE5 my MySQL installation does not have frm and ibd back files for my database, as suggested in this article to use it. — Musa Haidari, Mar 31 '14 at 06:59
@A5l-lE5 last night I was develpoing the database, so I run lots of update/delete/alter/drop/create commands. — Musa Haidari, Mar 31 '14 at 07:26
its not possible to recover now.if you havn't ran update/delete queires it was possible to trace the create and insert queries and run them again from binary logs but not now.:( hard luck — Divya, Mar 31 '14 at 07:28

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

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.

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

1 Answers1