1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
#
# The IO thread MUST not connect to RECOVERING group member and should
# get re-connect to ONLINE member of the group as recovery can take time
# and that would unnecessary delay receiver in getting binary logs.
#
# Test:
#
# 0. The test requires 3 servers:
# server1: primary
# server2: secondary
# server3: single server replica
# 1. Bootstrap group with server1
# 2. Lock server2 on recovery and join it to the group
# 3. Establish managed connection between server3 and server2 secondary
# and locked on recovery. Secondaries priority is higher than primary
# 4. Create and start new channel ch1_3: server2(source),
# server3(replica)
# 5. Confirm server1 and server2 members of group are added to
# performance_schema.replication_asynchronous_connection_failover.
# 6. Confirm connection is done with server1 due to server2 being on
# recovery.
# 7. Wait 30 seconds and validate that replica is still connected
# to server1.
# 8. After UNLOCK tables it shall reconnect to server2
# 9. Cleanup
#
--source include/have_group_replication_plugin.inc
--let $rpl_skip_group_replication_start= 1
--let $rpl_server_count= 3
--let $rpl_group_replication_single_primary_mode=1
--source include/group_replication.inc
--echo
--echo # 1. Bootstrap group with server1
--echo
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
set session sql_log_bin=0;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY , b INT);
set session sql_log_bin=1;
--source include/start_and_bootstrap_group_replication.inc
INSERT INTO test.t1 (b) VALUES (1);
--let $member1_uuid = `SELECT @@GLOBAL.server_uuid`
--let $_tmp_gr_group_name= `SELECT @@GLOBAL.group_replication_group_name`
--echo
--echo # 2. Lock server2 on recovery and join it to the group
--echo
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
set session sql_log_bin=0;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY , b INT);
set session sql_log_bin=1;
--let $rpl_connection_name= server_2
--source include/rpl_connection.inc
LOCK TABLES t1 READ;
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
--let $member2_uuid = `SELECT @@GLOBAL.server_uuid`
--let $group_replication_start_member_state= RECOVERING
--source include/start_group_replication.inc
--echo
--echo # 3. Establish managed connection between server3 and server2 secondary
--echo # and locked on recovery. Secondaries priority is higher than primary
--echo
--let $rpl_connection_name= server3
--source include/rpl_connection.inc
set session sql_log_bin=0;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY , b INT);
set session sql_log_bin=1;
--replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 $_tmp_gr_group_name FAILOVER_GROUP_NAME
--eval SELECT asynchronous_connection_failover_add_managed('ch1_3', 'GroupReplication', '$_tmp_gr_group_name', '127.0.0.1', $SERVER_MYPORT_2, '', 60, 80);
--echo
--echo # 4. Create and start new channel ch1_3: server2(source),
--echo # server3(replica)
--echo
--replace_result $SERVER_MYPORT_2 SERVER_2_PORT
--eval CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_AUTO_POSITION=1, SOURCE_CONNECTION_AUTO_FAILOVER=1, SOURCE_PORT=$SERVER_MYPORT_2, SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=1 FOR CHANNEL 'ch1_3'
--let $rpl_channel_name='ch1_3'
--source include/start_slave.inc
--let $rpl_channel_name=
--echo
--echo # 5. Confirm server1 and server2 members of group are added to
--echo # performance_schema.replication_asynchronous_connection_failover.
--echo
--let $wait_condition= SELECT COUNT(*)=2 FROM performance_schema.replication_asynchronous_connection_failover;
--source include/wait_condition.inc
--echo
--echo # 6. Confirm connection is done with server1 due to server2 being on
--echo # recovery.
--echo
--let $wait_condition= SELECT COUNT(*)=1 COUNT FROM performance_schema.replication_connection_status WHERE channel_name = "ch1_3" AND source_uuid = "$member1_uuid"
--source include/wait_condition.inc
--echo
--echo # 7. Wait 30 seconds and validate that replica is still connected
--echo # to server1.
--echo
--sleep 30
--let $assert_text= Verify server1 is on replication_asynchronous_connection_failover
--let $assert_cond= [SELECT count(*) COUNT FROM performance_schema.replication_connection_status WHERE channel_name = "ch1_3" AND source_uuid = "$member1_uuid", COUNT, 1] = "1"
--source include/assert.inc
--echo
--echo # 8. After UNLOCK tables it shall reconnect to server2
--echo
--let $rpl_connection_name= server_2
--source include/rpl_connection.inc
UNLOCK TABLES;
--let $group_replication_member_state= ONLINE
--source include/gr_wait_for_member_state.inc
--let $rpl_connection_name= server3
--source include/rpl_connection.inc
--let $wait_condition= SELECT COUNT(*)=1 COUNT FROM performance_schema.replication_connection_status WHERE channel_name = "ch1_3" AND source_uuid = "$member2_uuid"
--source include/wait_condition.inc
--echo
--echo # 9. Cleanup
--echo
SET SESSION sql_log_bin = 0;
call mtr.add_suppression("The source .* for channel 'ch1_3' has joined the group .*, and so added its entry into replication_asynchronous_connection_failover table.");
call mtr.add_suppression("The IO thread detected that the source .* does not belong to the group majority, thence the channel .* will try to connect to another source.");
call mtr.add_suppression("The group .* for the channel .* has been removed, and so removed its entry from replication_asynchronous_connection_failover_managed and all the group members from replication_asynchronous_connection_failover table.");
call mtr.add_suppression("The group .* for the channel .* has been added, and so added its entry in replication_asynchronous_connection_failover_managed and source to replication_asynchronous_connection_failover table.");
SET SESSION sql_log_bin = 1;
--let $rpl_channel_name='ch1_3'
--source include/stop_slave.inc
--let $rpl_reset_slave_all= 1
--let $rpl_multi_source= 1
--source include/rpl_reset_slave.inc
--let $rpl_channel_name=
--let $rpl_reset_slave_all=
--let $rpl_multi_source=
--replace_result $group_replication_group_name GROUP_NAME
--eval SELECT asynchronous_connection_failover_delete_managed('ch1_3', '$group_replication_group_name')
DROP TABLE t1;
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
DROP TABLE t1;
--let $rpl_skip_sync= 1
--let $rpl_only_running_threads= 1
--source include/group_replication_end.inc
|
