Bug #71420 innodb_force_recovery=6 seems to cause more problems(4,5 can corrupt sec index)
Submitted: 19 Jan 2014 18:48 Modified: 30 Jan 2014 17:41
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.5.35 OS:Any
Assigned to: Daniel Price CPU Architecture:Any

[19 Jan 2014 18:48] Shane Bester
Description:
Seen many times that users tried innodb_force_recovery=6 in a desperate situation..

At least two typical mistakes:

o. Forgot to turn off applications and/or change socket/port number in my.cnf
o. Directly try innodb_force_recovery=6 before trying to 
   startup and shutdown using 1,2,3,4,5 level.

A classic example is seeing this when trying to mysqldump a table:

InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
Version: '5.5.35' MySQL Community Server (GPL)

InnoDB: Assertion failure in thread 6820 in file btr0pcur.c line 430
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == buf_block_get_page_no(btr_pcur_get_block(cursor))

mysqld.exe!my_sigabrt_handler()[my_thr_init.c:501]
mysqld.exe!raise()[winsig.c:597]
mysqld.exe!abort()[abort.c:78]
mysqld.exe!btr_pcur_move_to_next_page()[btr0pcur.c:432]
mysqld.exe!btr_pcur_move_to_next()[btr0pcur.ic:362]
mysqld.exe!row_search_for_mysql()[row0sel.c:4685]
mysqld.exe!ha_innobase::general_fetch()[ha_innodb.cc:6194]
mysqld.exe!rr_sequential()[records.cc:461]
mysqld.exe!sub_select()[sql_select.cc:11802]
mysqld.exe!do_select()[sql_select.cc:11562]
mysqld.exe!JOIN::exec()[sql_select.cc:2386]
mysqld.exe!mysql_select()[sql_select.cc:2606]
mysqld.exe!handle_select()[sql_select.cc:297]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4627]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2178]
mysqld.exe!mysql_parse()[sql_parse.cc:5669]
mysqld.exe!dispatch_command()[sql_parse.cc:1043]
mysqld.exe!do_command()[sql_parse.cc:773]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:862]
mysqld.exe!handle_one_connection()[sql_connect.cc:783]
mysqld.exe!pthread_start()[my_winthread.c:63]
mysqld.exe!_callthreadstartex()[threadex.c:348]
mysqld.exe!_threadstartex()[threadex.c:326]

How to repeat:
Very easy to repeat on 5.5.35.

Run any workload with some DML statements on innodb.
Crash server or shutdown with innodb_fast_shutdown=2

Startup server with innodb_force_recovery=6 and try to dump the data, or
just keeping running your working with innodb_force_recovery enabled is enough to crash.

After using level 6 of recovery, it is possible that you will not be able to start the database at all anymore.

Suggested fix:
BEWARE of level 6 recovery. It is documented, but not too clearly imho:

http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

"A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures."

See commentary in bug also, and perhaps make bigger warnings in docs about how/when to use innodb_force_recovery=6 ?

http://bugs.mysql.com/bug.php?id=44176
[19 Jan 2014 19:23] MySQL Verification Team
At least make it hard to set innodb_force_recovery=6.  Add some undocumented option maybe. It's far too easy to not realize that you shouldn't actually start the instance of your production database with innodb_force_recovery=6 when reading http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

A separate physical copy should be used otherwise it'll get ruined.
[20 Jan 2014 8:16] MySQL Verification Team
From Marko:
---
yes, anything beyond 3 can permanently corrupt the database
4 can corrupt secondary indexes
5 can cause inconsistent results, also corrupt sec indexes (even if 4 did not have any effect)
[30 Jan 2014 17:41] Daniel Price
A warning has been added and content revisions made to the "Starting InnoDB on a Corrupted Database" section:

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

The latest revisions should appear soon, with the next published documentation build.

Please note that as of 5.6.15 and 5.7.3 that an innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.
[11 Feb 2016 10:49] Martin Mokrejs
Please fix the code generating stack dumps NOT to advice users to go straight for level 6 (refer to "You can try to recover the database with the my.cnf" below).

It is the right place to warn users from using levels above 3 as I learned now (too late for my case).

In summary, do not give destructive advices.

Below is an output from mysql-5.6.27.

InnoDB: End of page dump
2016-02-11 11:14:46 7fcbbb0f7740 InnoDB: uncompressed page, stored checksum in field1 2215800878, calculated checksums for field1: crc32 2024863159, innodb 27310463, none 3735928559, stored checksum in field2 537144060, calculated checksums for field2: crc32 2024863159, innodb 662309313, none 3735928559, page LSN 0 2035626805, low 4 bytes of LSN at page end 2036257994, page number (if stored to page already) 325, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an 'inode' page
InnoDB: Also the page in the doublewrite buffer is corrupt.
InnoDB: Cannot continue operation.
InnoDB: You can try to recover the database with the my.cnf
InnoDB: option:
InnoDB: innodb_force_recovery=6
2016-02-11 11:14:46 7fcbbb0f7740  InnoDB: Assertion failure in thread 140512993441600 in file buf0dblwr.cc line 559
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:14:46 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 134280 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x31)[0x7fcbba8d2751]
/usr/sbin/mysqld(handle_fatal_signal+0x3c9)[0x7fcbba678949]
/lib64/libpthread.so.0(+0x10550)[0x7fcbb8f29550]
/lib64/libc.so.6(gsignal+0x35)[0x7fcbb85833f5]
/lib64/libc.so.6(abort+0x17d)[0x7fcbb858489d]
/usr/sbin/mysqld(+0x861da4)[0x7fcbbaa86da4]
/usr/sbin/mysqld(+0x7914b4)[0x7fcbba9b64b4]
/usr/sbin/mysqld(+0x794422)[0x7fcbba9b9422]
/usr/sbin/mysqld(+0x7958c0)[0x7fcbba9ba8c0]
/usr/sbin/mysqld(+0x808e2a)[0x7fcbbaa2de2a]
/usr/sbin/mysqld(+0x750635)[0x7fcbba975635]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x55)[0x7fcbba5a7175]
/usr/sbin/mysqld(+0x4d5426)[0x7fcbba6fa426]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0x643)[0x7fcbba6fde83]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x7f8)[0x7fcbba59fbb8]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fcbb856fa95]
/usr/sbin/mysqld(+0x36fa25)[0x7fcbba594a25]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.