Bug #81106 InnoDB: error in sec index entry update in
Submitted: 16 Apr 2016 20:30 Modified: 17 May 2016 13:42
Reporter: Myroslav Mishov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.29 OS:CentOS (7.2)
Assigned to: CPU Architecture:Any

[16 Apr 2016 20:30] Myroslav Mishov
Description:
Currently I am updating my infrastructure from MySQL Community 5.6.10 to 5.6.29.
I've dumped my main database on old server and restored from mysqldump to new one. All went fine until I started replication. On new server in error.log file there are tons of statements like:

TRANSACTION 124345214, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7fdc9baaa700, query id 94971920 System lock

InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: error in sec index entry update in
InnoDB: index `userID_lastModified` of table `main2`.`tblLastModified`
InnoDB: tuple DATA TUPLE: 4 fields;
 0: len 8; hex 8000000038b07315; asc     8 s ;;
 1: len 8; hex 80052dbaf2d26f80; asc   -   o ;;
 2: len 4; hex 80000001; asc     ;;
 3: len 8; hex 800000016ee78e4e; asc     n  N;;

InnoDB: record PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000038b07315; asc     8 s ;;
 1: len 8; hex 80052d9d361e2e83; asc   - 6 . ;;
 2: len 4; hex 80000000; asc     ;;
 3: len 8; hex 80000000807291ed; asc      r  ;;

TRANSACTION 124345214, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7fdc9baaa700, query id 94971920 System lock

InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: error in sec index entry update in
InnoDB: index `lastModified` of table `main2`.`tblLastModified`
InnoDB: tuple DATA TUPLE: 3 fields;
 0: len 8; hex 80052efca654f93a; asc   .  T :;;
 1: len 4; hex 80000001; asc     ;;
 2: len 8; hex 8000000173dcea52; asc     s  R;;

InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 80052efca654d3ac; asc   .  T  ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 8; hex 80000001343fea7b; asc     4? {;;

How to repeat:
Don't know.
[16 Apr 2016 20:32] Myroslav Mishov
OS is CentOS 7.2.
[16 Apr 2016 20:42] Myroslav Mishov
Old server's OS is CentOS 6.2.
DB size appr. 500GB.

Errors appears in error.log on new server after starting  master(old_server) - slave(new_server) replication.
[17 Apr 2016 0:12] MySQL Verification Team
Hi, 

Can you show us output on slave?

SHOW CREATE TABLE `main2`.`tblLastModified`;
SELECT COUNT(*) FROM `main2`.`tblLastModified`;
CHECK TABLE `main2`.`tblLastModified` ;

Compress and upload the complete mysql error log and my.cnf from slave.
[17 Apr 2016 11:32] Myroslav Mishov
The main error log is too huge - more than 20GB and grows very quick.
So, I stopped slave, moved old log, started again and proceeded with 'stop slave;'. Then executed commands you asked the output from.
Here is the output:

| tblLastModified | CREATE TABLE `tblLastModified` (
  `itemType` int(1) NOT NULL,
  `itemId` bigint(20) NOT NULL,
  `userId` bigint(20) NOT NULL,
  `lastModified` bigint(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  PRIMARY KEY (`itemType`,`itemId`),
  KEY `userId` (`userId`),
  KEY `lastModified` (`lastModified`),
  KEY `userID_lastModified` (`userId`,`lastModified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> SELECT COUNT(*) FROM `main2`.`tblLastModified`;
+-----------+
| COUNT(*)  |
+-----------+
| 525796790 |
+-----------+
1 row in set (13 min 55.83 sec)

mysql> CHECK TABLE `main2`.`tblLastModified` ;
+-----------------------+-------+----------+---------------------------------------------------------------------------------------+
| Table                 | Op    | Msg_type | Msg_text                                                                              |
+-----------------------+-------+----------+---------------------------------------------------------------------------------------+
| main2.tblLastModified | check | Warning  | InnoDB: Index 'userId' contains 525796790 entries, should be 1150670770.              |
| main2.tblLastModified | check | Warning  | InnoDB: Index 'lastModified' contains 545283555 entries, should be 1150670770.        |
| main2.tblLastModified | check | Warning  | InnoDB: Index 'userID_lastModified' contains 545283555 entries, should be 1150670770. |
| main2.tblLastModified | check | error    | Corrupt                                                                               |
+-----------------------+-------+----------+---------------------------------------------------------------------------------------+
4 rows in set (2 hours 12 min 46.00 sec)

I don't know why is this table corrupted. I've restored from mysqldump, configured and started replication.
[17 Apr 2016 13:42] MySQL Verification Team
I honestly have no good answer for you.  I'd advise check /var/log/* for any hardware related errors (disk, memory).

Also, since this is Percona Server,  it is a bit out of our realm. Suggest you to open a bug report at their bug reporting site.
[18 May 2016 1:00] Bugs System
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".