3

I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns:

> +-----------------+-------+----------+----------------------------------- |

> clldata.mytable | check | warning  | Table is marked as crashed  

> clldata.mytable | check | warning  | Size of datafile is: 4654864 Should be: 4602520

> clldata.mytable | check | error    | Wrong bytesec: 0-0-0 at linkstart: 0

> clldata.mytable |
> check | error    | Corrupt                                            
> |
> +-----------------+-------+----------+-------------------------------------------------------+

4 rows in set (0.01 sec)

Running REPAIR TABLE mytable results in truncating about 90% of the rows that were originally in the table (and the size of the .MYD file also shrinks about 90%). And most of the remaining rows in mytable are clearly actually from one of the other corrupted tables.

Is there any hope of recovering data from such a table?

2

MY CONDOLENCES

MyISAM only caches index pages in the MyISAM Key Buffer. MyISAM never caches data. Notice how the messages from CHECK TABLE is only mentioning the datafile (that's the .MYD file) .

Since MyISAM data is fully reliant on the OS, rows of data are cached only in the OS. The MyISAM tables that are corrupt had open file handles when the crash happened (doesn't matter if mysqld crashed or the OS that crashed)

Recovery of the data is not possible.

FUTURE CONSIDERATIONS

Switching to InnoDB

In the future, please use InnoDB for all tables. InnoDB has a higher tolerance for crashes because it has transaction logs and double write buffers to recover data that was committed but not yet written as well as rollback segments and undo logs for rolling back uncommitted transactions.

Still Want to Use MyISAM

  • You should backups of the MyISAMs regularly
  • You should keep binary logs enabled, even if you don't use Replication. That way, you could use backups of the MyISAM tables and use the binary logs to replay INSERTs, UPDATEs, and DELETEs up to the point before the crash.
  • You should setup MySQL Repication so that the Slaves have good copies of the same tables

Give it a try!!!

  • 1
    Would a journalling file system have helped? – Vérace Jun 13 '14 at 8:30
  • @Vérace a journaling file system can only help InnoDB (with the Double Write Buffer disabled). It might help MyISAM, but it would call for run FLUSH TABLES mydb.mytable; after every INSERT, UPDATE, and DELETE command. Given MyISAM's full table lock nature, this would induce extreme performance degradation when doing writes to the MyISAM table in a heavy-read, heavy-write DB environment. (NOTE : Doing FLUSH TABLES on an individual table is new to MySQL 5.6). The critical point for MyISAM is the data being cache in RAM and being at the mercy of the Linux's memory management. – RolandoMySQLDBA Jun 13 '14 at 11:33
  • This is helpful, if not what I wanted to hear! Are there any reasons to want to use MyISAM instead of InnoDB? – hartshoj Jun 13 '14 at 13:50
  • 1
    @user3291354 I have many posts on when you chose one Storage Engine over the other : dba.stackexchange.com/… – RolandoMySQLDBA Jun 13 '14 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.