Debian Codegraph @

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

raw
1 ################################################################################
2 # The aim of the testcase is to test parameter
3 # 'group_replication_transaction_size_limit'
4 #
5 # 'group_replication_transaction_size_limit' should
6 # 1. limit size of transaction, roll-back larger transactions
7 # 2. Should not rollback/impact DDLs
8 # 3. If group_replication_transaction_size_limit is set to 0 it will be
9 # turned off
10 # 4. Transaction size limit of each server
11 # 4.1 Server 1 should allow transactions of all size
12 # 4.2 Server 2 should not allow any transaction(DML)
13 # 4.3 Server 3 should allow transactions of size less than equal to 1024(DML)
14 #
15 # Test:
16 # 0. Start 3 servers with GR
17 # 0.1 For server 1 'group_replication_transaction_size_limit=0'
18 # 0.2 For server 2 'group_replication_transaction_size_limit=1'
19 # 0.3 For server 3 'group_replication_transaction_size_limit=1024'
20 # 1. Connect to server 1, create a table (t1)
21 # Table t1 will be used later in step 3
22 # 2. Check DDLs
23 # 2.1 Create table t2 on server 1 and t3 on server 2.
24 # 2.2 Validate tables t2 and t3 are created on each server.
25 # 2.3 Drop table t2 on server 1 and table t3 on server 2.
26 # 2.4 Validate tables t2 and t3 are dropped on each server.
27 # 3. Check DMLs
28 # 3.1 Insert some data in table t1 on server 2,
29 # transaction should be rolledback
30 # 3.2 Insert some data in table t1 on server 1, data should be inserted
31 # 3.3 Insert some data in table t1 on server 3, data should be inserted
32 # 3.4 Insert large sample data in table t1 on server 3,
33 # transaction should be rolledback
34 # 3.5 Insert large sample data in table t1 on server 1,
35 # data should be inserted
36 # 3.6 Delete all data from table t1 on server 2,
37 # transaction should be rolledback
38 # 3.7 Delete all data from table t1 on server 3,
39 # transaction should be rolledback
40 # 3.8 Delete all data from table t1 on server 1, table will be empty
41 # 4. Validate that server1, server2 and server 3 has same set of data
42 # 5. Clean-up
43 ################################################################################
44 --echo
45 --echo ## 0. Start 3 servers with GR
46 --source include/big_test.inc
47 --source include/have_group_replication_plugin.inc
48 --let $rpl_server_count= 3
49 --source include/group_replication.inc
50
51 # Configure group replication on three servers
52
53 --echo
54 --let $rpl_connection_name= server2
55 --source include/rpl_connection.inc
56 set session sql_log_bin=0;
57 call mtr.add_suppression("Run function 'before_commit' in plugin 'group_replication' failed");
58 call mtr.add_suppression("Error on session .*. Transaction of size .* exceeds specified limit 9. To increase the limit please adjust group_replication_transaction_size_limit option.");
59 call mtr.add_suppression("Error on session .*. Transaction of size .* exceeds specified limit 808. To increase the limit please adjust group_replication_transaction_size_limit option.");
60 set session sql_log_bin=1;
61
62 --echo
63 --let $rpl_connection_name= server3
64 --source include/rpl_connection.inc
65 set session sql_log_bin=0;
66 call mtr.add_suppression("Run function 'before_commit' in plugin 'group_replication' failed");
67 call mtr.add_suppression("Error on session .*. Transaction of size .* exceeds specified limit 1024. To increase the limit please adjust group_replication_transaction_size_limit option.");
68 set session sql_log_bin=1;
69
70 # Make sure number of servers in the group is 3
71 --echo
72 --let $group_replication_number_of_members= 3
73 --source include/gr_wait_for_number_of_members.inc
74
75 ################################################################################
76 --echo
77 --echo ## 1. Connect to server 1, create a table (t1)
78 --echo ## Table t1 will be used later in step 3
79 --let $rpl_connection_name= server1
80 --source include/rpl_connection.inc
81 CREATE TABLE t1 (a INT PRIMARY KEY);
82
83 --source include/rpl_sync.inc
84 ################################################################################
85 --echo
86 --echo ## 2.1 Create table t2 on server 1 and t3 on server 2.
87
88 --let $rpl_connection_name= server1
89 --source include/rpl_connection.inc
90 CREATE TABLE t2 (a INT PRIMARY KEY);
91 --source include/rpl_sync.inc
92
93 --echo
94 --let $rpl_connection_name= server2
95 --source include/rpl_connection.inc
96 CREATE TABLE t3 (a INT PRIMARY KEY);
97 --source include/rpl_sync.inc
98 ################################################################################
99 --echo
100 --echo ## 2.2 Validate tables t2 and t3 are created on each server.
101
102 --let $rpl_connection_name= server1
103 --source include/rpl_connection.inc
104
105 --let $assert_text= 'Check table t2 exist on server 1'
106 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 1
107 --source include/assert.inc
108
109 --let $assert_text= 'Check table t3 exist on server 1'
110 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 1
111 --source include/assert.inc
112
113 --echo
114 --let $rpl_connection_name= server2
115 --source include/rpl_connection.inc
116
117 --let $assert_text= 'Check table t2 exist on server 2'
118 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 1
119 --source include/assert.inc
120
121 --let $assert_text= 'Check table t3 exist on server 2'
122 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 1
123 --source include/assert.inc
124
125 --echo
126 --let $rpl_connection_name= server3
127 --source include/rpl_connection.inc
128
129 --let $assert_text= 'Check table t2 exist on server 3'
130 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 1
131 --source include/assert.inc
132
133 --let $assert_text= 'Check table t3 exist on server 3'
134 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 1
135 --source include/assert.inc
136 ################################################################################
137 --echo
138 --echo ## 2.3 Drop table t2 on server 1 and table t3 on server 2.
139
140 ## Since DDLs are not blocked CREATE and DROP statements should be replicated
141 --let $rpl_connection_name= server1
142 --source include/rpl_connection.inc
143 DROP TABLE t2;
144 --source include/rpl_sync.inc
145
146 --echo
147 --let $rpl_connection_name= server2
148 --source include/rpl_connection.inc
149 DROP TABLE t3;
150 --source include/rpl_sync.inc
151 ################################################################################
152 --echo
153 --echo ## 2.4 Validate tables t2 and t3 are dropped on each server.
154
155 --let $rpl_connection_name= server1
156 --source include/rpl_connection.inc
157
158 --let $assert_text= 'Check table t2 dropped on server 1'
159 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 0
160 --source include/assert.inc
161
162 --let $assert_text= 'Check table t3 dropped on server 1'
163 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 0
164 --source include/assert.inc
165
166 --echo
167 --let $rpl_connection_name= server2
168 --source include/rpl_connection.inc
169
170 --let $assert_text= 'Check table t2 dropped on server 2'
171 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 0
172 --source include/assert.inc
173
174 --let $assert_text= 'Check table t3 dropped on server 2'
175 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 0
176 --source include/assert.inc
177
178 --echo
179 --let $rpl_connection_name= server3
180 --source include/rpl_connection.inc
181
182 --let $assert_text= 'Check table t2 dropped on server 3'
183 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t2\"]" = 0
184 --source include/assert.inc
185
186 --let $assert_text= 'Check table t3 dropped on server 3'
187 --let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t3\"]" = 0
188 --source include/assert.inc
189
190 ################################################################################
191 --echo
192 --echo ## 3.1 Insert some data in table t1 on server 2,
193 --echo ## transaction should be rolledback
194
195 ## Reason of rollback is Server 2 allows transaction of size <= 1 byte
196 ## Its expected all DMLs will be of size greater then 1 and will be rolledback
197 --let $rpl_connection_name= server2
198 --source include/rpl_connection.inc
199
200 --error ER_RUN_HOOK_ERROR
201 --eval INSERT INTO t1 VALUES (10)
202 --source include/rpl_sync.inc
203
204 ## Assert : Confirm table t1 has 0 rows on each server
205 --echo
206 --let $rpl_connection_name= server1
207 --source include/rpl_connection.inc
208
209 --let $assert_text= 'Checking the number of records in table t1'
210 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
211 --source include/assert.inc
212
213 --echo
214 --let $rpl_connection_name= server2
215 --source include/rpl_connection.inc
216
217 --let $assert_text= 'Checking the number of records in table t1'
218 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
219 --source include/assert.inc
220
221 --echo
222 --let $rpl_connection_name= server3
223 --source include/rpl_connection.inc
224
225 --let $assert_text= 'Checking the number of records in table t1'
226 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
227 --source include/assert.inc
228 ################################################################################
229 --echo
230 --echo ## 3.2 Insert some data in table t1 on server 1, data should be inserted
231
232 ## In server 1 all transactions should work since tx size switch is OFF
233 --let $rpl_connection_name= server1
234 --source include/rpl_connection.inc
235 INSERT INTO t1 VALUES (10);
236 --source include/rpl_sync.inc
237
238 ## Assert : Confirm table t1 has 1 row on each server
239 --echo
240 --let $rpl_connection_name= server1
241 --source include/rpl_connection.inc
242
243 --let $assert_text= 'Checking the number of records in table t1'
244 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 1
245 --source include/assert.inc
246
247 --echo
248 --let $rpl_connection_name= server2
249 --source include/rpl_connection.inc
250
251 --let $assert_text= 'Checking the number of records in table t1'
252 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 1
253 --source include/assert.inc
254
255 --echo
256 --let $rpl_connection_name= server3
257 --source include/rpl_connection.inc
258
259 --let $assert_text= 'Checking the number of records in table t1'
260 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 1
261 --source include/assert.inc
262 ################################################################################
263 --echo
264 --echo ## 3.3 Insert some data in table t1 on server 3, data should be inserted
265
266 ## In sever 3 transactions of size <= 1024 should work
267 --let $rpl_connection_name= server3
268 --source include/rpl_connection.inc
269 INSERT INTO t1 VALUES (20);
270 --source include/rpl_sync.inc
271
272 ## In section 3.2, 1 records was inserted so total records now are 2
273 ## Assert : Confirm table t1 has 2 rows on each server
274 --echo
275 --let $rpl_connection_name= server1
276 --source include/rpl_connection.inc
277
278 --let $assert_text= 'Checking the number of records in table t1'
279 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 2
280 --source include/assert.inc
281
282 --echo
283 --let $rpl_connection_name= server2
284 --source include/rpl_connection.inc
285
286 --let $assert_text= 'Checking the number of records in table t1'
287 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 2
288 --source include/assert.inc
289
290 --echo
291 --let $rpl_connection_name= server3
292 --source include/rpl_connection.inc
293
294 --let $assert_text= 'Checking the number of records in table t1'
295 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 2
296 --source include/assert.inc
297
298 # In next section we will insert 100 records
299 # There are already 2 records inserted in 3.2 and 3.3
300 # To avoid confusion later we will clean this 2 records for next operations
301 --echo
302 DELETE FROM t1;
303 --source include/rpl_sync.inc
304 ################################################################################
305 --echo
306 --echo ## 3.4 Insert large sample data in table t1 on server 3,
307 --echo ## transaction should be rolledback
308
309 ## We will insert 100 records, so size of transaction will go beyond 1024 limit
310 ## of server 3
311 --let $rpl_connection_name= server3
312 --source include/rpl_connection.inc
313
314 BEGIN;
315 --disable_query_log
316 --let $rows= 100
317 WHILE($rows)
318 {
319 --eval INSERT INTO t1 (a) VALUES ( $rows )
320 --dec $rows
321 }
322 --enable_query_log
323 --error ER_RUN_HOOK_ERROR
324 COMMIT;
325 --source include/rpl_sync.inc
326
327 ## Since there is rollback, records should not be present
328 ## Assert : Confirm table t1 has 0 rows on each server
329 --echo
330 --let $rpl_connection_name= server1
331 --source include/rpl_connection.inc
332
333 --let $assert_text= 'Checking the number of records in table t1'
334 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
335 --source include/assert.inc
336
337 --echo
338 --let $rpl_connection_name= server2
339 --source include/rpl_connection.inc
340
341 --let $assert_text= 'Checking the number of records in table t1'
342 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
343 --source include/assert.inc
344
345 --echo
346 --let $rpl_connection_name= server3
347 --source include/rpl_connection.inc
348
349 --let $assert_text= 'Checking the number of records in table t1'
350 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 0
351 --source include/assert.inc
352 ################################################################################
353 --echo
354 --echo ## 3.5 Insert large sample data in table t1 on server 1,
355 --echo ## data should be inserted
356
357 ## We will insert 100 records, for server 1 switch is off
358 ## so transaction should be committed successfully from server 1
359 --let $rpl_connection_name= server1
360 --source include/rpl_connection.inc
361
362 BEGIN;
363 --disable_query_log
364 --let $rows=100
365 WHILE($rows)
366 {
367 --eval INSERT INTO t1 (a) VALUES ( $rows )
368 --dec $rows
369 }
370 --enable_query_log
371 COMMIT;
372 --source include/rpl_sync.inc
373
374 ## Assert : Confirm table t1 has 100 rows on each server
375 --echo
376 --let $rpl_connection_name= server1
377 --source include/rpl_connection.inc
378
379 --let $assert_text= 'Checking the number of records in table t1'
380 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
381 --source include/assert.inc
382
383 --echo
384 --let $rpl_connection_name= server2
385 --source include/rpl_connection.inc
386
387 --let $assert_text= 'Checking the number of records in table t1'
388 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
389 --source include/assert.inc
390
391 --echo
392 --let $rpl_connection_name= server3
393 --source include/rpl_connection.inc
394
395 --let $assert_text= 'Checking the number of records in table t1'
396 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
397 --source include/assert.inc
398 ################################################################################
399 --echo
400 --echo ## 3.6 Delete all data from table t1 on server 2,
401 --echo ## transaction should be rolledback
402
403 ## Since 100 records will be deleted, transaction size is big and will be
404 ## rolledback
405 --let $rpl_connection_name= server2
406 --source include/rpl_connection.inc
407
408 # Make the transaction fail for total transaction size, not the write sets
409 SET @group_replication_transaction_size_limit_save= @@GLOBAL.group_replication_transaction_size_limit;
410 # 100 * 8 = 800 bytes for writesets
411 SET GLOBAL group_replication_transaction_size_limit= 808;
412
413 --error ER_RUN_HOOK_ERROR
414 --eval DELETE FROM t1
415 --source include/rpl_sync.inc
416
417 SET GLOBAL group_replication_transaction_size_limit= @group_replication_transaction_size_limit_save;
418
419 ## Confirm rows are not deleted
420 ## Assert : Confirm table t1 has 100 rows on each server
421 --echo
422 --let $rpl_connection_name= server1
423 --source include/rpl_connection.inc
424
425 --let $assert_text= 'Checking the number of records in table t1'
426 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
427 --source include/assert.inc
428
429 --echo
430 --let $rpl_connection_name= server2
431 --source include/rpl_connection.inc
432
433 --let $assert_text= 'Checking the number of records in table t1'
434 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
435 --source include/assert.inc
436
437 --echo
438 --let $rpl_connection_name= server3
439 --source include/rpl_connection.inc
440
441 --let $assert_text= 'Checking the number of records in table t1'
442 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
443 --source include/assert.inc
444 ################################################################################
445 --echo
446 --echo ## 3.7 Delete all data from table t1 on server 3,
447 --echo ## transaction should be rolledback
448 ## Since 100 records will be deleted, transaction size is big and will be
449 ## rolledback
450
451 ## Transaction size is sum of (Transaction changes + Transaction hash)
452 ## This test is important because in below transaction:
453 ## Transaction Change size is 667
454 ## Transaction Hash size is 1614
455 ## Limit of transaction size is 1024 for server 3
456 ##
457 ## Since 667+1614 > 1024 transaction should be rolledback
458 ## If transaction succedds, wait_condition will fail because of different
459 ## number of records before and after transaction indicating issues with code
460
461 --let $rpl_connection_name= server3
462 --source include/rpl_connection.inc
463
464 --error ER_RUN_HOOK_ERROR
465 --eval DELETE FROM t1
466 --source include/rpl_sync.inc
467
468 # Confirm rows are not deleted
469 ## Assert : Confirm table t1 has 100 rows on each server
470 --echo
471 --let $rpl_connection_name= server1
472 --source include/rpl_connection.inc
473
474 --let $assert_text= 'Checking the number of records in table t1'
475 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
476 --source include/assert.inc
477
478 --echo
479 --let $rpl_connection_name= server2
480 --source include/rpl_connection.inc
481
482 --let $assert_text= 'Checking the number of records in table t1'
483 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
484 --source include/assert.inc
485
486 --echo
487 --let $rpl_connection_name= server3
488 --source include/rpl_connection.inc
489
490 --let $assert_text= 'Checking the number of records in table t1'
491 --let $assert_cond= [SELECT COUNT(*) as count FROM t1,count, 1] = 100
492 --source include/assert.inc
493 ################################################################################
494 --echo
495 --echo ## 3.8 Delete all data from table t1 on server 1, table will be empty
496
497 --let $rpl_connection_name= server1
498 --source include/rpl_connection.inc
499
500 DELETE FROM t1;
501 --source include/rpl_sync.inc
502 ################################################################################
503 --echo
504 --echo ## 4. Validate that server1, server2 and server 3 has same set of data
505
506 --let $diff_tables= server1:test.t1, server2:test.t1, server3:test.t1
507 --source include/diff_tables.inc
508 ################################################################################
509 --echo
510 --echo ## 5. Clean-up
511
512 DROP TABLE t1;
513
514 --source include/group_replication_end.inc
515 ################################################################################