How to repair MyISAM table by using myisamchk

MyISAM Tables (extensions .MYD and .MYI) are corrupted due to a number of reasons and some of these are:

• tbl_name.frm is locked and cannot be changed
• Unable to locate file tbl_name.MYI (Errcode: nnn)
• File ends unexpectedly
• Record file crashed
• Received error nnn from table handler

For error stating ‘nnn’, you can get more information by running the command perror nnn, where nnn describes the error number. A few perror numbers and their explanations are:

Above listed errors in Myisam Tables can be fixed or repaired by using myisamchk repair method, with the exception of Perror 135 and 136 that cannot be fixed with this method. Such errors are caused due to space issues (no more room in record/index file) and are resolved by command Alter Table space, which increases maximum and average row length.

For errors listed in the table, use myisamchk or a MySQL Repair software to detect and fix MySQL database issues. Myisamchk repairs the corrupt elements in myisam tables on the basis of level and type of corruption. The following repair methods are involved:

1. Easy safe repair
2. Difficult repair
3. Very difficult repair

To decide the type of repair process, you need to check myisamtables by using the following steps:

1. Run the command –  myisamchk *.MYI or myisamchk -e *.MYI Suppress unnecessary information by suffixing –s (silent) option.
2. If the server MySQLd is shut down, use –update-state option to mark the table as checked.

Myisamchk lists the tables and the error type for those tables. Proceed to repair only those MyISAM tables for which myisamchk announces an error. Start the repair process by using ‘Easy Safe Repair’. However, if myisamchk lists unexpected errors (out of memory errors) or it crashes, try to repair the Tables by using difficult repair or very difficult repair process. 

Note: Before starting the repair process, take a backup of MySQL database to safeguard against data loss in case repair process doesn’t work.

1. Easy Safe Repair

• Start with quick recovery mode or –r –q commands. Type the following command to repair the index file. It doesn’t make any changes to the data file.

 myisamchk -r -q tbl_name (-r -q means “quick recovery mode”)

• Verify database to check if the file contains everything and ‘delete links’ point to correct locations within the data file. If it works, easy repair process has fixed the Tables.
• Repeat the process to repair next Table.

You can also repair MyISAM table using an alternative method:

• Use myisamchk -r tbl_name (-r means “recovery mode”). This command helps remove incorrect rows and deleted rows from the data file and reconstructs the index file.
• If the above step fails, use safe recovery mode – myisamchk –safe-recover tbl_name.

Safe recovery mode is a slower method and uses an old recovery method to handle a few cases that regular recovery mode cannot.

Note: You can accelerate Myisam table repair operation by setting the values of sort_buffer_size and key_buffer_size variables each to 25% of available memory.  

2. Difficult Repair

Difficult Repair is required only if the first 16KB block in the index file is destroyed, contains incorrect information, or index file is missing. Then it is necessary to create a new index file with the help of following steps:

• Change the location of the data file and move it to a safe place.
• Use the table description file to create new data and index files:

shell> mysql db_name 

This new data is empty.

mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit

• Copy and not move the content of old data file back onto the newly created data file. (Retain a copy of old data in case something goes wrong).

Note: In case of file replication, stop the process prior to performing the above steps, as it includes file system operations that are not logged by MySQL.

For verification, use myisamchk -r -q and if it works, Myisam table is repaired. If not, repair by using the next method.

3. Very Difficult Repair

Very difficult repair is required method in case the .frm description file has crashed. Normally, this file doesn’t crash, as the file is not changed after the table is created.

The best option is to restore the description file from a backup. You can also restore the index file and go back to Easy Repair. In the latter case, you should start with myisamchk -r.

But what if the database backup is not available.

In the absence of backup file, reconstruct the replica of the Table and create its copy in another database. Remove the new data file and then move the .frm description and .MYI index files from the other database to the crashed database. The newly constructed database provides new description and index files, but doesn’t contain .MYD data file.

To reconstruct new database, go back to Easy Repair and try to build the index file. This should work, but if you are not able to repair MyISAM tables by using myisamchk tables, try to repair it with the help of MySQL repair software such as Stellar Repair for MySQL.

4.  Repair with MySQL repair software

Repair MyISAM tables in three simple steps:

1. Select MySQL database
2. Scan and repair the corrupt elements
3. Preview the repaired database and save it in MySQL format on the specified server. Alternatively, save the tables in CSV/HTML/XLS format

The software repairs MyISAM tables without using myisamchk commands.

Conclusion

This blog post highlights MyISAM errors and their probable causes. Maintaining a backup of MyISAM tables helps in restoring the database but there are multiple factors that create hindrance in the backup restore. These include unavailability of the latest backup, data not in restorable condition and more. 

You can resolve a few errors by increasing the maximum and average row length but again, this is not the solution for all errors. 

The repair option requires changing the location of database directory, checking the permissions of the Table file, and shutting down of MySQL server.

Repairing with software eliminates the database-unavailability problem. Also, database administrators do not have to use complex queries and, most importantly, the advanced software repairs and recover all objects of MySQL database in the original format.

Was this article helpful?

YES0

NO

About The Author

Priyanka Chauhan

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.