Documentation Home
MySQL Backup and Recovery
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.5Mb
PDF (A4) - 0.5Mb
EPUB - 177.1Kb
HTML Download (TGZ) - 116.9Kb
HTML Download (Zip) - 123.9Kb

1.6.3 How to Repair MyISAM Tables

The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD).

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See CHECK TABLE Syntax, and REPAIR TABLE Syntax.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

  • tbl_name.frm is locked against change

  • Can't find file tbl_name.MYI (Errcode: nnn)

  • Unexpected end of file

  • Record file is crashed

  • Got error nnn from table handler

To get more information about the error, run perror nnn, where nnn is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table:

shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

Error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:


If you do not know the current table option values, use SHOW CREATE TABLE.

For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

This section is for the cases where a table check fails (such as those described in Section 1.6.2, “How to Check MyISAM Tables for Errors”), or you want to use the extended features that myisamchk provides.

The myisamchk options used for table maintenance with are described in myisamchk — MyISAM Table-Maintenance Utility. myisamchk also has variables that you can set to control memory allocation that may improve performance. See myisamchk Memory Usage.

If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still available for a while after mysqladmin returns, until all statement-processing has stopped and all index changes have been flushed to disk.

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as checked.

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try myisamchk -r -q tbl_name (-r -q means quick recovery mode). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.

  2. Use myisamchk -r tbl_name (-r means recovery mode). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.

  3. If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).


If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

  1. Move the data file to a safe place.

  2. Use the table description file to create new (empty) data and index files:

    shell> mysql db_name
    mysql> SET autocommit=1;
    mysql> TRUNCATE TABLE tbl_name;
    mysql> quit
  3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)


If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.

Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See REPAIR TABLE Syntax.

Stage 4: Very difficult repair

You should reach this stage only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r.

  2. If you do not have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, and then move the .frm description and .MYI index files from the other database to your crashed database. This gives you new description and index files, but leaves the .MYD data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

Download this Excerpt
PDF (US Ltr) - 0.5Mb
PDF (A4) - 0.5Mb
EPUB - 177.1Kb
HTML Download (TGZ) - 116.9Kb
HTML Download (Zip) - 123.9Kb
User Comments
  Posted by Adrian Singer on January 15, 2010
  Posted by Rick Umali on February 10, 2011
I landed on this webpage because of Error 145 ("Table was marked as crashed and should be repaired"). I was able to 'fix' it by just using "REPAIR TABLE" ( According to that doc, REPAIR TABLE is the equivalent of "myisamchk --recover (-r)".
  Posted by addision philip on August 8, 2011
In some circumstances, REPAIR Table & myisamchk – repair with r option are not able to fix corruption in tables. In that situation you can get help from any third party mysql repair utility that meets your requirement and budget.
Some top mysql repair software are:
Stellar Phoenix Database Recovery for MySQL
Recovery for MySQL
Note: First try free download demo version of any software before buy.
  Posted by Libin Babu on December 6, 2011
Hey. Check this link. It will explain how to solve #145 mysql error with necessary screenshots.
  Posted by John Park on September 12, 2012
MySQL automatically tries & checks to repair MyISAM tables if they are marked as 'Record-file is crashed',‘crashed’ 'No more room in index file' or ‘not closed properly’. MyISAM table damage and MySQL generates a lot of errors such as ‘Warning: Multiple instances of MySQL’, ‘Warning: Checking table….'‘Warning: Repairing table…’and needs to be repaired. For more info
  Posted by Jens Rantil on April 17, 2015
I also wanted to say I came here because I was getting "ERROR 145 (HY000): Table './mysql/proc' is marked as crashed and should be repaired" when executing a query with a subselect.

`SELECT * FROM mysql.proc` still worked for me, so I assume it was writing stuff to it that was an issue. I issues `USE mysql;REPAIR TABLE proc;`. The execution took 0.01 sec and my issue went away. I was happy this could be resolved without offline intervention.
Sign Up Login You must be logged in to post a comment.