MySQL Bugs: #53398: InnoDB: sec index record PHYSICAL RECORD: n

Bug #53398	InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits
Submitted:	4 May 2010 7:08	Modified:	3 Sep 2010 11:13
Reporter:	Devang Modi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql Ver 14.12 Distrib 5.0.77	OS:	Linux (Linux Server release 5.4 (Tikanga))
Assigned to:		CPU Architecture:	Any
Tags:	0, bits, compact, format, INDEX, info, InnoDB:, n_fields 2, physical, record, RECORD:, sec

Description:
I found the following error in Mysql's Log file, I do not know weather the records were processed yes/no. And what this error is indicating.
---------------------------
The server is acting as slave server.

These type of error we are constantly getting since the server we configured.

On master server these errors I do not have.

Even with this we have two more slave server but on those I also did not find such error.

---------------------------

TRANSACTION 0 627635373, ACTIVE 0 sec, process no 2614, OS thread id 1093237056 fetching rows, thread declared inside InnoDB 342
mysql tables in use 3, locked 2
133 lock struct(s), heap size 30704, undo log entries 20
MySQL thread id 2, query id 3403270 Sending data
UPDATE transactivity015, specificproduct SET transactivity015.closeddatecust = '2010-03-31',  transactivity015.archupdt = IF(transactivity015.archupdt = 'P', 'U', transactivity015.archupdt) WHERE transactivity015.recid <= '150000024586277' AND transactivity015.type IN ('DE', 'PU', 'AD') AND (transactivity015.closeddatecust IS NULL OR transactivity015.closeddatecust = '0000-00-00') AND transactivity015.specificproductid = specificproduct.recid AND specificproduct.datex <= '2010-03-28' AND transactivity015.customerlocid = '307363' AND transactivity015.locationid = '14879'

InnoDB: Submit a detailed bug report to http://bugs.mysql.com
100428 17:09:48  InnoDB: error clustered record for sec rec not found
InnoDB: index `specificproductid` of table `cvt/transactivity015`
InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 80000000000933bd; asc       3 ;; 1: len 8; hex 8000886c9a2c9e87; asc    l ,  ;;

InnoDB: clust index record PHYSICAL RECORD: n_fields 22; compact format; info bits 0
 0: len 8; hex 8000886c9a2c9e86; asc    l ,  ;; 1: len 6; hex 0000256da7e2; asc   %m  ;; 2: len 7; hex 00000e01e22e04; asc      . ;; 3: len 8; hex 8000000000003a1f; asc       : ;; 4: len 2; hex 5055; asc PU;; 5: len 8; hex 8000886c98bd7275; asc    l  ru;; 6: len 8; hex 8000000000027ff0; asc         ;; 7: len 3; hex 8fb47a; asc   z;; 8: len 3; hex 800000; asc    ;; 9: len 8; hex 800000000003e2f4; asc         ;; 10: len 8; hex 8000000000093174; asc       1t;; 11: len 4; hex 00000040; asc    @;; 12: len 4; hex 0ad7233c; asc   #<;; 13: len 4; hex 0000403f; asc   @?;; 14: SQL NULL; 15: len 4; hex 00000000; asc     ;; 16: SQL NULL; 17: len 3; hex 906621; asc  f!;; 18: len 3; hex 8fb47f; asc    ;; 19: len 3; hex 906621; asc  f!;; 20: len 8; hex 8000000000086ee7; asc       n ;; 21: len 1; hex 50; asc P;;

TRANSACTION 0 730778436, ACTIVE 0 sec, process no 2614, OS thread id 1093237056 fetching rows, thread declared inside InnoDB 299
mysql tables in use 2, locked 2
1373 lock struct(s), heap size 145392
MySQL thread id 2, query id 106534070 Sending data
UPDATE transactivity015, specificproduct SET transactivity015.closeddatevend = '2010-04-28', transactivity015.archupdt = IF(transactivity015.archupdt = 'P', 'U', transactivity015.archupdt) WHERE transactivity015.recid <= '150000025625210' AND transactivity015.locationid = '14879' AND transactivity015.type IN ('DE', 'PU' , 'AD') AND (transactivity015.closeddatevend IS NULL OR transactivity015.closeddatevend = '0000-00-00') AND specificproduct.recid = transactivity015.specificproductid AND specificproduct.datex <= DATE_SUB('2010-04-25', INTERVAL 0 DAY) AND specificproduct.productid = '2953'

InnoDB: Submit a detailed bug report to http://bugs.mysql.com

----------------------------

Please suggest steps as soon as possible

Devang

How to repeat:
Never tried

Thank you for the report.

Please attach full error log file.

Thank you for the feedback.

Errors started from:

100221 23:49:31 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100221 23:49:31 [Note] Slave SQL thread initialized, starting replication in log 'teak12-bin.000003' at position 518943098, relay log './teak5-relay-bin.000007' position: 518943236
100221 23:49:31 [Note] Slave I/O thread: connected to master 'repl@10.20.20.15:3306',  replication started in log 'teak12-bin.000003' at position 518943098
100221 23:50:07 [Note] Slave I/O thread killed while reading event 
100221 23:50:07 [Note] Slave I/O thread exiting, read up to log 'teak12-bin.000003', position 519248148
100221 23:50:07 [Note] Error reading relay log event: slave SQL thread was killed
100221 23:55:35  InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `cvt/accessrights` exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying rename table `cvt/#sql-30f0_a` to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: If table `cvt/accessrights` is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.

Before these I saw you imported InnoDB tablespace and log files. Did you do this correctly? Have you tried to fix it as described at http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-backup.html?

Also there are errors just before problem described here:

100326 19:03:43 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'teak12-bin.000048' position 657840201
100326 19:03:43 [ERROR] Slave I/O thread: error reconnecting to master 'repl@10.20.20.15:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400

Did master crash that time? If so slave probably crashed because same reason. Please check master error logs.

Thank you for the feedback.

There are still not clear records at March, 26:

100326 19:03:43 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry,
log 'teak12-bin.000048' position 657840201
100326 19:03:43 [ERROR] Slave I/O thread: error reconnecting to master
'repl@10.20.20.15:3306': Error: 'Lost connection to MySQL server at 'reading initial
communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400

Did master crash that time? If so slave probably crashed because same reason. Please
check master error logs.

Thank you for the feedback.

No, lost connection to master should not lead to corruption on slave. Have you checked hard disk on slave for errors?

If hard disk is OK please upgrade to current version 5.0.91, try with it and if problem still exists please send us master, slave configuration files and binary log events from which caused corruption.

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

thanking you

Devang