Unlock the Secrets of MySQL corrupted indexes

One of our customers recently had a Corrupted Index that affected the health of his database and caused latency for his customers. To identify and handle a corrupted index in MySQL, here are some common FAQs:

What is a MySQL corrupted index?

A MySQL corrupted index refers to a situation where the index data within a MySQL database becomes inconsistent or damaged, resulting in incorrect or incomplete query results. It can occur due to various reasons, such as hardware failures, software bugs, improper shutdowns, or disk errors.

What are the common symptoms of a MySQL corrupted index?

When a MySQL index is corrupted, you may experience several symptoms, such as slow query performance, incorrect or missing query results, frequent crashes or errors during database operations, or unusually high CPU or disk usage.

What are the common error messages related to MYSQL Index Corruption?

You can receive one of the following errors when running a CHECK TABLE command:

[ERROR] [MY-012728] [InnoDB] Record overlaps another
[ERROR] [MY-012738] [InnoDB] Apparent corruption in space 98 page 4 index `PRIMARY`
[ERROR] [MY-013050] [InnoDB] In page 4 of index `PRIMARY` of table `sales`.`store`, index tree level 2
[ERROR] [MY-010211] [Server] Got error 180 when reading table './sales/store'

How can I identify a corrupted index in MySQL?

To identify a corrupted index in MySQL, you can do the following:

• Run the CHECK TABLE command
• Use the myisamchk or the  innodb_table_monitor utility, depending on the storage engine you are using.
  These tools analyze the integrity of database tables and can identify any corruption issues, including problems with indexes.

How to run CHECK TABLE Command?

To identify a corrupted index in MySQL, and for MyISAM tables:

CHECK TABLE table_name;

Sample output:

+---------------------+-------+----------+-------------------------------------------------------+
| Table               | Op    | Msg_type | Msg_text                                              |
+---------------------+-------+----------+-------------------------------------------------------+
| database.table_name | check | error    | Corrupt                                               |
| database.table_name | check | error    | Found key at page 123456, but there are no records in |
| database.table_name | check | error    | Corrupt                                               |
+---------------------+-------+----------+-------------------------------------------------------+

How to Analyze InnoDB tables?

SELECT TABLE_NAME,
       INDEX_NAME, 
       INDEX_TYPE,
       CHECKSUM
FROM  information_schema.INNODB_SYS_TABLESTATS
WHERE TABLE_NAME = 'table_name'
  AND INDEX_NAME = 'index_name';

Sample output:

+------------+-------------+------------+---------+
| TABLE_NAME | INDEX_NAME  | INDEX_TYPE | CHECKSUM|
+------------+-------------+------------+---------+
| table_name | index_name  | 3          | 123456  |
+------------+-------------+------------+---------+

How can I fix a corrupted index in MySQL?

The method to fix a corrupted index in MySQL depends on the storage engine and the severity of the corruption. For MyISAM tables, you can use the myisamchk command-line tool with the --recover or --safe-recover options.

InnoDB tables may require more advanced recovery techniques, such as using the innodb_force_recovery option or restoring from backups. It is recommended to consult the MySQL documentation or seek professional assistance to ensure a proper recovery process.

How can I fix a corrupted index in MySQL MyISAM table?

REPAIR TABLE table_name;

Sample output:

+------------------ -+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| database.table_nam | repair | status   |   OK     |
+--------------------+--------+----------+----------+

How can I fix a corrupted index in MySQL InnDB table?

For InnoDB tables, the recovery process can be more complex.
Ideally, the innodb_force_recovery option should be used as a last resort when other recovery methods have failed or are not feasible. If possible, it's best to rely on regular backups to restore the database and minimize the need for advanced recovery techniques. Proper backup strategies, including offsite backups and point-in-time recovery options, can provide a more convenient and reliable approach to handle data corruption issues.
Here are the steps you can follow to recover:

1. Set the innodb_force_recovery option in the MySQL configuration file (e.g., my.cnf):

[mysqld]
innodb_force_recovery = 1

2. Start the MySQL server with the modified configuration.

3. Dump the data from the corrupted table(s) using the mysqldump utility:

mysqldump -u username -p database table_name > table_name_dump.sql

4. Drop the corrupted table:

DROP TABLE table_name;

5. Stop the MySQL server, remove the innodb_force_recovery option, and start the server again.

6. Restore the data from the dump file:

mysql -u username -p database < table_name_dump.sql

7. Remember to replace table_name and index_name in the above examples with the actual name of your table and index. Additionally, make sure to adjust the innodb_force_recovery option value based on the severity of the corruption (ranging from 1 to 6).

What do the innodb_force_recovery values mean, and how to choose?

The innodb_force_recovery option in MySQL is used to control the crash recovery process for InnoDB tables. It has a range of values from 0 to 6, each representing a different level of recovery. The option allows you to attempt recovery even when InnoDB detects corruption in the database files.

Here is an explanation of the different values and their meanings:

• 0: Normal start without recovery. No crash recovery is performed.
• 1: The least restrictive level. It allows InnoDB to start and try to recover using its automatic recovery mechanism. This level is suitable for most cases of corruption.
• 2: InnoDB ignores and prints warnings rather than halting if it encounters corruption issues. It tries to proceed with as much recovery as possible.
• 3 to 6: These levels increase in severity and are intended for advanced recovery purposes. They should be used with caution and only under the guidance of experienced database administrators or support personnel. Higher levels indicate more aggressive recovery techniques and may risk data loss.It's important to note that the higher the innodb_force_recovery value, the greater the chance of potential data loss. So it's recommended to start with the lowest possible value (e.g., 1) and escalate only if necessary.

Should I run CHECK TABLE every day to identify issues?

No. I wouldn't recommend executing commands such as CHECK TABLE without better understanding of your specific workload. The main reason is that  when running CHECK TABLE on large InnoDB tables, other threads may be blocked during the execution.
So executing CHECK TABLE too often as a cron job for example, can cause unexpected locks and failures of other queries in your production workload.

What should I look when suspecting a Corrupted Index?

To better understand the use case you should understand the following:

• Did you recently add a column to the table and corrupt the index?
• Did the database crash?
• Did queries fail once touching that table/index?
• Review the MySQL error log and the timestamp where the issues started occurring.

How to automatically locate a corrupted index?

One way to automatically locate corrupted indexes in MySQL is by using tools like EverSQL Sensor.
The Sensor is an advanced monitoring and diagnostics tool that helps identify and resolve performance issues within MySQL databases. By installing and configuring EverSQL Sensor, you can leverage its capabilities to detect and pinpoint corrupted indexes in your database. The tool continuously monitors the health of your indexes and provides real-time insights into their status. When a corrupted index is detected, EverSQL Sensor can generate alerts or notifications, allowing you to take immediate action to fix the issue.