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:
Tags: deadlock

[9 Nov 2012 22:19] Andrzej Kotulski
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.
[9 Nov 2012 22:20] Andrzej Kotulski
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
[9 Nov 2012 22:21] Andrzej Kotulski
call stack of these three threads

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

[1 Feb 2013 4:54] Erlend Dahl
We now have a patch for this that will be pushed to an upcoming version of 5.6.
[3 Apr 2013 10:32] Jon Stephens
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.
[3 Apr 2013 10:32] Jon Stephens
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