Debian Codegraph @

mysql-test/suite/galera/t/MW-369.test

raw
1 #
2 # Test A Outline:
3 # ===============
4 #
5 # This test tests the scenario for MW-369 where a new child table
6 # row referring to parent table row is inserted concurrently from
7 # another node while the transaction which tries to delete a
8 # referred row from the parent table is committing.
9 #
10 # The p table will originally have rows (1, 0), (2, 0).
11 # The c table will be empty.
12 #
13 # A new row (1, 1) pointing to parent row (1, 0) is inserted from
14 # connection node_2, the transaction which tries to remove the
15 # parent row (1, 0) is run from connection node_1.
16 #
17 # Expected outcome:
18 # ================
19 #
20 # The transaction on node_1 will fail. The parent table will contain
21 # rows (1, 0), (2, 0) and the child table will contain row (1, 1).
22 #
23
24 --source include/galera_cluster.inc
25 --source include/have_innodb.inc
26 --source include/have_debug_sync.inc
27
28 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
29 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
30 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
31
32 INSERT INTO p VALUES (1, 0);
33 INSERT INTO p VALUES (2, 0);
34
35 --let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
36 --let $mw_369_child_query = INSERT INTO c VALUES (1, 1)
37
38 #
39 # we must open connection node_1a here, MW-369.inc will use it later
40 #
41 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
42 --source MW-369.inc
43
44 # Commit fails
45 --connection node_1
46 --error ER_LOCK_DEADLOCK
47 --reap
48
49 --connection node_2
50 SELECT * FROM p;
51 SELECT * FROM c;
52
53 DROP TABLE c;
54 DROP TABLE p;
55
56 #
57 # Test B Outline:
58 # ===============
59 #
60 # This test tests the scenario for MW-369 where a existing
61 # child table row is updated concurrently from another node
62 # with a transaction which updates the parent table.
63 #
64 # The p table will originally have rows (1, 0), (2, 0).
65 # The c table will originally have rows (1, 1, 0) which points
66 # to parent table row (1, 0).
67 #
68 # Expected outcome:
69 # ================
70 #
71 # Both updates should succeed since they are done to separate tables and
72 # rows. The parent table will contain rows (1, 1), (2, 0). The child
73 # table will contain row (1, 1, 1).
74 #
75
76 --connection node_1
77 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
78 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
79 f2 INTEGER,
80 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
81
82 INSERT INTO p VALUES (1, 0);
83 INSERT INTO p VALUES (2, 0);
84 INSERT INTO c VALUES (1, 1, 0);
85
86 --let mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
87 --let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
88 --source MW-369.inc
89
90 # Commit succeeds
91 --connection node_1
92 --reap
93
94 --connection node_2
95 SELECT * FROM p;
96 SELECT * FROM c;
97
98 DROP TABLE c;
99 DROP TABLE p;
100
101 #
102 # Test C Outline:
103 # ===============
104 #
105 # This test tests the scenario for MW-369 where a child table row is
106 # deleted concurrently from the other node while a transaction updates
107 # the parent table referred by the child table row.
108 #
109 # The p table will originally have rows (1, 0), (2, 0)
110 # The c table will originally have row (1, 1) which points to parent
111 # table row (1, 0).
112 #
113 # A row (1, 1) pointing to parent row (1, 0) is deleted from
114 # connection node_2, the transaction which tries to update the
115 # parent row (1, 0) is run from connection node_1.
116 #
117 # Expected Outcome:
118 # ================
119 # Both operations on node_1 and node_2 should succeed without conflicts.
120 # The parent table should contain values (1, 1), (2, 0) and the child
121 # table should be empty.
122
123 --connection node_1
124 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
125 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
126 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
127
128 INSERT INTO p VALUES (1, 0);
129 INSERT INTO p VALUES (2, 0);
130 INSERT INTO c VALUES (1, 1);
131
132 --let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
133 --let $mw_369_child_query = DELETE FROM c WHERE f1 = 1
134 --source MW-369.inc
135
136 # Commit succeeds
137 --connection node_1
138 --reap
139
140 --connection node_2
141 SELECT * FROM p;
142 SELECT * FROM c;
143
144 DROP TABLE c;
145 DROP TABLE p;
146
147
148 #
149 # Test D Outline:
150 # ===============
151 #
152 # This test is similar to test A, where parent row is deleted while a child row
153 # is inserted simultaneously on node 2. However, in this test case the FK
154 # constraint's target column is a unique key, and parent row is not delete,
155 # but this key value is changed so that insert on node 2 will cause FK
156 # violation
157 #
158 # The p table will originally have rows (1, 0)
159 # The c table will originally be empty
160 #
161 # in node_1, parent row is updated to value (1,1)
162 # A row (1, 0) pointing to the old version of parent row (1, 0) is inserted
163 # in connection node_2
164 #
165 # Expected Outcome:
166 # ================
167 # This is a true conflict and one transaciton must abort. In this case it is node_1
168 # transaction, which was scheduled later.
169 # Parent table should have row (1,0)
170 # child table should have row (1,0)
171 #
172
173 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER UNIQUE KEY) ENGINE=INNODB;
174 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
175 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f2)) ;
176
177 INSERT INTO p VALUES (1, 0);
178
179 --let $mw_369_parent_query = UPDATE p SET f2 = 1 WHERE f1 = 1
180 --let $mw_369_child_query = INSERT INTO c VALUES (1, 0);
181 --source MW-369.inc
182
183 # Commit fails
184 --connection node_1
185 --error ER_LOCK_DEADLOCK
186 --reap
187
188 --connection node_2
189 SELECT * FROM p;
190 SELECT * FROM c;
191
192 DROP TABLE c;
193 DROP TABLE p;
194
195 #
196 # Test E Outline:
197 # ===============
198 #
199 # This test is similar to test B, where parent row is deleted while a child row
200 # is updated simultaneously on node 2. However, in this test case the FK
201 # constraint has ON DELETE CASCADE option, and the delete on parent row will
202 # cascade a delete on child row as well. This will cause true conflict with
203 # connection node_2, which tries to update unrelated column on child table.
204 #
205 # The p table will originally have rows (1, 0), (2,0)
206 # The c table will originally have row (1,1,0)
207 #
208 # in node_1, parent row (1,0) is deleted and cascaded delete will happen on
209 # child table row (1,1,0).
210 # in connection node_2 child table row is update to value (1,1,1)
211 #
212 # Expected Outcome:
213 # ================
214 # This is a true conflict and one transaciton must abort. In this case it is node_1
215 # transaction, which was scheduled later.
216 # Parent table should have rows (1,0), (2,0)
217 # child table should have row (1,1,1)
218 #
219
220
221 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
222 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
223 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)
224 ON DELETE CASCADE) ;
225
226 INSERT INTO p VALUES (1, 0);
227 INSERT INTO p VALUES (2, 0);
228 INSERT INTO c VALUES (1, 1, 0);
229
230 --let $mw_369_parent_query = DELETE FROM p WHERE f1 = 1
231 --let $mw_369_child_query = UPDATE c SET f2 = 1 WHERE f1 = 1
232 --source MW-369.inc
233
234 # Commit fails
235 --connection node_1
236 --error ER_LOCK_DEADLOCK
237 --reap
238
239 --connection node_2
240 SELECT * FROM p;
241 SELECT * FROM c;
242
243 DROP TABLE c;
244 DROP TABLE p;
245
246 --echo #
247 --echo # Start of 10.4 tests
248 --echo #
249 #
250 # Test F Outline:
251 # ===============
252 #
253 # Test two concurrent INSERTs on the child table.
254 #
255 # The pf table will originally have row (1)
256 # The cf table will originally be empty
257 #
258 # A new row (10, 1) pointing to parent row (1) is inserted from
259 # connection node_2. A transaction which tries to INSERT another child
260 # row (20, 1), pointing to the same parent, is run from connection node_1.
261 #
262 # Expected Outcome:
263 # =================
264 # Both INSERTs should succeed since they don't modify the common parent
265 # key.
266 #
267 # At the end of the test:
268 # parent table should have row (1)
269 # child table should have rows (10, 1), (20, 1)
270
271 --connection node_1
272
273 CREATE TABLE pf (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
274 CREATE TABLE cf (
275 f1 INTEGER PRIMARY KEY,
276 p_id INTEGER,
277 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pf (f1)
278 );
279
280 INSERT INTO pf VALUES (1);
281
282 # This is run on node1:
283 --let $mw_369_parent_query = INSERT INTO cf (f1, p_id) VALUES (10, 1)
284 # This is run on node2:
285 --let $mw_369_child_query = INSERT INTO cf (f1, p_id) VALUES (20, 1)
286 --source MW-369.inc
287
288 --connection node_1
289 --reap
290
291 --connection node_2
292 SELECT * FROM pf;
293 SELECT * FROM cf;
294
295 DROP TABLE cf;
296 DROP TABLE pf;
297
298 #
299 # Test G Outline:
300 # ===============
301 #
302 # This test is similar to test B where a existing
303 # child table row is updated concurrently from another node
304 # with a transaction which updates the parent table, except
305 # that here the child table row is inserted, not updated.
306 #
307 # The pg table will originally have rows (1, 0), (2, 0).
308 # The cg table will originally be empty
309 #
310 # Expected outcome:
311 # ================
312 #
313 # Both UPDATE and INSERT should succeed since they are done to separate tables
314 # and UPDATE to parent row does not touch the foreign key referenced by the
315 # child row INSERT. The parent table shall contain rows (1, 1), (2, 0).
316 # The child table shall contain row (1, 1, 0) which points to parent table
317 # row (1, 0).
318 #
319
320 --connection node_1
321 CREATE TABLE pg (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
322 CREATE TABLE cg (f1 INTEGER PRIMARY KEY, p_id INTEGER,
323 f2 INTEGER,
324 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES pg (f1)) ;
325
326 INSERT INTO pg VALUES (1, 0);
327 INSERT INTO pg VALUES (2, 0);
328
329 --let mw_369_parent_query = UPDATE pg SET f2 = 1 WHERE f1 = 1
330 --let $mw_369_child_query = INSERT INTO cg VALUES (1, 1, 0)
331 --source MW-369.inc
332
333 # Commit succeeds
334 --connection node_1
335 --reap
336
337 --connection node_2
338 SELECT * FROM pg;
339 SELECT * FROM cg;
340
341 DROP TABLE cg;
342 DROP TABLE pg;