database data_error ai_generated partial

InnoDB: Database page corruption on disk or a failed file read of page [page id: space=123, page number=456]. You may have to recover from a backup.

ID: database/mysql-innodb-redo-log-corruption

Also available as: JSON · Markdown · 中文
70%Fix Rate
86%Confidence
1Evidence
2024-01-22First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MySQL 8.0 active — — —
MySQL 8.4 active — — —
Percona Server 8.0 active — — —

Root Cause

InnoDB detected a checksum mismatch on a data page, indicating physical corruption of the ibd file or a failed read due to hardware issues (e.g., bad disk sector, memory corruption).

generic

中文

InnoDB 检测到数据页的校验和不匹配，表明 ibd 文件物理损坏或由于硬件问题（例如，坏磁盘扇区、内存损坏）导致读取失败。

Official Documentation

https://dev.mysql.com/doc/refman/8.0/en/innodb-recovery.html

Workarounds

  1. 75% success Set innodb_force_recovery to a value between 1 and 6 in my.cnf, then restart MySQL to bypass the corruption and dump the table: mysqldump -u root mydb my_table > dump.sql. After dump, drop and recreate the table, then restore. Example: innodb_force_recovery = 4. 
    Set innodb_force_recovery to a value between 1 and 6 in my.cnf, then restart MySQL to bypass the corruption and dump the table: mysqldump -u root mydb my_table > dump.sql. After dump, drop and recreate the table, then restore. Example: innodb_force_recovery = 4.
  2. 85% success Restore the affected table from a recent backup using mysqlbinlog or physical backup (e.g., Percona XtraBackup). Example: xtrabackup --prepare --target-dir=/backup; then copy the restored ibd file to the datadir. 
    Restore the affected table from a recent backup using mysqlbinlog or physical backup (e.g., Percona XtraBackup). Example: xtrabackup --prepare --target-dir=/backup; then copy the restored ibd file to the datadir.

中文步骤

  1. Set innodb_force_recovery to a value between 1 and 6 in my.cnf, then restart MySQL to bypass the corruption and dump the table: mysqldump -u root mydb my_table > dump.sql. After dump, drop and recreate the table, then restore. Example: innodb_force_recovery = 4.
  2. Restore the affected table from a recent backup using mysqlbinlog or physical backup (e.g., Percona XtraBackup). Example: xtrabackup --prepare --target-dir=/backup; then copy the restored ibd file to the datadir.

Dead Ends

Common approaches that don't work:

  1. Restart MySQL without any recovery steps, hoping the corruption is transient 95% fail

    InnoDB will attempt to read the corrupted page again during startup or on access, causing the same error or a crash.

  2. Run REPAIR TABLE on the affected table 100% fail

    REPAIR TABLE only works for MyISAM tables; for InnoDB, it returns 'The storage engine for the table doesn't support repair'.