Bug #41613 Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE
Submitted: 19 Dec 2008 5:13 Modified: 12 Nov 2009 15:05
Reporter: Zhenxing He Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1,6.0 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[19 Dec 2008 5:13] Zhenxing He
Description:
When master shuts down, the slave will set it's internal status to MYSQL_SLAVE_RUN_NOT_CONNECT and try to reconnect, during this period, SHOW SLAVE STATUS will display Slave_IO_running as 'No', but if user run START SLAVE IO_THREAD on slave, it will get a warning says that the slave is already running.

I think this is confusing for users, and what's more, it makes the following code that we used in our test code not 100% reliable. Please also see the comments of BUG#40459.

# shutdown master
connection master;
write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.1.expect;
wait
EOF
shutdown_server 10;

# wait for master to go away
source include/wait_until_disconnected.inc;

# wait for slave I/O to stop
source include/wait_for_slave_io_to_stop.inc;

# Start master server again
append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.1.expect;
restart
EOF

# Wait for master to start up
connection master;
enable_reconnect;
source include/wait_until_connected_again.inc;

# Start slave I/O thread again
# This command can generate a warning says that slave is already running, and even if we don't
# issue this command here, the slave I/O thread is most likely running right now because the
# restart of master is usually quick enough and the slave I/O thread has not given up
# reconnecting.
connection slave;
start slave io_thread;

So the problem is, when SHOW SLAVE STATUS reports that Slave_IO_running as 'No', it does not mean that the slave I/O thread is truly stopped, it can be in the state of trying to connect/reconnect, and it seems there is no way to distinguish in this case that the slave I/O thread is stopped or connecting.

How to repeat:
See the description.

Suggested fix:
make SHOW SLAVE STATUS report Slave_IO_running as 'Connecting' or alike when connecting or reconnecting instead of 'No' so that we can tell if the slave I/O thread is stopped or connecting.
[19 Dec 2008 5:39] Zhenxing He
Sorry, there reference bug number is wrong, should be BUG#41188, not BUG#40459.
[26 Dec 2008 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62339

2708 He Zhenxing	2008-12-26
      BUG#41613 Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE
      
      There are three internal status for slave I/O thread, both
      MYSQL_SLAVE_RUN_NOT_CONNECT and MYSQL_SLAVE_NOT_RUN are reported
      as 'No' for Slave_IO_running of command SHOW SLAVE STATUS.
      
      Change MYSQL_SLAVE_RUN_NOT_CONNECT to be reported as 'Connecting'.
[20 Feb 2009 14:41] Andrei Elkin
A separate test is not required as fixes are pretty straighforward. Approved.
[26 Feb 2009 7:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67638

2815 He Zhenxing	2009-02-26
      BUG#41613 Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE
      
      There are three internal status for slave I/O thread, both
      MYSQL_SLAVE_RUN_NOT_CONNECT and MYSQL_SLAVE_NOT_RUN are reported
      as 'No' for Slave_IO_running of command SHOW SLAVE STATUS.
      
      Change MYSQL_SLAVE_RUN_NOT_CONNECT to be reported as 'Connecting'.
[27 Feb 2009 3:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67772

2818 He Zhenxing	2009-02-27
      BUG#41613 Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE
        
      There are three internal status for slave I/O thread, both
      MYSQL_SLAVE_RUN_NOT_CONNECT and MYSQL_SLAVE_NOT_RUN are reported
      as 'No' for Slave_IO_running of command SHOW SLAVE STATUS.
      
      Change MYSQL_SLAVE_RUN_NOT_CONNECT to be reported as 'Connecting'.
[24 Mar 2009 17:19] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090324171507-s5aac9guj21l0jz6) (version source revid:zhenxing.he@sun.com-20090227032116-9jpfrsov87tsorq6) (merge vers: 6.0.10-alpha) (pib:6)
[25 Mar 2009 13:08] Jon Stephens
Documented in the 6.0.11 changelog as follows:

        The value of Slave_IO_running in the output of
        SHOW SLAVE STATUS did not
        distinguish between all 3 possible states of the slave 
        I/O thread (not running; running but not connected; 
        connected). Now the value Connecting (rather than No) is
        shown when the slave I/O thread is running but the slave 
        is not connected to a replication master.

        The server system variable Slave_running also
        reflects this change, and is now consistent with what 
        is shown for Slave_IO_running.

Also updated the description of Slave_IO_running with an explanation of the possible slave I/O thread states, and the differences in displayed values for these states in different versions of the server.
[2 Oct 2009 8:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85476

3117 He Zhenxing	2009-10-02
      Backport BUG#41613 Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE
      
      There are three internal status for slave I/O thread, both
      MYSQL_SLAVE_RUN_NOT_CONNECT and MYSQL_SLAVE_NOT_RUN are reported
      as 'No' for Slave_IO_running of command SHOW SLAVE STATUS.
      
      Change MYSQL_SLAVE_RUN_NOT_CONNECT to be reported as 'Connecting'.
[27 Oct 2009 9:49] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091027094604-9p7kplu1vd2cvcju) (version source revid:zhenxing.he@sun.com-20091026140226-uhnqejkyqx1aeilc) (merge vers: 6.0.14-alpha) (pib:13)
[27 Oct 2009 18:56] Jon Stephens
Already documented for 6.0.11. Closed.
[12 Nov 2009 8:20] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091027095744-rf45u3x3q5d1f5y0) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 14:42] Jon Stephens
Bugfix also documented in the 5.5.0 changelog; updated status description in the 5.5 and 6.0 versions of the Manual. Closed.