MySQL Bugs: #52136: InnoDB: error clustered record for sec rec not found

Bug #52136	InnoDB: error clustered record for sec rec not found
Submitted:	17 Mar 2010 12:34	Modified:	18 Apr 2010 11:09
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1.36	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	crash, error, innodb, qc

Description:
We're having a problem in MySQL 5.1.36 - we get errors in the error log, and sometimes MySQL crashes with these errors, other times not.  It is a new machine, replacing an older machine that is almost identical in every way (see below for the differences).  The older machine did not crash with the same SQL (no SQL changes) and the same MySQL package.  The new machine was built using backups from the old machine.  

The only differences between the machines:

Linux db03 2.6.24-1-amd64 Debian GNU/Linux lenny/sid (did not crash)
Linux db04 2.6.26-2-amd64 Debian GNU/Linux 5.0 (this one is crashing)

Here's one such error:

[ERROR] MySQL thread id 703704, query id 47377652 report01.company.com 10.1.4.126 dbname
Copying to tmp table
[ERROR] SELECT /* Reporting Query 261 */
[ERROR] 	l.label_id, 
[ERROR] 	l.label_name, 
[ERROR] 	rls.display_artist_name as release_display_artist_name, 
[ERROR] 	rls.release_name, 
[ERROR] 	rls.release_id, 
[ERROR] 	va.video_id, 
[ERROR] 	va.video_name, 
[ERROR] 	va.video_type, 
[ERROR] 	va.artist_name as video_artist_name, 
[ERROR] 	va.tv, 
[ERROR] 	rls.upc, 
[ERROR] 	SUM(rrd.total_sales ) total_sales, 
[ERROR] 	SUM(rrd.rightsholder_net ) AS net_earnings, 
[ERROR] 	SUM( rrd.total_sales * aser.rate ) total_sales_nc, 
[ERROR] 	SUM( rrd.rightsholder_net * aser.rate ) AS rightsholder_net_nc, 
[ERROR] 	SUM( (rrd.total_sales * (100 - IF(l.dlabel_display_percent IS NOT NULL AND
l.dlabel_display_percent >0, l.dlabel_display_percent
[ERROR] InnoDB: Submit a detailed bug report to http://bugs.mysql.com
[ERROR]  InnoDB: error clustered record for sec rec not found
[ERROR] InnoDB: index `royalty_journal_id` of table `dbname`.`royalty_report_detail`
[ERROR] InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits
0
[ERROR]  0: len 4; hex 00163bec; asc ; ;;
[ERROR]  1: len 4; hex 0854f18f; asc T ;;
[ERROR] InnoDB: clust index record PHYSICAL RECORD: n_fields 1; compact format; info bits
0
[ERROR]  0: len 8; hex 696e66696d756d00; asc infimum ;;
[ERROR] TRANSACTION 14D426C3D, ACTIVE 6 sec, process no 12231, OS thread id 1264200016
fetching rows, thread declared inside InnoDB 318
[ERROR] mysql tables in use 27, locked 0
[ERROR] MySQL thread id 703704, query id 47377652 report01.company.com 10.1.4.126 dbname
Copying to tmp table
[ERROR] SELECT /* Reporting Query 261 */
[ERROR] 	l.label_id, 
[ERROR] 	l.label_name, 
[ERROR] 	rls.display_artist_name as release_display_artist_name, 
[ERROR] 	rls.release_name, 
[ERROR] 	rls.release_id, 
[ERROR] 	va.video_id, 
[ERROR] 	va.video_name, 
[ERROR] 	va.video_type, 
[ERROR] 	va.artist_name as video_artist_name, 
[ERROR] 	va.tv, 
[ERROR] 	rls.upc, 
[ERROR] 	SUM(rrd.total_sales ) total_sales, 
[ERROR] 	SUM(rrd.rightsholder_net ) AS net_earnings, 
[ERROR] 	SUM( rrd.total_sales * aser.rate ) total_sales_nc, 
[ERROR] 	SUM( rrd.rightsholder_net * aser.rate ) AS rightsholder_net_nc, 
[ERROR] 	SUM( (rrd.total_sales * (100 - IF(l.dlabel_display_percent IS NOT NULL AND
l.dlabel_display_percent >0, l.dlabel_display_percent
[ERROR] InnoDB: Submit a detailed bug report to http://bugs.mysql.com
[ERROR]  InnoDB: error clustered record for sec rec not found
[ERROR] InnoDB: index `royalty_journal_id` of table `dbname`.`royalty_report_detail`

How to repeat:
I'm not sure if it happens every time we run the query or only sometimes.  Also, we have repaired the table and we are still getting the errors, so it's not a corrupt index.

Suggested fix:
Not sure.

We have also gotten this crash, which may or may not be related:

100315 22:32:05 - 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.

key_buffer_size=2147483648
read_buffer_size=16777216
max_used_connections=63
max_threads=800
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 28319702 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x7fae6c2898c0
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 = 0x4bc86110 thread_stack 0x31000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8ce39e]
/usr/sbin/mysqld(handle_segfault+0x35a)[0x5e249a]
/lib/libpthread.so.0[0x7fb632c8da80]
/lib/libc.so.6(gsignal+0x35)[0x7fb631c66ed5]
/lib/libc.so.6(abort+0x183)[0x7fb631c683f3]
/usr/sbin/mysqld[0x85ecc7]
/usr/sbin/mysqld[0x827251]
/usr/sbin/mysqld[0x8279bd]
/usr/sbin/mysqld[0x77ceaa]
/usr/sbin/mysqld[0x64f404]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x6b)[0x648a8b]
/usr/sbin/mysqld[0x63db35]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x96)[0x648ab6]
/usr/sbin/mysqld[0x648edd]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x94b)[0x65c36b]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x124)[0x6583a4]
/usr/sbin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x16c)[0x65dc4c]
/usr/sbin/mysqld[0x5eff7d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1071)[0x5f3cb1]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f87a7]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfe3)[0x5f9793]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f9f26]
/usr/sbin/mysqld(handle_one_connection+0x246)[0x5ec6c6]
/lib/libpthread.so.0[0x7fb632c85fc7]
/lib/libc.so.6(clone+0x6d)[0x7fb631d0459d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2ce04640 = select round(sum(rrd.total_sales * aser.rate), 2) as total_royalty
from royalty_report_detail rrd
join a_release_sale_detail arsd on arsd.royalty_report_detail_id = rrd.royalty_report_detail_id
join royalty_report_log rrl on rrl.royalty_report_id = rrd.royalty_report_id
join a_service_payment asp on asp.service_payment_id = rrl.service_payment_id
join a_statement_exchange_rate aser on aser.rightsholder_currency_code = 'USD' and aser.service_currency_code = rrl.currency_code and aser.statement_id = asp.statement_id
where arsd.release_id = '100139'
thd->thread_id=780296
thd->killed=NOT_KILLED
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.
100315 22:32:07 mysqld_safe Number of processes running now: 0
100315 22:32:07 mysqld_safe mysqld restarted
100315 22:32:07 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
100315 22:32:08  InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1576142575442
100315 22:32:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
(and then crash recovery here)

Thank you for the report.

But version 5.1.36 is old and several crashing bugs were fixed since. Please try current version 5.1.45 and if problem still exists provide configuration file for both machines, hardware information (CPU, how much physical RAM you have), full query which causes crashes and output of SHOW CREATE TABLE and SHOW TABLE STATUS for every underlying table.

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