1--source include/have_innodb.inc 2--source include/not_embedded.inc 3 4--echo # 5--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW 6--echo # DICT_CREATE_FOREIGN_CONSTR 7--echo # 8 9create table t1 (f1 int primary key) engine=InnoDB; 10--error ER_CANNOT_ADD_FOREIGN 11create table t2 (f1 int primary key, 12constraint c1 foreign key (f1) references t1(f1), 13constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; 14create table t2 (f1 int primary key, 15 constraint c1 foreign key (f1) references t1(f1)) engine=innodb; 16 17--replace_regex /#sql-[0-9a-f_]*'/#sql-temporary'/ 18--error ER_DUP_KEY 19alter table t2 add constraint c1 foreign key (f1) references t1(f1); 20 21set foreign_key_checks = 0; 22--error ER_FK_DUP_NAME 23alter table t2 add constraint c1 foreign key (f1) references t1(f1); 24 25drop table t2, t1; 26 27--echo # 28--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN 29--echo # NULL/NOT NULL MISMATCH 30--echo # 31 32set foreign_key_checks = 1; 33show variables like 'foreign_key_checks'; 34 35CREATE TABLE t1 36(a INT NOT NULL, 37 b INT NOT NULL, 38 INDEX idx(a)) ENGINE=InnoDB; 39 40CREATE TABLE t2 41(a INT KEY, 42 b INT, 43 INDEX ind(b), 44 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) 45 ENGINE=InnoDB; 46 47show create table t1; 48show create table t2; 49 50INSERT INTO t1 VALUES (1, 80); 51INSERT INTO t1 VALUES (2, 81); 52INSERT INTO t1 VALUES (3, 82); 53INSERT INTO t1 VALUES (4, 83); 54INSERT INTO t1 VALUES (5, 84); 55 56INSERT INTO t2 VALUES (51, 1); 57INSERT INTO t2 VALUES (52, 2); 58INSERT INTO t2 VALUES (53, 3); 59INSERT INTO t2 VALUES (54, 4); 60INSERT INTO t2 VALUES (55, 5); 61 62SELECT a, b FROM t1 ORDER BY a; 63SELECT a, b FROM t2 ORDER BY a; 64 65--error ER_NO_REFERENCED_ROW_2 66INSERT INTO t2 VALUES (56, 6); 67 68ALTER TABLE t1 CHANGE a id INT; 69 70SELECT id, b FROM t1 ORDER BY id; 71SELECT a, b FROM t2 ORDER BY a; 72 73--echo # Operations on child table 74--error ER_NO_REFERENCED_ROW_2 75INSERT INTO t2 VALUES (56, 6); 76--error ER_NO_REFERENCED_ROW_2 77UPDATE t2 SET b = 99 WHERE a = 51; 78DELETE FROM t2 WHERE a = 53; 79SELECT id, b FROM t1 ORDER BY id; 80SELECT a, b FROM t2 ORDER BY a; 81 82--echo # Operations on parent table 83DELETE FROM t1 WHERE id = 1; 84UPDATE t1 SET id = 50 WHERE id = 5; 85SELECT id, b FROM t1 ORDER BY id; 86SELECT a, b FROM t2 ORDER BY a; 87 88DROP TABLE t2, t1; 89 90--echo # 91--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART 92--echo # base bug#24818604 [GR] 93--echo # 94 95CREATE TABLE t1 (c1 INT PRIMARY KEY); 96CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)); 97 98INSERT INTO t1 VALUES (1); 99INSERT INTO t2 VALUES (1); 100 101SELECT unique_constraint_name FROM information_schema.referential_constraints 102WHERE table_name = 't2'; 103 104--source include/restart_mysqld.inc 105 106SELECT unique_constraint_name FROM information_schema.referential_constraints 107WHERE table_name = 't2'; 108 109SELECT * FROM t1; 110 111SELECT unique_constraint_name FROM information_schema.referential_constraints 112WHERE table_name = 't2'; 113 114DROP TABLE t2; 115DROP TABLE t1; 116 117--echo # 118--echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE 119--echo # 120 121--echo #Test-Case 1 122CREATE TABLE `emails` ( 123`id` int unsigned NOT NULL AUTO_INCREMENT, 124PRIMARY KEY (`id`) 125) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 126ROW_FORMAT=DYNAMIC; 127 128CREATE TABLE `email_stats` ( 129`id` bigint unsigned NOT NULL AUTO_INCREMENT, 130`email_id` int unsigned DEFAULT NULL, 131`date_sent` datetime NOT NULL, 132`generated_sent_date` date GENERATED ALWAYS AS 133(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'), 134'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL, 135PRIMARY KEY (`id`), 136KEY `IDX_ES1` (`email_id`), 137KEY `mautic_generated_sent_date_email_id` 138(`generated_sent_date`,`email_id`), 139CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES 140`emails` (`id`) ON DELETE SET NULL 141) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 142ROW_FORMAT=DYNAMIC; 143 144INSERT INTO `emails` VALUES (1); 145INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES 146(1,1,'2020-10-22 13:32:41'); 147SELECT * FROM `email_stats`; 148DELETE FROM `emails`; 149DELETE FROM `email_stats`; 150 151#clean up. 152DROP TABLE `email_stats`; 153DROP TABLE `emails`; 154 155--echo # Test-Case 2 156CREATE TABLE `emails` ( 157`id` int unsigned NOT NULL AUTO_INCREMENT, 158PRIMARY KEY (`id`) 159) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 160ROW_FORMAT=DYNAMIC; 161 162CREATE TABLE `email_stats` ( 163`id` bigint unsigned NOT NULL AUTO_INCREMENT, 164`email_id` int unsigned DEFAULT NULL, 165`date_sent` datetime NOT NULL, 166`generated_sent_date` date GENERATED ALWAYS AS 167(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'), 168'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL, 169PRIMARY KEY (`id`), 170KEY `IDX_ES1` (`email_id`), 171KEY `mautic_generated_sent_date_email_id` 172(`generated_sent_date`,`email_id`), 173CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES 174`emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL 175) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 176ROW_FORMAT=DYNAMIC; 177INSERT INTO `emails` VALUES (1); 178INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES 179(1,1,'2020-10-22 13:32:41'); 180UPDATE `emails` SET `id` = 2 where `id` = 1; 181SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL; 182SELECT * FROM `email_stats`; 183UPDATE `email_stats` SET `email_id`=2 184WHERE DATE(`generated_sent_date`) = '2020-10-22'; 185SELECT * FROM `email_stats`; 186 187#clean up. 188DROP TABLE `email_stats`; 189DROP TABLE `emails`; 190 191--echo # test-case 3 192CREATE TABLE `emails` ( 193`id` int unsigned NOT NULL AUTO_INCREMENT, 194PRIMARY KEY (`id`) 195) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 196ROW_FORMAT=DYNAMIC; 197CREATE TABLE `email_stats` ( 198`id` bigint unsigned NOT NULL AUTO_INCREMENT, 199`email_id` int unsigned DEFAULT NULL, 200`date_sent` datetime NOT NULL, 201`generated_sent_email` varchar(20) GENERATED ALWAYS AS 202(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL, 203PRIMARY KEY (`id`), 204KEY `idx_es1` (`email_id`), 205KEY `mautic_generated_sent_date_email` 206(`generated_sent_email`,`email_id`), 207CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES 208`emails` (`id`) ON DELETE SET NULL 209) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 210ROW_FORMAT=DYNAMIC; 211INSERT INTO `emails` VALUES (1); 212INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES 213(1,1,'2020-10-22 13:32:41'); 214SELECT * FROM `email_stats`; 215SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1'; 216DELETE FROM `emails`; 217SELECT * FROM `email_stats`; 218SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-'; 219 220#clean up. 221DROP TABLE `email_stats`; 222DROP TABLE `emails`; 223 224--echo # test-case 4 225CREATE TABLE `emails` ( 226`id` int unsigned NOT NULL AUTO_INCREMENT, 227PRIMARY KEY (`id`) 228) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 229ROW_FORMAT=DYNAMIC; 230CREATE TABLE `email_stats` ( 231`id` bigint unsigned NOT NULL AUTO_INCREMENT, 232`email_id` int unsigned DEFAULT NULL, 233`date_sent` datetime NOT NULL, 234`generated_sent_email` varchar(20) GENERATED ALWAYS AS 235(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL, 236PRIMARY KEY (`id`), 237KEY `idx_es1` (`email_id`), 238KEY `mautic_generated_sent_date_email` 239(`generated_sent_email`,`email_id`), 240CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES 241`emails` (`id`) ON UPDATE SET NULL 242) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 243ROW_FORMAT=DYNAMIC; 244INSERT INTO `emails` VALUES (1); 245INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES 246(1,1,'2020-10-22 13:32:41'); 247SELECT * FROM `email_stats`; 248SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1'; 249UPDATE `emails` SET `id` = 2 WHERE `id` = 1; 250SELECT * FROM `email_stats`; 251SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-'; 252 253#clean up. 254DROP TABLE `email_stats`; 255DROP TABLE `emails`; 256 257--echo # 258--echo # PS-7940 :ON DELETE CASCADE with generated column crashes in innobase_get_computed_value 259--echo # 260 261CREATE TABLE `t1` (`t1_id` int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY(`t1_id`)) ENGINE=InnoDB; 262INSERT INTO `t1` VALUES (10); 263CREATE TABLE `t2` ( 264 `t2_id` int unsigned NOT NULL AUTO_INCREMENT, 265 `c1` decimal(14,2) NOT NULL DEFAULT '0.00', 266 `t1_id` int unsigned DEFAULT NULL, 267 `testycol` bit(1) GENERATED ALWAYS AS (`c1` <> 0) VIRTUAL, 268 PRIMARY KEY (`t2_id`), 269 UNIQUE KEY `t1_id` (`t1_id`), 270 KEY `testycol` (`testycol`), 271 CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`t1_id`) ON DELETE CASCADE 272) ENGINE=InnoDB; 273INSERT INTO `t2` (`t2_id`, `c1`, `t1_id`) VALUES (1,5.00,10); 274 275--source include/restart_mysqld.inc 276 277DELETE FROM `t1` WHERE `t1_id`=10; 278 279DROP TABLE `t2`; 280DROP TABLE `t1`; 281