Bug #95319 SHOW SLAVE HOST coalesces certain server_id's into one

• Submitted: 9 May 2019 13:54
• Modified: 21 May 2019 7:08
• Reporter: lalit Choudhary
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.6,5.7, 5.7.26
• OS: Any
• CPU Architecture: Any
• Tags: replication

[9 May 2019 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 2019 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 2019 7:08] MySQL Verification Team

Hello Lalit,

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

regards,
Umesh