MySQL Bugs: #41613: Slave I/O status inconsistent between SHOW SLAVE STATUS and START SLAVE

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

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.

Sorry, there reference bug number is wrong, should be BUG#41188, not BUG#40459.

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

A separate test is not required as fixes are pretty straighforward. Approved.

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

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

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)

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.

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

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)

Already documented for 6.0.11. Closed.

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)

Bugfix also documented in the 5.5.0 changelog; updated status description in the 5.5 and 6.0 versions of the Manual. Closed.