Debian Codegraph @

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

raw
1 --source include/galera_cluster.inc
2 --source include/have_innodb.inc
3 --source include/have_debug.inc
4 --source include/have_debug_sync.inc
5
6 #
7 # we must open connection node_1a here, MW-369.inc will use it later
8 #
9 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
10
11 #
12 # cascading delete operation is replicated from node2
13 # and this conflicts with an update for child table in node1
14 #
15 # As a result, the update should fail for certification error
16 #
17 --connection node_1
18
19 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
20 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
21 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1) ON DELETE CASCADE);
22
23 INSERT INTO p VALUES (1, 0);
24 INSERT INTO p VALUES (2, 0);
25
26 INSERT INTO c VALUES (1, 1, 0);
27
28 --let $mw_369_parent_query = UPDATE c SET f2=1 where f1=1
29 --let $mw_369_child_query = DELETE FROM p WHERE f1 = 1
30
31 --connection node_1a
32 --source MW-369.inc
33
34 # Commit fails
35 --connection node_1
36 --error ER_LOCK_DEADLOCK
37 --reap
38 SET DEBUG_SYNC = 'RESET';
39
40 --connection node_2
41 SELECT * FROM p;
42 SELECT * FROM c;
43
44 DROP TABLE c;
45 DROP TABLE p;
46
47 #
48 # cascading update operation is replicated from node2
49 # and this conflicts with an update for child table in node1
50 #
51 # As a result, the update should fail for certification error
52 #
53 --connection node_1
54
55 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
56 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
57 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1) ON UPDATE CASCADE);
58
59
60 INSERT INTO p VALUES (1, 0);
61 INSERT INTO p VALUES (2, 0);
62
63 INSERT INTO c VALUES (1, 1, 0);
64
65 --let $mw_369_parent_query = UPDATE c SET f2=2 where f1=1
66 --let $mw_369_child_query = UPDATE p set f1=11 WHERE f1 = 1
67
68 --connection node_1a
69 --source MW-369.inc
70
71 # Commit fails
72 --connection node_1
73 --error ER_LOCK_DEADLOCK
74 --reap
75 SET DEBUG_SYNC = 'RESET';
76
77 --connection node_2
78 SELECT * FROM p;
79 SELECT * FROM c;
80
81 DROP TABLE c;
82 DROP TABLE p;
83
84 #
85 # ON UPDATE CASCADE tests
86 # Here we update primary key of parent table to cause cascaded update
87 # on child table
88 #
89 # cascading update operation is replicated from node2
90 # and this conflicts with an update for child table in node1
91 #
92 # As a result, the update should fail for certification error
93 #
94 --connection node_1
95
96 CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
97 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
98 CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1) ON UPDATE CASCADE);
99
100
101 INSERT INTO p VALUES (1, 0);
102 INSERT INTO p VALUES (2, 0);
103
104 INSERT INTO c VALUES (1, 1, 0);
105
106 --let $mw_369_parent_query = UPDATE c SET p_id=2 where f1=1
107 --let $mw_369_child_query = UPDATE p set f1=11 WHERE f1 = 1
108
109 --connection node_1a
110 --source MW-369.inc
111
112 # Commit fails
113 --connection node_1
114 --error ER_LOCK_DEADLOCK
115 --reap
116 SET DEBUG_SYNC = 'RESET';
117
118 # same as previous, but statements in different order
119 --connection node_2
120 SELECT * FROM p;
121 SELECT * FROM c;
122
123 --let $mw_369_parent_query = UPDATE p set f1=21 WHERE f1 = 11
124 --let $mw_369_child_query = UPDATE c SET p_id=2 where f1=1
125
126 --connection node_1a
127 --source MW-369.inc
128
129 # Commit fails
130 --connection node_1
131 --error ER_LOCK_DEADLOCK
132 --reap
133 SET DEBUG_SYNC = 'RESET';
134
135 --connection node_2
136 SELECT * FROM p;
137 SELECT * FROM c;
138
139 DROP TABLE c;
140 DROP TABLE p;
141
142 #
143 # CASCADE DELETE tests with two parent tables
144 # Here we cause cascaded operation on child table through
145 # one parent table and have other operation on the other
146 # parent table
147 #
148 # cascading update operation is replicated from node2
149 # but this does not conflict with an update for the other parent table in node1
150 #
151 # As a result, the update on p2 should succeed
152 #
153 --connection node_1
154
155 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
156 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
157 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
158 f2 INTEGER,
159 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
160 ON DELETE CASCADE,
161 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1));
162
163 INSERT INTO p1 VALUES (1, 0);
164 INSERT INTO p2 VALUES (1, 0);
165
166 INSERT INTO c VALUES (1, 1, 1, 0);
167
168 --let $mw_369_parent_query = UPDATE p2 SET f2=2 where f1=1
169 --let $mw_369_child_query = DELETE FROM p1 WHERE f1 = 1
170
171 --connection node_1a
172 --source MW-369.inc
173
174 # Commit succeeds
175 --connection node_1
176 --reap
177 SET DEBUG_SYNC = 'RESET';
178
179 --connection node_2
180 SELECT * FROM p1;
181 SELECT * FROM p2;
182 SELECT * FROM c;
183
184 DROP TABLE c;
185 DROP TABLE p1;
186 DROP TABLE p2;
187
188 #
189 # CASCADE DELETE tests with two parent tables
190 # Here we cause cascaded operation on child table through
191 # one parent table and issue other delete operation through the
192 # other parent table. The cascade progresses to same child table row where
193 # we should see the conflict to happen
194 #
195 # As a result, the update on p2 should fail
196 #
197 --connection node_1
198
199 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
200 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
201 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
202 f2 INTEGER,
203 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
204 ON DELETE CASCADE,
205 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
206 ON DELETE CASCADE);
207
208 INSERT INTO p1 VALUES (1, 0);
209 INSERT INTO p2 VALUES (1, 0);
210
211 INSERT INTO c VALUES (1, 1, 1, 0);
212
213 --let $mw_369_parent_query = DELETE FROM p2 WHERE f1=1
214 --let $mw_369_child_query = DELETE FROM p1 WHERE f1=1
215
216 --connection node_1a
217 --source MW-369.inc
218
219 # Commit succeeds
220 --connection node_1
221 --error ER_LOCK_DEADLOCK
222 --reap
223 SET DEBUG_SYNC = 'RESET';
224
225 --connection node_2
226 SELECT * FROM p1;
227 SELECT * FROM p2;
228 SELECT * FROM c;
229
230 DROP TABLE c,p1,p2;