Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2545

CLONE - "SHOW SLAVE STATUS" coalesces certain server_id's into one

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:

      Description

      I've hit a situation where a specific set of slave server ids appear to obscure one another in "SHOW SLAVE HOSTS" on the master - just as if we'd used the same value for server_id. 

      This was observed on PXC running as individual/standalone nodes in version "5.7.16-10-57-log".  I'm assuming this is present in upstream mysql 5.7.16; however I have not checked this yet.  (I have done a brief test in mysql 8.0.15, just because I had it to hand, and it showed showed no sign of the issue)

      The server IDs used to produce this in isolation were 1055094 and 1055062.

      A delve into the upstream mysql 5.7 source would appear to show the server_id values getting hashed with my_calc_hash - why you would hash an integer in this way is beyond me - but I have no familiarity with the code so may be looking in totally the wrong area.

      Reproducible test case:

       

      MASTER
      (localhost) [(none)]> SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
      ------------------------------------------------------------------------------------------------------------------------

      version() @@hostname @@report_host @@server_id @@server_uuid

      ------------------------------------------------------------------------------------------------------------------------

      5.7.16-10-57-log pxc-test-single-233.euw1a pxc-test-single-233-eu-west-1a 10972233 2b440df3-6679-11e9-87de-0afa4531bb18

      ------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      (localhost) [(none)]> CREATE USER 'm57_slave'@'10.9.72.0/255.255.255.0' IDENTIFIED WITH mysql_native_password BY 'flaps';
      Query OK, 0 rows affected (0.03 sec)

      (localhost) [(none)]> GRANT REPLICATION SLAVE ON . TO 'm57_slave'@'10.9.72.0/255.255.255.0';
      Query OK, 0 rows affected (0.00 sec)

      SLAVE1:
      (localhost) [(none)]> SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
      ------------------------------------------------------------------------------------------------------------------------

      version() @@hostname @@report_host @@server_id @@server_uuid

      ------------------------------------------------------------------------------------------------------------------------

      5.7.16-10-57-log pxc-test-single-166.euw1a pxc-test-single-166-eu-west-1a 10972166 2a4187e8-6679-11e9-878a-0a39661b4314

      ------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      (localhost) [(none)]> CHANGE MASTER TO MASTER_HOST='10.9.72.233', MASTER_USER='m57_slave', MASTER_PASSWORD='flaps', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'm57_slave';
      (localhost) [(none)]> START SLAVE;

      SLAVE2:

      (localhost) [(none)]> SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
      ------------------------------------------------------------------------------------------------------------------------

      version() @@hostname @@report_host @@server_id @@server_uuid

      ------------------------------------------------------------------------------------------------------------------------

      5.7.16-10-57-log pxc-test-single-188.euw1a pxc-test-single-188-eu-west-1a 10972188 2a6580d5-6679-11e9-892e-0a8988209930

      ------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      (localhost) [(none)]> CHANGE MASTER TO MASTER_HOST='10.9.72.233', MASTER_USER='m57_slave', MASTER_PASSWORD='flaps', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'm57_slave';
      (localhost) [(none)]> START SLAVE;

      MASTER:
      (localhost) [(none)]> show slave hosts;
      ------------------------------------------------------------------------------------------

      Server_id Host Port Master_id Slave_UUID

      ------------------------------------------------------------------------------------------

      10972188 pxc-test-single-188-eu-west-1a 3306 10972233 2a6580d5-6679-11e9-892e-0a8988209930
      10972166 pxc-test-single-166-eu-west-1a 3306 10972233 2a4187e8-6679-11e9-878a-0a39661b4314

      ------------------------------------------------------------------------------------------
      2 rows in set (0.00 sec)

      SLAVE1:
      Change server_id=1055062
      service mysql restart
      (localhost) [(none)]> SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
      ------------------------------------------------------------------------------------------------------------------------

      version() @@hostname @@report_host @@server_id @@server_uuid

      ------------------------------------------------------------------------------------------------------------------------

      5.7.16-10-57-log pxc-test-single-166.euw1a pxc-test-single-166-eu-west-1a 1055062 2a4187e8-6679-11e9-878a-0a39661b4314

      ------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      MASTER:
      (localhost) [(none)]> show slave hosts;
      ------------------------------------------------------------------------------------------

      Server_id Host Port Master_id Slave_UUID

      ------------------------------------------------------------------------------------------

      1055062 pxc-test-single-166-eu-west-1a 3306 10972233 2a4187e8-6679-11e9-878a-0a39661b4314
      10972188 pxc-test-single-188-eu-west-1a 3306 10972233 2a6580d5-6679-11e9-892e-0a8988209930

      ------------------------------------------------------------------------------------------
      2 rows in set (0.00 sec)

      SLAVE2:
      server_id = 1055094
      service mysql restart
      (localhost) [(none)]> SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
      ------------------------------------------------------------------------------------------------------------------------

      version() @@hostname @@report_host @@server_id @@server_uuid

      ------------------------------------------------------------------------------------------------------------------------

      5.7.16-10-57-log pxc-test-single-188.euw1a pxc-test-single-188-eu-west-1a 1055094 2a6580d5-6679-11e9-892e-0a8988209930

      ------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      (localhost) [(none)]> show slave hosts;
      ------------------------------------------------------------------------------------------

      Server_id Host Port Master_id Slave_UUID

      ------------------------------------------------------------------------------------------

      1055094 pxc-test-single-188-eu-west-1a 3306 10972233 2a6580d5-6679-11e9-892e-0a8988209930

      ------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      (localhost) [(none)]> show processlist;
      ----------------------------------------------------------------------------------------------------------------------------------------------------------+

      Id User Host db Command Time State Info Rows_sent Rows_examined

      ----------------------------------------------------------------------------------------------------------------------------------------------------------+

      129 m57_slave 10.9.72.166:36284 NULL Binlog Dump GTID 1010 Master has sent all binlog to slave; waiting for more updates NULL 0 0
      138 m57_slave 10.9.72.188:41760 NULL Binlog Dump GTID 919 Master has sent all binlog to slave; waiting for more updates NULL 0 0
      216 root localhost NULL Query 0 starting show processlist 0 0

      ----------------------------------------------------------------------------------------------------------------------------------------------------------+
      3 rows in set (0.00 sec)

       

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  glynastill Glyn Astill
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: