Debian Codegraph @

mysql-test/suite/galera/t/galera_fk_cascade_delete.test

raw
1 #
2 # Test Foreign Key Cascading DELETEs
3 #
4
5 --source include/galera_cluster.inc
6
7 --echo #
8 --echo # test phase with cascading foreign key through 3 tables
9 --echo #
10
11 --connection node_1
12
13 CREATE TABLE grandparent (
14 id INT NOT NULL PRIMARY KEY
15 ) ENGINE=InnoDB;
16
17 CREATE TABLE parent (
18 id INT NOT NULL PRIMARY KEY,
19 grandparent_id INT,
20 FOREIGN KEY (grandparent_id)
21 REFERENCES grandparent(id)
22 ON DELETE CASCADE
23 ) ENGINE=InnoDB;
24
25 CREATE TABLE child (
26 id INT NOT NULL PRIMARY KEY,
27 parent_id INT,
28 FOREIGN KEY (parent_id)
29 REFERENCES parent(id)
30 ON DELETE CASCADE
31 ) ENGINE=InnoDB;
32
33 INSERT INTO grandparent VALUES (1),(2);
34 INSERT INTO parent VALUES (1,1), (2,2);
35 INSERT INTO child VALUES (1,1), (2,2);
36
37 --connection node_2
38 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'grandparent'
39 --source include/wait_condition.inc
40 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parent'
41 --source include/wait_condition.inc
42 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'child'
43 --source include/wait_condition.inc
44 --let $wait_condition = SELECT COUNT(*) = 2 FROM child;
45 --source include/wait_condition.inc
46 --let $wait_condition = SELECT COUNT(*) = 2 FROM parent;
47 --source include/wait_condition.inc
48 --let $wait_condition = SELECT COUNT(*) = 2 FROM grandparent;
49 --source include/wait_condition.inc
50 DELETE FROM grandparent WHERE id = 1;
51
52 SELECT * FROM grandparent;
53 SELECT * FROM parent;
54 SELECT * FROM child;
55
56 --connection node_1
57 --let $wait_condition = SELECT COUNT(*) = 1 FROM child;
58 --source include/wait_condition.inc
59 --let $wait_condition = SELECT COUNT(*) = 1 FROM parent;
60 --source include/wait_condition.inc
61 --let $wait_condition = SELECT COUNT(*) = 1 FROM grandparent;
62 --source include/wait_condition.inc
63
64 SELECT * FROM grandparent;
65 SELECT * FROM parent;
66 SELECT * FROM child;
67
68 DROP TABLE child;
69 DROP TABLE parent;
70 DROP TABLE grandparent;
71
72 --echo
73 --echo Scenario 2, testing PA applying with FK cascade delete
74 --echo
75
76 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
77 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
78 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
79 f2 INTEGER,
80 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
81 ON DELETE CASCADE,
82 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
83 ON DELETE CASCADE);
84
85 --let $count = 100
86 --disable_query_log
87 while ($count)
88 {
89 --eval INSERT INTO p1 VALUES ($count, 0);
90 --eval INSERT INTO p2 VALUES ($count, 0);
91 --eval INSERT INTO c VALUES ($count, $count, $count, 0);
92 --dec $count
93 }
94
95 --connection node_2
96 set global wsrep_slave_threads=2;
97
98 --connection node_1
99 --let $count = 100
100 while ($count)
101 {
102 --eval DELETE FROM p2 WHERE f1=$count;
103 --eval DELETE FROM p1 WHERE f1=$count;
104
105 --dec $count
106 }
107 --enable_query_log
108
109 --connection node_2
110 set global wsrep_slave_threads=DEFAULT;
111
112
113 SELECT * FROM p1;
114 SELECT * FROM p2;
115 SELECT * FROM c;
116
117 --connection node_1
118 DROP TABLE c;
119 DROP TABLE p1,p2;
120
121 --echo
122 --echo Scenario 4, testing PA applying with FK cascade delete on
123 --echo more than one level
124 --echo
125 CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
126 CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
127 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
128 f2 INTEGER,
129 CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
130 ON DELETE CASCADE
131 ) ENGINE=INNODB;
132 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
133 f2 INTEGER,
134 CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
135 ON DELETE CASCADE
136 ) ENGINE=INNODB;
137 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
138 f2 INTEGER,
139 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
140 ON DELETE CASCADE,
141 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
142 ON DELETE CASCADE) ENGINE=INNODB;
143
144 --let $count = 100
145 --disable_query_log
146 while ($count)
147 {
148 --eval INSERT INTO gp1 VALUES ($count, 0);
149 --eval INSERT INTO gp2 VALUES ($count, 0);
150 --eval INSERT INTO p1 VALUES ($count, $count, $count, 0);
151 --eval INSERT INTO p2 VALUES ($count, $count, $count, 0);
152 --eval INSERT INTO c VALUES ($count, $count, $count, 0);
153 --dec $count
154 }
155
156 --connection node_2
157 set global wsrep_slave_threads=2;
158
159 --connection node_1
160 --let $count = 100
161 while ($count)
162 {
163 --eval DELETE FROM gp1 WHERE f1=$count;
164 --eval DELETE FROM gp2 WHERE f1=$count;
165
166 --dec $count
167 }
168 --enable_query_log
169
170 --connection node_2
171 set global wsrep_slave_threads=DEFAULT;
172
173 SELECT * FROM gp1;
174 SELECT * FROM gp2;
175 SELECT * FROM p1;
176 SELECT * FROM p2;
177 SELECT * FROM c;
178
179 --connection node_1
180 DROP TABLE c;
181 DROP TABLE p1,p2;
182 DROP TABLE gp1,gp2;
183
184 --echo
185 --echo Scenario 3, testing PA applying with FK cascade delete on
186 --echo more than one level in a diamond topology
187 --echo
188 CREATE TABLE ggp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
189 CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
190 f2 INTEGER,
191 CONSTRAINT pfk_6 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
192 ON DELETE CASCADE
193 ) ENGINE=INNODB;
194 CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
195 f2 INTEGER,
196 CONSTRAINT pfk_5 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
197 ON DELETE CASCADE
198 ) ENGINE=INNODB;
199 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
200 f2 INTEGER,
201 CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
202 ON DELETE CASCADE
203 ) ENGINE=INNODB;
204 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
205 f2 INTEGER,
206 CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
207 ON DELETE CASCADE
208 ) ENGINE=INNODB;
209 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
210 f2 INTEGER,
211 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
212 ON DELETE CASCADE,
213 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
214 ON DELETE CASCADE) ENGINE=INNODB;
215
216 --let $count = 100
217 --disable_query_log
218 while ($count)
219 {
220 --eval INSERT INTO ggp1 VALUES ($count, 0);
221 --eval INSERT INTO gp1 VALUES ($count, $count, $count, 0);
222 --eval INSERT INTO gp2 VALUES ($count, $count, $count, 0);
223 --eval INSERT INTO p1 VALUES ($count, $count, $count, 0);
224 --eval INSERT INTO p2 VALUES ($count, $count, $count, 0);
225 --eval INSERT INTO c VALUES ($count, $count, $count, 0);
226 --dec $count
227 }
228
229 --connection node_2
230 set global wsrep_slave_threads=2;
231
232 --connection node_1
233 --let $count = 100
234 while ($count)
235 {
236 --eval DELETE FROM ggp1 WHERE f1=$count;
237
238 --dec $count
239 }
240 --enable_query_log
241
242 --connection node_2
243 set global wsrep_slave_threads=DEFAULT;
244
245 SELECT * FROM ggp1;
246 SELECT * FROM gp2;
247 SELECT * FROM gp1;
248 SELECT * FROM p1;
249 SELECT * FROM p2;
250 SELECT * FROM c;
251
252 --connection node_1
253 DROP TABLE c;
254 DROP TABLE p1,p2;
255 DROP TABLE gp1,gp2;
256 DROP TABLE ggp1;