From: Andrew Miklas Date: October 4 2012 1:45am Subject: InnoDB corrupt after power failure List-Archive: http://lists.mysql.com/mysql/228313 Message-Id: <25257811-6BF7-4112-8EAF-244C58F83DDC@pagerduty.com> MIME-Version: 1.0 (Apple Message framework v1278) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi guys, I recently had a data corruption issue with InnoDB. MySQL was shut down = improperly (power failure), and when the system came back up, MySQL = refused to start. On inspection of the logs (see below), it looks like = the tablespace became seriously corrupted. In the end, I had to rebuild = the slave using mysqldump. I'm curious what happened here, since I thought InnoDB wasn't supposed = to become corrupted on an improper shutdown. One possibility that we = were exploring was that the filesystem journal setting was incorrect. = We were using ext3 with the journal set to writeback mode. Is this a = known bad config with InnoDB? Thanks for any help, Andrew ------- MySQL server version: Server version: 5.5.27-1~ppa1~lucid-log (Ubuntu) (Running on Ubuntu 10.04.2 LTS) 120831 20:56:01 InnoDB: The InnoDB memory heap is disabled 120831 20:56:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120831 20:56:01 InnoDB: Compressed tables use zlib 1.2.3.3 120831 20:56:02 InnoDB: Initializing buffer pool, size =3D 5.0G 120831 20:56:03 InnoDB: Completed initialization of buffer pool 120831 20:56:06 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 62096393185 120831 20:56:06 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 space 0 page 230. 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 space 0 page 373. 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 space 0 page 214. 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 space 0 page 222. 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 space 0 page 2673. 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 space 0 page 2681. 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 space 0 page 46. 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 space 0 page 62. InnoDB: Trying to recover it from the doublewrite buffer. InnoDB: Recovered the page from the doublewrite buffer. InnoDB: Doing recovery: scanned up to log sequence number 62096881152 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 198. InnoDB: You may have to recover from a backup. 120831 20:56:33 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex {Big dump here -- I can supply if needed} InnoDB: End of page dump 120831 20:56:33 InnoDB: Page checksum 3859504003, prior-to-4.0.14-form = checksum 1080681687 InnoDB: stored checksum 3859504003, prior-to-4.0.14-form stored checksum = 3870577874 InnoDB: Page lsn 14 1966349405, low 4 bytes of lsn at page end = 1966973261 InnoDB: Page number (if stored to page already) 198, InnoDB: space id (if created with >=3D MySQL-4.1.1 and stored already) 0 InnoDB: Page may be a system page InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 198. 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.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. 120831 20:56:33 InnoDB: Assertion failure in thread 140548948399904 in = file buf0buf.c line 3609 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. 20:56:33 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,=20 something is definitely wrong and this may fail. key_buffer_size=3D16777216 read_buffer_size=3D131072 max_used_connections=3D0 max_threads=3D800 thread_count=3D0 connection_count=3D0 It is possible that mysqld could use up to=20 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =3D = 1766302 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 =3D 0 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7fd41a7afdee] /usr/sbin/mysqld(handle_fatal_signal+0x493)[0x7fd41a6695d3] /lib/libpthread.so.0(+0xf8f0)[0x7fd419e2a8f0] /lib/libc.so.6(gsignal+0x35)[0x7fd4188b6a75] /lib/libc.so.6(abort+0x180)[0x7fd4188ba5c0] /usr/sbin/mysqld(+0x61f36f)[0x7fd41a87936f] /usr/sbin/mysqld(+0x62bc08)[0x7fd41a885c08] /usr/sbin/mysqld(+0x62c38a)[0x7fd41a88638a] /usr/sbin/mysqld(+0x61dfef)[0x7fd41a877fef] /usr/sbin/mysqld(+0x5f12f0)[0x7fd41a84b2f0] /usr/sbin/mysqld(+0x5f1728)[0x7fd41a84b728] /usr/sbin/mysqld(+0x5f4533)[0x7fd41a84e533] /usr/sbin/mysqld(+0x5e0615)[0x7fd41a83a615] /usr/sbin/mysqld(+0x5b3f5e)[0x7fd41a80df5e] = /usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x7fd4= 1a66bb21] /usr/sbin/mysqld(+0x30c011)[0x7fd41a566011] /usr/sbin/mysqld(_Z11plugin_initPiPPci+0xa34)[0x7fd41a56a124] /usr/sbin/mysqld(+0x28304d)[0x7fd41a4dd04d] /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x58b)[0x7fd41a4e122b] /lib/libc.so.6(__libc_start_main+0xfd)[0x7fd4188a1c4d] /usr/sbin/mysqld(+0x27e48d)[0x7fd41a4d848d] 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.