MyISAM was the default storage engine before MySQL Server version 5.5. When using the MyISAM storage engine in MySQL Server, the table data is stored in two files on the system’s disk: MYD (MyData) and MYI (MyIndex). Sometimes, when accessing the MySQL MyISAM tables, you encounter various errors or issues. Some of the common errors are:
The table ‘/var/lib/mysql/temp/#sql_2d22_1′ is full
ERROR 1726 (HY000): Storage engine ‘MyISAM’ does not support system tables. [mysql.db]
The table is marked as crashed and should be repaired
Such errors usually occur when there is corruption in the MyISAM tables or the database. MyISAM tables may get corrupted or damaged due to several internal and external factors. In this article, we will discuss the reasons for corruption in MyISAM tables and some effective solutions to repair them.
Causes of Corruption in MyISAM Tables
MyISAM tables can easily get corrupted due to reasons such as:
- Bugs in the MySQL or MyISAM code
- Lack of storage space on the disk
- Faulty hardware
- System failure or crash
- Abnormal termination of MySQL (mysqld) process
- Insufficient RAM led to a critical server crash.
How to Determine if MyISAM Tables are Corrupted?
Before moving forward with the repair process, first determine whether the MySQL database tables are corrupted or not. You can use the myisamchk command to check MyISAM tables for errors and issues. To check a particular table, use this command:
Note: Before running this command, stop the MySQL Server.
myisamchk Table_name…
For example: myisamchk Stellartable.MYI
To check all the tables in a database directory, you can run the below command:
myisamchk *.MYI
You can also run the myisamchk command with arguments to increase the level of checks by specifying tasks. For example, use myisamchk –m tbl_name to check all the indexes and rows in the tables.
myisamchk [options] tbl_name …
You can use the myisamchk-e tbl_name command for an extended check. This command thoroughly checks all the data in the table. It may take some time, especially for large tables.
You can also run the CHECK TABLE statement to check the MySQL database tables for corruption. It checks the tables and their views for errors. Here is how to use this statement:
Note: Ensure you have all the privileges required to run the CHECK TABLE statement.
CHECK TABLE tbl_name [, tbl_name] … [option] …
option: {
FOR UPGRADE
| QUICK
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}
If corruption is detected in MyISAM tables, then you need to repair and recover the tables.
Solutions to Repair MyISAM Tables in MySQL
Follow the solutions below to recover or repair the corrupt MyISAM tables.
Solution 1: Restore MyISAM Tables from Backup
If you have a recent, healthy backup of your MySQL database, then a simple option is to recover the MyISAM tables from the backup. For this, you can use the mysqldump – an open-source tool that restores the backup file in MySQL. It reloads the dump file, which reproduces all the statements in it.
Before using this tool, first check if all the privileges and roles are granted to your account. To check this, you can use the SHOW GRANTS statement. Next, ensure that you have CREATE privilege for objects in the statements, the ALTER privilege for the affected database, RELOAD or FLUSH TABLE privilege, and SELECT privilege for dumped tables.
Now, follow the steps below to restore the dump file using the mysqldump utility:
- First, clear the existing MySQL database and recreate it from scratch. For this, use the following command:
mysql > drop db_namemysql > create db_name
- Once the new database is created, restore the database using the following statement:
mysql -u root -p db_name < dump.sql
Note: This tool can help you to restore the dump file containing MyISAM tables, but it may take a lot of time if the database file size is more than 10 GB.
Solution 2: Use myisamchk Command
You can use the myisamchk command to repair or restore the MyISAM tables in the MySQL Server. It can help you recover all the data from the corrupt MyISAM tables, except unique keys. Here’s how to run the myisamchk command:
- First stop the MySQL Server.
- Next, run the below command:
myisamchk –recover TABLE
- Now, restart the MySQL Server.
Note: In some circumstances, it may delete corrupted rows. So, there is no guarantee of complete data recovery.
Solution 3: Run REPAIR TABLE Command
You can also repair the corrupt MyISAM tables using the REPAIR TABLE statement (see the example below):
REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] …
[QUICK] [EXTENDED] [USE_FRM]
This command helps you recover the corrupted MyISAM tables’ data. But it may lead to data loss. Also, the MySQL Server may exit while performing the repair operation. In such a case, you need to re-execute the REPAIR command to prevent data overwrites.
A Quick Solution to Repair Corrupted MyISAM Tables
To overcome the limitations of the above utilities or in case these utilities fail to restore the tables, you can use a professional MySQL repair tool, such as Stellar Repair for MySQL. It is an easy-to-use DIY tool that can repair corrupt large-sized database created in MyISAM storage engine with complete precision. It can recover all the objects, including tables, indexes, primary keys, etc., from the corrupt database without any data loss. It can also repair databases created in the InnoDB storage engine. The tool supports both Linux and Windows operating systems.
Conclusion
When the MyISAM tables get corrupted, the MySQL Server is unable to locate entries in the tables, which causes queries to fail or other issues. In this article, we have discussed the methods for easily and effectively repairing the MyISAM tables. The best option is to use a specialized MySQL repair tool, such as Stellar Repair for MySQL. It can help you repair and recover the corrupt tables from the MySQL database without causing any data loss. The tool is compatible with all MySQL Server versions, including 9.x, 8.x, 6.x, 5.x, and 4.x.
