Description:
See comment by Marcos Albe in bug #76522:
How to repeat:
[26 Mar 17:57] Marcos Albe
The bug still present, at least with switchover:
bm-support01 in ~/sandboxes/msb_5_7_24
○ → mysqlrpladmin --version
MySQL Utilities mysqlrpladmin version 1.6.5
License type: GPLv2
Check current slaves state:
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → mysqlrpladmin --=root:msandbox@127.0.0.1:19125 --discover-slaves-login=root:msandbox --rpl-user=msandbox:msandbox health
WARNING: Using a password on the command line interface can be insecure.
# Discovering slaves for master at 127.0.0.1:19125
# Discovering slave at 127.0.0.1:19126
# Found slave: 127.0.0.1:19126
# Discovering slave at 127.0.0.1:19127
# Found slave: 127.0.0.1:19127
# Checking privileges.
#
# Replication Topology Health:
+------------+--------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+--------+---------+--------+------------+---------+
| 127.0.0.1 | 19125 | MASTER | UP | ON | OK |
| 127.0.0.1 | 19126 | SLAVE | UP | ON | OK |
| 127.0.0.1 | 19127 | SLAVE | UP | ON | OK |
+------------+--------+---------+--------+------------+---------+
# ...done.
Check read-only status on each server:
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → ./use_all -e "SELECT @@global.read_only, @@global.port\G"
# server: 0
*************************** 1. row ***************************
@@global.read_only: 0
@@global.port: 19125
# server: 1
*************************** 1. row ***************************
@@global.read_only: 1
@@global.port: 19126
# server: 2
*************************** 1. row ***************************
@@global.read_only: 1
@@global.port: 19127
Failover and make 19126 the master:
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → mysqlrpladmin --master=root:msandbox@127.0.0.1:19125 --new-master=root:msandbox@127.0.0.1:19126 --discover-slaves-login=root:msandbox --demote-master --rpl-user=msandbox:msandbox switchover
WARNING: Using a password on the command line interface can be insecure.
# Discovering slaves for master at 127.0.0.1:19125
# Discovering slave at 127.0.0.1:19126
# Found slave: 127.0.0.1:19126
# Discovering slave at 127.0.0.1:19127
# Found slave: 127.0.0.1:19127
# Checking privileges.
# Performing switchover from master at 127.0.0.1:19125 to slave at 127.0.0.1:19126.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------+--------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+--------+---------+--------+------------+---------+
| 127.0.0.1 | 19126 | MASTER | UP | ON | OK |
| 127.0.0.1 | 19125 | SLAVE | UP | ON | OK |
| 127.0.0.1 | 19127 | SLAVE | UP | ON | OK |
+------------+--------+---------+--------+------------+---------+
# ...done.
Check read-only status...nothing changed:
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → ./use_all -e "SELECT @@global.read_only, @@global.port\G"
# server: 0
*************************** 1. row ***************************
@@global.read_only: 0
@@global.port: 19125
# server: 1
*************************** 1. row ***************************
@@global.read_only: 1
@@global.port: 19126
# server: 2
*************************** 1. row ***************************
@@global.read_only: 1
@@global.port: 19127
Now we failback again to 19125:
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → mysqlrpladmin --master=root:msandbox@127.0.0.1:19126 --new-master=root:msandbox@127.0.0.1:19125 --discover-slaves-login=root:msandbox --demote-master --rpl-user=msandbox:msandbox switchover
WARNING: Using a password on the command line interface can be insecure.
# Discovering slaves for master at 127.0.0.1:19126
# Discovering slave at 127.0.0.1:19125
# Found slave: 127.0.0.1:19125
# Discovering slave at 127.0.0.1:19127
# Found slave: 127.0.0.1:19127
# Checking privileges.
# Performing switchover from master at 127.0.0.1:19126 to slave at 127.0.0.1:19125.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------+--------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+--------+---------+--------+------------+---------+
| 127.0.0.1 | 19125 | MASTER | UP | ON | OK |
| 127.0.0.1 | 19126 | SLAVE | UP | ON | OK |
| 127.0.0.1 | 19127 | SLAVE | UP | ON | OK |
+------------+--------+---------+--------+------------+---------+
# ...done.
And now something changed, but is not what we expected! Rather the 19126 (the demoted master) became read-write (i.e. read-only is now 0):
bm-support01 in ~/sandboxes/rsandbox_5_7_24
○ → ./use_all -e "SELECT @@global.read_only, @@global.port\G"
# server: 0
*************************** 1. row ***************************
@@global.read_only: 0
@@global.port: 19125
# server: 1
*************************** 1. row ***************************
@@global.read_only: 0
@@global.port: 19126
# server: 2
*************************** 1. row ***************************
@@global.read_only: 1
@@global.port: 19127
