Contents
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:
| Command | Error Code | Causes of Error |
| shell> perror 126 | MySQL error code 126 | Index file is crashed |
| shell> perror 127 | MySQL error code 127 | Record-file is crashed |
| shell> perror 132 | MySQL error code 132 | Old database file |
| shell> perror 134 | MySQL error code 134 | Record was already deleted |
| shell> perror 141 | MySQL error code 141 | Duplicate unique key or constraint on write or update |
| shell> perror 144 | MySQL error code 144 | Table is crashed and last repair failed |
| shell> perror 145 | 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 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:
- Easy safe repair
- Difficult repair
- Very difficult repair
To decide the type of repair process, you need to check myisamtables 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.
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:
- Select MySQL database
- Scan and repair the corrupt elements
- 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.
