Bug #77476 innodb_force_recovery = 6 cannot recover ANY DATA when change buffer not empty
Submitted: 25 Jun 2015 0:28 Modified: 27 Nov 2019 21:52
Reporter: Beracah Yankama Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6-5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: crash recovery, page corruption

[25 Jun 2015 0:28] Beracah Yankama
Description:
Extremely large table (70G), corrupted in power outage, mismatch in the innodb doublewrite buffer & log files.  Mysql can't start or recover.  Innodb force recovery settings prevent its own recovery.  In innodb_force_recovery=6 and read only mode, no reason to expect change or doublewrite buffers to be empty. Prevents table dumping, startup or any kind of recovery without percona tools to read page by page.  It's unlikely that the entire table would be corrupt, so inability to read-recover valid rows is a serious problem.

with innodb_force_recovery = [1-3]  -------------------------------
[...]
InnoDB: End of page dump
2015-06-25T00:19:07.324618Z 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 2523095619, calculated checksums for field1: crc32 2576230603, innodb 3911438613, none 3735928559, stored checksum in field2 4065076226, calculated checksums for field2: crc32 2576230603, innodb 923939640, none 3735928559,  page LSN 177 441100175, low 4 bytes of LSN at page end 441143692, page number (if stored to page already) 3705319, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 126
2015-06-25T00:19:07.324640Z 0 [ERROR] [FATAL] InnoDB: The page in the doublewrite buffer is corrupt. Cannot continue operation. You can try to recover the database with innodb_force_recovery=6
2015-06-24 20:19:07 0x7f5c117fc720  InnoDB: Assertion failure in thread 140033407305504 in file ut0ut.cc line 906

However with innodb_force_recovery = 6 set  -------------------------------

2015-06-25T00:16:34.680732Z 0 [Note] InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on, skipping log redo
2015-06-25T00:16:34.681717Z 0 [ERROR] InnoDB: Change buffer must be empty when --innodb-read-only is set!

innodb_force_recovery = 4  -------------------------------

[Warning] InnoDB: Recovery skipped, --innodb-read-only set!
2015-06-25T00:17:23.396038Z 0 [Note] InnoDB: The log sequence number 725718196285 in the system tablespace does not match the log sequence number 725718196748 in the ib_logfiles!
2015-06-25T00:17:23.396050Z 0 [ERROR] InnoDB: Can't initiate database recovery, running in read-only-mode.

innodb_force_recovery = 5  -------------------------------

2015-06-25T00:18:27.073463Z 0 [Warning] InnoDB: Recovery skipped, --innodb-read-only set!
2015-06-25T00:18:27.073484Z 0 [Note] InnoDB: The log sequence number 725718196285 in the system tablespace does not match the log sequence number 725718196748 in the ib_logfiles!
2015-06-25T00:18:27.073497Z 0 [ERROR] InnoDB: Can't initiate database recovery, running in read-only-mode.

How to repeat:
Difficult to reproduce without corrupt data and my tables are too large for transmission.  

Suggested fix:
remove constraint that doublebuffer be empty on read only
[14 Apr 2018 14:58] tom fan
I've also experienced the same issue. Set --innodb_force_recovery from 1 to 6, then I don't know what else I can do. Feel very frustrated.
[27 Oct 2019 21:52] MySQL Verification Team
Sorry for the delay. Please check with latest version.
[28 Nov 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".