How to Fix Corrupted Tables in MySQL?

Methods to Fix Corrupted Tables in MySQL. MySQL tables can turn corrupt because of hardware failure, bugs in MySQL code, abrupt system shutdown, etc. When trying to access a corrupt table, you may receive errors like “tbl_name.frm is locked and cannot be changed”, “Got error nnn from table handler”, etc. Or, running queries may return incomplete results. 

Whatever be the case, you can’t read data within a corrupt table. To regain access to the data, you can restore the tables from your backup. If the backup doesn’t work, you will need to fix corruption in the tables. This post discusses the methods to repair MySQL database tables. 

Methods to Fix Corrupted Tables in MySQL

The method to fix corruption in a MySQL table varies depending on whether you use MyISAM or InnoDB for tables. 

Note: Make a backup of the tables you want to repair before proceeding on MySQL editor.

Method 1 – Use Myisamchk to Detect and Repair MyISAM Tables

MySQL provides ‘myisamchk’ utility to check MyISAM tables for errors and repair the corrupted tables. 

Note: Make sure to shut down the server, and no other user is using the tables when running the myisamchk utility. If you want to keep the server running while checking and repairing the table, use the CHECK TABLE command; unlike myisamchk, the CHECK TABLE command checks an InnoDB table for errors.

The syntax to check all the MyISAM tables is as follows:

 mysql > myisamchk –silent –fast /path/to/datadir/*/*.MYI

If the statement’s output does not return OK, it means that the table is corrupt. In that case, repair the MySQL table using the following syntax:

 mysql > myisamchk -r tbl_name

Here the ‘-r’ option helps repair any problem in the MyISAM tables. Use it for faster recovery of the database tables. If this option fails to recover the table, use the –safe-recover (-o) option to do a repair.

mysql > myisamchk -o tbl_name

If the repair is successful, an output such as the one below will get displayed:

Output

+————————–+——–+———-+———-+

| Table                    | Op     | Msg_type | Msg_text |

+————————–+——–+———-+———-+

| db_name.tbl_name         | repair | status   | OK       |

+————————–+——–+———-+———-+

Note: An alternative option to repair a MyISAM is to use the REPAIR TABLE command along with the table name. 

mysql > REPAIR TABLE tbl_name;

If the MyISAM table is still corrupt, see MySQL documentation for other methods to repair the corrupted table. 

Method 2 – Force InnoDB Recovery

You will hardly find the need to repair InnoDB tables. If an InnoDB table becomes corrupted, it automatically starts the crash recovery mechanism, which can fix any issues when the server is restarted.

However, if the InnoDB table is severely corrupted, MySQL might fail to start. In such a case, you will need to force start the InnoDB engine using the innodb_force_recovery option. To force InnoDB recovery, follow these steps:

1. Open your MySQL configuration file:

/etc/mysql/mysql.conf.d/mysqld.cnf

2. Add the following line in the [mysqld] section before restarting the server:

[mysqld]

innodb_force_recovery=1

Save and close the configuration file and try restarting the server. If the server doesn’t start, you may need to exceed the value from 1 to 6.

3. Once you can access the corrupted InnoDB table, dump (back up) your table to a new file using ‘mysqldump’ utility. 

mysql> mysqldump db_name tbl_name > dump_filename.sql

4. Drop the database table using the below command in mysql prompt:

mysql> -u user -p –execute=”DROP TABLE db_name.tbl_name”

5. Now, restore the table using the dump file created in step 3.

 mysql -u user -p < dump_filename.sql

Now open the table and see if it contains the data. 

End Note

If you fail to fix corrupted tables in MySQL using the above-discussed methods, try a MySQL repair tool to repair the tables. Stellar Repair for MySQL software can repair both MyISAM and InnoDB tables. Also, it helps recover all the table records while maintaining data integrity.