Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

I am working with version 10.0.19-MariaDB-1~trusty-log and I'm only able to restart mysqld with innodb_force_recovery=6 and I don't know why.

The output of the /usr/sbin/mysqld is the following:

```
root@birdwatch:~> /usr/sbin/mysqld
151112 12:49:53 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB-1~trusty) starting as process 4603 ...
151112 12:49:53 [Note] InnoDB: Using mutexes to ref count buffer pool pagesfile=hey_prova --log-output=FILE
151112 12:49:53 [Note] InnoDB: The InnoDB memory heap is disabled
151112 12:49:53 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
151112 12:49:53 [Note] InnoDB: Memory barrier is not used
151112 12:49:53 [Note] InnoDB: Compressed tables use zlib 1.2.8
151112 12:49:53 [Note] InnoDB: Using Linux native AIO
151112 12:49:53 [Note] InnoDB: Using CPU crc32 instructions
151112 12:49:53 [Note] InnoDB: Initializing buffer pool, size = 256.0M
151112 12:49:53 [Note] InnoDB: Completed initialization of buffer pool
151112 12:49:53 [Note] InnoDB: Highest supported file format is Barracuda.
151112 12:49:53 [Note] InnoDB: Log scan progressed past the checkpoint lsn 3384148
151112 12:49:53 [Note] InnoDB: Database was not shutdown normally!
151112 12:49:53 [Note] InnoDB: Starting crash recovery.
151112 12:49:53 [Note] InnoDB: Reading tablespace information from the .ibd files...
151112 12:49:53 [Note] InnoDB: Restoring possible half-written data pages
151112 12:49:53 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3391696
151112 12:49:53 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2015-11-12 12:49:53 7f3df3bfd700 InnoDB: Assertion failure in thread 139904059168512 in file page0cur.cc line 931
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug rep

Non-zero innodb_force_recovery is a dangerous zone and you should be careful increasing its value. Yes, 6 may corrupt your data permanently, but what other options do you have if MySQL doesn't start with lower values? Besides, if you need to start MySQL with innodb_force_recovery the database is already and permanently corrupt and needs to be rebuilt. Except probably rare cases like corruption in secondary indexes.

Anyway, if MySQL started with innodb_force_recovery=6 go ahead and dump the databases with --order-by-primary and possibly with --skip-lock-tables. Then stop MySQL, move datadir to a safe place and re-create the databases from scratch.

If mysqldump crashes then corruption is too severe for innodb_force_recovery.

(A hint - dump all tables one by one, so you can find out what tables cause MySQL crash).

Then I'd invite you to out data recovery portal, where you can upload the database and download back repaired SQL dump. Of if you prefer DIY approach the same toolkit on GitHub. See instructions in one of posts on our blog.

DISCLAIMER: I'm author of both tools.

StackExchange Database Administrators Q#120864, answer score: 7