MySQL Bugs: #64517: MySQL Crashed. Database Corruption. Force Recovery not working

Bug #64517	MySQL Crashed. Database Corruption. Force Recovery not working
Submitted:	1 Mar 2012 19:57	Modified:	19 Mar 2013 19:57
Reporter:	Werner Maisl	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1.49	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	corruption, crash, Force Recovery

Description:
I get this error,when I try to start mysqld:

;InnoDB: End of page dump
120301 20:40:21  InnoDB: Page checksum 1432376437, prior-to-4.0.14-form checksum 3833993753
InnoDB: stored checksum 119512179, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 152942849, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 4,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 4294967295 0
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

When I try to start mysqld with force_recovery I get this error:

;InnoDB: End of page dump
120301 20:53:18  InnoDB: Page checksum 4169226463, prior-to-4.0.14-form checksum 299649398
InnoDB: stored checksum 4169226463, prior-to-4.0.14-form stored checksum 299649398
InnoDB: Page lsn 0 166124781, low 4 bytes of lsn at page end 166124781
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be a file space header page
120301 20:53:18InnoDB: Error: trying to access a stray pointer 0x80007f19099a7ff8
InnoDB: buf pool start is at 0x7f1909994000, end at 0x7f190a194000
InnoDB: Probable reason is database corruption or memory
InnoDB: corruption. If this happens in an InnoDB database recovery, see
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: how to force recovery.
120301 20:53:18  InnoDB: Assertion failure in thread 139745596950304 in file ../../../storage/innobase/include/buf0buf.ic line 264
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.
120301 20:53:18 - 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=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346514 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...
stack_bottom = (nil) thread_stack 0x30000
mysqld(my_print_stacktrace+0x29) [0x7f190f120829]
mysqld(handle_segfault+0x404) [0x7f190ee2ab14]
/lib/libpthread.so.0(+0xeff0) [0x7f190e68cff0]
/lib/libc.so.6(gsignal+0x35) [0x7f190d12f1b5]
/lib/libc.so.6(abort+0x180) [0x7f190d131fc0]
mysqld(page_cur_search_with_match+0x938) [0x7f190f0537d8]
mysqld(btr_cur_search_to_nth_level+0x629) [0x7f190eff06a9]
mysqld(btr_pcur_open_on_user_rec+0x64) [0x7f190f0ae6a4]
mysqld(+0x553e8f) [0x7f190f00de8f]
mysqld(dict_load_sys_table+0x59) [0x7f190f013e69]
mysqld(dict_boot+0x944) [0x7f190f0af514]
mysqld(innobase_start_or_create_for_mysql+0x11c2) [0x7f190f088212]
mysqld(+0x5257eb) [0x7f190efdf7eb]
mysqld(ha_initialize_handlerton(st_plugin_int*)+0x31) [0x7f190ef274e1]
mysqld(+0x4fd702) [0x7f190efb7702]
mysqld(plugin_init(int*, char**, int)+0x7d8) [0x7f190efbac68]
mysqld(+0x372a7d) [0x7f190ee2ca7d]
mysqld(main+0x1ee) [0x7f190ee2fdde]
/lib/libc.so.6(__libc_start_main+0xfd) [0x7f190d11bc8d]
mysqld(+0x29b279) [0x7f190ed55279]
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.

My last backup is more than one week old, is there a way to recover my data ?

How to repeat:
I dont know, how to repeat the error. It suddenly occoured on Monday (27.02.2012) in the evening.

Can you first compress and upload the entire mysql error log, from as far back as you have it.  Thanks,

Syslog when the crash occoured

Attachment: syslog (application/octet-stream, text), 52.92 KiB.

I have attached the syslog when the crash occoured.
MySQL is logged to the syslog (because of plesk 10.4 ).
Do u need the whole syslog or only the mysql part ?

is there anything to indicate disk failures near the time ? dmesg, /var/log/messages errors could tell. page nearly fully zeroed out is suspicious.  what about any expected restarts prior to the crash?

There Havnt been a restart in the last two weeks.
And there is nothing inside /var/log/messages, that indicates a disk error ( I have chcked them also)

Please, send your my.cnf file content. What exact version, 5.1.x, is this?

my.cnf

Attachment: my.cnf (application/octet-stream, text), 3.46 KiB.

contend of /etc/mysql/conf.d

Attachment: mysqld_safe_syslog.cnf (application/octet-stream, text), 21 bytes.

The MySQL Version is 5.1.49-3

Thank you for the feedback.

Have you tried innodb_force_recovery=6? Did you restart your computer after crash as suggested by InnoDB:

It is also possible that your operating
system has corrupted its own file cache
and rebooting your computer removes the
error.

?

I have tried Have you tried innodb_force_recovery from 1 to 6
And I have restarted the Computer serval times.

Since I have not been able to fix that error, I have set up a fresh system, but I have safed the the Log Directory (and serval other directories) as well as the mysql data directory

Thank you for the feedback.

If you can upload data and log files, please do it.

Thank you for the files.

Unfortunately I missed them in time and seems that nobody else got them from FTP server as well, so they got expired. Please re-upload them if you still have those files.

I am sorry for this.

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".