How to Repair InnoDB Table Corruption in MySQL

Summary: The blog provides detailed information about MySQL primary database engine InnoDB, its advantages, and the best practices you can follow while running it. It also describes the guidelines to repair Innodb table corruption and the professional recovery solution.

InnoDB is MySQL storage engine being used by default in MySQL 5.5 and later versions. It is known to possess crash recovery capabilities and also known as a crash-safe storage engine. However, under some circumstances, it appears that the InnoDB pages can turn corrupt during a crash. This calls upon the need for a manual crash recovery.

In such cases, either the operating system, hardware component or the I\O system is blamed by the application. Such cases occur more frequently on Windows OS and while the database runs in a virtualized environment. There are some advantages offered by InnoDB as discussed in the upcoming section.

Advantages of InnoDB

1. The DML operation follows the ACID model. The transactions in InnoDB feature rollback-commit and crash recovery capabilities for protecting the user data. Row level locking accelerates multi-user performance and concurrency.
2. The data stored on the disk is arranged and organized by InnoDB tables to optimize primary key-based queries. Each table has primary key index known as clustered index that arranges the data contents with the aim to minimize I\O for primary key lookups.
3. InnoDB supports foreign key constraints with the purpose to maintain the integrity of data. With the foreign keys, updates, deletes and inserts are analyzed to make sure that they do not lead to inconsistency issues across other tables.
4. If the Server turns inaccessible due to any hardware or software outcome issue then, the user will not require executing anything additional to restart the database. Moreover, transactions that are in process or not committed due to crash, will be cancelled.
5. Inserts, deletes and updates are optimized through an auto mechanism known as change buffering. Apart from enabling concurrent read\ write access to the same table, it also cached modified data contents to restructure the disk I\O.

InnoDB Table- Best Practices to Follow

1. 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.
2. Using most frequently queried columns or column, specify the primary key for each table. You can also use an auto-increment value in case no primary key exists.
3. Enable innodb_file_per_table option to put indexes and data for individual tables into distinct files.
4. 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.
5. It is recommended to turn off ‘autocommit’ as the performance is affected to a considerable extent.
6. 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.

Determine if the Server Supports InnoDB

InnoDB does not repair table space, but it is possible to rebuild secondary indexes using ALTER TABLE DROP/ ADD KEY. For execution, it is important to determine if InnoDB is available and supported by the Server. To know this:

1. Execute command SHOW ENGINES to access all different My SQL storage engines. Then search for DEFAULT in the InnoDB line.
2. Alternatively, enter the query INFORMATION_SCHEMA ENGINES. In case you are not able to find InnoDB, you will have mysqld binary compiled without InnoDB support and you will require getting another one.
3. In case InnoDB exists but is disabled, return to the startup navigate options and configuration file. Also, get rid of the skip-InnoDB option (if it exists).

Guidelines to Troubleshoot InnoDB Issues:

Several guidelines and measures help troubleshoot InnoDB in a more efficient manner without wasting time looking for alternate methods:

1. If you suspect a bug or any of the InnoDB operation fails, analyze the MySQL Server error log for InnoDB specific errors
2. If the failure is due to the deadlock, execute with innodb_print_all_deadlocks statement enabled. Details about all the InnoDB deadlocks will be printed to MySQL Server error log
3. 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
4. It is always a good practice to run MySQL Server from the command prompt while troubleshooting Innodb problems rather than from the Windows service or mysqld_safe
5. 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

When Manual Innodb Troubleshoot Tips Fail to Recover Data!

Troubleshooting InnoDB tables require a thorough technical expertise on all related parameters. If you find any difficulty while you repair the MySQL database or any of the database components appears missing, you can try MySQL repair software to repair and restore corrupt InnoDB tables.

Stellar Repair for MySQL – The Way Forward

The tool recovers all the database objects including tables, keys, data types, table properties, triggers, views, etc. Both the primary database engines including InnoDB and MyISAM are supported by the tool and hence in case of corruption, the tables based on respective engines are restored to functional mode. All types of corruption, irrespective of its level are supported, and data is retrieved from the corrupt files.

This MySQL recovery is a Windows-based tool and therefore supports Windows 10, 8.1, 8, 7, Vista & XP versions. However, Linux database files can also be recovered by copying the folders and ‘ibdata1’ file to Windows machine. Time-saving data recovery is offered as the tool supports batch processing of corrupt MySQL database files simultaneously in a single attempt!