Debian Codegraph @

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

raw
1 ################################################################################
2 # Test cases to verify concurrent transactions behaviour on tables with
3 # multi level foreign keys (eg: t1 refererring t2 and t2 referring t3)
4 #
5 # Test:
6 # 0. The test requires two servers: M1 and M2.
7 # 1. With both members ONLINE. Create three tables on M1.
8 # 2. With different combinations of DML transactions test concurrent
9 # transactions behaviour by calling gr_parallel_local_and_remote_transactions.inc.
10 # 3. Assert that number of certified transactions are the expected ones after
11 # all the combinations of step2.
12 ################################################################################
13
14 --source include/have_debug_sync.inc
15 --source include/have_group_replication_plugin.inc
16 --source include/group_replication.inc
17
18 --echo
19 --echo ############################################################
20 --echo # Create 3 tables on server1 (t3 referring to t2 and t2 referring to t1)
21 --let $rpl_connection_name= server1
22 --source include/rpl_connection.inc
23 CREATE TABLE t1 (c1 INT PRIMARY KEY);
24 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c1));
25 CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t2(c1));
26 INSERT INTO t1 VALUES (1), (3);
27 --source include/rpl_sync.inc
28
29 --let $local_server_connection1=server1
30 --let $local_server_connection2=server_1
31 --let $remote_server_connection=server2
32
33 --echo
34 --echo ############################################################
35 --echo # Scenario 1: INSERT vs UPDATE
36 --let $local_transaction=INSERT INTO t2 VALUES(2, 1); INSERT INTO t3 VALUES(3, 2);
37 --let $remote_transaction= UPDATE t1 SET c1=2 WHERE c1=1;
38 --let $conflict_test=1
39 --source include/gr_parallel_local_and_remote_transactions.inc
40
41 --echo
42 --echo ############################################################
43 --echo # Scenario 2: INSERT vs DELETE
44 --let $local_transaction=INSERT INTO t2 VALUES(3, 2); INSERT INTO t3 VALUES(4, 3);
45 --let $remote_transaction= DELETE FROM t1 WHERE c1=2;
46 --let $conflict_test=1
47 --source include/gr_parallel_local_and_remote_transactions.inc
48
49 # Uncomment the below scenario 3 and 4 after fixing
50 # Bug#24347476 HIGH PRIORITY TRX FAILED TO KILL LOW PRIORITY TRX WHEN FOREIGN KEYS ARE INVOLVED
51 #--echo
52 #--echo ############################################################
53 #--echo # Scenario 3: UPDATE vs INSERT
54 #--let $local_transaction= UPDATE t1 SET c1=4 WHERE c1=3;
55 #--let $remote_transaction=INSERT INTO t2 VALUES(2, 3); INSERT INTO t3 VALUES(3, 2);
56 #--let $conflict_test=1
57 #--source include/gr_parallel_local_and_remote_transactions.inc
58
59 #--echo
60 #--echo ############################################################
61 #--echo # Scenario 4: DELETE vs INSERT
62 #--let $local_transaction= DELETE FROM t1 WHERE c1=3
63 #--let $remote_transaction=INSERT INTO t2 VALUES(3, 3); INSERT INTO t3 VALUES(4, 3);
64 #--let $conflict_test=1
65 #--source include/gr_parallel_local_and_remote_transactions.inc
66
67 --echo
68 --echo ############################################################
69 --echo # Clean up.
70 DROP TABLE t3, t2, t1;
71 --source include/group_replication_end.inc