Bug #95319 SHOW SLAVE HOST coalesces certain server_id's into one
Submitted: 9 May 13:54 Modified: 21 May 7:08
Reporter: lalit Choudhary Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6,5.7, 5.7.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication

[9 May 13:54] lalit Choudhary
Description:
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. 

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

How to repeat:
We need to use the exact same server_id  to reproduce this issue.

1. replication setup with 1 master having 2 slaves.
2. MASTER :server_id=10972233
   SLAVE1: server_id=10972166
   SLAVE2: server_id=10972188

master [localhost] {msandbox} ((none)) > SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
+------------+---------------------+---------------+-------------+--------------------------------------+
| version()  | @@hostname          | @@report_host | @@server_id | @@server_uuid                        |
+------------+---------------------+---------------+-------------+--------------------------------------+
| 5.7.26-log | lalit-ThinkPad-T480 | NULL          |    10972233 | 00022001-1111-1111-1111-111111111111 |
+------------+---------------------+---------------+-------------+--------------------------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
+------------+---------------------+---------------+-------------+--------------------------------------+
| version()  | @@hostname          | @@report_host | @@server_id | @@server_uuid                        |
+------------+---------------------+---------------+-------------+--------------------------------------+
| 5.7.26-log | lalit-ThinkPad-T480 | SBslave1      |    10972166 | 00022002-2222-2222-2222-222222222222 |
+------------+---------------------+---------------+-------------+--------------------------------------+
1 row in set (0.01 sec)

slave2 [localhost] {msandbox} ((none)) > SELECT version(), @@hostname, @@report_host, @@server_id, @@server_uuid;
+------------+---------------------+---------------+-------------+--------------------------------------+
| version()  | @@hostname          | @@report_host | @@server_id | @@server_uuid                        |
+------------+---------------------+---------------+-------------+--------------------------------------+
| 5.7.26-log | lalit-ThinkPad-T480 | SBslave2      |    10972188 | 00022003-3333-3333-3333-333333333333 |
+------------+---------------------+---------------+-------------+--------------------------------------+
1 row in set (0.00 sec)

ON master:
master [localhost] {msandbox} ((none)) > show slave hosts;                        
+-----------+----------+-------+-----------+--------------------------------------+
| Server_id | Host     | Port  | Master_id | Slave_UUID                           |
+-----------+----------+-------+-----------+--------------------------------------+
|  10972166 | SBslave1 | 22002 |  10972233 | 00022002-2222-2222-2222-222222222222 |
|  10972188 | SBslave2 | 22003 |  10972233 | 00022003-3333-3333-3333-333333333333 |
+-----------+----------+-------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

on slave1:
changed server-id=1055062
mysqld restart

master [localhost] {msandbox} ((none)) > show slave hosts;
+-----------+----------+-------+-----------+--------------------------------------+
| Server_id | Host     | Port  | Master_id | Slave_UUID                           |
+-----------+----------+-------+-----------+--------------------------------------+
|   1055062 | SBslave1 | 22002 |  10972233 | 00022002-2222-2222-2222-222222222222 |
|  10972188 | SBslave2 | 22003 |  10972233 | 00022003-3333-3333-3333-333333333333 |
+-----------+----------+-------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

On slave2:
changed server-id=1055094 

master [localhost] {msandbox} ((none)) > show slave hosts;
+-----------+----------+-------+-----------+--------------------------------------+
| Server_id | Host     | Port  | Master_id | Slave_UUID                           |
+-----------+----------+-------+-----------+--------------------------------------+
|   1055094 | SBslave2 | 22003 |  10972233 | 00022003-3333-3333-3333-333333333333 |
+-----------+----------+-------+-----------+--------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
show slave hosts command should not combine server id's.
[10 May 13:16] Glyn Astill
I am the original reporter of this bug.

From a quick delve in the 5.7 code (which I am completely unfamiliar with - so I could be way off the mark) it would appear the server_id is hashed, so I suspect this could possibly be a hash collision.

If that is correct, I would also be interested to know the reasoning behind hashing an integer value in this instance.
[21 May 7:08] Umesh Shastry
Hello Lalit,

Thank you for the report and test case.
Verified as described with 5.7.26 build.

regards,
Umesh