Debian Codegraph @

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

raw
1 ################################################################################
2 # Bug#24466187 MTS WORKERS FAILS TO WAIT FOR COMPLETION OF DEPENDENT
3 # TRANSACTIONS
4 # Test cases to verify that dependent transaction check and
5 # conflict detection algorithm is working properly when the foreign key is
6 # referring to a unique key.
7 #
8 # Test:
9 # 0. The test requires two servers: M1 and M2.
10 # 1. With both members ONLINE. Create two tables on M1.
11 # 2. With different test combinations,
12 # call gr_parallel_local_and_remote_transactions.inc
13 # and test the behaviour as expected or not.
14 # 2.1. Scenario 1: FOREIGN KEY referring an UNIQUE KEY
15 # conflicting case: updating referring tuple while
16 # the tuple with referring key is getting inserted
17 # in child table.
18 # 2.2. Scenario 2: FOREIGN KEY referring an UNIQUE KEY
19 # non-conflicting case: inserting a tuple in child's table
20 # while updating a tuple in parent's table which is not
21 # getting referred.
22 # 3. Check data sanity after the above scenarios.
23 #
24 # Check that the group replication parallel applier indexes of the DMLs
25 # that involves foreign key which is referring an unique key is calculated
26 # properly.
27 #
28 # 0. The test requires two servers: M1 and M2.
29 # 1. With both members ONLINE. Create two tables on M1.
30 # 2. Execute few DMLs on these two tables on M1.
31 # 3. Dump Group Replication applier channel relay log.
32 # 4. Validate indexes on M2.
33 ################################################################################
34
35 --source include/have_debug_sync.inc
36 --source include/have_group_replication_plugin.inc
37 --source include/group_replication.inc
38
39 --echo
40 --echo ############################################################
41 --echo # Create two tables on server1.
42 --echo # t1 : parent table with a primary key and unique key.
43 --echo # t2 : child table with a primary key and foreign key referring
44 --echo # t1's unique key.
45 --echo
46 --let $rpl_connection_name= server1
47 --source include/rpl_connection.inc
48 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY NOT NULL);
49 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c2));
50 INSERT INTO t1 VALUES(1, 1);
51 INSERT INTO t1 VALUES(2, 2);
52 --source include/rpl_sync.inc
53
54 --echo
55 --echo ############################################################
56 --echo # Scenario 1: FOREIGN KEY referring an UNIQUE KEY
57 --echo # conflicting case: updating referring tuple while
58 --echo # the tuple with referring key is getting inserted
59 --echo # in child table
60 --let $local_server_connection1=server1
61 --let $local_server_connection2=server_1
62 --let $remote_server_connection=server2
63 --let $local_transaction= INSERT INTO t2 VALUES (1, 1)
64 --let $remote_transaction=UPDATE t1 SET c2=3 WHERE c1=1
65 --let $conflict_test=1
66 --source include/gr_parallel_local_and_remote_transactions.inc
67
68 --echo ############################################################
69 --echo # check data sanity
70 --let $rpl_connection_name= server1
71 --source include/rpl_connection.inc
72
73 --let $assert_text= Table t1 will contain update row after the above execution
74 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c2 = 3, count, 1]" = "1"
75 --source include/assert.inc
76
77 --let $assert_text= Table t2 should have zero rows after the above conflict
78 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t2, count, 1]" = "0"
79 --source include/assert.inc
80
81 --echo
82 --echo ############################################################
83 --echo # Scenario 2: FOREIGN KEY referring an UNIQUE KEY
84 --echo # non-conflicting case: inserting a tuple in child's table
85 --echo # while updating a tuple in parent's table which is not getting
86 --echo # referred
87 --let $local_server_connection1=server1
88 --let $local_server_connection2=server_1
89 --let $remote_server_connection=server2
90 --let $local_transaction= INSERT INTO t2 VALUES (1, 3)
91 --let $remote_transaction=UPDATE t1 SET c2=4 WHERE c1=2
92 --let $conflict_test=0
93 --source include/gr_parallel_local_and_remote_transactions.inc
94
95 --echo ############################################################
96 --echo # check data sanity
97 --let $rpl_connection_name= server1
98 --source include/rpl_connection.inc
99
100 --let $assert_text= Table t1 will contain update row after the above execution
101 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c2 = 4, count, 1]" = "1"
102 --source include/assert.inc
103
104 --let $assert_text= Table t2 will contain one tuple
105 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t2 WHERE t2.c2= 3, count, 1]" = "1"
106 --source include/assert.inc
107
108 --echo
109 --echo ############################################################
110 --echo # Clean up.
111 DROP TABLE t2;
112 DROP TABLE t1;
113 --source include/rpl_sync.inc
114
115 --echo
116 --echo ##################################################################
117 --echo # Scenario: Check the group replication parallel applier indexes
118 --echo # of the DMLs that involves foreign key which is referring
119 --echo # an unique key.
120 --echo
121 --let $rpl_connection_name= server2
122 --source include/rpl_connection.inc
123 --source include/stop_group_replication.inc
124 # This creates new relay log and view_id will have
125 # last_committed=0 sequence_number=0
126 --source include/start_group_replication.inc
127
128 --echo
129 --echo #####################################################################
130 --echo # Create two tables on server1.
131 --echo # t1 : parent table with a primary key and unique key.
132 --echo # t2 : child table with a primary key and foreign key referring
133 --echo # t1's unique key.
134 --echo
135 --let $rpl_connection_name= server1
136 --source include/rpl_connection.inc
137 # last_committed=1 sequence_number=2
138 CREATE TABLE t1 (a INT,b INT UNIQUE NOT NULL, PRIMARY KEY(a));
139 # last_committed=2 sequence_number=3
140 CREATE TABLE t2 (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t1(b));
141
142 --echo
143 --echo #####################################################################
144 --echo # Execute few DMLs on these two tables
145 --echo
146 # last_committed=3 sequence_number=4
147 INSERT INTO t1 VALUES(11,1);
148 # last_committed=3 sequence_number=5
149 INSERT INTO t1 VALUES(12,2);
150 # last_committed=4 sequence_number=6
151 INSERT INTO t2 VALUES(31,1);
152 # last_committed=5 sequence_number=7
153 INSERT INTO t2 VALUES(32,2);
154 # last_committed=6 sequence_number=8
155 UPDATE t2 SET c1=21 WHERE c2=1;
156 # last_committed=7 sequence_number=9
157 DELETE FROM t2 WHERE c2=2;
158 # last_committed=9 sequence_number=10
159 DELETE FROM t1 WHERE b=2;
160 # last_committed=8 sequence_number=11
161 DELETE FROM t2 WHERE c1=21;
162 # last_committed=11 sequence_number=12
163 DROP TABLE t2;
164 # last_committed=12 sequence_number=13
165 DROP TABLE t1;
166 --source include/rpl_sync.inc
167
168 --echo
169 --echo #####################################################
170 --echo # Dump Group Replication applier channel relay log
171 --echo
172 --let $rpl_connection_name= server2
173 --source include/rpl_connection.inc
174 --let $dump_file=$MYSQLTEST_VARDIR/tmp/group_replication_parallel_applier_indexes.dump
175 --exec $MYSQL_BINLOG --force-if-open $server_2_datadir/server-relay-log-group_replication_applier.000003 > $dump_file
176 --let $grep_file= $dump_file
177 --let $grep_pattern= last_committed
178 --let $grep_output= print_each
179 --replace_regex /# at [0-9]*/# at #/ /#[0-9]+/#time#/ /[[:space:]]+[0-9]+:[0-9]+:[0-9]+/ ##:##:##/ /end_log_pos [0-9]+/end_log_pos ##/ /original_committed_timestamp=[0-9]*/original_committed_timestamp=##:##:##/ /immediate_commit_timestamp=[0-9]*/immediate_commit_timestamp=##:##:##/ /transaction_length=[0-9]*/transaction_length=#/
180 --source include/grep_pattern.inc
181
182 --echo
183 --echo ################################
184 --echo # Validate indexes on member 2.
185 --echo #
186 --let $binlog_file= server-relay-log-group_replication_applier.000003
187 --let $logical_timestamps= 0 0;1 2;2 3;3 4;3 5;4 6;5 7;6 8;7 9;9 10;8 11;11 12;12 13
188 --source include/assert_logical_timestamps.inc
189
190 # Cleanup
191 --remove_file $dump_file
192 --source include/group_replication_end.inc