Bug #105877 group replication node always in offline mode after switch
Description:
In the MGR cluster of three nodes, set group_REPLICATION_exit_state_Action = OFFLINE_MODE. After failover occurs, the original active node is set to offline mode as expected. After the fault occurred, the faulty node was repaired manually:
stop group_replication;
start group_replication;
Such operations. However, the original faulty node is in offline mode and is not automatically adjusted. Manual modification is required.
How to repeat:
-- 1. Before switching
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ONLINE | PRIMARY | 0 | 1 | 0 | 0 | gr_applier |
| ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
| cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
-- The original active node is configured：
-- set global group_replication_exit_state_action='OFFLINE_MODE';
-- set global group_replication_unreachable_majority_timeout=13;
-- set global group_replication_autorejoin_tries=0;
select * from performance_schema.global_variables where
VARIABLE_NAME in ('group_replication_exit_state_action','read_only','super_read_only','offline_mode');
+-------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------------------+----------------+
| group_replication_exit_state_action | OFFLINE_MODE |
| offline_mode | OFF |
| read_only | OFF |
| super_read_only | OFF |
+-------------------------------------+----------------+
-- 2. Trigger switch
-- 10.0.135.75
iptables -L
iptables -I INPUT -s 10.0.135.76 -ptcp -j DROP
iptables -I INPUT -s 10.0.135.77 -ptcp -j DROP
date # Mon Dec 13 19:57:17 CST 2021
iptables -L
-- 3. After switching state
-- 10.0.135.76（The new master node）
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
| c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | UNREACHABLE | PRIMARY | 0 | 0 | 0 | 0 | gr_applier |
| ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
| cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | PRIMARY | 0 | 0 | 0 | 0 | gr_applier |
| cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
-- 10.0.135.75（The owner node）
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
| c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ERROR | | 0 | 0 | 0 | 0 | gr_applier |
| ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | UNREACHABLE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
| cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | UNREACHABLE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier |
+--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+
+--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+
| c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ERROR | | 0 | 0 | 0 | 0 | gr_applier |
+--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+
select * from performance_schema.global_variables where
VARIABLE_NAME in ('group_replication_exit_state_action','read_only','super_read_only','offline_mode');
+-------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------------------+----------------+
| group_replication_exit_state_action | OFFLINE_MODE |
| offline_mode | ON |
| read_only | ON |
| super_read_only | ON |
+-------------------------------------+----------------+
(root@127.0.0.1) [performance_schema]> use testdb;
Database changed
(root@127.0.0.1) [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| sbtest1 |
| t1 |
| t2 |
| t3 |
+------------------+
4 rows in set (0.00 sec)
(root@127.0.0.1) [testdb]> insert into t2 values(7);
-- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
[mysql@ygsxgud01 ~]$ mysql -usysbench -psysbench -h10.0.135.75
-- ERROR 3032 (HY000): The server is currently in offline mode
-- 5. Fault Recovery policy
-- 10.0.135.75（The owner node）
iptables -F
iptables -X
date # Mon Dec 13 19:58:54 CST 2021
iptables -L
(root@127.0.0.1) [testdb]> insert into t2 values(7);
-- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql --login-path=dbadmin
start group_replication;
-- ERROR 3093 (HY000): The START GROUP_REPLICATION command failed since the group is already running.
stop group_replication;
start group_replication;
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
| ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | PRIMARY | 0 | 1 | 13603 | 0 | gr_applier |
| c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ONLINE | SECONDARY | 0 | 1 | 0 | 0 | gr_applier |
| cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 1 | 13603 | 0 | gr_applier |
+--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+
-- Group Replication is still in the Offline state after being restarted and needs to be manually handled
+-------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------------------+----------------+
| group_replication_exit_state_action | OFFLINE_MODE |
| offline_mode | ON |
| read_only | ON |
| super_read_only | ON |
+-------------------------------------+----------------+
set global offline_mode=off;
+-------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------------------+----------------+
| group_replication_exit_state_action | OFFLINE_MODE |
| offline_mode | OFF |
| read_only | ON |
| super_read_only | ON |
+-------------------------------------+----------------+
Suggested fix:
After Group Replication is restarted, the offline_mode mode should be adjusted when Group Replication exits.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: In the MGR cluster of three nodes, set group_REPLICATION_exit_state_Action = OFFLINE_MODE. After failover occurs, the original active node is set to offline mode as expected. After the fault occurred, the faulty node was repaired manually: stop group_replication; start group_replication; Such operations. However, the original faulty node is in offline mode and is not automatically adjusted. Manual modification is required. How to repeat: -- 1. Before switching +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ONLINE | PRIMARY | 0 | 1 | 0 | 0 | gr_applier | | ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | | cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ -- The original active node is configured： -- set global group_replication_exit_state_action='OFFLINE_MODE'; -- set global group_replication_unreachable_majority_timeout=13; -- set global group_replication_autorejoin_tries=0; select * from performance_schema.global_variables where VARIABLE_NAME in ('group_replication_exit_state_action','read_only','super_read_only','offline_mode'); +-------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------------------+----------------+ | group_replication_exit_state_action | OFFLINE_MODE | | offline_mode | OFF | | read_only | OFF | | super_read_only | OFF | +-------------------------------------+----------------+ -- 2. Trigger switch -- 10.0.135.75 iptables -L iptables -I INPUT -s 10.0.135.76 -ptcp -j DROP iptables -I INPUT -s 10.0.135.77 -ptcp -j DROP date # Mon Dec 13 19:57:17 CST 2021 iptables -L -- 3. After switching state -- 10.0.135.76（The new master node） +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ | c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | UNREACHABLE | PRIMARY | 0 | 0 | 0 | 0 | gr_applier | | ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | | cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | PRIMARY | 0 | 0 | 0 | 0 | gr_applier | | cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ -- 10.0.135.75（The owner node） +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ | c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ERROR | | 0 | 0 | 0 | 0 | gr_applier | | ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | UNREACHABLE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | | cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | UNREACHABLE | SECONDARY | 0 | 0 | 0 | 0 | gr_applier | +--------------------------------------+-------------+--------+-------------+-----------+----------+---------+-------+----------+------------+ +--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+ | c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ERROR | | 0 | 0 | 0 | 0 | gr_applier | +--------------------------------------+-------------+--------+---------+---------+----------+---------+-------+----------+------------+ select * from performance_schema.global_variables where VARIABLE_NAME in ('group_replication_exit_state_action','read_only','super_read_only','offline_mode'); +-------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------------------+----------------+ | group_replication_exit_state_action | OFFLINE_MODE | | offline_mode | ON | | read_only | ON | | super_read_only | ON | +-------------------------------------+----------------+ (root@127.0.0.1) [performance_schema]> use testdb; Database changed (root@127.0.0.1) [testdb]> show tables; +------------------+ | Tables_in_testdb | +------------------+ | sbtest1 | | t1 | | t2 | | t3 | +------------------+ 4 rows in set (0.00 sec) (root@127.0.0.1) [testdb]> insert into t2 values(7); -- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement [mysql@ygsxgud01 ~]$ mysql -usysbench -psysbench -h10.0.135.75 -- ERROR 3032 (HY000): The server is currently in offline mode -- 5. Fault Recovery policy -- 10.0.135.75（The owner node） iptables -F iptables -X date # Mon Dec 13 19:58:54 CST 2021 iptables -L (root@127.0.0.1) [testdb]> insert into t2 values(7); -- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement mysql --login-path=dbadmin start group_replication; -- ERROR 3093 (HY000): The START GROUP_REPLICATION command failed since the group is already running. stop group_replication; start group_replication; +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | master_uuid | member_host | mgrpot | mgrstat | mgrrole | cfct_qsz | apl_qsz | ckd_t | cfct_dtd | chan_name | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ | ca26d8f2-40a3-11ec-a3a0-0050569bbf6d | 10.0.135.76 | 3306 | ONLINE | PRIMARY | 0 | 1 | 13603 | 0 | gr_applier | | c8a7b448-40a3-11ec-a514-0050569be145 | 10.0.135.75 | 3306 | ONLINE | SECONDARY | 0 | 1 | 0 | 0 | gr_applier | | cb9a5813-40a3-11ec-a099-0050569bdf16 | 10.0.135.77 | 3306 | ONLINE | SECONDARY | 0 | 1 | 13603 | 0 | gr_applier | +--------------------------------------+-------------+--------+---------+-----------+----------+---------+-------+----------+------------+ -- Group Replication is still in the Offline state after being restarted and needs to be manually handled +-------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------------------+----------------+ | group_replication_exit_state_action | OFFLINE_MODE | | offline_mode | ON | | read_only | ON | | super_read_only | ON | +-------------------------------------+----------------+ set global offline_mode=off; +-------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------------------+----------------+ | group_replication_exit_state_action | OFFLINE_MODE | | offline_mode | OFF | | read_only | ON | | super_read_only | ON | +-------------------------------------+----------------+ Suggested fix: After Group Replication is restarted, the offline_mode mode should be adjusted when Group Replication exits.