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