Skip navigation links
13.1.4.2 Problems from Tables Not Being Closed Properly »
Section Navigation      [Toggle]

13.1.4.1. Corrupted MyISAM Tables

Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if any of the following events occur:

  • The mysqld process is killed in the middle of a write.

  • An unexpected computer shutdown occurs (for example, the computer is turned off).

  • Hardware failures.

  • You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time.

  • A software bug in the MySQL or MyISAM code.

Typical symptoms of a corrupt table are:

  • You get the following error while selecting data from the table: 

    Incorrect key file for table: '...'. Try to repair it

  • Queries don't find rows in the table or return incomplete results.

You can check the health of a MyISAM table using the CHECK TABLE statement, and repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command. See Section 12.4.2.3, “CHECK TABLE Syntax”, Section 12.4.2.6, “REPAIR TABLE Syntax”, and Section 4.6.2, “myisamchk — MyISAM Table-Maintenance Utility”.

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation. This is a bug. You should try to create a reproducible test case that demonstrates the problem. See Section A.5.4.2, “What to Do If MySQL Keeps Crashing”, and MySQL Internals: Porting.

MySQL Enterprise.  Find out about problems before they occur. Subscribe to the MySQL Enterprise Monitor for expert advice about the state of your servers. For more information, see http://www.mysql.com/products/enterprise/advisors.html.


User Comments

Posted by daniel gaddis on March 29 2004 6:02am[Delete] [Edit]

if you set
[mysqld]
ft_min_word_len=3

you should also set
[myisamchk]
ft_min_word_len=3

if you use myisamchk

Posted by Harel Malka on October 13 2004 9:41pm[Delete] [Edit]

I ran into a case where a large myiasm table threw the above error when it was included inside a transaction (by mistake) along with other innoDB tables. A bunch of mass deletes were taking place, and the operation always ended with the table corrupted.
A quick conversion to innoDB solved the problem:

ALTER TABLE tablename TYPE=InnoDB;

Posted by Cherry Austin on July 13 2005 3:44pm[Delete] [Edit]

Thanks for that! I had a tiny MyISAM table but, all the same, queries were sometimes taking too long to execute & the container page wouldn't load properly. Changed to INNO-DB, problem cleared!

Add your own comment.