Debian Codegraph @

mysql-test/suite/innodb/r/virtual_fk.result

raw
1 #
2 # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
3 # WHEN A VIRTUAL INDEX EXISTS.
4 # UPDATE CASCADE
5 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
6 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
7 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
8 INSERT INTO t1 VALUES(1);
9 INSERT INTO t2 VALUES(1, DEFAULT);
10 UPDATE t1 SET fld1= 2;
11 EXPLAIN SELECT fld2 FROM t2;
12 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
13 x x t2 x index x fld2 x x x x x
14 SELECT fld2 FROM t2;
15 fld2
16 2
17 SELECT * FROM t2;
18 fld1 fld2
19 2 2
20 DROP TABLE t2, t1;
21 # UPDATE SET NULL
22 CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY,
23 KEY(fld1));
24 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
25 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE SET NULL);
26 INSERT INTO t1 VALUES(1, 2);
27 INSERT INTO t2 VALUES(1, DEFAULT);
28 UPDATE t1 SET fld1= 2;
29 EXPLAIN SELECT fld2 FROM t2;
30 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
31 x x t2 x index x fld2 x x x x x
32 SELECT fld2 FROM t2;
33 fld2
34 NULL
35 SELECT * FROM t2;
36 fld1 fld2
37 NULL NULL
38 DROP TABLE t2, t1;
39 # DELETE CASCADE
40 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
41 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
42 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
43 INSERT INTO t1 VALUES(1);
44 INSERT INTO t1 VALUES(2);
45 INSERT INTO t2 VALUES(1, DEFAULT);
46 INSERT INTO t2 VALUES(2, DEFAULT);
47 DELETE FROM t1 WHERE fld1= 1;
48 EXPLAIN SELECT fld2 FROM t2;
49 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
50 x x t2 x index x fld2 x x x x x
51 SELECT fld2 FROM t2;
52 fld2
53 2
54 SELECT * FROM t2;
55 fld1 fld2
56 2 2
57 DROP TABLE t2, t1;
58 # DELETE SET NULL
59 CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, KEY(fld1));
60 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
61 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE SET NULL);
62 INSERT INTO t1 VALUES(1, 1);
63 INSERT INTO t1 VALUES(2, 2);
64 INSERT INTO t2 VALUES(1, DEFAULT);
65 INSERT INTO t2 VALUES(2, DEFAULT);
66 DELETE FROM t1 WHERE fld1= 1;
67 EXPLAIN SELECT fld2 FROM t2;
68 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
69 x x t2 x index x fld2 x x x x x
70 SELECT fld2 FROM t2;
71 fld2
72 2
73 NULL
74 SELECT * FROM t2;
75 fld1 fld2
76 2 2
77 NULL NULL
78 DROP TABLE t2, t1;
79 # VIRTUAL INDEX CONTAINS FK CONSTRAINT COLUMN
80 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
81 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT, fld3 INT AS (fld2) VIRTUAL,
82 KEY(fld3, fld1),
83 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
84 INSERT INTO t1(fld1) VALUES(1);
85 INSERT INTO t2(fld1, fld2) VALUES(1, 3);
86 UPDATE t1 SET fld1= 2;
87 SELECT fld3, fld1 FROM t2;
88 fld3 fld1
89 3 2
90 EXPLAIN SELECT fld3, fld1 FROM t2;
91 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
92 x x t2 x index x fld3 x x x x x
93 SELECT * FROM t2;
94 fld1 fld2 fld3
95 2 3 3
96 DROP TABLE t2, t1;
97 # Multiple level of VIRTUAL columns.
98 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
99 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
100 fld3 INT AS (fld2) VIRTUAL, KEY(fld3), KEY(fld2),
101 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
102 INSERT INTO t1(fld1) VALUES(1);
103 INSERT INTO t2(fld1) VALUES(1);
104 UPDATE t1 SET fld1= 2;
105 SELECT fld2 FROM t2;
106 fld2
107 2
108 EXPLAIN SELECT fld2 FROM t2;
109 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
110 x x t2 x index x fld2 x x x x x
111 SELECT fld3 FROM t2;
112 fld3
113 2
114 EXPLAIN SELECT fld3 FROM t2;
115 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
116 x x t2 x index x fld3 x x x x x
117 SELECT * FROM t2;
118 fld1 fld2 fld3
119 2 2 2
120 DROP TABLE t2, t1;
121 # Drop the VIRTUAL INDEX using alter copy ALGORITHM
122 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
123 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY vk(fld2),
124 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
125 ON UPDATE CASCADE);
126 INSERT INTO t1(fld1) VALUES(1);
127 INSERT INTO t2(fld1) VALUES(1);
128 UPDATE t1 SET fld1= 2;
129 SELECT fld2, fld1 FROM t2;
130 fld2 fld1
131 2 2
132 EXPLAIN SELECT fld2, fld1 FROM t2;
133 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
134 x x t2 x ALL x NULL x x x x x
135 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
136 UPDATE t1 SET fld1= 3;
137 SELECT fld2, fld1 FROM t2;
138 fld2 fld1
139 3 3
140 EXPLAIN SELECT fld2, fld1 FROM t2;
141 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
142 x x t2 x ALL x NULL x x x x x
143 DROP TABLE t2, t1;
144 # Drop the VIRTUAL INDEX using INPLACE alter ALGORITHM
145 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
146 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
147 KEY vk(fld2), KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
148 ON UPDATE CASCADE);
149 INSERT INTO t1(fld1) VALUES(1);
150 INSERT INTO t2(fld1) VALUES(1);
151 UPDATE t1 SET fld1= 2;
152 SELECT fld2, fld1 FROM t2;
153 fld2 fld1
154 2 2
155 EXPLAIN SELECT fld2, fld1 FROM t2;
156 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
157 x x t2 x ALL x NULL x x x x x
158 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
159 UPDATE t1 SET fld1= 3;
160 SELECT fld2, fld1 FROM t2;
161 fld2 fld1
162 3 3
163 EXPLAIN SELECT fld2, fld1 FROM t2;
164 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
165 x x t2 x ALL x NULL x x x x x
166 DROP TABLE t2, t1;
167 # Add the VIRTUAL INDEX using COPY alter ALGORITHM
168 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
169 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
170 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
171 ON UPDATE CASCADE);
172 INSERT INTO t1(fld1) VALUES(1);
173 INSERT INTO t2(fld1) VALUES(1);
174 UPDATE t1 SET fld1= 2;
175 SELECT fld2, fld1 FROM t2;
176 fld2 fld1
177 2 2
178 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY;
179 UPDATE t1 SET fld1= 3;
180 SELECT fld2, fld1 FROM t2;
181 fld2 fld1
182 3 3
183 EXPLAIN SELECT fld2, fld1 FROM t2;
184 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
185 x x t2 x ALL x NULL x x x x x
186 DROP TABLE t2, t1;
187 # Add the VIRTUAL INDEX using INPLACE alter ALGORITHM
188 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
189 CREATE TABLE t2(fld1 INT NOT NULL,fld2 INT AS (fld1) VIRTUAL,
190 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
191 ON UPDATE CASCADE);
192 INSERT INTO t1(fld1) VALUES(1);
193 INSERT INTO t2(fld1) VALUES(1);
194 UPDATE t1 SET fld1= 2;
195 SELECT fld2, fld1 FROM t2;
196 fld2 fld1
197 2 2
198 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE;
199 UPDATE t1 SET fld1= 3;
200 SELECT fld2, fld1 FROM t2;
201 fld2 fld1
202 3 3
203 EXPLAIN SELECT fld2, fld1 FROM t2;
204 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
205 x x t2 x ALL x NULL x x x x x
206 DROP TABLE t2, t1;
207 # Drop the VIRTUAL INDEX contains fk constraint column
208 # using alter copy ALGORITHM
209 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
210 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
211 fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
212 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
213 ON UPDATE CASCADE);
214 INSERT INTO t1(fld1) VALUES(1);
215 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
216 UPDATE t1 SET fld1= 2;
217 EXPLAIN SELECT fld3, fld1 FROM t2;
218 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
219 x x t2 x index x vk x x x x x
220 SELECT fld3, fld1 FROM t2;
221 fld3 fld1
222 2 2
223 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
224 UPDATE t1 SET fld1= 3;
225 SELECT fld3, fld1 FROM t2;
226 fld3 fld1
227 2 3
228 EXPLAIN SELECT fld3, fld1 FROM t2;
229 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
230 x x t2 x ALL x NULL x x x x x
231 DROP TABLE t2, t1;
232 # Drop the VIRTUAL INDEX which contains fk constraint column
233 # using INPLACE alter operation
234 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
235 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
236 fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
237 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
238 ON UPDATE CASCADE);
239 INSERT INTO t1(fld1) VALUES(1);
240 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
241 UPDATE t1 SET fld1= 2;
242 EXPLAIN SELECT fld3, fld1 FROM t2;
243 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
244 x x t2 x index x vk x x x x x
245 SELECT fld3, fld1 FROM t2;
246 fld3 fld1
247 2 2
248 alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE;
249 UPDATE t1 SET fld1= 3;
250 SELECT fld3, fld1 FROM t2;
251 fld3 fld1
252 2 3
253 EXPLAIN SELECT fld3, fld1 FROM t2;
254 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
255 x x t2 x ALL x NULL x x x x x
256 DROP TABLE t2, t1;
257 # Add the VIRTUAL INDEX contains fk constraint column
258 # using copy alter operatiON
259 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
260 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
261 fld3 INT AS (fld2) VIRTUAL, KEY(fld1),
262 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
263 INSERT INTO t1(fld1) VALUES(1);
264 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
265 UPDATE t1 SET fld1= 2;
266 SELECT fld3, fld1 FROM t2;
267 fld3 fld1
268 2 2
269 alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY;
270 UPDATE t1 SET fld1= 3;
271 SELECT fld3, fld1 FROM t2;
272 fld3 fld1
273 2 3
274 EXPLAIN SELECT fld3, fld1 FROM t2;
275 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
276 x x t2 x index x vk x x x x x
277 DROP TABLE t2, t1;
278 # Cascading UPDATEs and DELETEs for the multiple
279 # fk dependent TABLEs
280 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
281 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
282 KEY(fld1), KEY(fld2, fld1),
283 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
284 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
285 KEY(fld2, fld1),
286 FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
287 INSERT INTO t1 VALUES(1), (2);
288 INSERT INTO t2(fld1) VALUES(1), (2);
289 INSERT INTO t3(fld1) VALUES(1), (2);
290 UPDATE t1 SET fld1= 4 WHERE fld1= 1;
291 EXPLAIN SELECT fld2, fld1 FROM t2;
292 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
293 x x t2 x index x fld2 x x x x x
294 SELECT fld2, fld1 FROM t2;
295 fld2 fld1
296 2 2
297 4 4
298 EXPLAIN SELECT fld2, fld1 FROM t3;
299 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
300 x x t3 x index x fld2 x x x x x
301 SELECT fld2, fld1 FROM t3;
302 fld2 fld1
303 2 2
304 4 4
305 DROP TABLE t3, t2, t1;
306 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
307 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
308 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), KEY(fld1),
309 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
310 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
311 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
312 FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
313 INSERT INTO t1 VALUES(1), (2);
314 INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
315 INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
316 UPDATE t1 SET fld1= 4 WHERE fld1= 1;
317 EXPLAIN SELECT fld3, fld1 FROM t2;
318 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
319 x x t2 x index x fld3 x x x x x
320 SELECT fld3, fld1 FROM t2;
321 fld3 fld1
322 1 4
323 2 2
324 EXPLAIN SELECT fld3, fld1 FROM t3;
325 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
326 x x t3 x index x fld3 x x x x x
327 SELECT fld3, fld1 FROM t3;
328 fld3 fld1
329 1 4
330 2 2
331 DROP TABLE t3, t2, t1;
332 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
333 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
334 KEY(fld1), KEY(fld2, fld1),
335 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
336 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
337 KEY(fld2, fld1), FOREIGN KEY(fld1) REFERENCES t2(fld1)
338 ON DELETE CASCADE);
339 INSERT INTO t1 VALUES(1), (2);
340 INSERT INTO t2(fld1) VALUES(1), (2);
341 INSERT INTO t3(fld1) VALUES(1), (2);
342 DELETE FROM t1 WHERE fld1= 1;
343 EXPLAIN SELECT fld2, fld1 FROM t2;
344 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
345 x x t2 x index x fld2 x x x x x
346 SELECT fld2, fld1 FROM t2;
347 fld2 fld1
348 2 2
349 EXPLAIN SELECT fld2, fld1 FROM t3;
350 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
351 x x t3 x index x fld2 x x x x x
352 SELECT fld2, fld1 FROM t3;
353 fld2 fld1
354 2 2
355 DROP TABLE t3, t2, t1;
356 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
357 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
358 fld3 INT AS (fld2) VIRTUAL,
359 KEY(fld3, fld1), KEY(fld1),
360 FOREIGN KEY(fld1) REFERENCES t1(fld1)
361 ON DELETE CASCADE);
362 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
363 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
364 FOREIGN KEY(fld1) REFERENCES t2(fld1)
365 ON DELETE CASCADE);
366 INSERT INTO t1 VALUES(1), (2);
367 INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
368 INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
369 DELETE FROM t1 WHERE fld1= 1;
370 EXPLAIN SELECT fld3, fld1 FROM t2;
371 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
372 x x t2 x index x fld3 x x x x x
373 SELECT fld3, fld1 FROM t2;
374 fld3 fld1
375 2 2
376 EXPLAIN SELECT fld3, fld1 FROM t3;
377 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
378 x x t3 x index x fld3 x x x x x
379 SELECT fld3, fld1 FROM t3;
380 fld3 fld1
381 2 2
382 DROP TABLE t3, t2, t1;
383 # RENAME TABLE
384 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
385 CREATE TABLE t2(fld1 INT NOT NULL,
386 fld2 INT AS (fld1) VIRTUAL,
387 KEY(fld2, fld1),
388 FOREIGN KEY(fld1) REFERENCES t1(fld1)
389 ON DELETE CASCADE);
390 INSERT INTO t1 VALUES(1), (2);
391 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
392 RENAME TABLE t2 to t3;
393 DELETE FROM t1 WHERE fld1= 1;
394 EXPLAIN SELECT fld2, fld1 FROM t3;
395 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
396 x x t3 x index x fld2 x x x x x
397 SELECT fld2, fld1 FROM t3;
398 fld2 fld1
399 2 2
400 DROP TABLE t3, t1;
401 # FOREIGN_KEY_CHECKS disabled DURING INPLACE ALTER
402 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
403 CREATE TABLE t2(fld1 INT NOT NULL,
404 fld2 INT AS (fld1) VIRTUAL,
405 FOREIGN KEY(fld1) REFERENCES t1(fld1)
406 ON UPDATE CASCADE);
407 INSERT INTO t1 VALUES(1), (2);
408 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
409 SET foreign_key_checks = 0;
410 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM=INPLACE;
411 SET foreign_key_checks = 1;
412 UPDATE t1 SET fld1= 3 WHERE fld1= 2;
413 SELECT fld2 FROM t2;
414 fld2
415 1
416 3
417 EXPLAIN SELECT fld2 FROM t2;
418 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
419 x x t2 x index x vk x x x x x
420 DROP TABLE t2, t1;
421 # GENERATED COLUMN COMPUTATION FAILS when SQL_MODE
422 # is set to ERROR_FOR_DIVISION_BY_ZERO
423 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
424 CREATE TABLE t2(fld1 INT NOT NULL,
425 fld2 INT AS (100/fld1) VIRTUAL,
426 KEY(fld2),
427 FOREIGN KEY(fld1) REFERENCES t1(fld1)
428 ON UPDATE CASCADE);
429 INSERT INTO t1 VALUES(1), (2);
430 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
431 UPDATE t1 SET fld1= 0 WHERE fld1= 2;
432 ERROR 22012: Division by 0
433 EXPLAIN SELECT fld2 FROM t2;
434 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
435 x x t2 x index x fld2 x x x x x
436 SELECT fld2 FROM t2;
437 fld2
438 100
439 50
440 DROP TABLE t2, t1;
441 # CHANGE SQL_MODE and try the ERROR_FOR_DIVISION_BY_ZERO
442 SET sql_mode = STRICT_ALL_TABLES;
443 Warnings:
444 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
445 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
446 CREATE TABLE t2(fld1 INT NOT NULL,
447 fld2 INT AS (100/fld1) VIRTUAL,
448 KEY(fld2),
449 FOREIGN KEY(fld1) REFERENCES t1(fld1)
450 ON UPDATE CASCADE);
451 INSERT INTO t1 VALUES(1), (2);
452 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
453 UPDATE t1 SET fld1= 0 WHERE fld1= 2;
454 EXPLAIN SELECT fld2 FROM t2;
455 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
456 x x t2 x index x fld2 x x x x x
457 SELECT fld2 FROM t2;
458 fld2
459 100
460 NULL
461 SELECT * FROM t2;
462 fld1 fld2
463 1 100
464 0 NULL
465 DROP TABLE t2, t1;
466 SET sql_mode = default;
467 # ADD FOREIGN CONSTRAINT USING COPY
468 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
469 CREATE TABLE t2(fld1 INT NOT NULL,
470 fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
471 ALTER TABLE t2 ADD FOREIGN KEY (fld1)
472 REFERENCES t1(fld1) ON UPDATE CASCADE,
473 ALGORITHM=copy;
474 INSERT INTO t1 VALUES(1);
475 INSERT INTO t2 VALUES(1, DEFAULT);
476 UPDATE t1 SET fld1= 2;
477 EXPLAIN SELECT fld2 FROM t2;
478 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
479 x x t2 x index x fld2 x x x x x
480 SELECT fld2 FROM t2;
481 fld2
482 2
483 SELECT * FROM t2;
484 fld1 fld2
485 2 2
486 DROP TABLE t2, t1;
487 # ADD FOREIGN CONSTRAINT USING INPLACE
488 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
489 CREATE TABLE t2(fld1 INT NOT NULL,
490 fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
491 SET foreign_key_checks = 0;
492 ALTER TABLE t2 ADD FOREIGN KEY (fld1)
493 REFERENCES t1(fld1) ON UPDATE CASCADE,
494 ALGORITHM=inplace;
495 SET foreign_key_checks = 1;
496 INSERT INTO t1 VALUES(1);
497 INSERT INTO t2 VALUES(1, DEFAULT);
498 UPDATE t1 SET fld1= 2;
499 EXPLAIN SELECT fld2 FROM t2;
500 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
501 x x t2 x index x fld2 x x x x x
502 SELECT fld2 FROM t2;
503 fld2
504 2
505 SELECT * FROM t2;
506 fld1 fld2
507 2 2
508 DROP TABLE t2, t1;
509 # DROP FOREIGN CONSTRAINT USING COPY
510 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
511 CREATE TABLE t2(fld1 INT NOT NULL,
512 fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
513 CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
514 ON UPDATE CASCADE);
515 INSERT INTO t1 VALUES(1);
516 INSERT INTO t2 VALUES(1, DEFAULT);
517 ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=COPY;
518 UPDATE t1 SET fld1= 2;
519 EXPLAIN SELECT fld2 FROM t2;
520 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
521 x x t2 x index x fld2 x x x x x
522 SELECT fld2 FROM t2;
523 fld2
524 1
525 SELECT * FROM t2;
526 fld1 fld2
527 1 1
528 DROP TABLE t2, t1;
529 # DROP FOREIGN CONSTRAINT USING INPLACE
530 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
531 CREATE TABLE t2(fld1 INT NOT NULL,
532 fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
533 CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
534 ON UPDATE CASCADE);
535 SET foreign_key_checks = 0;
536 ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
537 SET foreign_key_checks = 1;
538 INSERT INTO t1 VALUES(1);
539 INSERT INTO t2 VALUES(1, DEFAULT);
540 UPDATE t1 SET fld1= 2;
541 EXPLAIN SELECT fld2 FROM t2;
542 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
543 x x t2 x index x fld2 x x x x x
544 SELECT fld2 FROM t2;
545 fld2
546 1
547 SELECT * FROM t2;
548 fld1 fld2
549 1 1
550 DROP TABLE t2, t1;
551 # ADD VC INDEX and ADD FK IN SAME COPY ALTER
552 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
553 CREATE TABLE t2(fld1 INT NOT NULL,
554 fld2 INT AS (fld1) VIRTUAL);
555 INSERT INTO t1 VALUES(1);
556 INSERT INTO t2 VALUES(1, DEFAULT);
557 ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
558 ON UPDATE CASCADE, ALGORITHM=copy;
559 UPDATE t1 SET fld1= 2;
560 EXPLAIN SELECT fld2 FROM t2;
561 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
562 x x t2 x index x fld2 x x x x x
563 SELECT fld2 FROM t2;
564 fld2
565 2
566 SELECT * FROM t2;
567 fld1 fld2
568 2 2
569 DROP TABLE t2, t1;
570 # ADD VC INDEX and ADD FK IN SAME INPLACE ALTER
571 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
572 CREATE TABLE t2(fld1 INT NOT NULL,
573 fld2 INT AS (fld1) VIRTUAL);
574 INSERT INTO t1 VALUES(1);
575 INSERT INTO t2 VALUES(1, DEFAULT);
576 SET foreign_key_checks = 0;
577 ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
578 ON UPDATE CASCADE, ALGORITHM=inplace;
579 SET foreign_key_checks = 1;
580 UPDATE t1 SET fld1= 2;
581 EXPLAIN SELECT fld2 FROM t2;
582 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
583 x x t2 x index x fld2 x x x x x
584 SELECT fld2 FROM t2;
585 fld2
586 2
587 SELECT * FROM t2;
588 fld1 fld2
589 2 2
590 DROP TABLE t2, t1;
591 # ADD VC INDEX and DROP FK IN SAME COPY ALTER
592 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
593 CREATE TABLE t2(fld1 INT NOT NULL,
594 fld2 INT AS (fld1) VIRTUAL,
595 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
596 ON UPDATE CASCADE);
597 INSERT INTO t1 VALUES(1);
598 INSERT INTO t2 VALUES(1, DEFAULT);
599 ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=copy;
600 UPDATE t1 SET fld1= 2;
601 EXPLAIN SELECT fld2 FROM t2;
602 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
603 x x t2 x index x fld2 x x x x x
604 SELECT fld2 FROM t2;
605 fld2
606 1
607 SELECT * FROM t2;
608 fld1 fld2
609 1 1
610 DROP TABLE t2, t1;
611 # ADD VC INDEX and DROP FK IN SAME INPLACE ALTER
612 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
613 CREATE TABLE t2(fld1 INT NOT NULL,
614 fld2 INT AS (fld1) VIRTUAL,
615 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
616 ON UPDATE CASCADE);
617 INSERT INTO t1 VALUES(1);
618 INSERT INTO t2 VALUES(1, DEFAULT);
619 SET foreign_key_checks = 0;
620 ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=inplace;
621 SET foreign_key_checks = 1;
622 UPDATE t1 SET fld1= 2;
623 EXPLAIN SELECT fld2 FROM t2;
624 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
625 x x t2 x index x fld2 x x x x x
626 SELECT fld2 FROM t2;
627 fld2
628 1
629 SELECT * FROM t2;
630 fld1 fld2
631 1 1
632 DROP TABLE t2, t1;
633 # DROP VC INDEX and ADD FK IN SAME COPY ALTER
634 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
635 CREATE TABLE t2(fld1 INT NOT NULL,
636 fld2 INT AS (fld1) VIRTUAL,
637 KEY idx(fld2));
638 INSERT INTO t1 VALUES(1);
639 INSERT INTO t2 VALUES(1, DEFAULT);
640 ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
641 ON UPDATE CASCADE, ALGORITHM=COPY;
642 UPDATE t1 SET fld1= 2;
643 EXPLAIN SELECT fld2 FROM t2;
644 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
645 x x t2 x ALL x NULL x x x x x
646 SELECT fld2 FROM t2;
647 fld2
648 2
649 SELECT * FROM t2;
650 fld1 fld2
651 2 2
652 DROP TABLE t2, t1;
653 # DROP VC INDEX and ADD FK IN SAME INPLACE ALTER
654 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
655 CREATE TABLE t2(fld1 INT NOT NULL,
656 fld2 INT AS (fld1) VIRTUAL,
657 KEY idx(fld2));
658 INSERT INTO t1 VALUES(1);
659 INSERT INTO t2 VALUES(1, DEFAULT);
660 SET foreign_key_checks = 0;
661 ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
662 ON UPDATE CASCADE, ALGORITHM=INPLACE;
663 SET foreign_key_checks = 1;
664 UPDATE t1 SET fld1= 2;
665 EXPLAIN SELECT fld2 FROM t2;
666 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
667 x x t2 x ALL x NULL x x x x x
668 SELECT fld2 FROM t2;
669 fld2
670 2
671 SELECT * FROM t2;
672 fld1 fld2
673 2 2
674 DROP TABLE t2, t1;
675 # DROP VC INDEX and DROP FK IN SAME COPY ALTER
676 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
677 CREATE TABLE t2(fld1 INT NOT NULL,
678 fld2 INT AS (fld1) VIRTUAL,
679 KEY idx(fld2),
680 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
681 ON UPDATE CASCADE);
682 INSERT INTO t1 VALUES(1);
683 INSERT INTO t2 VALUES(1, DEFAULT);
684 ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=COPY;
685 UPDATE t1 SET fld1= 2;
686 EXPLAIN SELECT fld2 FROM t2;
687 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
688 x x t2 x ALL x NULL x x x x x
689 SELECT fld2 FROM t2;
690 fld2
691 1
692 SELECT * FROM t2;
693 fld1 fld2
694 1 1
695 DROP TABLE t2, t1;
696 # DROP VC INDEX and DROP FK IN SAME INPLACE ALTER
697 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
698 CREATE TABLE t2(fld1 INT NOT NULL,
699 fld2 INT AS (fld1) VIRTUAL,
700 KEY idx(fld2),
701 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
702 ON UPDATE CASCADE);
703 INSERT INTO t1 VALUES(1);
704 INSERT INTO t2 VALUES(1, DEFAULT);
705 SET foreign_key_checks = 0;
706 ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
707 SET foreign_key_checks = 1;
708 UPDATE t1 SET fld1= 2;
709 EXPLAIN SELECT fld2 FROM t2;
710 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
711 x x t2 x ALL x NULL x x x x x
712 SELECT fld2 FROM t2;
713 fld2
714 1
715 SELECT * FROM t2;
716 fld1 fld2
717 1 1
718 DROP TABLE t2, t1;
719 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
720 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
721 KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
722 INSERT INTO t1 VALUES(1);
723 INSERT INTO t2(f1) VALUES(1);
724 EXPLAIN SELECT f1, f2 FROM t2;
725 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
726 1 SIMPLE t2 NULL index NULL f1 9 NULL 1 100.00 Using index
727 Warnings:
728 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2`
729 SELECT f1, f2 FROM t2;
730 f1 f2
731 1 1
732 INSERT INTO t2(f1) VALUES(2);
733 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`))
734 DROP TABLE t2, t1;
735 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
736 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
737 KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1)
738 ON UPDATE CASCADE)ENGINE=INNODB;
739 INSERT INTO t1 VALUES(1);
740 INSERT INTO t2(f1) VALUES(1);
741 EXPLAIN SELECT f1, f2 FROM t2;
742 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
743 1 SIMPLE t2 NULL index NULL f1 9 NULL 1 100.00 Using index
744 Warnings:
745 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2`
746 SELECT f1, f2 FROM t2;
747 f1 f2
748 1 1
749 UPDATE t1 SET f1 = 2 WHERE f1 = 1;
750 EXPLAIN SELECT f1, f2 FROM t2;
751 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
752 1 SIMPLE t2 NULL index NULL f1 9 NULL 1 100.00 Using index
753 Warnings:
754 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2`
755 SELECT f1, f2 FROM t2;
756 f1 f2
757 2 2
758 DROP TABLE t2, t1;
759 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
760 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
761 KEY (f1, f2))ENGINE=INNODB;
762 INSERT INTO t1 VALUES(1);
763 INSERT INTO t2(f1) VALUES(1);
764 SET FOREIGN_KEY_CHECKS = 0;
765 ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
766 ON UPDATE CASCADE, ALGORITHM=INPLACE;
767 SET FOREIGN_KEY_CHECKS = 1;
768 UPDATE t1 SET f1 = 3;
769 EXPLAIN SELECT f1, f2 FROM t2;
770 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
771 1 SIMPLE t2 NULL index NULL f1 9 NULL 1 100.00 Using index
772 Warnings:
773 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2`
774 SELECT f1, f2 FROM t2;
775 f1 f2
776 3 3
777 DROP TABLE t2, t1;
778 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
779 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
780 KEY (f1, f2))ENGINE=INNODB;
781 INSERT INTO t1 VALUES(1);
782 INSERT INTO t2(f1) VALUES(1);
783 ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
784 ON UPDATE CASCADE, ALGORITHM=COPY;
785 UPDATE t1 SET f1 = 3;
786 EXPLAIN SELECT f1, f2 FROM t2;
787 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
788 1 SIMPLE t2 NULL index NULL f1 9 NULL 1 100.00 Using index
789 Warnings:
790 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2`
791 SELECT f1, f2 FROM t2;
792 f1 f2
793 3 3
794 DROP TABLE t2, t1;
795 CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
796 CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
797 f3 INT AS (2) VIRTUAL,
798 FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
799 KEY idx1 (f2, f1, f3))ENGINE=INNODB;
800 INSERT INTO t1 VALUES(1);
801 INSERT INTO t2(f1) VALUES(1);
802 ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=INPLACE;
803 UPDATE t1 SET f1 = 3;
804 EXPLAIN SELECT f1, f3 FROM t2;
805 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
806 1 SIMPLE t2 NULL index NULL idx1 9 NULL 1 100.00 Using index
807 Warnings:
808 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f3` AS `f3` from `test`.`t2`
809 SELECT f1, f3 FROM t2;
810 f1 f3
811 3 2
812 DROP TABLE t2, t1;
813 CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
814 CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
815 f3 INT AS (2) VIRTUAL,
816 FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
817 KEY idx1 (f2, f1, f3))ENGINE=INNODB;
818 INSERT INTO t1 VALUES(1);
819 INSERT INTO t2(f1) VALUES(1);
820 ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=COPY;
821 UPDATE t1 SET f1 = 3;
822 EXPLAIN SELECT f1, f3 FROM t2;
823 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
824 1 SIMPLE t2 NULL index NULL idx1 9 NULL 1 100.00 Using index
825 Warnings:
826 Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f3` AS `f3` from `test`.`t2`
827 SELECT f1, f3 FROM t2;
828 f1 f3
829 3 2
830 DROP TABLE t2, t1;
831 #
832 # Bug#32124113 MYSQL CRASH FOR GENERATED COLUMN DELETE WITH FOREIGN KEY
833 #
834 CREATE TABLE `emails` (
835 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
836 PRIMARY KEY (`id`)
837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
838 ROW_FORMAT=DYNAMIC;
839 Warnings:
840 Warning 1681 Integer display width is deprecated and will be removed in a future release.
841 CREATE TABLE `email_stats` (
842 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
843 `email_id` int(10) unsigned DEFAULT NULL,
844 `date_sent` datetime NOT NULL,
845 `generated_sent_date` date GENERATED ALWAYS AS
846 (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
847 '-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
848 PRIMARY KEY (`id`),
849 KEY `IDX_ES1` (`email_id`),
850 KEY `mautic_generated_sent_date_email_id`
851 (`generated_sent_date`,`email_id`),
852 CONSTRAINT `FK_ES1` FOREIGN KEY (`email_id`) REFERENCES
853 `emails` (`id`) ON DELETE SET NULL
854 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
855 ROW_FORMAT=DYNAMIC;
856 Warnings:
857 Warning 1681 Integer display width is deprecated and will be removed in a future release.
858 Warning 1681 Integer display width is deprecated and will be removed in a future release.
859 CREATE TABLE `emails_metadata` (
860 `email_id` int(10) unsigned NOT NULL,
861 PRIMARY KEY (`email_id`),
862 CONSTRAINT `FK_EM1` FOREIGN KEY (`email_id`) REFERENCES
863 `emails` (`id`) ON DELETE CASCADE
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
865 ROW_FORMAT=DYNAMIC;
866 Warnings:
867 Warning 1681 Integer display width is deprecated and will be removed in a future release.
868 INSERT INTO `emails` VALUES (1);
869 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
870 (1,1,'2020-10-22 13:32:41');
871 INSERT INTO `emails_metadata` VALUES (1);
872 DELETE FROM `emails`;
873 DROP TABLE `email_stats`;
874 DROP TABLE `emails_metadata`;
875 DROP TABLE `emails`;