Debian Codegraph @

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

raw
1 ################################################################################
2 # Bug #24479782 GR UNABLE TO DETECT CONFLICTS WHEN INSERTING SAME VALUE INTO
3 # UNIQUE COLUMN
4 # Test cases to verify that concurrent transactions with intersecting
5 # write set, NULLABLE UNIQUE KEY, do conflict and test cases to verify
6 # that value NULL does not participate in conflicting transactions.
7 #
8 # Test:
9 # 0. The test requires two servers: M1 and M2.
10 # 1. With both members ONLINE. Create a table 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 UNIQUE KEY, NULLABLE, SINGLE COLUMN KEY,
15 # conflicting case (1,2) vs (3,2).
16 # 2.2 UNIQUE KEY, NULLABLE, SINGLE COLUMN KEY,
17 # non-conflicting case (5,6) vs (7,8).
18 # 2.3 UNIQUE KEY, NULLABLE, SINGLE COLUMN KEY,
19 # non-conflicting case (NULL vs NULL).
20 # 2.4 UNIQUE KEY, NULLABLE, COMPOSITE KEY,
21 # non-conflicting case (x,NULL) vs (x, NULL).
22 # 2.5 UNIQUE KEY, NULLABLE, COMPOSITE KEY,
23 # non-conflicting case (NULL,NULL) vs (NULL, NULL).
24 # 2.6 FOREIGN KEY ON NULLABLE UNIQUE KEY,
25 # non-conflicting case (NULL referring key while it is getting
26 # updated in the parent table).
27 # 3. Do the tuple sanity check after test combinations.
28 # 4. After all the above experiments, check that server's
29 # (server1's and server2's) binlog content is good for replay purpose.
30
31 ################################################################################
32
33 --source include/big_test.inc
34 --source include/have_debug_sync.inc
35 --source include/have_group_replication_plugin.inc
36 --source include/group_replication.inc
37
38 --echo
39 --echo ############################################################
40 --echo # Create a table with Nullable UNIQUEY KEY
41 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY);
42 --source include/rpl_sync.inc
43
44 --echo
45 --echo ###########################################################
46 --echo # Scenario 1: UNIQUE NULL KEY, conflicting case
47 --let $local_server_connection1=server1
48 --let $local_server_connection2=server_1
49 --let $remote_server_connection=server2
50 --let $local_transaction=INSERT INTO t1 VALUES (1, 2)
51 --let $remote_transaction=INSERT INTO t1 VALUES (3, 2)
52 --let $conflict_test=1
53 --source include/gr_parallel_local_and_remote_transactions.inc
54
55 --echo
56 --echo ############################################################
57 --echo # Scenario 2: UNIQUE NULL KEY, non-conflicting case
58 --echo
59 --let $local_server_connection1=server1
60 --let $local_server_connection2=server_1
61 --let $remote_server_connection=server2
62 --let $local_transaction=INSERT INTO t1 VALUES (5, 6)
63 --let $remote_transaction=INSERT INTO t1 VALUES (7, 8)
64 --let $conflict_test=0
65 --source include/gr_parallel_local_and_remote_transactions.inc
66
67 --echo
68 --echo ############################################################
69 --echo # Check the data sanity
70 --let $rpl_connection_name= server1
71 --source include/rpl_connection.inc
72 --let $assert_text= Table t1 will contain row after the above execution
73 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 3 AND t1.c2 = 2, count, 1]" = "1"
74 --source include/assert.inc
75
76 --let $assert_text= Table t1 will contain row after the above execution
77 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 5 AND t1.c2 = 6, count, 1]" = "1"
78 --source include/assert.inc
79
80 --let $assert_text= Table t1 will contain row after the above execution
81 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 7 AND t1.c2 = 8, count, 1]" = "1"
82 --source include/assert.inc
83
84 --let $assert_text= Table t1 will contain 3 rows after the above execution
85 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "3"
86 --source include/assert.inc
87
88 --echo
89 --echo ##################################################################
90 --echo # Scenario 3: UNIQUE NULL KEY, non-conflicting case (NULL vs NULL)
91 --echo
92 --let $local_server_connection1=server1
93 --let $local_server_connection2=server_1
94 --let $remote_server_connection=server2
95 --let $local_transaction=INSERT INTO t1 VALUES (8, NULL)
96 --let $remote_transaction=INSERT INTO t1 VALUES (9, NULL)
97 --let $conflict_test=0
98 --source include/gr_parallel_local_and_remote_transactions.inc
99
100 --echo
101 --echo ############################################################
102 --echo # Check the data sanity
103 --let $rpl_connection_name= server1
104 --source include/rpl_connection.inc
105 --let $assert_text= Table t1 will contain row after the above execution
106 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 8 OR t1.c1 = 9, count, 1]" = "2"
107 --source include/assert.inc
108
109 --let $assert_text= Table t1 will contain 5 rows after the above execution
110 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "5"
111 --source include/assert.inc
112
113 --echo
114 --echo ############################################################
115 --echo # Create a table with composite UNIQUE KEY on server1.
116 DROP TABLE t1;
117 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, UNIQUE KEY(c2,c3));
118 --source include/rpl_sync.inc
119
120 --echo
121 --echo ##################################################################
122 --echo # Scenario 4: COMPOSITE UNIQUE KEY
123 --echo # non-conflicting case (x,NULL) vs (x,NULL)
124 --echo ##################################################################
125 --let $local_server_connection1=server1
126 --let $local_server_connection2=server_1
127 --let $remote_server_connection=server2
128 --let $local_transaction=INSERT INTO t1 VALUES (1, 2, NULL)
129 --let $remote_transaction=INSERT INTO t1 VALUES (2, 2, NULL)
130 --let $conflict_test=0
131 --source include/gr_parallel_local_and_remote_transactions.inc
132
133 --echo
134 --echo ##################################################################
135 --echo # Scenario 5: COMPOSITE UNIQUE KEY
136 --echo # (non-conflicting case (NULL,NULL) vs (NULL,NULL)
137 --echo ##################################################################
138 --echo
139 --let $local_server_connection1=server1
140 --let $local_server_connection2=server_1
141 --let $remote_server_connection=server2
142 --let $local_transaction=INSERT INTO t1 VALUES (3, NULL, NULL)
143 --let $remote_transaction=INSERT INTO t1 VALUES (4, NULL, NULL)
144 --let $conflict_test=0
145 --source include/gr_parallel_local_and_remote_transactions.inc
146
147 --echo
148 --echo ############################################################
149 --echo # Check the data sanity
150 --let $rpl_connection_name= server1
151 --source include/rpl_connection.inc
152 --let $assert_text= Table t1 will contain row after the above execution
153 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 >=1 AND t1.c1 <= 4, count, 1]" = "4"
154 --source include/assert.inc
155
156 --let $assert_text= Table t1 will contain 4 rows after the above execution
157 --let $assert_cond= "[SELECT COUNT(*) FROM t1]" = "4"
158 --source include/assert.inc
159
160 --echo
161 --echo ###################################################################
162 --echo # Create two tables (parent and child) and create a foreign key in
163 --echo # child table that refers Nullable Unique key of the parent table
164 DROP TABLE t1;
165 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY);
166 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c2));
167 INSERT INTO t1 VALUES (1, NULL);
168 --source include/rpl_sync.inc
169
170 --echo
171 --echo ##################################################################
172 --echo # Scenario 6: Non-conflicting case (NULL tuple does not care for
173 --echo # a matching tuple in parent table)
174 --echo
175 --let $local_server_connection1=server1
176 --let $local_server_connection2=server_1
177 --let $remote_server_connection=server2
178 --let $local_transaction= UPDATE t1 SET c2=10
179 --let $remote_transaction= INSERT INTO t2 VALUES (1, NULL);
180 --let $conflict_test=0
181 --source include/gr_parallel_local_and_remote_transactions.inc
182
183 --echo
184 --echo ############################################################
185 --echo # Check the data sanity
186 --let $rpl_connection_name= server1
187 --source include/rpl_connection.inc
188 --let $assert_text= Table t1 will contain row after the above execution
189 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 =1 AND t1.c2 = 10, count, 1]" = "1"
190 --source include/assert.inc
191
192 --let $assert_text= Table t1 will contain row after the above execution
193 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t2 WHERE t2.c1 =1, count, 1]" = "1"
194 --source include/assert.inc
195
196 --echo
197 --echo ######################################################################
198 --echo # Scenario 7: After all the above experiments, check that server's
199 --echo # (server1's and server2's) binlog content is good for replay purpose.
200 --echo
201
202 # Switch off the GR and binlog before going for replay.
203 --source include/stop_group_replication.inc
204 SET SQL_LOG_BIN=0;
205 # Create backup for t1 and t2 tables
206 RENAME TABLE t2 TO t4;
207 RENAME TABLE t1 TO t3;
208 # Check Server1's binlog.
209 --exec $MYSQL_BINLOG --force-if-open $server_1_datadir/server-binary-log.000001 > $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
210 # Before replaying, RESET MASTER, so that GTID numbers are cleared and server
211 # state is ready for replay.
212 RESET MASTER;
213 # Replay the binlog content.
214 --exec $MYSQL --user=root --host=127.0.0.1 --port=$SERVER_MYPORT_1 < $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
215
216 # Compare the data with our backup.
217 --let $diff_tables= server1:t1,server1:t3
218 --source include/diff_tables.inc
219
220 --let $diff_tables= server1:t2,server1:t4
221 --source include/diff_tables.inc
222
223 # Delete the tables so that server state is ready for next test case.
224 DROP TABLE t2, t1;
225
226 # Check Server2's binlog.
227 --exec $MYSQL_BINLOG --force-if-open $server_2_datadir/server-binary-log.000001 > $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
228 # Before replaying, RESET MASTER, so that GTID numbers are cleared and server
229 # state is ready for replay.
230 RESET MASTER;
231 # Replay the binlog content.
232 --exec $MYSQL --user=root --host=127.0.0.1 --port=$SERVER_MYPORT_1 < $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
233
234 # Compare the data with our backup.
235 --let $diff_tables= server1:t1,server1:t3
236 --source include/diff_tables.inc
237
238 --let $diff_tables= server1:t2,server1:t4
239 --source include/diff_tables.inc
240
241 # Delete the tables so that server state is ready for next test case.
242 DROP TABLE t4, t3, t2, t1;
243
244 # Put back to the original server state after the above experiments.
245 RESET MASTER;
246 --exec $MYSQL --user=root --host=127.0.0.1 --port=$SERVER_MYPORT_1 < $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
247 SET SQL_LOG_BIN=1;
248 --source include/start_group_replication.inc
249
250 --echo
251 --echo ############
252 --echo # Cleanup
253 --echo
254 DROP TABLE t2;
255 DROP TABLE t1;
256 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_replay.sql
257
258 --source include/group_replication_end.inc