Mysql – innodb completely crashed after interruption

innodbMySQL

I am having a major crash on mysql affecting a lot of websites.

We had a crash on our SAN storage. Since that time, mysql refuse to start.

I tried to reinstall mysql, but still no luck. I think something must be damaged, but i don't know a lot about how to rescue mysql exactly. Please be so kind to guide me step by step 🙂

I can't start it even in recovery mode :

[root@bd1 ~]# /etc/init.d/mysqld start –innodb_force_recovery=1
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

Here is our mysql version :
mysqld –version

mysqld Ver 5.6.19 for Linux on x86_64 (MySQL Community Server (GPL))

Here are the logs i see when i restart the mysql server :

2014-06-06 21:06:58 2261 [Note] /usr/sbin/mysqld: Shutdown complete

140606 21:07:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140606 21:07:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-06-06 21:07:23 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-06-06 21:07:23 2699 [Note] Plugin 'FEDERATED' is disabled.
2014-06-06 21:07:23 2699 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-06-06 21:07:23 2699 [Note] InnoDB: The InnoDB memory heap is disabled
2014-06-06 21:07:23 2699 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-06-06 21:07:23 2699 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-06-06 21:07:23 2699 [Note] InnoDB: Using Linux native AIO
2014-06-06 21:07:23 2699 [Note] InnoDB: Not using CPU crc32 instructions
2014-06-06 21:07:23 2699 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-06-06 21:07:23 2699 [Note] InnoDB: Completed initialization of buffer pool
2014-06-06 21:07:23 2699 [Note] InnoDB: Highest supported file format is Barracuda.
2014-06-06 21:07:23 2699 [Note] InnoDB: Log scan progressed past the checkpoint lsn 6615336358
2014-06-06 21:07:23 2699 [Note] InnoDB: Database was not shutdown normally!
2014-06-06 21:07:23 2699 [Note] InnoDB: Starting crash recovery.
2014-06-06 21:07:23 2699 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-06-06 21:07:24 2699 [Note] InnoDB: Restoring possible half-written data pages 
2014-06-06 21:07:24 2699 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 6615358419
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 17388800
2014-06-06 21:07:25 2699 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 2014-06-06 21:07:25 7f829b0c1700  InnoDB: Assertion failure in thread 140198923736832 in file log0recv.cc line 1159
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
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.
31 01:07:25 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=8388608
read_buffer_size=131072
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 = 338837 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...
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 stack_bottom = 0 thread_stack 0x40000
80 81 82 83 84 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6885]
85 86 87 88 /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663234]
/lib64/libpthread.so.0(+0xf710)[0x7f82c4f8d710]
89 /lib64/libc.so.6(gsignal+0x35)[0x7f82c3c38925]
/lib64/libc.so.6(abort+0x175)[0x7f82c3c3a105]
90 91 92 93 94 /usr/sbin/mysqld[0x97d2db]
95 96 97 98 99 /usr/sbin/mysqld[0x97f544]
/usr/sbin/mysqld[0xa496cf]
Fatal signal 11 while backtracing

Best Answer

it might be not as bad. InnoDB dictionary is corrupt, but data may be still good. As long as MySQL start with innodb_force_recovery=6 dump as many tables tables as it allows:

    for d in `mysql -NBe "select distinct TABLE_SCHEMA  from information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')"`
    do
        mkdir -p "dumps/$d"
        for t in `mysql -NBe "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$d' AND TABLE_TYPE = 'BASE TABLE' AND  ENGINE='InnoDB';"`
        do
            mysqldump --skip-lock-tables $d $t > "dumps/$d/$t.sql"
            # let MySQL start if it crashed
            if [ "$?" -ne 0 ]; then sleep 30; else echo "$d/$t" >> crashed fi
        done
     done

Then take list of "crashed" tables and recover them with data recovery toolkit.

You need to parse InnoDB tables space:

 ./stream_parser -f /path/to/table.ibd

and fetch records from it:

 ./c_parser -5f pages-XXXX/FIL_PAGE_INDEX/0-<minimal id>.pages -t table.sql

Then load the dump back into MySQL(as well as dumps taken with mysqldump)

P.S. I should probably write a blog post as I answered this question hundred times here.

Related Question