MySQL – How to Repair or Drop/Create a Corrupted Table

innodbMySQLrecovery

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.

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

Best Answer

This situation sounds a lot like a post I answered two years ago (InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage))

First, here is the InnoDB Architecture

InnoDB Architecture

In that post, I basically point out that the metadata of the table is still present inside the system tablespace file ibdata1.

When you ran SHOW TABLES;, all mysqld did was report the .frm files. It is possible to create the table as MyISAM because it has no attachments to ibdata1 or any other metadata. Yet, you cannot convert it to InnoDB because the metadata somehow imagines that the .frm and some tablespace_id is attached to the table name. Based on my old post, I would just mysqldump all the other tables and call you table a lost cause. However, Percona has the Data Recovery Toolkit for just such an occasion.

In the webpage about the Data Recovery Toolkit says

The Percona Data Recovery Tool for InnoDB is a free toolkit for recovering lost or corrupted data from InnoDB tables. It provides a way to extract InnoDB's data from files or raw devices in a tab-separated format when standard InnoDB recovery cannot be used for some reason such as a dropped table or severe corruption that prevents InnoDB from recognizing the data. It can be used for MySQL recovery to retrieve deleted tables, recover previous versions of updated rows, or fix InnoDB file corruption after a hardware failure such as a memory fault or bad cabling.

Percona has used these tools to perform MySQL data recovery for many customers, including extremely large datasets. Although the tools are powerful, performing InnoDB data recovery correctly requires expert knowledge of the InnoDB storage format and source code plus experience, and intuition. Percona can help with our data recovery service.

I have not used it, but if Percona made it, you should give it a try.

If you have a backup of that lost table (in the form of a mysqldump), you should carry out the steps from my old post and then load the backup of the lost table.

SUGGESTION

Once you get this situation squared away, you should tune InnoDB to flush changes to disk.

[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

That way, when you shutdown mysql, InnoDB can more thoroughly flush changes.

If you want even better assurance of InnoDB being flushed to disk, you should use MySQL in Linux.

Related Solutions

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk

MySQL repair deleted data – Any chance of recovery

myisamchck did its best job, so that may be the maximum what you can get out of mytable.MYD.

The structure of MyISAM record is pretty simple, integers are stored in packed format, strings are prefixed with their length(1 or 2 bytes). So you can write a C program with pre-defined pattern that would try to fetch records of out the MYD file. This approached worked for me on wp_comments table.

Two major problems here:

A record may be fragmented. Recollecting the fragmented record in a corrupt MYD seems to be extremely difficult if not impossible problem.
Table structure should allow to identify a good record in a stream of bytes.

P.S.

InnoDB is recommended storage engine for a reason.