Debian Codegraph @

mysql-test/suite/innodb/t/foreign_key.test

raw
1 --echo #
2 --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
3 --echo # DICT_CREATE_FOREIGN_CONSTR
4 --echo #
5
6 create table t1 (f1 int primary key) engine=InnoDB;
7 --error ER_FK_DUP_NAME
8 create table t2 (f1 int primary key,
9 constraint c1 foreign key (f1) references t1(f1),
10 constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
11 create table t2 (f1 int primary key,
12 constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
13
14 --error ER_FK_DUP_NAME
15 alter table t2 add constraint c1 foreign key (f1) references t1(f1);
16
17 set foreign_key_checks = 0;
18 --error ER_FK_DUP_NAME
19 alter table t2 add constraint c1 foreign key (f1) references t1(f1);
20
21 drop table t2, t1;
22
23 --echo #
24 --echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
25 --echo # NULL/NOT NULL MISMATCH
26 --echo #
27
28 set foreign_key_checks = 1;
29 show variables like 'foreign_key_checks';
30
31 CREATE TABLE t1
32 (a INT NOT NULL,
33 b INT NOT NULL,
34 INDEX idx(a)) ENGINE=InnoDB;
35
36 CREATE TABLE t2
37 (a INT KEY,
38 b INT,
39 INDEX ind(b),
40 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
41 ENGINE=InnoDB;
42
43 show create table t1;
44 show create table t2;
45
46 INSERT INTO t1 VALUES (1, 80);
47 INSERT INTO t1 VALUES (2, 81);
48 INSERT INTO t1 VALUES (3, 82);
49 INSERT INTO t1 VALUES (4, 83);
50 INSERT INTO t1 VALUES (5, 84);
51
52 INSERT INTO t2 VALUES (51, 1);
53 INSERT INTO t2 VALUES (52, 2);
54 INSERT INTO t2 VALUES (53, 3);
55 INSERT INTO t2 VALUES (54, 4);
56 INSERT INTO t2 VALUES (55, 5);
57
58 SELECT a, b FROM t1 ORDER BY a;
59 SELECT a, b FROM t2 ORDER BY a;
60
61 --error ER_NO_REFERENCED_ROW_2
62 INSERT INTO t2 VALUES (56, 6);
63
64 --echo NewDD TODO: Runtime Bug#25722221 - RENAME COLUMN DID NOT UPDATE MYSQL.FOREIGN_KEY_COLUMN_USAGE FOR FK CONSTRAINT
65 #ALTER TABLE t1 CHANGE a id INT;
66
67 # SELECT id, b FROM t1 ORDER BY id;
68 # SELECT a, b FROM t2 ORDER BY a;
69
70 --echo # Operations on child table
71 --echo TODO: Waiting for Bug#24666169
72 #--error ER_NO_REFERENCED_ROW_2
73 #INSERT INTO t2 VALUES (56, 6);
74 #--error ER_NO_REFERENCED_ROW_2
75 #UPDATE t2 SET b = 99 WHERE a = 51;
76 #DELETE FROM t2 WHERE a = 53;
77 #SELECT id, b FROM t1 ORDER BY id;
78 #SELECT a, b FROM t2 ORDER BY a;
79
80 #--echo # Operations on parent table
81 #DELETE FROM t1 WHERE id = 1;
82 #UPDATE t1 SET id = 50 WHERE id = 5;
83 #SELECT id, b FROM t1 ORDER BY id;
84 #SELECT a, b FROM t2 ORDER BY a;
85
86 DROP TABLE t2, t1;
87
88 --echo #
89 --echo # Bug#20752436: INNODB: FAILING ASSERTION: 0 IN FILE HANDLER0ALTER.CC
90 --echo # LINE 6647
91 --echo #
92 --echo # Verify that index types that cannot be used as foreign keys are
93 --echo # ignored when creating foreign keys.
94
95 let $SAVED_foreign_key_checks= `SELECT @@foreign_key_checks`;
96 set @@foreign_key_checks=0;
97
98 CREATE TABLE t1(a CHAR(100), b GEOMETRY NOT NULL SRID 0) ENGINE InnoDB;
99
100 --echo # Creating a foreign key on a GEOMETRY column is not supported
101 --error ER_FK_NO_INDEX_CHILD
102 ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
103
104 --echo # Adds FULLTEXT and SPATAL indices which cannot be used as foreign keys
105 ALTER TABLE t1 ADD FULLTEXT INDEX(a), ADD SPATIAL INDEX(b);
106
107 --echo # Adds a foreign key on column with FULLTEXT index.
108 --echo # The FULLTEXT index cannot be used and the generated key must be kept
109 ALTER TABLE t1 ADD CONSTRAINT fi_a FOREIGN KEY(a) REFERENCES ti2(a);
110 ANALYZE TABLE t1;
111 SHOW INDEXES FROM t1;
112
113 --echo # Attempt to add a foreign key on column with SPATIAL index.
114 --echo # The SPATIAL index cannot be used so this becomes an attempt at
115 --echo # creating a foreign key on a GEOMETRY column which is not supported
116 --error ER_DROP_INDEX_FK
117 ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
118
119 DROP TABLE t1;
120 eval set @@foreign_key_checks= $SAVED_foreign_key_checks;
121
122 --echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
123 --echo # base bug#24818604 [GR]
124 --echo #
125
126 CREATE TABLE t1 (c1 INT PRIMARY KEY);
127 CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
128
129 INSERT INTO t1 VALUES (1);
130 INSERT INTO t2 VALUES (1);
131
132 SELECT unique_constraint_name FROM information_schema.referential_constraints
133 WHERE table_name = 't2';
134
135 --source include/restart_mysqld.inc
136
137 SELECT unique_constraint_name FROM information_schema.referential_constraints
138 WHERE table_name = 't2';
139
140 SELECT * FROM t1;
141
142 SELECT unique_constraint_name FROM information_schema.referential_constraints
143 WHERE table_name = 't2';
144
145 DROP TABLE t2;
146 DROP TABLE t1;
147
148 --echo #
149 --echo # BUG#25976199 GROUP_REPLICATION.GR_FOREIGN_CHECKS_DISABLED TEST FAILING
150 --echo # ON PB2
151 --echo #
152
153 SET foreign_key_checks=0;
154
155 CREATE TABLE child_table (c1 INT PRIMARY KEY,
156 FOREIGN KEY (c1) REFERENCES parent_table(c1));
157
158 CREATE TABLE parent_table (c1 INT PRIMARY KEY);
159
160 SET foreign_key_checks=1;
161
162 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN WHERE
163 ID='test/child_table_ibfk_1';
164 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE
165 ID='test/child_table_ibfk_1';
166
167 --error ER_FK_CANNOT_DROP_PARENT
168 DROP TABLE parent_table;
169 DROP TABLE child_table;
170 DROP TABLE parent_table;
171
172 --echo #
173 --echo # Bug#26764604 - INNODB: FAILING ASSERTION: COL_NR < TABLE->N_DEF
174 --echo #
175
176 SELECT @@foreign_key_checks;
177
178 CREATE TABLE t1 (
179 pk int(11) NOT NULL AUTO_INCREMENT,
180 unique1 int(11) DEFAULT NULL,
181 unique2 int(11) DEFAULT NULL,
182 two int(11) DEFAULT NULL,
183 four int(11) DEFAULT NULL,
184 ten int(11) DEFAULT NULL,
185 twenty int(11) DEFAULT NULL,
186 forty int(11) DEFAULT NULL,
187 sixty int(11) DEFAULT NULL,
188 eighty int(11) DEFAULT NULL,
189 tenPercent int(11) DEFAULT NULL,
190 twentyPercent int(11) DEFAULT NULL,
191 fiftyPercent int(11) DEFAULT NULL,
192 unique1gc int(11) GENERATED ALWAYS AS ((unique1 + unique2)) VIRTUAL,
193 unique2gc int(11) GENERATED ALWAYS AS ((unique1 * unique2)) VIRTUAL,
194 twogc int(11) GENERATED ALWAYS AS (((two * two) + 1)) VIRTUAL,
195 fourgc int(11) GENERATED ALWAYS AS (((four + four) - 1)) VIRTUAL,
196 tengc int(11) GENERATED ALWAYS AS ((twogc + ten)) VIRTUAL,
197 twentygc int(11) GENERATED ALWAYS AS ((((twenty * twogc) * unique1) % 20)) VIRTUAL,
198 fortygc int(11) GENERATED ALWAYS AS (forty) VIRTUAL,
199 sixtygc int(11) GENERATED ALWAYS AS (sixty) VIRTUAL,
200 eightygc int(11) GENERATED ALWAYS AS ((((eighty - twentygc) - ten) - two)) VIRTUAL,
201 PRIMARY KEY (pk),
202 KEY allkey(unique1,unique2,two,four,ten,twenty,forty,sixty,eighty,tenPercent,twentyPercent,fiftyPercent),
203 KEY kugc1 (unique1gc),
204 KEY kugc2 (unique2gc),
205 KEY kumixgc1 (unique1gc,unique2gc),
206 KEY kgc1 (twogc,fourgc),
207 KEY kgc2 (fourgc,tengc),
208 KEY kgc3 (tengc,twentygc),
209 KEY kgc4 (twentygc,fortygc),
210 KEY kgc5 (fortygc,sixtygc),
211 KEY kgc6 (sixtygc,eightygc),
212 KEY allkeygc(unique1gc,unique2gc,twogc,fourgc,tengc,twentygc,fortygc,sixtygc,eightygc)
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
214
215 --error ER_FK_NO_INDEX_PARENT
216 CREATE TABLE t2_fk (
217 pk int(11) NOT NULL,
218 sixty int(11) DEFAULT NULL,
219 PRIMARY KEY (pk),
220 CONSTRAINT fk6 FOREIGN KEY (sixty) REFERENCES t1(sixty)
221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
222
223 DROP TABLE t1;
224
225 --echo #
226 --echo # Bug#27014308 - UPGRADING TO 8.0.3 FAILS IF FOREIGN KEYS OF EXACTLY 64 CHARACTERS ARE PRESENT
227 --echo #
228
229 --echo # test with FK identifier of length exactly 64 characters;
230 CREATE TABLE parent (
231 id INT NOT NULL,
232 PRIMARY KEY (id)
233 ) ENGINE=INNODB;
234
235 CREATE TABLE child (
236 id INT,
237 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
238 INT,
239 INDEX par_ind
240 (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
241 CONSTRAINT
242 `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
243 FOREIGN
244 KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
245 REFERENCES parent(id)
246 ON DELETE CASCADE
247 ) ENGINE=INNODB;
248
249 DROP TABLE child;
250 DROP TABLE parent;
251
252 --echo # test with FK identifier of length exactly 65 characters,
253 --echo # child table creation should fail
254
255 CREATE TABLE parent (
256 id INT NOT NULL,
257 PRIMARY KEY (id)
258 ) ENGINE=INNODB;
259
260 --error ER_TOO_LONG_IDENT
261 CREATE TABLE child (
262 id INT,
263 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
264 INT,
265 INDEX par_ind
266 (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
267 CONSTRAINT
268 `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
269 FOREIGN
270 KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
271 REFERENCES parent(id)
272 ON DELETE CASCADE
273 ) ENGINE=INNODB;
274
275 DROP TABLE parent;
276
277 --echo # Test delete/update rules combination.
278 CREATE TABLE t1
279 (a INT NOT NULL,
280 b INT NOT NULL,
281 INDEX idx(a)) ENGINE=InnoDB;
282
283 CREATE TABLE t2
284 (a INT KEY,
285 b INT,
286 INDEX ind(b),
287 FOREIGN KEY (b) REFERENCES t1(a))
288 ENGINE=InnoDB;
289
290 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
291 DROP TABLE t2;
292
293 CREATE TABLE t2
294 (a INT KEY,
295 b INT,
296 INDEX ind(b),
297 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE)
298 ENGINE=InnoDB;
299
300 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
301 DROP TABLE t2;
302
303 CREATE TABLE t2
304 (a INT KEY,
305 b INT,
306 INDEX ind(b),
307 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL)
308 ENGINE=InnoDB;
309
310 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
311 DROP TABLE t2;
312
313 CREATE TABLE t2
314 (a INT KEY,
315 b INT,
316 INDEX ind(b),
317 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION)
318 ENGINE=InnoDB;
319
320 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
321 DROP TABLE t2;
322
323 CREATE TABLE t2
324 (a INT KEY,
325 b INT,
326 INDEX ind(b),
327 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE)
328 ENGINE=InnoDB;
329
330 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
331 DROP TABLE t2;
332
333 CREATE TABLE t2
334 (a INT KEY,
335 b INT,
336 INDEX ind(b),
337 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL)
338 ENGINE=InnoDB;
339
340 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
341 DROP TABLE t2;
342
343 CREATE TABLE t2
344 (a INT KEY,
345 b INT,
346 INDEX ind(b),
347 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE NO ACTION)
348 ENGINE=InnoDB;
349
350 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
351 DROP TABLE t2;
352
353 CREATE TABLE t2
354 (a INT KEY,
355 b INT,
356 INDEX ind(b),
357 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
358 ENGINE=InnoDB;
359
360 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
361 DROP TABLE t2;
362
363 CREATE TABLE t2
364 (a INT KEY,
365 b INT,
366 INDEX ind(b),
367 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE SET NULL)
368 ENGINE=InnoDB;
369
370 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
371 DROP TABLE t2;
372
373 CREATE TABLE t2
374 (a INT KEY,
375 b INT,
376 INDEX ind(b),
377 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE NO ACTION)
378 ENGINE=InnoDB;
379
380 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
381 DROP TABLE t2;
382
383 CREATE TABLE t2
384 (a INT KEY,
385 b INT,
386 INDEX ind(b),
387 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE RESTRICT)
388 ENGINE=InnoDB;
389
390 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
391 DROP TABLE t2;
392
393 CREATE TABLE t2
394 (a INT KEY,
395 b INT,
396 INDEX ind(b),
397 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE CASCADE)
398 ENGINE=InnoDB;
399
400 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
401 DROP TABLE t2;
402
403 CREATE TABLE t2
404 (a INT KEY,
405 b INT,
406 INDEX ind(b),
407 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE SET NULL)
408 ENGINE=InnoDB;
409
410 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
411 DROP TABLE t2;
412
413 CREATE TABLE t2
414 (a INT KEY,
415 b INT,
416 INDEX ind(b),
417 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE NO ACTION)
418 ENGINE=InnoDB;
419
420 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
421 DROP TABLE t2;
422
423 CREATE TABLE t2
424 (a INT KEY,
425 b INT,
426 INDEX ind(b),
427 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE RESTRICT)
428 ENGINE=InnoDB;
429
430 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
431 DROP TABLE t2;
432
433 CREATE TABLE t2
434 (a INT KEY,
435 b INT,
436 INDEX ind(b),
437 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE CASCADE)
438 ENGINE=InnoDB;
439
440 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
441 DROP TABLE t2;
442
443 CREATE TABLE t2
444 (a INT KEY,
445 b INT,
446 INDEX ind(b),
447 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE SET NULL)
448 ENGINE=InnoDB;
449
450 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
451 DROP TABLE t2;
452
453 CREATE TABLE t2
454 (a INT KEY,
455 b INT,
456 INDEX ind(b),
457 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE NO ACTION)
458 ENGINE=InnoDB;
459
460 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
461 DROP TABLE t2;
462
463 CREATE TABLE t2
464 (a INT KEY,
465 b INT,
466 INDEX ind(b),
467 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE RESTRICT)
468 ENGINE=InnoDB;
469
470 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
471 DROP TABLE t2;
472
473 CREATE TABLE t2
474 (a INT KEY,
475 b INT,
476 INDEX ind(b),
477 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE CASCADE)
478 ENGINE=InnoDB;
479
480 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
481 DROP TABLE t2;
482
483 CREATE TABLE t2
484 (a INT KEY,
485 b INT,
486 INDEX ind(b),
487 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE SET NULL)
488 ENGINE=InnoDB;
489
490 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
491 DROP TABLE t2;
492
493 CREATE TABLE t2
494 (a INT KEY,
495 b INT,
496 INDEX ind(b),
497 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE NO ACTION)
498 ENGINE=InnoDB;
499
500 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
501 DROP TABLE t2;
502
503
504 CREATE TABLE t2
505 (a INT KEY,
506 b INT,
507 INDEX ind(b),
508 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE RESTRICT)
509 ENGINE=InnoDB;
510
511 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
512 DROP TABLE t2;
513
514 DROP TABLE t1;
515
516
517 --echo #
518 --echo # Bug#28581468 - RENAMING PARENT COLUMN IN A FOREIGN KEY FAILS WITH STRANGE ERROR.
519 --echo #
520
521 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
522 CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
523
524 ALTER TABLE t1 CHANGE a id INT;
525 CHECK TABLE t1;
526 CHECK TABLE t2;
527
528 DROP TABLE t1, t2;
529
530 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
531 CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
532
533 ALTER TABLE t1 CHANGE a id INT NOT NULL;
534 CHECK TABLE t1;
535 CHECK TABLE t2;
536
537 DROP TABLE t1, t2;
538
539 --echo #
540 --echo # Bug #27020089 "I_S.INNODB_SYS_FOREIGN LOST FOREIGN KEY INFORMATION."
541 --echo #
542 CREATE TABLE t1(pk INT PRIMARY KEY);
543 CREATE TABLE `......................................`
544 (fk INT, FOREIGN KEY(fk) REFERENCES t1(pk));
545 SHOW CREATE TABLE `......................................`;
546 SELECT id, for_name, ref_name FROM information_schema.innodb_foreign WHERE id LIKE 'test%';
547 DROP TABLES `......................................`, t1;
548
549 --echo #
550 --echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
551 --echo #
552
553 --echo #Test-Case 1
554 CREATE TABLE `emails` (
555 `id` int unsigned NOT NULL AUTO_INCREMENT,
556 PRIMARY KEY (`id`)
557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
558 ROW_FORMAT=DYNAMIC;
559
560 CREATE TABLE `email_stats` (
561 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
562 `email_id` int unsigned DEFAULT NULL,
563 `date_sent` datetime NOT NULL,
564 `generated_sent_date` date GENERATED ALWAYS AS
565 (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
566 '-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
567 PRIMARY KEY (`id`),
568 KEY `IDX_ES1` (`email_id`),
569 KEY `mautic_generated_sent_date_email_id`
570 (`generated_sent_date`,`email_id`),
571 CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
572 `emails` (`id`) ON DELETE SET NULL
573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
574 ROW_FORMAT=DYNAMIC;
575
576 INSERT INTO `emails` VALUES (1);
577 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
578 (1,1,'2020-10-22 13:32:41');
579 SELECT * FROM `email_stats`;
580 DELETE FROM `emails`;
581 DELETE FROM `email_stats`;
582
583 #clean up.
584 DROP TABLE `email_stats`;
585 DROP TABLE `emails`;
586
587 --echo # Test-Case 2
588 CREATE TABLE `emails` (
589 `id` int unsigned NOT NULL AUTO_INCREMENT,
590 PRIMARY KEY (`id`)
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
592 ROW_FORMAT=DYNAMIC;
593
594 CREATE TABLE `email_stats` (
595 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
596 `email_id` int unsigned DEFAULT NULL,
597 `date_sent` datetime NOT NULL,
598 `generated_sent_date` date GENERATED ALWAYS AS
599 (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
600 '-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
601 PRIMARY KEY (`id`),
602 KEY `IDX_ES1` (`email_id`),
603 KEY `mautic_generated_sent_date_email_id`
604 (`generated_sent_date`,`email_id`),
605 CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
606 `emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
608 ROW_FORMAT=DYNAMIC;
609 INSERT INTO `emails` VALUES (1);
610 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
611 (1,1,'2020-10-22 13:32:41');
612 UPDATE `emails` SET `id` = 2 where `id` = 1;
613 SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL;
614 SELECT * FROM `email_stats`;
615 UPDATE `email_stats` SET `email_id`=2
616 WHERE DATE(`generated_sent_date`) = '2020-10-22';
617 SELECT * FROM `email_stats`;
618
619 #clean up.
620 DROP TABLE `email_stats`;
621 DROP TABLE `emails`;
622
623 --echo # test-case 3
624 CREATE TABLE `emails` (
625 `id` int unsigned NOT NULL AUTO_INCREMENT,
626 PRIMARY KEY (`id`)
627 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
628 ROW_FORMAT=DYNAMIC;
629 CREATE TABLE `email_stats` (
630 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
631 `email_id` int unsigned DEFAULT NULL,
632 `date_sent` datetime NOT NULL,
633 `generated_sent_email` varchar(20) GENERATED ALWAYS AS
634 (CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
635 PRIMARY KEY (`id`),
636 KEY `idx_es1` (`email_id`),
637 KEY `mautic_generated_sent_date_email`
638 (`generated_sent_email`,`email_id`),
639 CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
640 `emails` (`id`) ON DELETE SET NULL
641 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
642 ROW_FORMAT=DYNAMIC;
643 INSERT INTO `emails` VALUES (1);
644 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
645 (1,1,'2020-10-22 13:32:41');
646 SELECT * FROM `email_stats`;
647 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
648 DELETE FROM `emails`;
649 SELECT * FROM `email_stats`;
650 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
651
652 #clean up.
653 DROP TABLE `email_stats`;
654 DROP TABLE `emails`;
655
656 --echo # test-case 4
657 CREATE TABLE `emails` (
658 `id` int unsigned NOT NULL AUTO_INCREMENT,
659 PRIMARY KEY (`id`)
660 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
661 ROW_FORMAT=DYNAMIC;
662 CREATE TABLE `email_stats` (
663 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
664 `email_id` int unsigned DEFAULT NULL,
665 `date_sent` datetime NOT NULL,
666 `generated_sent_email` varchar(20) GENERATED ALWAYS AS
667 (CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
668 PRIMARY KEY (`id`),
669 KEY `idx_es1` (`email_id`),
670 KEY `mautic_generated_sent_date_email`
671 (`generated_sent_email`,`email_id`),
672 CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
673 `emails` (`id`) ON UPDATE SET NULL
674 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
675 ROW_FORMAT=DYNAMIC;
676 INSERT INTO `emails` VALUES (1);
677 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
678 (1,1,'2020-10-22 13:32:41');
679 SELECT * FROM `email_stats`;
680 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
681 UPDATE `emails` SET `id` = 2 WHERE `id` = 1;
682 SELECT * FROM `email_stats`;
683 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
684
685 #clean up.
686 DROP TABLE `email_stats`;
687 DROP TABLE `emails`;