Diagnosing and Resolving InnoDB Page Corruption in MySQL

Problem Background

In April, an instance crashed due to an InnoDB page error. The server automatically restarted, so no production impact was observed, but the error log recorded messages such as:

InnoDB: End of page dump
InnoDB: Page may be an index page where index id is 8196
2023-04-11T07:57:42.508371+08:00 0 [ERROR] [FATAL] InnoDB: Apparent corruption of an index page [page id: space=3859, page number=842530] to be written to data file. We intentionally crash the server to prevent corrupt data from ending up in data files.
2023-04-11 07:57:42 0x7fe4d42cf080 InnoDB: Assertion failure in thread 140620788985984 in file ut0ut.cc line 921
InnoDB: We intentionally generate a memory trap.

The same type of error re‑appeared in June, this time causing the instance to crash again. The repeated errors indicate a serious data‑page corruption that must be investigated and resolved.

Problem Analysis

Data‑page corruption can be classified into two scenarios based on whether the instance can start:

Instance starts normally.

Instance fails to start.

Scenario 1: Instance Starts Normally

Use the page number from the error log to query InnoDB metadata tables. In a test environment, create a sample table and run queries such as:

mysql> use test;
... (output omitted for brevity) ...
ERROR: No query specified

Then retrieve the page information:

mysql> use information_schema;
mysql> select * from INNODB_BUFFER_PAGE where PAGE_NUMBER=1156 limit 10;
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+-----------------+------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
| POOL_ID | BLOCK_ID | SPACE | PAGE_NUMBER | PAGE_TYPE | FLUSH_TYPE | FIX_COUNT | IS_HASHED | NEWEST_MODIFICATION | OLDEST_MODIFICATION | ACCESS_TIME | TABLE_NAME      | INDEX_NAME | NUMBER_RECORDS | DATA_SIZE | COMPRESSED_SIZE | PAGE_STATE | IO_FIX  | IS_OLD | FREE_PAGE_CLOCK |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+-----------------+------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
|       0 |       64 |   126 |        1156 | INDEX     |          0 |         0 | NO        | 0                   | 0                   | 0           | `test`.`t_user` | idx_name   |          515   | 15965    | 0               | FILE_PAGE | IO_NONE | NO     | 0               |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+-----------------+------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
1 row in set (0.18 sec)

Determine whether the corrupted page belongs to a primary key or a secondary index. If it is a primary‑key index, data loss is possible; if it is a secondary index, dropping and rebuilding the index may suffice.

Scenario 2: Instance Fails to Start

Two methods can be used to force the instance to start:

Method 1 – innodb_force_recovery

Set the innodb_force_recovery parameter (values 1‑6) in my.cnf . Start with the lowest non‑zero value and increase only if necessary, because values ≥4 may cause permanent data loss.

Method 2 – inno_space Tool

The inno_space command‑line utility can inspect and repair corrupt InnoDB pages. For example, to skip a corrupt leaf page and recover most data:

# Assume the error log shows a corrupt page in file .test/t_user.ibd, offset 163840
# Delete the corrupted part of the page
./inno -f /opt/mysql/data/3307/test/t_user.ibd -d 10
# Update the checksum of the page
./inno -f /opt/mysql/data/3307/test/t_user.ibd -u 10
# Restart MySQL service

Problem Summary

Understanding the two scenarios of InnoDB page corruption enables administrators to respond calmly, minimize data loss, and, in extreme cases, recover without losing data. In production environments—especially finance—backups are indispensable; the article stresses “backup, backup, backup”.

References

innodb_buffer_page: https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-buffer-page-table.html

innodb_sys_indexes: https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-sys-indexes-table.html

innodb_sys_table: https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-sys-tables-table.html

innodb_force_recovery: https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

inno_space: https://github.com/baotiao/inno_space

inno_space tool introduction: http://mysql.taobao.org/monthly/2021/11/02/