Best Ways to Repair InnoDB Table Corruption in MySQL

Summary: You may experience InnoDB table corruption due to MySQL Server crash, hardware failure or bugs, etc. This blog will talk about corrupted database table in MySQL and what causes it. Also, it will outline best ways to repair InnoDB table corruption in MySQL and some best practices you can follow to prevent InnoDB from getting corrupted.

IMPORTANT! Repairing corrupted table manually may work, but it involves the risk of data loss. If you cannot risk losing data, try restoring the database from a recent database backup. However, if the backup is not the most recent backup, you may lose even more data. You can avoid data loss risk when repairing corrupt InnoDB tables (or MyISAM tables) by using Stellar Repair for MySQL software. It helps recover data from corrupt or damaged tables in the original format in just a few clicks.

InnoDB is MySQL storage engine  used by default in MySQL 5.5 and later versions. It is also known as a crash-safe storage engine (as opposed to MyISAM), owing to its ability to recover data after crash. However, under some circumstances, it appears that InnoDB table can get corrupted. When this happens, data held within the table becomes unreadable. Also, a corrupt table can cause InnoDB file corruption.

What Causes InnoDB Table Corruption in MySQL?

Some reasons that may result in corrupted table are as follows:

• MySQL service crashes after reboot
• MySQL Server restarts automatically
• Power failure or bad memory stops MySQL Server when writing to database table
• There’s a bug in operating system or in MySQL code
• Hardware failure
• The machine, on which MySQL is running, is shut down unexpectedly

You may encounter InnoDB table corruption issue more frequently on Windows OS while the database runs in a virtualized environment. Now, let’s discuss how to diagnose & repair InnoDB table in MySQL.

Before You Proceed

Try identifying precisely what you’re dealing with. Doing so, will help you troubleshoot InnoDB issue in a more efficient manner without wasting time looking for alternate methods.

To diagnose InnoDB issues, perform the following:

1. Analyze MySQL Server Log

If you suspect or find corruption in InnoDB tables, examine the MySQL Server error log to find relevant information about specific InnoDB errors. The error log will help you understand:

• If the table gets corrupt due to database page corruption. A corrupt page in MySQL database can lead to corruption in a db table.
• If the failure occurs due to a deadlock, execute with innodb_print_all_deadlocks statement enabled. Details about all the InnoDB deadlocks will be printed to MySQL Server error log.
• If you encounter InnoDB data dictionary failure issues, you will need to resolve “inability to open .InnoDB files”, “failed CREATE TABLE statement” and “system cannot find the path specified” errors. Troubleshooting these errors will enable access to InnoDB data dictionary.

2. Check Table for Errors

While checking MySQL error log can help you identify corruption, you may have problem identifying, which table was affected by it. You can check InnoDB table for errors by using two methods. However, which method can be used here depends on whether MySQL Server is running or it is offline/crashed.

• If the Server is running, use CHECK TABLE command to find errors in InnoDB table.

mysql> CHECK TABLE table_name;

• If the Server is offline or has crashed, use Innochecksum to check for page corruption errors on an InnoDB table.

shell> innochecksum [options] file_name

How to Repair Corrupt InnoDB Table in MySQL?

InnoDB comes with a crash recovery mechanism that helps repair and recover db table from crashes automatically. Also, you can use the ‘Dump and Reload’ method to rebuild one or more corrupted tables. However, in case of severe corruption, you may find it difficult to restart the MySQL server. And so, you will need to manually fix InnoDB file in MySQL that has the corrupt table.

Note: Before attempting to repair InnoDB file corruption caused due to a corrupt table, perform recovery from a recent backup. If the backup copy is corrupt or it is not the most recent copy, follow the steps discussed below.

Also Read: How to Repair MySQL Database Table Without Downtime?

Follow a step-by-step procedure to fix corrupted InnoDB table

Note: Make sure to back up the InnoDB file before performing the repair procedure to avoid further damage to table data.

Step 1: Attempt to restart the MySQL service and check if you can access the server. To do so, follow these steps:

• Press ‘Windows’ and ‘R’ keys together.
• In the dialog box that appears, type services.msc, and then press Enter.
• In the Services dialog box, search for MySQL Service and right-click on it, and click stop, start or restart service.

If you cannot access the Server, forcing InnoDB recovery may help. For this, enable ‘innodb_force_recovery’ option in MySQL my.cnf configuration file by performing the following:

• Open the configuration file, and in the [mysqld] section, add the following line:

• Now save and close the my.cnf file.
• Try restarting the MySQL service again.
• If you’re able to access the corrupt table, dump the table data by using the mysqldump command:

You can dump several tables separated by whitespace in the above command.

• Next, drop the table from the db:

• Now try restoring the table using the newly created dump file:

Step 2: Stop the MySQL service, and then disable InnoDB recovery mode. For this, enter the following line in the [mysqld] section:

Step 3: Save the changes to the my.cnf file and restart the MySQL service by using the following command:

Restore the database table from the dump (i.e. backup file) that you just created.

What to do when Manual InnoDB Troubleshoot Tips Fail to Recover Data?

If you find any difficulty while manually repairing InnoDB tables of MySQL database or any of the database components appears missing, you can try MySQL repair software to repair and restore corrupt InnoDB tables.

Key Features of Stellar Repair for MySQL

• Recovers all the database objects including tables, keys, data types, table properties, triggers, views, etc.
• Repairs both InnoDB and MyISAM tables.
• Can handle all types of MySQL corruption errors, such as corruption of InnoDB tablespace, missing database file, MySQL index file corruption, etc.
• Repairs MySQL database created on Windows (10, 8.1, 8, 7, Vista & XP) and Linux OS based systems.
• Supports batch processing of corrupt MySQL database files simultaneously in a single attempt.

Wrapping Up

Manually repairing corrupt InnoDB table may result in data loss. So, if you cannot risk losing data, use Stellar Repair for MySQL software to repair InnoDB table corruption in MySQL. The software helps fix corrupt database table and recovers table properties, relationship constraints and keys.

Once you’ve fixed InnoDB file corruption, follow these best practices to prevent InnoDB corruption.

• It is a good practice to group sets associated with DML tasks into transactions. This can be done by bracketing the DML operations with START TRANSACTION and COMMIT statements.
• When using most frequently queried columns, specify the primary key for each table. You can also use an auto-increment value in case no primary key exists.
• Enable innodb_file_per_table option to put indexes and data for individual tables into distinct files.
• If any issue persists with the engine indicated in ENGINE= clause of CREATE TABLE, run the Server with –sql_mode=NO_ENGINE_SUBSTITUTION option to prevent tables that are formulated with different storage engine.
• It is advisable not to use LOCK TABLES statement and use SELECT … FOR UPDATE syntax for unlocking the rows that are required to update in order to acquire exclusive write access to a set of rows. InnoDB possesses the ability to handle multiple read\ write sessions to the same table simultaneously.
• Run MySQL Server from the command prompt while troubleshooting Innodb problems rather than from the Windows service or mysqld_safe
• Make sure that InnoDB Monitors are enabled to acquire information about the existing problem. If the persisting issue is performance-related and the Server freezes, make sure to enable the standard Monitor for printing InnoDB internal state-related information. Else, enable Lock Monitors if the problem appears to be with locks.