Debian Codegraph @

mysql-test/suite/group_replication/t/gr_start_stop_behaviour.test

raw
1 ################################################################################
2 # Verify that
3 #
4 # 1) START/STOP GROUP_REPLICATION commands are not allowed inside a
5 # transaction
6 #
7 # 2) START/STOP GROUP_REPLICATION command is not allowed from the session
8 # if it acquired a lock on a table (using LOCK TABLES command)
9 #
10 # 3) START/STOP GROUP_REPLICATION commands do not do implicit commits
11 #
12 # Test:
13 # 0. The test requires two servers: M1 and M2.
14 # 1. With both the members ONLINE. Create a table on M1.
15 # 2. Initiate a transaction on M1 and try stop GR in the middle of the transaction
16 # - Check that STOP GR throws ER_LOCK_OR_ACTIVE_TRANSACTION
17 # - Check that the failure did not utilize any GTID values
18 # - Check that the failure did not committed any ongoing transaction
19 # - COMMIT on M1 (UUID_M1:1). Check data is available in table on M1.
20 # - Check data is available in table on M2.
21 # 3. Stop GR while GTID_NEXT is specified on M1.
22 # - Check that STOP GR fails
23 # - Check GTID_OWNED is not affected
24 # - COMMIT and set GTID_NEXT to AUTOMATIC . Check GTID_OWNED is empty.
25 # - Check GTID_EXECUTED on M1.
26 # 4. Initiate a transaction on M1 and try start GR in the middle of the transaction
27 # - Check that START GR throws ER_LOCK_OR_ACTIVE_TRANSACTION
28 # - Check that the failure did not utilize any GTID values
29 # - Check that the failure did not committed any ongoing transaction
30 # - COMMIT on M1 (UUID_M1:1). Check data is available in table on M1.
31 # - Clean server by doing RESET MASTER
32 # 5. Initiate a transaction on M1 (session 1) and try to start GR from session 2
33 # - From session 1, INSERT a tuple into table that does not have primary key
34 # i.e., GR incompatible DML
35 # - From session 2, START GROUP_REPLICATION
36 # - Try to commit session1's transaction, check that COMMIT fails in before_commit hook
37 # because there are no writeset for the transaction
38 #
39 # 6. Start GR on M1 while GTID_NEXT is specified.
40 # - Check START fails
41 # - Check GTID_OWNED is non-empty after the failure
42 # - COMMIT and set GTID_NEXT to AUTOMATIC on M1. Check GTID_OWNED is empty.
43 # - Check GTID_EXECUTED on M1.
44 # 7. Acquire LOCK on table using LOCK TABLE and try to stop group replication
45 # - On M2, Session 1, LOCK table t1.
46 # - On M1, Insert a tuple into t1
47 # - Let the Insert reach applier thread on M2 and wait till it is trying acquire lock on t1
48 # - Check that STOP GR on session 1 fails with error ER_LOCK_OR_ACTIVE_TRANSACTION
49 # - Unlock the tables to proceed with next test case
50 # 8. Acquire LOCK on table using LOCK TABLE and try to start group replication
51 # - STOP GR to test the case
52 # - Lock table t1
53 # - Check that START GR fails with error ER_LOCK_OR_ACTIVE_TRANSACTION
54 # - Unlock the tables and START GR and proceed to the next step
55 # 9. Clean up.
56 ################################################################################
57
58 --source include/big_test.inc
59 --source include/have_group_replication_plugin.inc
60 --source include/group_replication.inc
61
62 --let $rpl_connection_name= server1
63 --source include/rpl_connection.inc
64 SET SESSION sql_log_bin= 0;
65 --source include/gtid_utils.inc
66 SET SESSION sql_log_bin= 1;
67 --let $rpl_connection_name= server2
68 --source include/rpl_connection.inc
69 SET SESSION sql_log_bin= 0;
70 --source include/gtid_utils.inc
71 SET SESSION sql_log_bin= 1;
72
73 --echo
74 --echo ############################################################
75 --echo # 1. Create a table on the group.
76 --echo ############################################################
77 --let $rpl_connection_name= server1
78 --source include/rpl_connection.inc
79 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
80 --source include/rpl_sync.inc
81
82 --echo
83 --echo ############################################################
84 --echo # 2. Try to stop Group Replication in the middle of a ongoing
85 --echo # transaction. It should throw error
86 --echo # ER_LOCK_OR_ACTIVE_TRANSACTION
87 --echo ############################################################
88 BEGIN;
89 INSERT INTO t1 VALUES (2);
90
91 --error ER_LOCK_OR_ACTIVE_TRANSACTION
92 STOP GROUP_REPLICATION;
93
94 --let $assert_text= GTID_EXECUTED must contain 3 group transactions
95 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-3")
96 --source include/assert.inc
97
98 --let $rpl_connection_name= server_1
99 --source include/rpl_connection.inc
100 --let $assert_text= There is no value 2 in table t1
101 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 2, count, 1] = 0
102 --source include/assert.inc
103
104 --let $rpl_connection_name= server1
105 --source include/rpl_connection.inc
106 COMMIT;
107 --source include/rpl_sync.inc
108
109 --let $assert_text= There is a value 2 in table t1 on server1
110 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 2, count, 1] = 1
111 --source include/assert.inc
112
113 --let $assert_text= GTID_EXECUTED must contain 4 group transactions
114 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4")
115 --source include/assert.inc
116
117 --let $rpl_connection_name= server2
118 --source include/rpl_connection.inc
119 --let $assert_text= There is a value 2 in table t1 on server2
120 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 2, count, 1] = 1
121 --source include/assert.inc
122
123 --let $assert_text= GTID_EXECUTED must contain 4 group transactions
124 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4")
125 --source include/assert.inc
126
127 --echo
128 --echo ############################################################
129 --echo # 3. Stop Group Replication while GTID_NEXT is specified
130 --echo # and check an error is thrown to prove that
131 --echo # STOP GROUP_REPLICATION does not create any
132 --echo # implicit commits.
133 --echo ############################################################
134 --let $rpl_connection_name= server1
135 --source include/rpl_connection.inc
136 SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1';
137
138 --error ER_CANT_EXECUTE_COMMAND_WITH_ASSIGNED_GTID_NEXT
139 STOP GROUP_REPLICATION;
140
141 --let $session_gtid_owned = `SELECT @@SESSION.GTID_OWNED;`
142
143 # Do the checks on another connection as asserts wont fail properly in the session context
144 --let $rpl_connection_name= server_1
145 --source include/rpl_connection.inc
146
147 --let $assert_text= GTID_EXECUTED must contain 4 group transactions
148 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4")
149 --source include/assert.inc
150
151 --let $assert_text= GTID_OWNED is still set since aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1 transaction was not committed
152 --let $assert_cond= "$session_gtid_owned" <> ""
153 --source include/assert.inc
154
155 # Stop Group Replication on another connection
156
157 --source include/stop_group_replication.inc
158
159 --let $rpl_connection_name= server1
160 --source include/rpl_connection.inc
161
162 COMMIT;
163 SET GTID_NEXT= AUTOMATIC;
164
165 --let $assert_text= GTID_OWNED is empty after committing aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1
166 --let $assert_cond= "[SELECT @@SESSION.GTID_OWNED]" = ""
167 --source include/assert.inc
168
169 --let $assert_text= GTID_EXECUTED must contain 4 group transactions and 1 local transaction
170 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4,aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1")
171 --source include/assert.inc
172
173
174 --echo
175 --echo ############################################################
176 --echo # 4. Try to Start Group Replication in the middle of a ongoing
177 --echo # transaction. It should throw error
178 --echo # ER_LOCK_OR_ACTIVE_TRANSACTION
179 --echo ############################################################
180 --let $rpl_connection_name= server1
181 --source include/rpl_connection.inc
182 BEGIN;
183 INSERT INTO t1 VALUES (4);
184
185 --error ER_LOCK_OR_ACTIVE_TRANSACTION
186 START GROUP_REPLICATION;
187
188 --let $assert_text= GTID_EXECUTED must still contain 4 group transactions and 1 local transaction
189 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4,aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1")
190 --source include/assert.inc
191
192 # We need to check on a different connection to do not see
193 # ongoing transaction on server2 (not committed) data.
194 --let $rpl_connection_name= server_2
195 --source include/rpl_connection.inc
196 --let $assert_text= There is no value 4 in table t1
197 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 4, count, 1] = 0
198 --source include/assert.inc
199
200 --let $rpl_connection_name= server1
201 --source include/rpl_connection.inc
202 COMMIT;
203
204 --let $assert_text= There is a value 4 in table t1 on server1
205 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 4, count, 1] = 1
206 --source include/assert.inc
207
208 --let $server1_uuid= query_get_value(SELECT @@SERVER_UUID, @@SERVER_UUID, 1)
209 --let $assert_text= GTID_EXECUTED must contain 4 group transactions and 2 local transaction
210 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-4,aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1,$server1_uuid:1")
211 --source include/assert.inc
212
213 # Clean server since it has more transactions than the group.
214 DROP TABLE t1;
215 RESET MASTER;
216
217 --echo
218 --echo ##################################################################
219 --echo # 5. Try to start Group Replication while there are ongoing
220 --echo # transaction in parallel that does not have any GR compatible
221 --echo # DMLs (no write set that goes to certification). It should be
222 --echo # commit hook error.
223 --echo ##################################################################
224
225 # Create a table with no primary key locally for testing the scenario
226 SET SESSION sql_log_bin= 0;
227 CALL mtr.add_suppression("Failed to extract the set of items written");
228 CALL mtr.add_suppression("Run function 'before_commit' in plugin 'group_replication' failed");
229 CREATE TABLE t_without_primary_key(i INT);
230 SET SESSION sql_log_bin= 1;
231
232 BEGIN;
233 # No error as the group replication is not started
234 INSERT INTO t_without_primary_key VALUES (1);
235
236 # From a different session, execute start group replication.
237 --let $rpl_connection_name= server_1
238 --source include/rpl_connection.inc
239 --source include/start_group_replication.inc
240
241 --let $rpl_connection_name= server1
242 --source include/rpl_connection.inc
243 # There are no DMLs in the transaction that needs to be certified
244 # This situation is an error in Group Replication.
245 --error ER_RUN_HOOK_ERROR
246 COMMIT;
247
248 SET SESSION sql_log_bin= 0;
249 DROP TABLE t_without_primary_key;
250 SET SESSION sql_log_bin= 1;
251
252 --echo
253 --echo ############################################################
254 --echo # 6. Start Group Replication while GTID_NEXT is specified.
255 --echo # It shall fail leaving GTID_NEXT untouched
256 --echo ############################################################
257
258 # Stop group replication to get ready to test the next scenario.
259 --source include/stop_group_replication.inc
260
261 # Clean server since it has more transactions than the group.
262 DROP TABLE t1;
263 RESET MASTER;
264
265 --let $rpl_connection_name= server1
266 --source include/rpl_connection.inc
267
268 SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1';
269
270 --error ER_CANT_EXECUTE_COMMAND_WITH_ASSIGNED_GTID_NEXT
271 START GROUP_REPLICATION;
272
273 --let $session_gtid_owned = `SELECT @@SESSION.GTID_OWNED;`
274
275 # Do the checks on another connection as asserts wont fail properly in the session context
276 --let $rpl_connection_name= server_1
277 --source include/rpl_connection.inc
278
279 --let $assert_text= GTID_EXECUTED is empty
280 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"")
281 --source include/assert.inc
282
283 --let $assert_text= GTID_OWNED is still set since the ongoing transaction was not committed
284 --let $assert_cond= "$session_gtid_owned" <> ""
285 --source include/assert.inc
286
287 --let $rpl_connection_name= server1
288 --source include/rpl_connection.inc
289
290 COMMIT;
291
292 SET GTID_NEXT= AUTOMATIC;
293
294 --let $assert_text= GTID_OWNED is empty after committing aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1
295 --let $assert_cond= "[SELECT @@SESSION.GTID_OWNED]" = ""
296 --source include/assert.inc
297
298 --let $assert_text= GTID_EXECUTED must contain only the local transaction
299 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"aaaaaaaa-aaaa-aaaa-aaaa-bbbbbbbbbbbb:1")
300 --source include/assert.inc
301
302 # Clean server since it has more transactions than the group.
303 RESET MASTER;
304
305 --source include/start_group_replication.inc
306
307 --let $assert_text= GTID_EXECUTED must contain 6 group transactions
308 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_EXECUTED,"$group_replication_group_name:1-6")
309 --source include/assert.inc
310
311 --echo
312 --echo #################################################################
313 --echo # 7. Verify that if a session is in lock_table_mode (exclusively
314 --echo # acquired a lock on a table), stop group replication command
315 --echo # is not allowed from the session.
316 --echo #################################################################
317
318 # Acquire LOCK on table t1 in server2
319 --let $rpl_connection_name= server2
320 --source include/rpl_connection.inc
321 LOCK table t1 WRITE;
322
323 # Execute a DML on server1
324 --let $rpl_connection_name= server1
325 --source include/rpl_connection.inc
326 INSERT INTO t1 values (6);
327
328 # Check that applier thread is waiting to acquire lock on t1
329 --let $rpl_connection_name= server2
330 --source include/rpl_connection.inc
331 let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist WHERE info='Waiting for table metadata lock';
332 --source include/wait_condition.inc
333
334 # STOP GROUP_REPLICATION after acquiring exclusive lock in the session
335 # should be blocked (could lead to deadlock between client thread
336 # and applier thread)
337 --error ER_LOCK_OR_ACTIVE_TRANSACTION
338 STOP GROUP_REPLICATION;
339 UNLOCK tables;
340
341 --echo
342 --echo #################################################################
343 --echo # 8. Verify that if a session is in lock_table_mode (exclusively
344 --echo # acquired a lock on a table), start group replication command
345 --echo # is not allowed from the session.
346 --echo #################################################################
347
348 --let $rpl_connection_name= server2
349 --source include/rpl_connection.inc
350 # Stop group replication to get ready to test the next scenario.
351 --source include/stop_group_replication.inc
352
353 # Acquire LOCK on table t1 in server2
354 --let $rpl_connection_name= server2
355 --source include/rpl_connection.inc
356 LOCK table t1 WRITE;
357
358 # START GROUP_REPLICATION after acquiring exclusive lock in the session
359 # should be blocked (this command will try to convert the server
360 # into read_only mode and it will hang there until MDL lock acquired
361 # is released)
362 --error ER_LOCK_OR_ACTIVE_TRANSACTION
363 START GROUP_REPLICATION;
364
365 # Clean up for the test case
366 UNLOCK tables;
367 --source include/start_group_replication.inc
368
369 --echo
370 --echo ############################################################
371 --echo # 9. Clean up
372 --echo ############################################################
373 --let $rpl_connection_name= server1
374 --source include/rpl_connection.inc
375 DROP TABLE t1;
376
377 --let $rpl_connection_name= server1
378 --source include/rpl_connection.inc
379 SET SESSION sql_log_bin= 0;
380 --source include/gtid_utils_end.inc
381 SET SESSION sql_log_bin= 1;
382 --let $rpl_connection_name= server2
383 --source include/rpl_connection.inc
384 SET SESSION sql_log_bin= 0;
385 --source include/gtid_utils_end.inc
386 SET SESSION sql_log_bin= 1;
387
388 --source include/group_replication_end.inc