MySQL Bugs: #30703: SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS'

Bug #30703	SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS'
Submitted:	29 Aug 2007 17:26	Modified:	12 Nov 2009 12:52
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0 (all)	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Particularly, when Slave_IO_Running is No, as reported e.g by
running `SHOW SLAVE STATUS' right after wait_for_slave_io_to_stop.inc (and no more  
activities with slave's thread is possible),
SHOW STATUS LIKE 'Slave_running' still can report YES as
registered by slow pb environments such as

https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=mysql-5.1-new-rpl&order=360

So the sequence of 

wait_for_slave_io_to_stop.inc
SHOW SLAVE STATUS
SHOW STATUS LIKE 'Slave_running'

yields

Slave_IO_Running  No
SHOW STATUS LIKE 'Slave_running' YES

whereas it must be No, NO.

The bug looks serious if a replication monitoring application relies on
"inexact" behaviour of SHOW STATUS LIKE 'Slave_running'.

Can not suggest which Category to set of
Information Schema or Replication or smth else.

How to repeat:
Execute rpl_packet.test of version prior 
Bug #30209  	rpl_packet.test: Slave_running mismatch (timing bug?)
fixes (see the link in the Description)

Suggested fix:
Consider if SLAVE STATUS LIKE handling via some select deals with some cached values instead of active_mi.{,rli}.slave_running flags.

Thank you for the report.

According to my findings, SHOW STATUS doesn't use cached results, but problem occurs when active_mi->slave_running == 1.

To repeat:

$cat t/rpl_bug30703.test 
--source include/master-slave.inc
connection slave;
SHOW STATUS LIKE 'Slave_running';
 --vertical_results
show slave status;
SHOW STATUS LIKE 'Slave_running';
 --vertical_results
show slave status;

Run test above as ./mysql-test-run.pl  --manual-gdb --record rpl_bug30703

In slave gdb session:

Breakpoint 1, mysql_parse (thd=0x5825c18, inBuf=0x5872028 "stop slave", length=10, found_semicolon=0xb0061dcc) at sql_parse.cc:5588
5588      DBUG_ENTER("mysql_parse");
(gdb) b show_slave_running
Breakpoint 2 at 0xb8921: file mysqld.cc, line 6887.
(gdb) c
Continuing.
080807 13:52:19 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/Users/apple/bzr/mysql-5.1/mysql-test/var/log/slave-relay-bin.000001' position: 4
080807 13:52:19 [Note] Slave I/O thread: connected to master 'root@127.0.0.1:9306',replication started in log 'FIRST' at position 4
080807 13:52:19 [Note] next log '/Users/apple/bzr/mysql-5.1/mysql-test/var/log/slave-relay-bin.000002' is currently active
080807 13:52:19 [Note] next log '/Users/apple/bzr/mysql-5.1/mysql-test/var/log/slave-relay-bin.000003' is currently active

Breakpoint 2, show_slave_running (thd=0x5825c18, var=0xb0060384, buff=0xb005ff34 "") at mysqld.cc:6887
6887      var->type= SHOW_MY_BOOL;
(gdb) set active_mi->slave_running=0
(gdb) c
Continuing.

Breakpoint 2, show_slave_running (thd=0x5825c18, var=0xb0060384, buff=0xb005ff34 "") at mysqld.cc:6887
6887      var->type= SHOW_MY_BOOL;
(gdb) set active_mi->slave_running=1
(gdb) c
Continuing.

$cat r/rpl_bug30703.result 
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
SHOW STATUS LIKE 'Slave_running';
Variable_name   Value
Slave_running   OFF
show slave status;
...
Slave_IO_Running        No
Slave_SQL_Running       Yes
...
SHOW STATUS LIKE 'Slave_running';
Variable_name   Slave_running
Value   ON
show slave status;
...
Slave_IO_Running        No
Slave_SQL_Running       Yes
...

Possible fix can be:

=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       2008-06-19 14:02:32 +0000
+++ sql/mysqld.cc       2008-08-07 11:43:14 +0000
@@ -6887,7 +6887,7 @@
   var->type= SHOW_MY_BOOL;
   pthread_mutex_lock(&LOCK_active_mi);
   var->value= buff;
-  *((my_bool *)buff)= (my_bool) (active_mi && active_mi->slave_running &&
+  *((my_bool *)buff)= (my_bool) (active_mi && MYSQL_SLAVE_RUN_CONNECT == active_mi->slave_running &&
                                  active_mi->rli.slave_running);
   pthread_mutex_unlock(&LOCK_active_mi);
   return 0;

But as 1 is MYSQL_SLAVE_RUN_NOT_CONNECT current behavior can be considered correct also.

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/68188

3101 Guangbao Ni	2009-03-04
      Bug #30703  SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS'
      
      SHOW SHOW STATUS LIKE 'Slave_running' command believes that 
      if active_mi->slave_running != 0, then io thread is running normally.
      But it isn't so in fact. When some errors happen to make io thread 
      try to reconnect master, then it will become transitional status 
      (MYSQL_SLAVE_RUN_NOT_CONNECT == 1), which also doesn't equal 0.
      Yet, "SHOW SLAVE STATUS" believes that only if 
      active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT, then io thread is running.
      So "SHOW SLAVE STATUS" can get the correct result.
      
      
      Fixed to make SHOW SHOW STATUS LIKE 'Slave_running' command have the same check condition
      with "SHOW SLAVE STATUS". It only believe that the io thread is running 
      when active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT.

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/69670

3102 Guangbao Ni	2009-03-19
      Fixed test cases for bug#30703

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/69689

2829 Guangbao Ni	2009-03-19
      Bug #30703  SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE STATUS'
      
      SHOW SHOW STATUS LIKE 'Slave_running' command believes that
      if active_mi->slave_running != 0, then io thread is running normally.
      But it isn't so in fact. When some errors happen to make io thread
      try to reconnect master, then it will become transitional status
      (MYSQL_SLAVE_RUN_NOT_CONNECT == 1), which also doesn't equal 0.
      Yet, "SHOW SLAVE STATUS" believes that only if
      active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT, then io thread is running.
      So "SHOW SLAVE STATUS" can get the correct result.
      
      
      Fixed to make SHOW SHOW STATUS LIKE 'Slave_running' command have the same
      check condition with "SHOW SLAVE STATUS". It only believe that the io thread
      is running when active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT.

pushed into mysql-6.0-rpl

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090324171507-s5aac9guj21l0jz6) (version source revid:gni@mysql.com-20090319151542-2r6a2l4lhty4fkaq) (merge vers: 6.0.11-alpha) (pib:6)

Documented bugfix in the 6.0.11 changelog.

See BUG#41613 for changelog entry and other docs change info.

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/85014

3113 Luis Soares	2009-09-29
      Bug #30703  SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE
      STATUS'
      
      NOTE: this is the backport to next-mr.
            
      SHOW SHOW STATUS LIKE 'Slave_running' command believes that
      if active_mi->slave_running != 0, then io thread is running normally.
      But it isn't so in fact. When some errors happen to make io thread
      try to reconnect master, then it will become transitional status
      (MYSQL_SLAVE_RUN_NOT_CONNECT == 1), which also doesn't equal 0.
      Yet, "SHOW SLAVE STATUS" believes that only if
      active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT, then io thread is running.
      So "SHOW SLAVE STATUS" can get the correct result.
            
            
      Fixed to make SHOW SHOW STATUS LIKE 'Slave_running' command have the same
      check condition with "SHOW SLAVE STATUS". It only believe that the io thread
      is running when active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT.

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/85028

3116 Luis Soares	2009-09-29
      Bug #30703  SHOW STATUS LIKE 'Slave_running' is not compatible with `SHOW SLAVE
      STATUS'
            
      NOTE: this is the backport to next-mr.
                  
      SHOW SHOW STATUS LIKE 'Slave_running' command believes that
      if active_mi->slave_running != 0, then io thread is running normally.
      But it isn't so in fact. When some errors happen to make io thread
      try to reconnect master, then it will become transitional status
      (MYSQL_SLAVE_RUN_NOT_CONNECT == 1), which also doesn't equal 0.
      Yet, "SHOW SLAVE STATUS" believes that only if
      active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT, then io thread is running.
      So "SHOW SLAVE STATUS" can get the correct result.
                  
                  
      Fixed to make SHOW SHOW STATUS LIKE 'Slave_running' command have the same
      check condition with "SHOW SLAVE STATUS". It only believe that the io thread
      is running when active_mi->slave_running == MYSQL_SLAVE_RUN_CONNECT.

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)

Also documented bugfix in the 5.5.0 changelog; closed.

Also see bug#51089