MySQL 5.6 slave Slave_IO_State is in system lock state

[Vadim Pestovnikov]

Hi all,
I was wondering if someone had the same situation.

I checked pretty much everything and cannot find out a solution for it.

My installation as follow:
CentOS 6.4 64 bit; ext4 + LVM; os, data and logs are on separate logical volumes; MySQL 5.6.14 tar.gz build 64 bit.

A few days ago, when my QAs started testing an app, I found out that all my slave became a far far away behind my master.

I found some transaction locks through "show engine innodb status" but they are gone by now.

On my master, "show processlist" gives me an absolutely normal output, I think:

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 85208
State: Waiting for next activation
Info: NULL
*************************** 3. row ***************************
Id: 688
User: rpluser
Host: db3:44039
db: NULL
Command: Binlog Dump GTID
Time: 97903
State: Writing to net
Info: NULL
*************************** 4. row ***************************
Id: 695
User: rpluser
Host: db2:36226
db: NULL
Command: Binlog Dump GTID
Time: 97897
State: Writing to net
Info: NULL
...

mysql> select * from performance_schema.threads\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 97725
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES

On my slaves, "show processlist" shows me that the system user in the system lock state:

mysql> show processlist\G
*************************** 1. row ***************************
Id: 601
User: system user
Host:
db: NULL
Command: Connect
Time: 123530
State: System lock
Info: NULL
*************************** 2. row ***************************
Id: 602
User: system user
Host:
db: NULL
Command: Connect
Time: 248924
State: Reading event from the relay log
Info: NULL
...

mysql> select * from performance_schema.threads\G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 156259
PROCESSLIST_STATE: System lock
PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES

The problem is I couldn't figure out what is "PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS".

I saw some reports that it could be MySQL 5.6. bug when you run "flush tables with read locks" on a slave, but I am afraid it is not my case.

Any comments or work around would be extremely appreciated !!!


Thanks in advance.