The discussion in this section describes how to repair a Crashed MyISAM table by using myisamchk on MyISAM tables (extensions .MYI and .MYD). Before moving towards the solution we must know about possible reasons for the corrupted MyISAM table.
MyISAM Tables are corrupted due to a number of reasons and some of these are:
- 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:
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
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 Tablespace, which increases maximum and average row length.
ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;
For errors listed in the table, use myisamchk or a MySQL Recovery Software to detect and fix MySQL database issues.
Methods to Repair a Crashed MyISAM Tables
The following repair methods are involved:
- Easy safe repair.
- Difficult repair.
- Very difficult repair.
To decide the type of repair process, you need to check MyISAM tables by using the following steps:
- Run the command – myisamchk *.MYI or myisamchk -e *.MYI Suppress unnecessary information by suffixing –s (silent) option.
- If the server MySQLd is shut down, use –update-state option to mark the table as checked.
You have to repair only those MyISAM tables for which myisamchk announces an error and starts from ‘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 a very difficult repair process.
Note: Before starting the repair process, take a backup of the MySQL database to safeguard against data loss in case the 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 the database to check if the file contains everything and ‘delete links’ point to correct locations within the data file. If it works, an easy repair process has fixed the Tables.
- Repeat the process to repair the 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 you found that the above step fails, then 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 the 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:
| 1 | shell> Mysql db_name |
This new data is empty.
| 1
2 3 |
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name; mysql> quit |
- Copy and not move the content of the old data file back onto the newly created data file. (Retain a copy of old data in case something goes wrong).
Note: In the 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.
After successfully completion of process verify it by using myisamchk -r -q and if it works, then your MyISAM table is repaired. If not, repaired then follow 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. But what if the database backup is not available. In the absence of a 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 descriptions and index files but doesn’t contain .MYD data file.
To reconstruct a new database, go back to Easy Repair and try to build the index file. It is the last manual method to repair MyISAM tables and hopefully, this should work.
Sometimes these above manual methods don’t work. In such a condition you have relied on a Professional Tool.
Conclusion
This blog post highlights MyISAM errors and their probable causes. 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 the database directory, checking the permissions of the Table file, and shutting down of MySQL server. Repairing with software eliminates the database-unavailability problem.