Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Lately we have been pestered by infrequent sporadic database crashes on one of our MySQL instances. Each time it is due to a failed page checksum.

It happens on a specific database but spans multiple tables.

The page dump shows that the records are from vastly different times. Some are from begin 2012, some can be from end 2012 or begin 2013.

(sub-question: are InnoDB page checksums only checked when the InnoDB page is accessed or does MySQL have some type of background check randomly checking page checksums? Or implicit accessing of pages?)

As soon as the instance crashes and shuts down, I can restart it and all will be fine again, until it fails another checksum again. This seems to keep occurring, like the database is riddled with corrupt pages.

Funny thing is that the server has a replication slave which is experiencing the same issue except it keeps failing on different pages than the master.

We brought both servers down and ran extensive hardware tests on both and nothing came out of that. According to the tests the hardware is fine. (tested storage, mem, RAID controller)

If (most likely) not hardware, we thought maybe kernel, driver or other software issue. And just to be on the safe side we (temporarily) decommissioned both servers, did a logical dump of the database and imported it into a replacement set of servers (master-slave). All ran well for a little while and unfortunately the issue has risen again.

A strange thing is also that it is highly unlikely AFAIK that the applications connected to this database are accessing InnoDB pages containing such old records (we need to archive, I know. Besides the point).

Any suggestions? All we can think of now is start hammering the database to see if we can somehow gain any knowledge of this strange issue.

Excerpt from error log:

InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 776734.
InnoDB: You may have to recover from a backup. 131129 8:56:53 InnoDB: Page dump in ascii and hex (16384 bytes):
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 776734.
InnoDB: You may have to recover from a backup.
131129 8:56:53 InnoDB: Page dump in ascii and hex (16384 bytes):


InnoDB: End of page dump
131129 8:56:53 InnoDB: Page checksum 2131627764, prior-to-4.0.14-form checksum 4106206621 InnoDB: stored checksum 2131627764, prior-to-4.0.14-form stored checksum 4106206621
InnoDB: Page lsn 48 728724673, low 4 bytes of lsn at page end 728724673
InnoDB: Page number (if stored to page already) 776734,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 475
InnoDB: Page may be an index page where index id is 764
InnoDB: (index "PRIMARY" of table "database"."table")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 776734.
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.
131129 9:06:01 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
131129 9:06:01 InnoDB: Using Linux native AIO
131129 9:06:01 InnoDB: Initializing buffer pool, size = 4.0G
131129 9:06:01 InnoDB: Completed initialization of buffer pool
131129 9:06:01 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 276390983531
131129 9:06:01 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 276390983600
131129 9:06:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 1072631696, file name /server-bin.000054
131129 9:06:04 InnoDB: 1.1.4 started; log sequence number 276390983600
131129 9:06:04 [Note] Recovering after a crash using /server-bin
131129 9:06:11 [Note] Starting crash recovery...
131129 9:06:11 [Note] Crash recovery finished.
131129 9:06:11 [Note] Event Scheduler: Loaded 0 events
131129 9:06:11 [Note] mysqld: ready for connections.

share|improve this question
    
Can you please provide pertinent error log information? /var/log/mysql.err or something similar –  Craig Efrein Dec 3 '13 at 13:06
    
Is this mysql 5.1 or 5.5? I see the link referencing 5.1 but I would have thought InnoDB 1.1.4 would be in a 5.5 server. What's the specific major.minor.release version of MySQL here? Also, have you identified specific queries that caused the errors, and if so, does the same query cause the same error consistently? –  Michael - sqlbot Dec 4 '13 at 5:32
    
This is MySQL 5.5.8-log –  naschoff Dec 4 '13 at 7:10
    
When we examine the binary log, the last activity are inserts to the table. The strange thing however, is that the inserts are obviously recent and the ascii page dump of the InnoDB page that failed it's checksum, contains data that is >= a year older. –  naschoff Dec 4 '13 at 7:12
    
And thus shouldn't be accessed during this insert. (the table is linear growing) –  naschoff Dec 4 '13 at 7:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.