MySQL Table Crash – How to Fix Crashed Table

MYISAM tables are very fragile and get damaged/crash every now and then. First advice is to move to Innodb engine it is simply performed by using alter table command.

Now, to fix crashed MYISAM tables the proper way is to bring the MYSQL down and once it is down and there is no traffic you can fix them using this command line statement:

myisamchk --silent --force --fast  /path/table-name.MYI

Trying to repair/optimise MYISAM tables on a live server usually does not work. From MYSQL CLI the command should be

mysql> repair table tablename;

instead of optimize table if the table was crashed.

You should get away from MyISAM as soon as possible. Why ???

• MyISAM caches only index pages in the Key Buffer
• InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool

I have mentioned this many times before

• May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
• Apr 08, 2013 : If MySQL isn't caching, then what is?
• Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
• Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
• Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?

This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed. A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:

• Switch to InnoDB
• Increase installed RAM
• Configure as much buffer pool as possible

Give it a Try !!!