How to Fix MyISAM Table Corruption in MySQL?

In MySQL Server 5.5 and earlier versions, the MyISAM was the default storage engine. So, while creating table, you did not require to specify the engine, unlike in MySQL 8.4, where the default engine is InnoDB. When MyISAM tables are created, the engine creates two files which are named as data and index files. Thus, each MyISAM table has two disk files – tbl_name.MYD and tbl_name.MYI. 

These tables support B-tree indexes, compressed tables, hash indexes, and many other features. But, these do not support transactions or foreign key constraints and have no built-in crash recovery. These limitations make MyISAM tables more prone to inconsistencies and corruption. If the MyISAM tables get corrupted, you may encounter various corruption-related errors when you try to access them. In this article, we will discuss the causes behind MyISAM table corruption and the solutions to repair them. 

Causes of MyISAM Table Corruption in MySQL

Corruption in the MyISAM tables can occur due to the reasons mentioned below:

• Copying the .MYD and .MYI files while the tables are already in use.
• MySQL application crashes while you’re working on the tables.
• Multiple MySQL Server instances sharing the same database directories.
• You might be trying to run myisamchk on the MyISAM table while it is in use.
• Bugs in the MySQL or MyISAM code.
• Lack of storage space on the disk.
• Faulty hardware.

Check MyISAM Tables for Corruption

If you face the following errors when accessing MyISAM tables, it indicates corruption.

• The table is marked as crashed and should be repaired
• Can’t find file tbl_name.MYI (Errcode: nnn)
• Unexpected end of file
• Record file is crashed
• Got error nnn from table handle

To confirm the corruption, you can run CHECK TABLE your table name; command. This command helps you inspect the MySQL database tables for issues. It will return OK if the table is clean; else, it will return errors that require counter steps to repair it.

You can even use the myisamchk command to check MyISAM tables for errors and issues. But you need to first stop the MySQL Server to run this command. To check all the tables in a database directory, you can run the below command:

myisamchk *.MYI

To increase the level of check on this command, use arguments in it. This helps you easily specify tasks according to the requirements. For example, you can use the argument ‘e’ for an extended check. 

Solutions to Fix MyISAM Tables Corruption

If you have a readable backup (dump) file, then you can restore it using mysqldump utility to recover MyISAM tables. To use this open-source tool, use the SHOW GRANTS statement to check if all the privileges and roles are granted to your account. The privileges, like CREATE, ALTER, RELOAD, FLUSH TABLE, and SELECT are a must to restore the dump file. Next, follow the steps below to restore the dump file using the mysqldump utility:

First, drop and recreate the MySQL database using the below command:

mysql > drop db_namemysql > create db_name

Next, restore the database using the statement below:

mysql -u root -p db_name < dump.sql

If the dump file is not available, then follow the below methods to repair and restore MyISAM tables in MySQL.

1 – Use myisamchk Command

You can use the myisamchk command to repair or restore the MyISAM tables in the MySQL Server. If the table doesn’t contain unique keys, then you can run the myisamchk command to repair MyISAM tables. Steps are as follows:

• First, stop the MySQL Server.
• Next, run the command as given below:

myisamchk –recover TABLE

• Now, restart the MySQL Server.

Limitations of this command:

• It may not guarantee complete data recovery, as sometimes it deletes corrupted rows while repairing.
• It requires stopping the MySQL Server while repairing. If you try running this command while server is running, it can increase data corruption.
• It requires a large amount of space on your system disk where the MySQL Server is hosting. Make sure the space is twice the size of the data file.

2 – REPAIR TABLE Command

The REPAIR TABLE command can reconstruct the indexes, tables, and fix any minor errors in the MyISAM tables. But this command may lead to data loss. Sometimes, your server suddenly gets exited while the repair command is executing. In such a situation, you are required to re-execute the REPAIR command. 

Here is how to use this command:

REPAIR TABLE table name;

3 – Try a Reliable MySQL Repair Tool

If your MySQL database file is too large and severely corrupted or damaged, then a MySQL repair tool can help you. Stellar Repair for MySQL is a professional tool that can repair MyISAM and InnoDB tables in less time and without data loss. You need a small space on your system to download and install the software. It can even help you recover indexes, primary keys, and other components from corrupt MyISAM tables with complete integrity. 

End Note

You can follow the methods discussed above to repair and restore corrupt MyISAM tables. You can restore the dump file or use the REPAIR TABLE command or myisamchk command to restore the tables in MySQL. But all these methods have some limitations. To overcome the limitations of these repair methods, you can use a professional MySQL repair tool, like Stellar Repair for MySQL. It helps you recover the corrupt MyISAM tables, without causing any data loss. The tool is compatible with all MySQL Server versions, including 5.5, 6.x, and 8.x.