MySQL Bugs: #81106: InnoDB: error in sec index entry update in

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

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.

OS is CentOS 7.2.

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.

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.

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.

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.

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