| Bug #76522 | mysqlfailover do not set read_only to slave during preparing candidate promotion | ||
|---|---|---|---|
| Submitted: | 28 Mar 2015 22:35 | Modified: | 16 Dec 2016 0:08 |
| Reporter: | Shintaro Tabata | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Utilities | Severity: | S3 (Non-critical) |
| Version: | 1.5.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Nov 2016 10:26]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=78746 marked as duplicate of this one.
[22 Nov 2016 10:27]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=80124 marked as duplicate of this one.
[23 Nov 2016 4:21]
monty solomon
Please test and confirm that the issues raised in bugs 78746 and 80124 are indeed resolved by any fixes for this bug. 78746 is for switchover 80124 is for failover
[13 Dec 2016 17:45]
Chuck Bell
Posted by developer: Pushed to release-1.6.5
[16 Dec 2016 0:08]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL Utilities 1.6.5 release, and here's the changelog entry: Corrects a logic error when detecting and setting READ_ONLY mode on the candidate server during promotion for failover. Thank you for the bug report.
[26 Mar 2019 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
Description: During preparing candidate promotion, mysqlfailover tries to block write to slave (temp_master) in Topology._prepare_candidate_for_failover even if slave is not set read_only. However, this block do not work because Server.read_only has string value("OFF") and set query is never executed at Server.set_read_only. At Server.connect(self, log_version=False): > self.read_only = self.show_server_variable("READ_ONLY")[0][1] At Server.set_read_only(self, on=False): > # Only turn on|off read only if it were off at connect() > if not self.read_only: > return self.exec_query("SET @@GLOBAL.READ_ONLY = %s" % > "ON" if on else "OFF") > return None mysql> show variables like 'read_only'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | read_only | OFF | +------------------+-------+ 1 rows in set (0.00 sec) How to repeat: I did not check temp_master can be written. I found Server.set_read_only does not work when I tried to use this method at another case. However, I think we can check this bug by comparing repeating write log time and failover log time. Suggested fix: modify Server.connect like below mysql/utilities/common/server.py self.db_conn = None raise self.connect_error = None - self.read_only = self.show_server_variable("READ_ONLY")[0][1] + if self.show_server_variable("READ_ONLY")[0][1] == "ON": + self.read_only = True + else: + self.read_only = False def get_connection(self): """Return a new connection to the server.