MySQL Bugs: #13963: SHOW SLAVE HOSTS is unreliable

Bug #13963	SHOW SLAVE HOSTS is unreliable
Submitted:	12 Oct 2005 15:47	Modified:	6 Mar 2010 19:09
Reporter:	John David Duncan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
The SHOW SLAVE HOSTS command could be very useful, but in fact its output is ambiguous and uninformative. Slaves appear in the output of SHOW SLAVE HOSTS if they have the "report-host" option set and they either *are* or *have previously been* connected. If an expected slave does not appear in the list, nobody knows whether this is because the slave is not connected or if it simply did not start with the "report-host" option. When a slave does appear in the list, there is no way of knowing whether it is currently connected or simply has been at some time in the past.

The output also contains strange information. "Rpl_recovery_rank" is not implemented, and "Port" simply indicates master's port number (which is easily available from other sources, and says absolutely nothing about the slave).

How to repeat:
Start a slave with the --report-host option. Run SHOW SLAVE HOSTS on the master. Run STOP SLAVE on the slave. Connect another slave started without the --report-host option. Run SHOW SLAVE HOSTS again.

Suggested fix:
1) If "report-host" is not set, but a slave thread is started, have report-host default to a value like "Slave_n" (where n is the server-id).

2) Remove the "Rpl_recovery_rank" column from SHOW SLAVE HOSTS unless it is implemented.

3) Remove the "Port" column or use it to indicate the client TCP port of the slave's IO thread.

4) Remove a slave from the output table after it disconnects, or add a column to the table to indicate its status as "connected" or "disconnected," or add a "last seen" timestamp column.

5) Bind "report-host" to a server global variable, at least in a read-only fashion (the only current way for a DBA to know what "report-host" is set to is to look in my.cnf).

6) When the mysqlbinlog utility connects to a master over the network, it should identify itself as "mysqlbinlog"

Reproduced using a recent 5.0bk pull under FC4

See HLS of WL#2860 for the issue which must be decided.

Related to BUG#21132.

The port doesn't show the master's port.  It shows the port the slave is configured to report with --report-host.  If this is not configure, it shows the default port (3306).

Sorry, I mean --report-port :-(  There are several other --report-X parameters too.

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

2818 Li-Bing.Song@sun.com	2009-09-24
      Bug #13963  	SHOW SLAVE HOSTS is unreliable
      
      Slaves only appear in the output of SHOW SLAVE HOSTS when report-host option
      is set. If an expected slave does not appear in the list, nobody knows
      whether the slave does not connect or has started without the "report-host"
      option. The output also contains a strange field "Rpl_recovery_rank" which has 
      never been implemented and the manual of MySQL6.0 declares that the field has
      been removed from MySQL6.0.
      
      This patch is done with these,
      According to the manual of MySQL6.0, "Rpl_recovery_rank" is removed.
      Slaves will register themself to master no matter if report_host option is set
      or not. When slaves are registering themself, their Server_ids, report_host
      and other information are together sent to master. Sever_ids are never null 
      and is unique in one replication group. Slaves always can be identified with 
      different Server_ids no matter if report_host exists.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
        'Rpl_recovery_rank' field has been removed.
     @ sql/repl_failsafe.cc
        rpl_recovery_rank has been removed.
     @ sql/slave.cc

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

2818 Li-Bing.Song@sun.com	2009-09-24
      Bug #13963  	SHOW SLAVE HOSTS is unreliable
      
      Slaves only appear in the output of SHOW SLAVE HOSTS when report-host option
      is set. If an expected slave does not appear in the list, nobody knows
      whether the slave does not connect or has started without the "report-host"
      option. The output also contains a strange field "Rpl_recovery_rank" which has 
      never been implemented and the manual of MySQL6.0 declares that the field has
      been removed from MySQL6.0.
      
      This patch is done with these,
      According to the manual of MySQL6.0, "Rpl_recovery_rank" is removed.
      Slaves will register themself to master no matter if report_host option is set
      or not. When slaves are registering themself, their Server_ids, report_host
      and other information are together sent to master. Sever_ids are never null 
      and is unique in one replication group. Slaves always can be identified with 
      different Server_ids no matter if report_host exists.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
        'Rpl_recovery_rank' field has been removed.
     @ sql/repl_failsafe.cc
        rpl_recovery_rank has been removed.
     @ sql/slave.cc

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

2818 Li-Bing.Song@sun.com	2009-09-28
      Bug #13963  	SHOW SLAVE HOSTS is unreliable
      
      Before the patch, slaves only appear in the output of SHOW SLAVE HOSTS 
      when report-host option is set. If an expected slave does not appear in 
      the list, nobody knows whether the slave does not connect or has started
      without the "report-host" option. The output also contains a strange 
      field "Rpl_recovery_rank" which has never been implemented and the manual 
      of MySQL6.0 declares that the field has been removed from MySQL6.0.
      
      This patch is done with these,
      According to the manual of MySQL5.4, "Rpl_recovery_rank" is removed.
      Slaves will register themself to master no matter if report_host option is set
      or not. When slaves are registering themselves, their Server_ids, report_host
      and other information are together sent to master. Sever_ids are never null 
      and is unique in one replication group. Slaves always can be identified with 
      different Server_ids no matter if report_host exists.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
        'Rpl_recovery_rank' field has been removed.
     @ sql/repl_failsafe.cc
        rpl_recovery_rank has been removed.
     @ sql/slave.cc

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

3636 Li-Bing.Song@sun.com	2009-10-02
      Bug #13963  	SHOW SLAVE HOSTS is unreliable
      
      Before the patch, slaves only appear in the output of SHOW SLAVE HOSTS 
      when report-host option is set. If an expected slave does not appear in  
      the list, nobody knows whether the slave does not connect or has started
      without the "report-host" option. The output also contains a strange  
      field "Rpl_recovery_rank" which has never been implemented and the manual 
      of MySQL5.4 declares that the field has been removed from MySQL5.4.
                                        
      This patch is done with these,
      According to the manual of MySQL5.4, "Rpl_recovery_rank" is removed.
      Slaves will register themselves to master no matter if report_host option is set
      or not. When slaves are registering themselves, their Server_ids, report_host
      and other information are together sent to master. Sever_ids are never null 
      and is unique in one replication group. Slaves always can be identified with  
      different Server_ids no matter if report_host exists.

Pushed into mysql-6.0-codebase-bugfixing

Pushed into mysql-6.0-codebase-bugfixing

The test case is flaky, it depends on ports in use:

CURRENT_TEST: rpl.rpl_show_slave_hosts
--- /export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-opt/mysql-test/suite/rpl/r/rpl_show_slave_hosts.result	2009-10-02 09:10:23.000000000 +0300
+++ /export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-opt/mysql-test/suite/rpl/r/rpl_show_slave_hosts.reject	2009-10-02 10:49:19.000000000 +0300
@@ -5,13 +5,13 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
 RESET SLAVE;
-CHANGE MASTER TO master_host='127.0.0.1',master_port=13000,master_user='root';
+CHANGE MASTER TO master_host='127.0.0.1',master_port=13010,master_user='root';
 START SLAVE IO_THREAD;
 SHOW SLAVE HOSTS;
 Server_id	Host	Port	Master_id
 3	slave2	3306	1
-2		13001	1
+2		13011	1

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

3637 Tor Didriksen	2009-10-02
      Bug#13963 SHOW SLAVE HOSTS is unreliable
      
      Remove dependency on port assignments from test case result.
      to reproduce failure, do e.g.
      $./mtr --mtr-build-thread=1 rpl_show_slave_hosts
     @ mysql-test/suite/rpl/r/rpl_show_slave_hosts.result
        disable log of CHANGE MASTER...
        mask column 3, which also depends on port assignments
     @ mysql-test/suite/rpl/t/rpl_show_slave_hosts.test
        disable log of CHANGE MASTER...
        mask column 3, which also depends on port assignments

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:ingo.struewing@sun.com-20091002090120-tju5sesnz8xtfb25) (merge vers: 6.0.14-alpha) (pib:12)

Documented bugfix in the 6.0.14 changelog as follows:

        Formerly, only slaves that had been started with the
        --report-hosts option were visible in the output of SHOW SLAVE
        HOSTS. Now, all slaves that are registered with the master
        appear in SHOW SLAVE HOSTS output.

Closed.

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

3136 Li-Bing.Song@sun.com	2009-10-20
            Bug #13963  	SHOW SLAVE HOSTS is unreliable
            
            Before the patch, slaves only appear in the output of SHOW SLAVE HOSTS 
            when report-host option is set. If an expected slave does not appear in  
            the list, nobody knows whether the slave does not connect or has started
            without the "report-host" option. The output also contains a strange  
            field "Rpl_recovery_rank" which has never been implemented and the manual 
            of MySQL5.4 declares that the field has been removed from MySQL5.4.
                                              
            This patch is done with these,
            According to the manual of MySQL5.4, "Rpl_recovery_rank" is removed.
            Slaves will register themselves to master no matter if report_host option is set
            or not. When slaves are registering themselves, their Server_ids, report_host
            and other information are together sent to master. Sever_ids are never null 
            and is unique in one replication group. Slaves always can be identified with  
            different Server_ids no matter if report_host exists.

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

3136 Li-Bing.Song@sun.com	2009-10-20
            Bug#13963  	SHOW SLAVE HOSTS is unreliable
            
            Before the patch, slaves only appear in the output of SHOW SLAVE HOSTS 
            when report-host option is set. If an expected slave does not appear in  
            the list, nobody knows whether the slave does not connect or has started
            without the "report-host" option. The output also contains a strange  
            field "Rpl_recovery_rank" which has never been implemented and the manual 
            of MySQL5.4 declares that the field has been removed from MySQL5.4.
                                              
            This patch is done with these,
            According to the manual of MySQL5.4, "Rpl_recovery_rank" is removed.
            Slaves will register themselves to master no matter if report_host option is set
            or not. When slaves are registering themselves, their Server_ids, report_host
            and other information are together sent to master. Sever_ids are never null 
            and is unique in one replication group. Slaves always can be identified with  
            different Server_ids no matter if report_host exists.

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

3137 Li-Bing.Song@sun.com	2009-10-23
      BUG #13963
      Postfix

Pushed into 6.0.14-alpha (revid:aelkin@mysql.com-20091202145207-zjr6kdpwm5z5jj2z) (version source revid:aelkin@mysql.com-20091202145207-zjr6kdpwm5z5jj2z) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:aelkin@mysql.com-20091201190718-ls6a6i82bs4vovf9) (version source revid:aelkin@mysql.com-20091201190718-ls6a6i82bs4vovf9) (merge vers: 5.6.0-beta) (pib:13)

Also documented in the 5.6.0 changelog. Closed.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091202211633-8reso8l6h11gw9ot) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

Added to changelog entry per IRC discussion with Mats:

      As part of the fix for this issue, the Rpl_recovery_rank column, 
      which had appeared in the output of SHOW SLAVE HOSTS in some MySQL 
      releases, was removed because the corresponding variable (also 
      removed by this fix) was never actually used.

Also updated option/variable info for Rpl_recovery_rank.

No change in status.