MySQL Bugs: #44959: InnoDB: Assertion failure in thread xxxx in file .\btr\btr0pcur.c line 217

Bug #44959	InnoDB: Assertion failure in thread xxxx in file .\btr\btr0pcur.c line 217
Submitted:	19 May 2009 18:39	Modified:	19 May 2009 21:36
Reporter:	Craig Barton	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (Server 2003 R2 Standard Edition Service Pack 2)
Assigned to:		CPU Architecture:	Any

Description:
The query shown below causes MySQL to crash with these error messages:

"InnoDB: Assertion failure in thread 372 in file .\btr\btr0pcur.c line 217"
or...
"InnoDB: Assertion failure in thread 3340 in file .\btr\btr0pcur.c line 217"

and...
"mysqld got exception 0xc0000005 ;"

This happens consistently when the query is executed in the middle of a string of queries, but it does not fail if I execute it by itself.

The queries are requested by a VB6 program via the MySQL odbc 5.1.5-win32 connector.

All tables involved are InnoDB.

tblAppUserTableAccess - contains 1906 records
tblSessionWork02 - contains 90887 records  (record count may have been different at time of error)

Running on a quad core Dell server, 4GB RAM, 3.00 GHz 
with Physical Address Extension
Microsoft Windows Server 2003 R2 Standard Edition Service Pack 2

This instance of MySQL is not yet in production status, so I can adjust it, bring it down, etc. at will.  

Thank you for taking a look at this with me!

Craig

How to repeat:
From the MySQL error log: 

query at 1F08E280=

UPDATE tblAppUserTableAccess INNER JOIN tblSessionWork02 ON (tblSessionWork02.fcurProcessNumber = 84683447753343.0014) AND (CAST(tblSessionWork02.flngField01 AS CHAR(30)) || 'XYZ' || CAST(tblSessionWork02.flngField02 AS CHAR(30)) = CAST(tblAppUserTableAccess.flngAppUserId AS CHAR(30)) || 'XYZ' || CAST(tblAppUserTableAccess.flngTableId AS CHAR(30))) SET tblAppUserTableAccess.fblnGlobalSearchDataIsDirty = -1, tblAppUserTableAccess.fblnLetUserCreateRecords = tblSessionWork02.fblnField01, tblAppUserTableAccess.fblnLetUserDeleteRecords = tblSessionWork02.fblnField02, tblAppUserTableAccess.fblnLetUserReadRecords = tblSessionWork02.fblnField03, tblAppUserTableAccess.fblnLetUserUpdateRecords = tblSessionWork02.fblnField04, tblAppUserTableAccess.fstrUserGroupNames = tblSessionWork02.fstrField01

Thank you for the bug report. Which exactly server version are you using 5.1.XX and could you provide a dump file with the tables involved (create table and insert statements)?. Thanks in advance.

MySQL Version: "MySQL 5.1.32-community-log via TCP/IP"

Thank you for the feedback. Version 32-bit or 64-bit?. Also how I asked you can you provide a dump file plus the my.ini file. See in your host_name.err file (data directory) if a call stack was printed and paste here. Thanks in advance.

my.ini

Attachment: my.ini (application/octet-stream, text), 9.32 KiB.

The version of Windows running on this server is 32 bit.

Error Log

Attachment: BugReport44959_MySQLLog.txt (text/plain), 14.37 KiB.

DDL to Create the "tblAppUserTableAccess" table

Attachment: TableStructure_TblAppUserTableAccess.txt (text/plain), 2.83 KiB.

DDL to Create the "tblSessionWork02" table

Attachment: TableStructure_TblSessionWork02.txt (text/plain), 5.47 KiB.

Thank you for the feedback. Duplicate of bug: http://bugs.mysql.com/bug.php?id=39320. Please wait for the release of 5.1.35. Thanks in advance.

Many thanks for researching that Miguel.

Craig

After reading the comments associated with duplicate bug# 39320, I had thought that setting innodb_locks_unsafe_for_binlog=1 in my.ini file and then restarting my server would have prevented this error, however the error still occurs.

Is there another workaround that you know of?

Craig: you should use repeatable read isolation level for all transactions.  read committed can cause a crash too....

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html