MySQL Bugs: #67545: 'stop slave' may cause deadlock

Bug #67545	'stop slave' may cause deadlock
Submitted:	9 Nov 2012 22:19	Modified:	3 Apr 2013 10:32
Reporter:	Andrzej Kotulski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.6.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock

Description:
Running 'stop slave' on slave may cause deadlock.

How to repeat:
Not sure how often that happens, I was running 5.6.7 slave connected to 5.1 master with some additional features not available on slave (high 'read relay log' error rate causes frequent slave I/O threads restarts). I typed stop slave in mysql console and deadlock happened.

Threads on the slave causing deadlock:
thread 799: 'stop slave' command typed in mysql console, waits for mi->run_lock
thread 764: slave I/O thread restart, waits for LOCK_status
thread 801: some thread wanting to get heartbeat period via show_heartbeat_period function, waits for active_mi

locks causing problems:
LOCK_status (owned by thread 801)
active_mi (owned by thread 799)
mi->run_lock (owned by thread 764)

So with these three jobs deadlock can happen. Below /var/log/mysql file tail and call stack are included.

log file tail:
121108 17:33:33 [Note] Error reading relay log event: slave SQL thread was killed
121108 17:33:33 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
121108 17:33:33 [Note] Slave I/O thread killed while reading event
121108 17:33:33 [Note] Slave I/O thread exiting, read up to log 'binary-logs-3307.000080', position 455431192
121108 17:33:52 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
121108 17:33:52 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
121108 17:33:52 [Note] Slave SQL thread initialized, starting replication in log 'binary-logs-3307.000080' at position 455431192, relay log '/var/lib/mysql/mysqld-relay-bin-3307.000065' position: 6745985
121108 17:33:52 [Note] Slave I/O thread: connected to master 'tfbrepl@10.162.213.33:3307',replication started in log 'binary-logs-3307.000080' at position 455431192
121108 17:33:52 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
121108 17:33:52 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master, maybe it is a *VERY OLD MASTER*. Error_code: 1193
121108 17:33:54 [Note] Error reading relay log event: slave SQL thread was killed
121108 17:33:54 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
121108 17:33:54 [Note] Slave I/O thread killed while reading event
121108 17:33:54 [Note] Slave I/O thread exiting, read up to log 'binary-logs-3307.000080', position 459744084

call stack of these three threads

Attachment: call stack.txt (text/plain), 11.25 KiB.

We now have a patch for this that will be pushed to an upcoming version of 5.6.

Fixed in 5.6+. Documented in the 5.6.11 and 5.7.1 changelogs as follows:

        STOP SLAVE could cause a deadlock when issued concurrently with
        a statement such as SHOW STATUS that retrieved the values for
        one or more of the status variables Slave_retried_transactions,
        Slave_heartbeat_period, Slave_received_heartbeats,
        Slave_last_heartbeat, or Slave_running.

Closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html