Mysql not staring when innodb is enabled

Question

I am using Centos 5 with mysql version 5.5.30. Everything was working fine until the abrupt shutdown.

It crashed mysql and am getting the following error (in mysql error log file) when starting it normally. If I disable innodb (from my.cnf), it starts mysql without any issue but only support myisam.

150324 22:42:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150324 22:42:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150324 22:42:10 [Note] libgovernor.so not found
150324 22:42:10 [Warning] option 'max_allowed_packet': unsigned value 94371840000 adjusted to 1073741824
150324 22:42:10 [Note] Plugin 'FEDERATED' is disabled.
150324 22:42:10 InnoDB: The InnoDB memory heap is disabled
150324 22:42:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150324 22:42:10 InnoDB: Compressed tables use zlib 1.2.3
150324 22:42:10 InnoDB: Using Linux native AIO
150324 22:42:10 InnoDB: Initializing buffer pool, size = 128.0M
150324 22:42:10 InnoDB: Completed initialization of buffer pool
150324 22:42:10 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 254624508054
150324 22:42: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...
InnoDB: Doing recovery: scanned up to log sequence number 254625886208
150324 22:42:56  InnoDB: Error: page 4 log sequence number 254626313846
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:56  InnoDB: Error: page 5 log sequence number 254631317961
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:56  InnoDB: Error: page 884737 log sequence number 254626106580
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:56  InnoDB: Error: page 901121 log sequence number 254628999816
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:57  InnoDB: Error: page 893070 log sequence number 254626307058
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:57  InnoDB: Error: page 900895 log sequence number 254626761099
InnoDB: is in the future! Current system log sequence number 254625886157.
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.
150324 22:42:58  InnoDB: Error: page 890447 log sequence number 254627421913
InnoDB: is in the future! Current system log sequence number 254625886157.
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: Error: trying to access page number 4294054783 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
150324 22:42:58  InnoDB: Assertion failure in thread 47830133103776 in file fil0fil.c line 4493
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.
17:12:58 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 = 338533 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/libexec/mysqld(my_print_stacktrace+0x2e)[0x7b865e]
/usr/libexec/mysqld(handle_fatal_signal+0x3e2)[0x6840d2]
/lib64/libpthread.so.0[0x305300eca0]
/lib64/libc.so.6(gsignal+0x35)[0x30520302c5]
/lib64/libc.so.6(abort+0x110)[0x3052031d70]
/usr/libexec/mysqld[0x86b338]
/usr/libexec/mysqld[0x84ab51]
/usr/libexec/mysqld[0x84b3ca]
/usr/libexec/mysqld[0x83b62b]
/usr/libexec/mysqld[0x80d3b4]
/usr/libexec/mysqld[0x80d5dc]
/usr/libexec/mysqld[0x80efae]
/usr/libexec/mysqld[0x7fcf88]
/usr/libexec/mysqld[0x7ca61a]
/usr/libexec/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x47)[0x685ff7]
/usr/libexec/mysqld[0x58304a]
/usr/libexec/mysqld(_Z11plugin_initPiPPci+0xb61)[0x586471]
/usr/libexec/mysqld[0x507c04]
/usr/libexec/mysqld(_Z11mysqld_mainiPPc+0x868)[0x50ac28]
/lib64/libc.so.6(__libc_start_main+0xf4)[0x305201d9c4]
/usr/libexec/mysqld[0x501319]
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.
150324 22:42:58 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

In order to recover innodb database, need to have mysql working with innodb support. How do I get this working?

Staring has never been the strong point of any database. Cats are better at staring. On a more serious note: Check your system time to start with. It looks like you have some hardware issues. — Norbert, Mar 24 '15 at 17:28
The system time is perfectly alright as per the timezone am in. The above log is generated once I run the command "service mysqld start" and it fails. Secondly, if I disable the Innodb support from my.cnf file, it just starts fine, if its an hardware issue, that also should not work. Hope it makes sense. — Darshit Zalavadiya, Mar 24 '15 at 17:33
You have warnings that the data in the log is in the future. Something must have gone wrong with the time in your system. Just for a try: Put your time into the future (about 24hrs) and see if it wants to recover. — Norbert, Mar 24 '15 at 17:38
@BK435 just tried that and its running from past 5 mins, fingers crossed if that works. — Darshit Zalavadiya, Mar 24 '15 at 17:45
Same error after using innodb_force_recovery = 1 as well as 2. — Darshit Zalavadiya, Mar 24 '15 at 17:48
@NorbertvanNobelen The time is correct, its in yymmdd format and am in GMT+5:30. — Darshit Zalavadiya, Mar 24 '15 at 17:53
You can try the percona recover tool method but I have been down that path before and it is not easy and could take awhile..You can try going to innodb force recovery 6...no matter what flag level you use it is always in read only mode if you can get mysql to start.. — BK435, Mar 24 '15 at 18:11

score 0 · Answer 1 · answered Mar 27 '15 at 15:30

Your data appears quite corrupt. Consider the following

Take a complete backup of the data first, as innodb_force_recover can actually corrupt data
Update my.cnf to make the database unconnectable, the general idea is to make sure no applications run queries that might crash the server. If you are using TCP you can try set port=3307, but if apps are connecting to 'localhost' which uses unix you might need to stop them.
Try starting with successively increasing values of innodb_force_recovery from 1 through 6
Once started create a mysqldump, always check at the end to see if the dump actually finished rather than stopping because the server crashes. You may need to try dumping each table one by one if the server keeps crashing on some tables.
Re-initialise a new data directory and reload the sqldump. Always move/copy the old files, don't delete, you might need to go back to them.

If you can't get this to work, try download the most recent MySQL version as it may have fixed bugs that will stop it from crashing out on the corrupt data. This is more likely than you think and worth a try.

Lastly if you are really hosed, there is InnoDB recovery software that could potentially be used but it is quite complex to use.

Mysql not staring when innodb is enabled

1 Answers1