Debian Codegraph @

mysql-test/suite/group_replication/r/gr_nullable_unique_key_conflict.result

raw
1 include/group_replication.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection server1]
6
7 ############################################################
8 # Create a table with Nullable UNIQUEY KEY
9 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY);
10 include/rpl_sync.inc
11
12 ###########################################################
13 # Scenario 1: UNIQUE NULL KEY, conflicting case
14
15 ############################################################
16 # 0. Initial setup and checks.
17 [connection server1]
18 SET SESSION sql_log_bin= 0;
19 include/gtid_utils.inc
20 SET SESSION sql_log_bin= 1;
21 include/gtid_step_reset.inc
22
23 ############################################################
24 # 1. Set a debug sync before broadcast message to group on
25 # connection local_server_connection1.
26 [connection server1]
27 SET @debug_save= @@GLOBAL.DEBUG;
28 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
29
30 #####################################################################
31 # 2. Commit local_transaction that will be blocked before broadcast.
32 BEGIN;
33 INSERT INTO t1 VALUES (1, 2);
34 COMMIT;
35
36 ############################################################
37 # 3. Wait until local transaction reaches the
38 # group_replication_before_message_broadcast debug sync point.
39 [connection server_1]
40
41 ############################################################
42 # 4. Execute a transaction on remote server, that will reach first
43 # certification, since transaction on the local server
44 # is blocked before broadcast.
45 [connection server2]
46 BEGIN;
47 INSERT INTO t1 VALUES (3, 2);
48 COMMIT;
49
50 ############################################################
51 # 5. Signal the waiting thread on local server to resume the
52 # transaction.
53 [connection server_1]
54 SET DEBUG_SYNC='now SIGNAL waiting';
55 SET @@GLOBAL.DEBUG= @debug_save;
56
57 ############################################################
58 # 6. Wait for remote transaction to be executed succesfully
59 on local server.
60 [connection server2]
61 include/sync_slave_sql_with_master.inc
62 ############################################################
63 # 7. If the test case is conflict scenario, local transaction
64 # will end up in an error stating that it was aborted,
65 # since transactions are conflicting and transaction on
66 # remote server was ordered first. If the test case is
67 # is positive scenario, no error will be seen here.
68 [connection server1]
69 ERROR 40000: Plugin instructed the server to rollback the current transaction.
70 [connection server1]
71 SET @@GLOBAL.DEBUG=@debug_save;
72 ############################################################
73 # 8. Sync everything
74 include/rpl_sync.inc
75 ############################################################
76 # 9. Assert that number of certified transactions are the
77 # expected ones.
78 include/assert.inc [The value of Count_Transactions_Checked should be 3 after starting group replication]
79 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
80 ############################################################
81 # 10. Assert that GTID is increased as expected
82 include/gtid_step_assert.inc [count=1, only_count=1]
83 [connection server1]
84 SET SESSION sql_log_bin= 0;
85 include/gtid_utils_end.inc
86 SET SESSION sql_log_bin= 1;
87 ############################################################
88 # 11. Cleanup (restore the connection back to original)
89 [connection server1]
90
91 ############################################################
92 # Scenario 2: UNIQUE NULL KEY, non-conflicting case
93
94
95 ############################################################
96 # 0. Initial setup and checks.
97 [connection server1]
98 SET SESSION sql_log_bin= 0;
99 include/gtid_utils.inc
100 SET SESSION sql_log_bin= 1;
101 include/gtid_step_reset.inc
102
103 ############################################################
104 # 1. Set a debug sync before broadcast message to group on
105 # connection local_server_connection1.
106 [connection server1]
107 SET @debug_save= @@GLOBAL.DEBUG;
108 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
109
110 #####################################################################
111 # 2. Commit local_transaction that will be blocked before broadcast.
112 BEGIN;
113 INSERT INTO t1 VALUES (5, 6);
114 COMMIT;
115
116 ############################################################
117 # 3. Wait until local transaction reaches the
118 # group_replication_before_message_broadcast debug sync point.
119 [connection server_1]
120
121 ############################################################
122 # 4. Execute a transaction on remote server, that will reach first
123 # certification, since transaction on the local server
124 # is blocked before broadcast.
125 [connection server2]
126 BEGIN;
127 INSERT INTO t1 VALUES (7, 8);
128 COMMIT;
129
130 ############################################################
131 # 5. Signal the waiting thread on local server to resume the
132 # transaction.
133 [connection server_1]
134 SET DEBUG_SYNC='now SIGNAL waiting';
135 SET @@GLOBAL.DEBUG= @debug_save;
136
137 ############################################################
138 # 6. Wait for remote transaction to be executed succesfully
139 on local server.
140 [connection server2]
141 include/sync_slave_sql_with_master.inc
142 ############################################################
143 # 7. If the test case is conflict scenario, local transaction
144 # will end up in an error stating that it was aborted,
145 # since transactions are conflicting and transaction on
146 # remote server was ordered first. If the test case is
147 # is positive scenario, no error will be seen here.
148 [connection server1]
149 [connection server1]
150 SET @@GLOBAL.DEBUG=@debug_save;
151 ############################################################
152 # 8. Sync everything
153 include/rpl_sync.inc
154 ############################################################
155 # 9. Assert that number of certified transactions are the
156 # expected ones.
157 include/assert.inc [The value of Count_Transactions_Checked should be 5 after starting group replication]
158 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
159 ############################################################
160 # 10. Assert that GTID is increased as expected
161 include/gtid_step_assert.inc [count=2, only_count=1]
162 [connection server1]
163 SET SESSION sql_log_bin= 0;
164 include/gtid_utils_end.inc
165 SET SESSION sql_log_bin= 1;
166 ############################################################
167 # 11. Cleanup (restore the connection back to original)
168 [connection server1]
169
170 ############################################################
171 # Check the data sanity
172 [connection server1]
173 include/assert.inc [Table t1 will contain row after the above execution]
174 include/assert.inc [Table t1 will contain row after the above execution]
175 include/assert.inc [Table t1 will contain row after the above execution]
176 include/assert.inc [Table t1 will contain 3 rows after the above execution]
177
178 ##################################################################
179 # Scenario 3: UNIQUE NULL KEY, non-conflicting case (NULL vs NULL)
180
181
182 ############################################################
183 # 0. Initial setup and checks.
184 [connection server1]
185 SET SESSION sql_log_bin= 0;
186 include/gtid_utils.inc
187 SET SESSION sql_log_bin= 1;
188 include/gtid_step_reset.inc
189
190 ############################################################
191 # 1. Set a debug sync before broadcast message to group on
192 # connection local_server_connection1.
193 [connection server1]
194 SET @debug_save= @@GLOBAL.DEBUG;
195 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
196
197 #####################################################################
198 # 2. Commit local_transaction that will be blocked before broadcast.
199 BEGIN;
200 INSERT INTO t1 VALUES (8, NULL);
201 COMMIT;
202
203 ############################################################
204 # 3. Wait until local transaction reaches the
205 # group_replication_before_message_broadcast debug sync point.
206 [connection server_1]
207
208 ############################################################
209 # 4. Execute a transaction on remote server, that will reach first
210 # certification, since transaction on the local server
211 # is blocked before broadcast.
212 [connection server2]
213 BEGIN;
214 INSERT INTO t1 VALUES (9, NULL);
215 COMMIT;
216
217 ############################################################
218 # 5. Signal the waiting thread on local server to resume the
219 # transaction.
220 [connection server_1]
221 SET DEBUG_SYNC='now SIGNAL waiting';
222 SET @@GLOBAL.DEBUG= @debug_save;
223
224 ############################################################
225 # 6. Wait for remote transaction to be executed succesfully
226 on local server.
227 [connection server2]
228 include/sync_slave_sql_with_master.inc
229 ############################################################
230 # 7. If the test case is conflict scenario, local transaction
231 # will end up in an error stating that it was aborted,
232 # since transactions are conflicting and transaction on
233 # remote server was ordered first. If the test case is
234 # is positive scenario, no error will be seen here.
235 [connection server1]
236 [connection server1]
237 SET @@GLOBAL.DEBUG=@debug_save;
238 ############################################################
239 # 8. Sync everything
240 include/rpl_sync.inc
241 ############################################################
242 # 9. Assert that number of certified transactions are the
243 # expected ones.
244 include/assert.inc [The value of Count_Transactions_Checked should be 7 after starting group replication]
245 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
246 ############################################################
247 # 10. Assert that GTID is increased as expected
248 include/gtid_step_assert.inc [count=2, only_count=1]
249 [connection server1]
250 SET SESSION sql_log_bin= 0;
251 include/gtid_utils_end.inc
252 SET SESSION sql_log_bin= 1;
253 ############################################################
254 # 11. Cleanup (restore the connection back to original)
255 [connection server1]
256
257 ############################################################
258 # Check the data sanity
259 [connection server1]
260 include/assert.inc [Table t1 will contain row after the above execution]
261 include/assert.inc [Table t1 will contain 5 rows after the above execution]
262
263 ############################################################
264 # Create a table with composite UNIQUE KEY on server1.
265 DROP TABLE t1;
266 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, UNIQUE KEY(c2,c3));
267 include/rpl_sync.inc
268
269 ##################################################################
270 # Scenario 4: COMPOSITE UNIQUE KEY
271 # non-conflicting case (x,NULL) vs (x,NULL)
272 ##################################################################
273
274 ############################################################
275 # 0. Initial setup and checks.
276 [connection server1]
277 SET SESSION sql_log_bin= 0;
278 include/gtid_utils.inc
279 SET SESSION sql_log_bin= 1;
280 include/gtid_step_reset.inc
281
282 ############################################################
283 # 1. Set a debug sync before broadcast message to group on
284 # connection local_server_connection1.
285 [connection server1]
286 SET @debug_save= @@GLOBAL.DEBUG;
287 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
288
289 #####################################################################
290 # 2. Commit local_transaction that will be blocked before broadcast.
291 BEGIN;
292 INSERT INTO t1 VALUES (1, 2, NULL);
293 COMMIT;
294
295 ############################################################
296 # 3. Wait until local transaction reaches the
297 # group_replication_before_message_broadcast debug sync point.
298 [connection server_1]
299
300 ############################################################
301 # 4. Execute a transaction on remote server, that will reach first
302 # certification, since transaction on the local server
303 # is blocked before broadcast.
304 [connection server2]
305 BEGIN;
306 INSERT INTO t1 VALUES (2, 2, NULL);
307 COMMIT;
308
309 ############################################################
310 # 5. Signal the waiting thread on local server to resume the
311 # transaction.
312 [connection server_1]
313 SET DEBUG_SYNC='now SIGNAL waiting';
314 SET @@GLOBAL.DEBUG= @debug_save;
315
316 ############################################################
317 # 6. Wait for remote transaction to be executed succesfully
318 on local server.
319 [connection server2]
320 include/sync_slave_sql_with_master.inc
321 ############################################################
322 # 7. If the test case is conflict scenario, local transaction
323 # will end up in an error stating that it was aborted,
324 # since transactions are conflicting and transaction on
325 # remote server was ordered first. If the test case is
326 # is positive scenario, no error will be seen here.
327 [connection server1]
328 [connection server1]
329 SET @@GLOBAL.DEBUG=@debug_save;
330 ############################################################
331 # 8. Sync everything
332 include/rpl_sync.inc
333 ############################################################
334 # 9. Assert that number of certified transactions are the
335 # expected ones.
336 include/assert.inc [The value of Count_Transactions_Checked should be 11 after starting group replication]
337 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
338 ############################################################
339 # 10. Assert that GTID is increased as expected
340 include/gtid_step_assert.inc [count=2, only_count=1]
341 [connection server1]
342 SET SESSION sql_log_bin= 0;
343 include/gtid_utils_end.inc
344 SET SESSION sql_log_bin= 1;
345 ############################################################
346 # 11. Cleanup (restore the connection back to original)
347 [connection server1]
348
349 ##################################################################
350 # Scenario 5: COMPOSITE UNIQUE KEY
351 # (non-conflicting case (NULL,NULL) vs (NULL,NULL)
352 ##################################################################
353
354
355 ############################################################
356 # 0. Initial setup and checks.
357 [connection server1]
358 SET SESSION sql_log_bin= 0;
359 include/gtid_utils.inc
360 SET SESSION sql_log_bin= 1;
361 include/gtid_step_reset.inc
362
363 ############################################################
364 # 1. Set a debug sync before broadcast message to group on
365 # connection local_server_connection1.
366 [connection server1]
367 SET @debug_save= @@GLOBAL.DEBUG;
368 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
369
370 #####################################################################
371 # 2. Commit local_transaction that will be blocked before broadcast.
372 BEGIN;
373 INSERT INTO t1 VALUES (3, NULL, NULL);
374 COMMIT;
375
376 ############################################################
377 # 3. Wait until local transaction reaches the
378 # group_replication_before_message_broadcast debug sync point.
379 [connection server_1]
380
381 ############################################################
382 # 4. Execute a transaction on remote server, that will reach first
383 # certification, since transaction on the local server
384 # is blocked before broadcast.
385 [connection server2]
386 BEGIN;
387 INSERT INTO t1 VALUES (4, NULL, NULL);
388 COMMIT;
389
390 ############################################################
391 # 5. Signal the waiting thread on local server to resume the
392 # transaction.
393 [connection server_1]
394 SET DEBUG_SYNC='now SIGNAL waiting';
395 SET @@GLOBAL.DEBUG= @debug_save;
396
397 ############################################################
398 # 6. Wait for remote transaction to be executed succesfully
399 on local server.
400 [connection server2]
401 include/sync_slave_sql_with_master.inc
402 ############################################################
403 # 7. If the test case is conflict scenario, local transaction
404 # will end up in an error stating that it was aborted,
405 # since transactions are conflicting and transaction on
406 # remote server was ordered first. If the test case is
407 # is positive scenario, no error will be seen here.
408 [connection server1]
409 [connection server1]
410 SET @@GLOBAL.DEBUG=@debug_save;
411 ############################################################
412 # 8. Sync everything
413 include/rpl_sync.inc
414 ############################################################
415 # 9. Assert that number of certified transactions are the
416 # expected ones.
417 include/assert.inc [The value of Count_Transactions_Checked should be 13 after starting group replication]
418 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
419 ############################################################
420 # 10. Assert that GTID is increased as expected
421 include/gtid_step_assert.inc [count=2, only_count=1]
422 [connection server1]
423 SET SESSION sql_log_bin= 0;
424 include/gtid_utils_end.inc
425 SET SESSION sql_log_bin= 1;
426 ############################################################
427 # 11. Cleanup (restore the connection back to original)
428 [connection server1]
429
430 ############################################################
431 # Check the data sanity
432 [connection server1]
433 include/assert.inc [Table t1 will contain row after the above execution]
434 include/assert.inc [Table t1 will contain 4 rows after the above execution]
435
436 ###################################################################
437 # Create two tables (parent and child) and create a foreign key in
438 # child table that refers Nullable Unique key of the parent table
439 DROP TABLE t1;
440 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY);
441 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c2));
442 INSERT INTO t1 VALUES (1, NULL);
443 include/rpl_sync.inc
444
445 ##################################################################
446 # Scenario 6: Non-conflicting case (NULL tuple does not care for
447 # a matching tuple in parent table)
448
449
450 ############################################################
451 # 0. Initial setup and checks.
452 [connection server1]
453 SET SESSION sql_log_bin= 0;
454 include/gtid_utils.inc
455 SET SESSION sql_log_bin= 1;
456 include/gtid_step_reset.inc
457
458 ############################################################
459 # 1. Set a debug sync before broadcast message to group on
460 # connection local_server_connection1.
461 [connection server1]
462 SET @debug_save= @@GLOBAL.DEBUG;
463 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
464
465 #####################################################################
466 # 2. Commit local_transaction that will be blocked before broadcast.
467 BEGIN;
468 UPDATE t1 SET c2=10;
469 COMMIT;
470
471 ############################################################
472 # 3. Wait until local transaction reaches the
473 # group_replication_before_message_broadcast debug sync point.
474 [connection server_1]
475
476 ############################################################
477 # 4. Execute a transaction on remote server, that will reach first
478 # certification, since transaction on the local server
479 # is blocked before broadcast.
480 [connection server2]
481 BEGIN;
482 INSERT INTO t2 VALUES (1, NULL);;
483 COMMIT;
484
485 ############################################################
486 # 5. Signal the waiting thread on local server to resume the
487 # transaction.
488 [connection server_1]
489 SET DEBUG_SYNC='now SIGNAL waiting';
490 SET @@GLOBAL.DEBUG= @debug_save;
491
492 ############################################################
493 # 6. Wait for remote transaction to be executed succesfully
494 on local server.
495 [connection server2]
496 include/sync_slave_sql_with_master.inc
497 ############################################################
498 # 7. If the test case is conflict scenario, local transaction
499 # will end up in an error stating that it was aborted,
500 # since transactions are conflicting and transaction on
501 # remote server was ordered first. If the test case is
502 # is positive scenario, no error will be seen here.
503 [connection server1]
504 [connection server1]
505 SET @@GLOBAL.DEBUG=@debug_save;
506 ############################################################
507 # 8. Sync everything
508 include/rpl_sync.inc
509 ############################################################
510 # 9. Assert that number of certified transactions are the
511 # expected ones.
512 include/assert.inc [The value of Count_Transactions_Checked should be 19 after starting group replication]
513 include/assert.inc [The value of Count_Conflicts_Detected should be 1 after starting group replication]
514 ############################################################
515 # 10. Assert that GTID is increased as expected
516 include/gtid_step_assert.inc [count=2, only_count=1]
517 [connection server1]
518 SET SESSION sql_log_bin= 0;
519 include/gtid_utils_end.inc
520 SET SESSION sql_log_bin= 1;
521 ############################################################
522 # 11. Cleanup (restore the connection back to original)
523 [connection server1]
524
525 ############################################################
526 # Check the data sanity
527 [connection server1]
528 include/assert.inc [Table t1 will contain row after the above execution]
529 include/assert.inc [Table t1 will contain row after the above execution]
530
531 ######################################################################
532 # Scenario 7: After all the above experiments, check that server's
533 # (server1's and server2's) binlog content is good for replay purpose.
534
535 include/stop_group_replication.inc
536 SET SQL_LOG_BIN=0;
537 RENAME TABLE t2 TO t4;
538 RENAME TABLE t1 TO t3;
539 RESET MASTER;
540 include/diff_tables.inc [server1:t1,server1:t3]
541 include/diff_tables.inc [server1:t2,server1:t4]
542 DROP TABLE t2, t1;
543 RESET MASTER;
544 include/diff_tables.inc [server1:t1,server1:t3]
545 include/diff_tables.inc [server1:t2,server1:t4]
546 DROP TABLE t4, t3, t2, t1;
547 RESET MASTER;
548 SET SQL_LOG_BIN=1;
549 include/start_group_replication.inc
550
551 ############
552 # Cleanup
553
554 DROP TABLE t2;
555 DROP TABLE t1;
556 include/group_replication_end.inc