MySQL Bugs: #70938: Table index gets corrupted

Bug #70938	Table index gets corrupted
Submitted:	18 Nov 2013 11:31	Modified:	28 Dec 2013 17:43
Reporter:	Duncan Ferguson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	mysql Ver 14.14 Distrib 5.5.33, for Lin	OS:	Linux (CentOS 5.9 64 bit)
Assigned to:		CPU Architecture:	Any

Description:
On two separate occasions over the last few months, table indexes on InnoDB tables have become corrupted requiring a table backup and restore using 'innodb_force_recovery=2'.  Details in this ticket are for the latest index corruption.

There was no different or unusual activity compared to any other time, and in fact the system would have been quieter as it was the middle of the night.  The only actions expected at this time would be a housekeeping job which runs a select  to gather a comparison record, then a delete in batches of 10,000 to remove anything older than the comparison record.

How to repeat:
Cannot reliably repeat at this time.

Except from log file
===
131109  3:59:08  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 4a9fc6f80009a5f1005aa3460045....
InnoDB: End of page dump
131109  3:59:08  InnoDB: Page checksum 1556211847, prior-to-4.0.14-form checksum 3337389443
InnoDB: stored checksum 1251985144, prior-to-4.0.14-form stored checksum 3337389443
InnoDB: Page lsn 2654 1264502520, low 4 bytes of lsn at page end 1264502520
InnoDB: Page number (if stored to page already) 632305,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 17537
InnoDB: Page may be an index page where index id is 42774
InnoDB: (index "servicecheck" of table "odw"."servicecheck_results")
InnoDB: Dump of the parent page:
131109  3:59:08  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 6e5d56a60002a1......
131109  3:59:08  InnoDB: Page checksum 1851610790, prior-to-4.0.14-form checksum 3330199627
InnoDB: stored checksum 1851610790, prior-to-4.0.14-form stored checksum 3330199627
InnoDB: Page lsn 2654 3387683117, low 4 bytes of lsn at page end 3387683117
InnoDB: Page number (if stored to page already) 172460,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 17537
InnoDB: Page may be an index page where index id is 42774
InnoDB: (index "servicecheck" of table "odw"."servicecheck_results")
InnoDB: Corruption of an index tree: table "odw"."servicecheck_results", index "servicecheck",

A recovery was tried automatically, apparently succeeded, main thread restarted and the index corruption failure started all over again

The full log will be attached shortly.

Suggested fix:
The table index does not get corrupted

The file is 5.5M in size compressed - cannot connect to ftp.oracle.com currently - seems the server is unavailable

Thank you for the report.

Please either try to split the file into two archives and upload via web interface (preferred) or upload via SFTP server as described in the "Files" tab of this bug report. Note, SFTP server will keep the file 7 days only.

MySQL error log file, part 1

Attachment: bug-mysql-err-70938-pt1.gz (application/x-gzip, text), 2.71 MiB.

MySQL error log file, part 2

Attachment: bug-mysql-err-70938-pt2.gz (application/x-gzip, text), 2.79 MiB.

Thank you for the report.

In the end of the second part of the log you have a message about the disk is full. Therefore this reminds me bug #68895.

Please, check your disk space and next time, when same issue happens, check disk space again, so we can confirm or reject that this bug is duplicate of bug #68895

Thanks for your response.

However, I don't believe the disk was full at that time. The 'disk space full' entries in the mysql error log are at 23:58 on 9th, which was during the restore.

I filled the disk up during the restore as I didn't remember to turn the binary logs off during the restore so I had to restore it twice.

There are no disk full errors during the crash in the mysql logs - the crash was aroung 03:30 on 9th

Thank you for the feedback.

> The only actions expected at this time would be a housekeeping job which runs a select  to gather a comparison record, then a delete in batches of 10,000 to remove anything older than the comparison record.

Can you repeat this crash on purpose? Say, if you run this query manually.

I expected disk issues, because such a query can create huge temporary table, which, in its turn, can lead to lack of space.

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