Summary: This article lists some of the common errors you may encounter when a MySQL table gets corrupt. It also outlines the possible reasons behind the MySQL database table corruption. The article also explains what you can do to fix corrupt tables and how a MySQL database repair software can come in handy to recover data within the table and all other database objects.
As a MySQL database user, you might encounter any of these errors:
- “Table is marked as crashed and should be repaired”
- “Table is crashed and last repair failed”
- “Table tbl_name doesn’t exist”
- “Incorrect key file for table: ‘…’. Try to repair it”, etc.
These are warning signs that your MySQL table is corrupted.
What Causes Corruption in MySQL Database Table?
Following are possible reasons leading to MySQL database table corruption:
- Issues in the underlying platform used by MySQL, including disk subsystem, drivers, controllers, etc.
- Abrupt system shutdown or server crash
- MySQL process gets killed in the middle of writing to the database
- Software bug or hardware failure
Encountering any of these MySQL corruption errors can make the data within the tables inaccessible. Let’s discuss the solutions to fix the damaged tables.
How to Fix MySQL Database Corruption Errors?
If you have an updated backup of your database, restore the database from backup to recover the inaccessible table data. If the backup is not available, perform the steps in the sequence below to fix corrupt MySQL tables and recover data:
Note: Take a backup of your MySQL database and tables to prevent further damage during the repair and recovery process.
Step 1: Check Table for Error
First, you must check the database table for corruption errors. You can use the CHECK TABLE command to diagnose corrupt InnoDB and MyISAM tables:
| CHECK TABLE tbl_name option; |
Here, replace ‘tbl_name’ with the name of the corrupt database table. And replace ‘option’ with any of these options: QUICK, FAST, MEDIUM, EXTENDED, and CHANGED.
| Option | Description |
| QUICK | Performs faster repair of InnoDB and MyISAM tables. Skips scanning the rows to look for incorrect links |
| FAST | Check only MyISAM tables that have not been closed properly. Do not apply to InnoDB tables. |
| CHANGED | Check only MyISAM tables that have not been closed since the last check was performed. Do not apply to InnoDB tables. |
| MEDIUM | Verify if the deleted links are valid by scanning rows. Applies only to MyISAM tables. |
| EXTENDED | Performs a full key lookup for all the keys for each row of the MyISAM table. It takes a long time to execute. |
To check more than one table for corruption, use the following command:
| CHECK TABLE tbl_name1, tbl_name2, tbl_name 3 option; |
Note: The CHECK TABLE command can only be used when the MySQL process is running. But, if the process/server is not running, you can use the following ‘myisamchk’ command to check MyISAM tables for corruption:
myisamchk [options] tbl_name …
This command checks a single table.
Execute the following command to check all tables in the MySQL database for corruption:
myisamchk /path/to/datadir/*/*.MYI
The myisamchk utility does not work for InnoDB tables.
Step 2: Repair Corrupt Table
Note: The myisamchk command only applies to MyISAM tables and does not work for InnoDB tables. For repairing and recovering an Innodb table, skip to Step 3.
The myisamchk utility can also help repair a corrupt (damaged) MyISAM table. To use the utility for repairing the damaged table, do the following:
- Stop the MySQL service by typing:
| service mysqld stop
cd /var/lib/mysql |
- Change to the directory where the database is located.
| cd /var/lib/mysql/table_name
myisamchk <TABLE> |
- To repair a damaged table, run the following command by replacing TABLE with the name of the table you want to repair:
| myisamchk –recover <TABLE> |
- Restart your MySQL service.
| service mysqld start |
Step 3: Force InnoDB Recovery
To fix corruption in InnoDB tables, use the ‘innodb_force_recovery’ option. This option forces the InnoDB database engine to start up without any background operations, which allows you to dump your tables.
- Open your MySQL configuration file (my.cnf), and add the following to the [mysqld] section:
| [mysqld]
innodb_force_recovery=1 service mysql restart |
You can start innodb_force_recovery with a value of 1 and increase the value, as necessary. Once you have initiated innodb recovery, restart your MySQL server and then dump your tables. However, in some situations, you may need to set the value to 4 till 6, which can corrupt the data.
Caution! If you’re able to dump the tables with an innodb_force_recovery=3 or less value, then you can safely perform the recovery process. However, setting innodb_force_recovery to a value greater than 4 involves data loss risk.
- If you’re able to start your database, run the following mysqldump command to export all of the databases to a SQL file (i.e., backup file that contains all the statements to restore the database):
| mysqldump –all-databases –add-drop-database –add-drop-table > mydump.sql |
- Start the MySQL service, and then attempt to drop the database containing the affected tables by using the DROP DATABASE command. If you cannot drop a database, try deleting it manually by performing the below steps after stopping the MySQL server.
| cd /var/lib/mysql
rm -rf <DATABASE> |
- After executing the above command, comment out the below line in the [mysqld] to disable InnoDB recovery:
| #innodb_force_recovery=… |
- Save all the changes you have made to the my.cnf file, and then start your server.
| service mysqld start |
- Ty restoring the database and table from the MySQL dump (backup file) that you have created in Step 2:
| mysql -u user -p < mydump.sql |
Performing these steps will restore the MySQL database and its tables. If the data is still missing, you will need to use professional MySQL database repair software, such as Stellar Repair for MySQL to restore your database and recover its data. The software can repair both InnoDB and MyISAM tables keeping the data intact. It supports repairing MySQL tables on Windows as well as Linux systems.
Conclusion
MySQL database tables can get corrupt due to sudden system shutdown, server crash, hardware failures, bugs in MySQL code, etc. Corruption in the MySQL table results in the errors listed in this article. You can try to repair corrupted MyISAM tables by using the ‘myisamchk’ utility or force InnoDB recovery. However, it may take considerable time to repair MyISAM tables and perform InnoDB recovery manually. Also, the manual recovery process can result in data loss. A better alternative is to use Stellar Repair for MySQL software. The software allows repairing both MyISAM and InnoDB database tables without making any changes to the original data.