How to repair, or drop/create a corrupted table in mysql?
database, mysql, recover, repair
Solution
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.
Problem
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)?