MySQL Bugs: #82153: Assertion failure in thread 139768130299648 in file btr0pcur.c line 430

Bug #82153	Assertion failure in thread 139768130299648 in file btr0pcur.c line 430
Submitted:	7 Jul 2016 20:54	Modified:	11 Jul 2016 14:52
Reporter:	Joe Abaya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	mysql55w-server-5.5.49-1.w6.x86_64	OS:	CentOS (CentOS release 6.8 (Final))
Assigned to:		CPU Architecture:	Any

Description:
Database crash during normal operation and table corruption/index issues post recovery mysql stuck in restart loop.

did a search bug database and found similar issues, but nothing similar enough to explain root cause of issue.

https://bugs.mysql.com/bug.php?id=81445

https://bugs.mysql.com/bug.php?id=81452

ESX VM showed no HW errors in /var/log/messages or VirtualCenter Console.

160707 1:44:49 InnoDB: Assertion failure in thread 139768130299648 in file btr0pcur.c line 430
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == buf_block_get_page_no(btr_pcur_get_block(cursor))
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.
01:44:49 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,
something is definitely wrong and this may fail.

How to repeat:
Have not tried to repeat issue due to production server. I have not encountered this on our development server of the same levels.

Suggested fix:
"innodb_force_recoverry=1" to restart.

We checked your database by running mysqlcheck --all-databases and got some corruptions on two tables:

1. aradial.AccountingLog

Warning : InnoDB: The B-tree of index "AcctUserIdDateIn" is corrupted.
Warning : InnoDB: The B-tree of index "AcctUserIdTransIn" is corrupted.
Warning : InnoDB: The B-tree of index "AcctUserIdYransIdByDate" is corrupted.
error : Corrupt

2. aradial.RejectionLog

Warning : InnoDB: Index 'RejectionLogMain' contains 2307151 entries, should be 2307152.
error : Corrupt

OPTIMIZE table fixed both tables, and no data appears to be missing.

Error from mysqld.log

Attachment: mysqld.log (application/octet-stream, text), 7.84 KiB.

my cnf file

Attachment: my.cnf (application/octet-stream, text), 648 bytes.

Thank you for the report.
This reminds me of Shane's Bug #71420

InnoDB: Assertion failure in thread **** in file btr0pcur.c line 430
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == buf_block_get_page_no(btr_pcur_get_block(cursor))

Most likely it is caused by the same reason as explained in Bug #71420, documentation has been updated after Bug #71420.

Umesh,

Thank you for the input those do look similar.  However we had not done any recovery greater that "innodb_force_recovery = 1" and the indexes were corrupted.  

We did also have the same issue the next day and could not get recovery to allow for DB start.  So we had to restore from mysqldump taken and manually roll up data from application logs.  

I suspect at this time that the mysql_upgrade was not run cleanly during our server migration in January.  This laid the foundation for our issues.  That were triggered by recent patching of the mysql packages.  

Thank you again I will close this Bug due to my suspicion, and now that I have restored and ensured mysql_upgrade was run.  I doubt I will see the issue again.  

-- joe

Thank you Joe for confirming.

Regards,
Umesh