Bug #57706 Innodb crash recovery failure
Submitted: 25 Oct 2010 8:13 Modified: 25 Nov 2010 9:24
Reporter: Ashwini Pai Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[25 Oct 2010 8:13] Ashwini Pai
Description:
I'm using MySQL Server 5.1. 2 days ago there was a power outage and the server crashed. There are 2 logfiles, ib_logfile0 and ib_logfile1 in the data directory.
Now the Mysql server is not coming up and is failing with the follwoing reasons. 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: Warning: database page corruption or a failed
InnoDB: file read of page 16714.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of page 16718.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of page 16734.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of page 16738.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
101024 17:05:10  InnoDB: ERROR: We were only able to scan the log up to
InnoDB: 0 2497784832, but a checkpoint was at 0 2497784975.
InnoDB: It is possible that the database is now corrupt!
101024 17:05:11  InnoDB: Error: page 5 log sequence number 0 2508785685
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:11  InnoDB: Error: page 6 log sequence number 0 2508785685
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:11  InnoDB: Error: page 16411 log sequence number 0 2497786851
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:11  InnoDB: Error: page 16714 log sequence number 0 2508785685
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:11  InnoDB: Error: page 0 log sequence number 0 2508758820
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
InnoDB: Last MySQL binlog file position 0 797370700, file name .\EduWorld-bin.000004
101024 17:05:11  InnoDB: Started; log sequence number 0 2497784975
101024 17:05:12 [Note] Recovering after a crash using EduWorld-bin
101024 17:05:12  InnoDB: Error: page 14536 log sequence number 0 2497792657
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:12  InnoDB: Error: page 14541 log sequence number 0 2497804336
InnoDB: is in the future! Current system log sequence number 0 2497784975.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:13  InnoDB: Error: page 14542 log sequence number 0 2497816200
InnoDB: is in the future! Current system log sequence number 0 2497789843.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:13  InnoDB: Error: page 14544 log sequence number 0 2497827939
InnoDB: is in the future! Current system log sequence number 0 2497794909.
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.1/en/forcing-recovery.html
InnoDB: for more information.
101024 17:05:13  InnoDB: Assertion failure in thread 2324 in file G:\mysql-5.1.47-winbuild\mysql-community-nt-5.1.47-build\storage\innobase\include\buf0buf.ic line 187
InnoDB: Failing assertion: block->state == BUF_BLOCK_FILE_PAGE
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.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
101024 17:05:13 - mysqld got exception 0xc0000005 ;
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=419430400
read_buffer_size=65536
max_used_connections=0
max_threads=160
threads_connected=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 461821 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 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...
InnoDB: Thread 2312 stopped in file .\os\os0sync.c line 271
InnoDB: Thread 2316 stopped in file .\os\os0sync.c line 574
005EEE06    mysqld.exe!buf_block_get_page_no()[buf0buf.ic:187]
0060152B    mysqld.exe!trx_undo_page_get_end()[trx0undo.ic:205]
006015BF    mysqld.exe!trx_undo_page_get_next_rec()[trx0undo.ic:270]
005FFCF0    mysqld.exe!trx_purge_get_next_rec()[trx0purge.c:840]
005FFF82    mysqld.exe!trx_purge_fetch_next_rec()[trx0purge.c:992]
00623E15    mysqld.exe!row_purge()[row0purge.c:598]
00623EFD    mysqld.exe!row_purge_step()[row0purge.c:672]
005EE285    mysqld.exe!que_thr_step()[que0que.c:1256]
005EE843    mysqld.exe!que_run_threads_low()[que0que.c:1319]
005EE9BA    mysqld.exe!que_run_threads()[que0que.c:1357]
006001E2    mysqld.exe!trx_purge()[trx0purge.c:1114]
005B4675    mysqld.exe!srv_master_thread()[srv0srv.c:2591]
75D14911    kernel32.dll!BaseThreadInitThunk()
7736E4B6    ntdll.dll!RtlInitializeExceptionChain()
7736E489    ntdll.dll!RtlInitializeExceptionChain()
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.

How to repeat:
A huge data file and log file.

Suggested fix:
Its the second time I'm coming across the same problem. The last time it happened i had to reinstall the MySQL. Can somebody suggest a better fix as to how to recover from this error.
[25 Oct 2010 9:24] Valeriy Kravchuk
What exact CPU(s) do you have? 

We had similar bugs in the past (http://bugs.mysql.com/bug.php?id=52132 is the latest example I can find), but were never able to reproduce this assertion failure.
[26 Nov 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".