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