MySQL Crashed – InnoDB

crashinnodbMySQLrecovery

Im using mysql 5.5

All of my Tables are in innoDB.

Today my app went down and the mysql was unable to start.

Here are my findings.

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.
170806 10:50:09 [Note] Plugin 'FEDERATED' is disabled.
170806 10:50:09 InnoDB: The InnoDB memory heap is disabled
170806 10:50:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170806 10:50:09 InnoDB: Compressed tables use zlib 1.2.3
170806 10:50:09 InnoDB: Using Linux native AIO
170806 10:50:09 InnoDB: Initializing buffer pool, size = 4.0G
170806 10:50:09 InnoDB: Completed initialization of buffer pool
170806 10:50:09 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170806 10:50:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170806 10:50:17  InnoDB: Error: page 7 log sequence number 302558359800
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 1 log sequence number 302441785451
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 2 log sequence number 303029483310
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 4 log sequence number 302739570567
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 11 log sequence number 301115446423
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 5 log sequence number 303030219699
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: Cleaning up trx with id 15D24474E
InnoDB: Cleaning up trx with id 15D236307
InnoDB: Cleaning up trx with id 15D2360AA
InnoDB: Cleaning up trx with id 15D2359A6
170806 10:50:19 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170806 10:50:19 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170806 10:50:19  InnoDB: Assertion failure in thread 140545344018176 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:20:19 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=7385088
read_buffer_size=131072
max_used_connections=0
read_buffer_size=131072
max_used_connections=0
max_threads=500
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 = 1101118 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
170806 10:50:19 [Note] Server socket created on IP: '0.0.0.0'.
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7add75]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x688cd4]
/lib64/libpthread.so.0(+0xf7e0)[0x7fd45a6667e0]
/lib64/libc.so.6(gsignal+0x35)[0x7fd45982d495]
/lib64/libc.so.6(abort+0x175)[0x7fd45982ec75]
/usr/sbin/mysqld[0x85c462]
/usr/sbin/mysqld[0x85c7e1]
/usr/sbin/mysqld[0x929f32]
/usr/sbin/mysqld[0x91e802]
/usr/sbin/mysqld[0x85b15a]
/usr/sbin/mysqld[0x8507f2]
/lib64/libpthread.so.0(+0x7aa1)[0x7fd45a65eaa1]
/lib64/libc.so.6(clone+0x6d)[0x7fd4598e3bcd]
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.

Then I have added innodb_force_recovery=6, So now its started,But unable to login.

InnoDB: space id 22147 did not exist in memory. Retrying an open.
170806 18:19:31  InnoDB: error: space object of table 'db/tbl',
InnoDB: space id 56405 did not exist in memory. Retrying an open.    
InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.
    InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.
    InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.

How can I identify what caused this issue and what is the fix?

Best Answer

As per your log it shows you have corruption in the InnoDB tablespace.

InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

As per MySQL documentation Here for Forcing InnoDB Recovery

you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 1

Warning

Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Before doing so, ensure that you have a backup copy of your database in case you need to recreate it. Values of 4 or greater can permanently corrupt data files. Only use an innodb_force_recovery setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and only increase the value incrementally, as necessary.

Related Question