| 1 |
SET GLOBAL innodb_stats_persistent = 0; |
| 2 |
# |
| 3 |
# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW |
| 4 |
# DICT_CREATE_FOREIGN_CONSTR |
| 5 |
# |
| 6 |
create table t1 (f1 int primary key) engine=InnoDB; |
| 7 |
create table t2 (f1 int primary key, |
| 8 |
constraint c1 foreign key (f1) references t1(f1), |
| 9 |
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; |
| 10 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 11 |
create table t2 (f1 int primary key, |
| 12 |
constraint c1 foreign key (f1) references t1(f1)) engine=innodb; |
| 13 |
alter table t2 add constraint c1 foreign key (f1) references t1(f1); |
| 14 |
ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") |
| 15 |
set foreign_key_checks = 0; |
| 16 |
alter table t2 add constraint c1 foreign key (f1) references t1(f1); |
| 17 |
ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/c1' |
| 18 |
drop table t2, t1; |
| 19 |
# |
| 20 |
# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN |
| 21 |
# NULL/NOT NULL MISMATCH |
| 22 |
# |
| 23 |
set foreign_key_checks = 1; |
| 24 |
show variables like 'foreign_key_checks'; |
| 25 |
Variable_name Value |
| 26 |
foreign_key_checks ON |
| 27 |
CREATE TABLE t1 |
| 28 |
(a INT NOT NULL, |
| 29 |
b INT NOT NULL, |
| 30 |
INDEX idx(a)) ENGINE=InnoDB; |
| 31 |
CREATE TABLE t2 |
| 32 |
(a INT KEY, |
| 33 |
b INT, |
| 34 |
INDEX ind(b), |
| 35 |
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) |
| 36 |
ENGINE=InnoDB; |
| 37 |
show create table t1; |
| 38 |
Table Create Table |
| 39 |
t1 CREATE TABLE `t1` ( |
| 40 |
`a` int(11) NOT NULL, |
| 41 |
`b` int(11) NOT NULL, |
| 42 |
KEY `idx` (`a`) |
| 43 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 44 |
show create table t2; |
| 45 |
Table Create Table |
| 46 |
t2 CREATE TABLE `t2` ( |
| 47 |
`a` int(11) NOT NULL, |
| 48 |
`b` int(11) DEFAULT NULL, |
| 49 |
PRIMARY KEY (`a`), |
| 50 |
KEY `ind` (`b`), |
| 51 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE |
| 52 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 53 |
INSERT INTO t1 VALUES (1, 80); |
| 54 |
INSERT INTO t1 VALUES (2, 81); |
| 55 |
INSERT INTO t1 VALUES (3, 82); |
| 56 |
INSERT INTO t1 VALUES (4, 83); |
| 57 |
INSERT INTO t1 VALUES (5, 84); |
| 58 |
INSERT INTO t2 VALUES (51, 1); |
| 59 |
INSERT INTO t2 VALUES (52, 2); |
| 60 |
INSERT INTO t2 VALUES (53, 3); |
| 61 |
INSERT INTO t2 VALUES (54, 4); |
| 62 |
INSERT INTO t2 VALUES (55, 5); |
| 63 |
SELECT a, b FROM t1 ORDER BY a; |
| 64 |
a b |
| 65 |
1 80 |
| 66 |
2 81 |
| 67 |
3 82 |
| 68 |
4 83 |
| 69 |
5 84 |
| 70 |
SELECT a, b FROM t2 ORDER BY a; |
| 71 |
a b |
| 72 |
51 1 |
| 73 |
52 2 |
| 74 |
53 3 |
| 75 |
54 4 |
| 76 |
55 5 |
| 77 |
INSERT INTO t2 VALUES (56, 6); |
| 78 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE) |
| 79 |
ALTER TABLE t1 CHANGE a id INT; |
| 80 |
SELECT id, b FROM t1 ORDER BY id; |
| 81 |
id b |
| 82 |
1 80 |
| 83 |
2 81 |
| 84 |
3 82 |
| 85 |
4 83 |
| 86 |
5 84 |
| 87 |
SELECT a, b FROM t2 ORDER BY a; |
| 88 |
a b |
| 89 |
51 1 |
| 90 |
52 2 |
| 91 |
53 3 |
| 92 |
54 4 |
| 93 |
55 5 |
| 94 |
# Operations on child table |
| 95 |
INSERT INTO t2 VALUES (56, 6); |
| 96 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) |
| 97 |
UPDATE t2 SET b = 99 WHERE a = 51; |
| 98 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) |
| 99 |
DELETE FROM t2 WHERE a = 53; |
| 100 |
SELECT id, b FROM t1 ORDER BY id; |
| 101 |
id b |
| 102 |
1 80 |
| 103 |
2 81 |
| 104 |
3 82 |
| 105 |
4 83 |
| 106 |
5 84 |
| 107 |
SELECT a, b FROM t2 ORDER BY a; |
| 108 |
a b |
| 109 |
51 1 |
| 110 |
52 2 |
| 111 |
54 4 |
| 112 |
55 5 |
| 113 |
# Operations on parent table |
| 114 |
DELETE FROM t1 WHERE id = 1; |
| 115 |
UPDATE t1 SET id = 50 WHERE id = 5; |
| 116 |
SELECT id, b FROM t1 ORDER BY id; |
| 117 |
id b |
| 118 |
2 81 |
| 119 |
3 82 |
| 120 |
4 83 |
| 121 |
50 84 |
| 122 |
SELECT a, b FROM t2 ORDER BY a; |
| 123 |
a b |
| 124 |
52 2 |
| 125 |
54 4 |
| 126 |
55 50 |
| 127 |
DROP TABLE t2, t1; |
| 128 |
# |
| 129 |
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART |
| 130 |
# base bug#24818604 [GR] |
| 131 |
# |
| 132 |
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB; |
| 133 |
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)) |
| 134 |
ENGINE=InnoDB; |
| 135 |
INSERT INTO t1 VALUES (1); |
| 136 |
INSERT INTO t2 VALUES (1); |
| 137 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 138 |
WHERE table_name = 't2'; |
| 139 |
unique_constraint_name |
| 140 |
PRIMARY |
| 141 |
# |
| 142 |
# MDEV-28317 Assertion failure on rollback of FOREIGN KEY operation |
| 143 |
# |
| 144 |
SET foreign_key_checks=0; |
| 145 |
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB; |
| 146 |
CREATE TABLE child(a INT,FOREIGN KEY(a) REFERENCES parent(a) ON DELETE CASCADE) |
| 147 |
ENGINE=InnoDB; |
| 148 |
INSERT INTO child VALUES(1); |
| 149 |
ALTER TABLE child DROP INDEX a; |
| 150 |
connect incomplete, localhost, root,,; |
| 151 |
BEGIN; |
| 152 |
DELETE FROM child; |
| 153 |
connection default; |
| 154 |
INSERT INTO parent SET a=0; |
| 155 |
FLUSH TABLES; |
| 156 |
# restart |
| 157 |
disconnect incomplete; |
| 158 |
SET GLOBAL innodb_stats_persistent = 0; |
| 159 |
INSERT INTO child SET a=0; |
| 160 |
INSERT INTO child SET a=1; |
| 161 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE CASCADE) |
| 162 |
DELETE FROM parent; |
| 163 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE CASCADE) |
| 164 |
ALTER TABLE child ADD INDEX(a); |
| 165 |
DELETE FROM parent; |
| 166 |
DROP TABLE child,parent; |
| 167 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 168 |
WHERE table_name = 't2'; |
| 169 |
unique_constraint_name |
| 170 |
PRIMARY |
| 171 |
SELECT * FROM t1; |
| 172 |
c1 |
| 173 |
1 |
| 174 |
SELECT unique_constraint_name FROM information_schema.referential_constraints |
| 175 |
WHERE table_name = 't2'; |
| 176 |
unique_constraint_name |
| 177 |
PRIMARY |
| 178 |
DROP TABLE t2; |
| 179 |
DROP TABLE t1; |
| 180 |
SET FOREIGN_KEY_CHECKS=0; |
| 181 |
CREATE TABLE staff ( |
| 182 |
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 183 |
store_id TINYINT UNSIGNED NOT NULL, |
| 184 |
PRIMARY KEY (staff_id), |
| 185 |
KEY idx_fk_store_id (store_id), |
| 186 |
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 187 |
) ENGINE=InnoDB; |
| 188 |
CREATE TABLE store ( |
| 189 |
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 190 |
manager_staff_id TINYINT UNSIGNED NOT NULL, |
| 191 |
PRIMARY KEY (store_id), |
| 192 |
UNIQUE KEY idx_unique_manager (manager_staff_id), |
| 193 |
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 194 |
) ENGINE=InnoDB; |
| 195 |
LOCK TABLE staff WRITE; |
| 196 |
UNLOCK TABLES; |
| 197 |
DROP TABLES staff, store; |
| 198 |
SET FOREIGN_KEY_CHECKS=1; |
| 199 |
# |
| 200 |
# MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX |
| 201 |
# |
| 202 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 203 |
CREATE DATABASE best default character set latin1; |
| 204 |
CREATE TABLE t3 (a INT PRIMARY KEY, |
| 205 |
CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; |
| 206 |
CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b), |
| 207 |
FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB; |
| 208 |
RENAME TABLE best.t2 TO test.t2; |
| 209 |
ERROR 42S01: Table 't2' already exists |
| 210 |
SHOW CREATE TABLE best.t2; |
| 211 |
Table Create Table |
| 212 |
t2 CREATE TABLE `t2` ( |
| 213 |
`a` int(11) NOT NULL, |
| 214 |
`b` text DEFAULT NULL, |
| 215 |
PRIMARY KEY (`a`), |
| 216 |
FULLTEXT KEY `b` (`b`), |
| 217 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`) |
| 218 |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
| 219 |
DROP DATABASE best; |
| 220 |
# |
| 221 |
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs |
| 222 |
# |
| 223 |
connect con1, localhost, root,,; |
| 224 |
INSERT INTO t1 SET a=1; |
| 225 |
BEGIN; |
| 226 |
DELETE FROM t1; |
| 227 |
connection default; |
| 228 |
INSERT INTO t3 SET a=1; |
| 229 |
connection con1; |
| 230 |
kill query @id; |
| 231 |
connection default; |
| 232 |
ERROR 70100: Query execution was interrupted |
| 233 |
connection con1; |
| 234 |
ROLLBACK; |
| 235 |
connection default; |
| 236 |
disconnect con1; |
| 237 |
DROP TABLE t3,t1; |
| 238 |
# |
| 239 |
# MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N |
| 240 |
# or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN |
| 241 |
# |
| 242 |
CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB; |
| 243 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a); |
| 244 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 245 |
ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL; |
| 246 |
ERROR HY000: Cannot change column 'a': used in a foreign key constraint 't1_ibfk_1' of table 'test.t1' |
| 247 |
ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY; |
| 248 |
ALTER TABLE t1 CHANGE COLUMN a b TIME; |
| 249 |
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE |
| 250 |
ALTER TABLE t1 CHANGE COLUMN a b TIME, DROP FOREIGN KEY t1_ibfk_1; |
| 251 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 252 |
DROP TABLE t1; |
| 253 |
# |
| 254 |
# MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N |
| 255 |
# upon DROP FOREIGN KEY |
| 256 |
# |
| 257 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 258 |
CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY fk1 (b) REFERENCES t1 (a)) |
| 259 |
ENGINE=InnoDB; |
| 260 |
ALTER TABLE t2 DROP FOREIGN KEY fk1, DROP FOREIGN KEY fk1; |
| 261 |
ERROR 42000: Can't DROP FOREIGN KEY `fk1`; check that it exists |
| 262 |
DROP TABLE t2, t1; |
| 263 |
CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB; |
| 264 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 265 |
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); |
| 266 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 267 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); |
| 268 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); |
| 269 |
Warnings: |
| 270 |
Note 1831 Duplicate index `ft2`. This is deprecated and will be disallowed in a future release |
| 271 |
DROP TABLE t1; |
| 272 |
CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY) |
| 273 |
ENGINE=InnoDB; |
| 274 |
SET SESSION FOREIGN_KEY_CHECKS = OFF; |
| 275 |
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); |
| 276 |
SET SESSION FOREIGN_KEY_CHECKS = ON; |
| 277 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); |
| 278 |
Warnings: |
| 279 |
Warning 1088 failed to load FOREIGN KEY constraints |
| 280 |
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); |
| 281 |
Warnings: |
| 282 |
Note 1831 Duplicate index `ft2`. This is deprecated and will be disallowed in a future release |
| 283 |
DROP TABLE t1; |
| 284 |
# |
| 285 |
# MDEV-18630 Conditional jump or move depends on uninitialised value |
| 286 |
# in ib_push_warning / dict_create_foreign_constraints_low |
| 287 |
# |
| 288 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 289 |
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); |
| 290 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 291 |
SHOW WARNINGS; |
| 292 |
Level Code Message |
| 293 |
Warning 150 Alter table `test`.`t1` with foreign key (a) constraint failed. Referenced table `test`.`t2` not found in the data dictionary. |
| 294 |
Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 295 |
Warning 1215 Cannot add foreign key constraint for `t1` |
| 296 |
DROP TABLE t1; |
| 297 |
# |
| 298 |
# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error |
| 299 |
# |
| 300 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB; |
| 301 |
CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB; |
| 302 |
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f); |
| 303 |
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1); |
| 304 |
DROP TABLE t1, t2; |
| 305 |
CREATE TABLE t1 (a INT, b INT, KEY idx(a)) ENGINE=InnoDB; |
| 306 |
SET FOREIGN_KEY_CHECKS= OFF; |
| 307 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x); |
| 308 |
ALTER TABLE t1 DROP KEY idx; |
| 309 |
ALTER TABLE t1 CHANGE a c INT; |
| 310 |
DROP TABLE t1; |
| 311 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB; |
| 312 |
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1); |
| 313 |
ALTER TABLE t1 ADD COLUMN f INT; |
| 314 |
SET FOREIGN_KEY_CHECKS= OFF; |
| 315 |
ALTER TABLE t1 DROP KEY idx; |
| 316 |
ALTER TABLE t1 ADD KEY idx (f1); |
| 317 |
SET FOREIGN_KEY_CHECKS= ON; |
| 318 |
ALTER TABLE t1 DROP f3; |
| 319 |
ALTER TABLE t1 CHANGE f f3 INT; |
| 320 |
DROP TABLE t1; |
| 321 |
SET FOREIGN_KEY_CHECKS=1; |
| 322 |
# |
| 323 |
# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE |
| 324 |
# ADD FOREIGN KEY |
| 325 |
# |
| 326 |
CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, |
| 327 |
PRIMARY KEY (`department_id`)) engine=innodb; |
| 328 |
CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, |
| 329 |
`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb; |
| 330 |
CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb; |
| 331 |
ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES |
| 332 |
`people` (`people_id`); |
| 333 |
ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` |
| 334 |
(`people_id`); |
| 335 |
ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people` |
| 336 |
(`people_id`); |
| 337 |
drop table title, department, people; |
| 338 |
create table t1 (a int primary key, b int) engine=innodb; |
| 339 |
create table t2 (c int primary key, d int, |
| 340 |
foreign key (d) references t1 (a) on update cascade) engine=innodb; |
| 341 |
insert t1 values (1,1),(2,2),(3,3); |
| 342 |
insert t2 values (4,1),(5,2),(6,3); |
| 343 |
flush table t2 with read lock; |
| 344 |
connect con1,localhost,root; |
| 345 |
delete from t1 where a=2; |
| 346 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) |
| 347 |
update t1 set a=10 where a=1; |
| 348 |
connection default; |
| 349 |
unlock tables; |
| 350 |
connection con1; |
| 351 |
connection default; |
| 352 |
lock table t2 write; |
| 353 |
connection con1; |
| 354 |
delete from t1 where a=2; |
| 355 |
connection default; |
| 356 |
unlock tables; |
| 357 |
connection con1; |
| 358 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) |
| 359 |
connection default; |
| 360 |
unlock tables; |
| 361 |
disconnect con1; |
| 362 |
create user foo; |
| 363 |
grant select,update on test.t1 to foo; |
| 364 |
connect foo,localhost,foo; |
| 365 |
update t1 set a=30 where a=3; |
| 366 |
disconnect foo; |
| 367 |
connection default; |
| 368 |
select * from t2; |
| 369 |
c d |
| 370 |
5 2 |
| 371 |
4 10 |
| 372 |
6 30 |
| 373 |
drop table t2, t1; |
| 374 |
drop user foo; |
| 375 |
# |
| 376 |
# MDEV-17595 - Server crashes in copy_data_between_tables or |
| 377 |
# Assertion `thd->transaction.stmt.is_empty() || |
| 378 |
# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' |
| 379 |
# fails in close_tables_for_reopen upon concurrent |
| 380 |
# ALTER TABLE and FLUSH |
| 381 |
# |
| 382 |
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB; |
| 383 |
INSERT INTO t1 VALUES(1),(2); |
| 384 |
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; |
| 385 |
INSERT INTO t2 VALUES(2); |
| 386 |
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; |
| 387 |
DROP TABLE t2, t1; |
| 388 |
create table t1 (pk int primary key, data int) engine=innodb; |
| 389 |
insert t1 values (1,1),(2,2),(3,3); |
| 390 |
create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb; |
| 391 |
insert t2 values (1),(2); |
| 392 |
insert t2 values (10); |
| 393 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`)) |
| 394 |
flush tables; |
| 395 |
flush status; |
| 396 |
update t1 set data=10 where pk+1>10; |
| 397 |
show status like '%opened_tab%'; |
| 398 |
Variable_name Value |
| 399 |
Opened_table_definitions 4 |
| 400 |
Opened_tables 4 |
| 401 |
flush tables; |
| 402 |
flush status; |
| 403 |
update t2 set t1_pk=11 where t1_pk+1>10; |
| 404 |
show status like '%opened_tab%'; |
| 405 |
Variable_name Value |
| 406 |
Opened_table_definitions 4 |
| 407 |
Opened_tables 4 |
| 408 |
flush tables; |
| 409 |
flush status; |
| 410 |
lock tables t1 write; |
| 411 |
show status like '%opened_tab%'; |
| 412 |
Variable_name Value |
| 413 |
Opened_table_definitions 2 |
| 414 |
Opened_tables 2 |
| 415 |
insert t1 values (4,4); |
| 416 |
show status like '%opened_tab%'; |
| 417 |
Variable_name Value |
| 418 |
Opened_table_definitions 5 |
| 419 |
Opened_tables 5 |
| 420 |
unlock tables; |
| 421 |
create function foo() returns int |
| 422 |
begin |
| 423 |
insert t1 values (5,5); |
| 424 |
return 5; |
| 425 |
end| |
| 426 |
flush tables; |
| 427 |
flush status; |
| 428 |
select foo(); |
| 429 |
foo() |
| 430 |
5 |
| 431 |
show status like '%opened_tab%'; |
| 432 |
Variable_name Value |
| 433 |
Opened_table_definitions 5 |
| 434 |
Opened_tables 5 |
| 435 |
drop function foo; |
| 436 |
drop table t2, t1; |
| 437 |
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk)) ENGINE=InnoDB; |
| 438 |
XA START 'xid'; |
| 439 |
INSERT INTO t1 VALUES (1,2); |
| 440 |
CREATE TABLE x AS SELECT * FROM t1; |
| 441 |
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state |
| 442 |
connect con1,localhost,root,,test; |
| 443 |
SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 0; |
| 444 |
SET lock_wait_timeout=2; |
| 445 |
ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE; |
| 446 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 447 |
disconnect con1; |
| 448 |
connection default; |
| 449 |
XA END 'xid'; |
| 450 |
XA ROLLBACK 'xid'; |
| 451 |
DROP TABLE t1; |
| 452 |
CREATE TABLE t1 (pk INT PRIMARY KEY, |
| 453 |
f1 VARCHAR(10), f2 VARCHAR(10), |
| 454 |
f3 VARCHAR(10), f4 VARCHAR(10), |
| 455 |
f5 VARCHAR(10), f6 VARCHAR(10), |
| 456 |
f7 VARCHAR(10), f8 VARCHAR(10), |
| 457 |
INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4), |
| 458 |
INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB; |
| 459 |
INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb', |
| 460 |
'mariadb', 'mariadb', 'mariadb', 'mariadb'), |
| 461 |
(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb', |
| 462 |
'mariadb', 'mariadb', 'mariadb', 'mariadb'), |
| 463 |
(3, 'innodb', 'innodb', 'innodb', 'innodb', |
| 464 |
'innodb', 'innodb', 'innodb', 'innodb'); |
| 465 |
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL; |
| 466 |
START TRANSACTION; |
| 467 |
DELETE FROM t1 where f1='mariadb'; |
| 468 |
SELECT * FROM t1; |
| 469 |
pk f1 f2 f3 f4 f5 f6 f7 f8 |
| 470 |
2 NULL mariadb mariadb mariadb mariadb mariadb mariadb mariadb |
| 471 |
3 innodb innodb innodb innodb innodb innodb innodb innodb |
| 472 |
ROLLBACK; |
| 473 |
ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE; |
| 474 |
START TRANSACTION; |
| 475 |
DELETE FROM t1 where f3='mariadb'; |
| 476 |
SELECT * FROM t1; |
| 477 |
pk f1 f2 f3 f4 f5 f6 f7 f8 |
| 478 |
3 innodb innodb innodb innodb innodb innodb innodb innodb |
| 479 |
ROLLBACK; |
| 480 |
ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL; |
| 481 |
UPDATE t1 SET f6='update'; |
| 482 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL) |
| 483 |
ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE; |
| 484 |
UPDATE t1 SET f6='cascade'; |
| 485 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL) |
| 486 |
DROP TABLE t1; |
| 487 |
# Start of 10.2 tests |
| 488 |
# |
| 489 |
# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint |
| 490 |
# |
| 491 |
CREATE TABLE users ( |
| 492 |
id int unsigned AUTO_INCREMENT PRIMARY KEY, |
| 493 |
name varchar(32) NOT NULL DEFAULT '' |
| 494 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 495 |
CREATE TABLE matchmaking_groups ( |
| 496 |
id bigint unsigned AUTO_INCREMENT PRIMARY KEY, |
| 497 |
host_user_id int unsigned NOT NULL UNIQUE, |
| 498 |
CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) |
| 499 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 500 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 501 |
CREATE TABLE matchmaking_group_users ( |
| 502 |
matchmaking_group_id bigint unsigned NOT NULL, |
| 503 |
user_id int unsigned NOT NULL, |
| 504 |
PRIMARY KEY (matchmaking_group_id,user_id), |
| 505 |
UNIQUE KEY user_id (user_id), |
| 506 |
CONSTRAINT FOREIGN KEY (matchmaking_group_id) |
| 507 |
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 508 |
CONSTRAINT FOREIGN KEY (user_id) |
| 509 |
REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 510 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 511 |
CREATE TABLE matchmaking_group_maps ( |
| 512 |
matchmaking_group_id bigint unsigned NOT NULL, |
| 513 |
map_id tinyint unsigned NOT NULL, |
| 514 |
PRIMARY KEY (matchmaking_group_id,map_id), |
| 515 |
CONSTRAINT FOREIGN KEY (matchmaking_group_id) |
| 516 |
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 517 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 518 |
INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); |
| 519 |
INSERT INTO matchmaking_groups VALUES (10,1),(11,2); |
| 520 |
INSERT INTO matchmaking_group_users VALUES (10,1),(11,2); |
| 521 |
INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66); |
| 522 |
BEGIN; |
| 523 |
UPDATE users SET name = 'qux' WHERE id = 1; |
| 524 |
connect con1,localhost,root; |
| 525 |
connection con1; |
| 526 |
SET innodb_lock_wait_timeout= 0; |
| 527 |
DELETE FROM matchmaking_groups WHERE id = 10; |
| 528 |
connection default; |
| 529 |
COMMIT; |
| 530 |
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); |
| 531 |
matchmaking_group_id user_id |
| 532 |
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); |
| 533 |
matchmaking_group_id map_id |
| 534 |
SELECT * FROM users; |
| 535 |
id name |
| 536 |
1 qux |
| 537 |
2 bar |
| 538 |
DROP TABLE |
| 539 |
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; |
| 540 |
# |
| 541 |
# MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout |
| 542 |
# |
| 543 |
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
| 544 |
CREATE TABLE t2 ( |
| 545 |
id INT NOT NULL PRIMARY KEY, |
| 546 |
ref_id INT NOT NULL DEFAULT 0, |
| 547 |
f INT NULL, |
| 548 |
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE |
| 549 |
) ENGINE=InnoDB; |
| 550 |
INSERT INTO t1 VALUES (1),(2); |
| 551 |
INSERT INTO t2 VALUES (1,1,10),(2,2,20); |
| 552 |
SHOW CREATE TABLE t2; |
| 553 |
Table Create Table |
| 554 |
t2 CREATE TABLE `t2` ( |
| 555 |
`id` int(11) NOT NULL, |
| 556 |
`ref_id` int(11) NOT NULL DEFAULT 0, |
| 557 |
`f` int(11) DEFAULT NULL, |
| 558 |
PRIMARY KEY (`id`), |
| 559 |
KEY `ref_id` (`ref_id`), |
| 560 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE |
| 561 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 562 |
connection con1; |
| 563 |
BEGIN; |
| 564 |
UPDATE t2 SET f = 11 WHERE id = 1; |
| 565 |
connection default; |
| 566 |
SET innodb_lock_wait_timeout= 0; |
| 567 |
DELETE FROM t1 WHERE id = 1; |
| 568 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 569 |
SET innodb_lock_wait_timeout= 1; |
| 570 |
connection con1; |
| 571 |
COMMIT; |
| 572 |
connection default; |
| 573 |
SELECT * FROM t2; |
| 574 |
id ref_id f |
| 575 |
1 1 11 |
| 576 |
2 2 20 |
| 577 |
DELETE FROM t1 WHERE id = 1; |
| 578 |
SELECT * FROM t2; |
| 579 |
id ref_id f |
| 580 |
2 2 20 |
| 581 |
DROP TABLE t2, t1; |
| 582 |
# |
| 583 |
# MDEV-15199 Referential integrity broken in ON DELETE CASCADE |
| 584 |
# |
| 585 |
CREATE TABLE member (id int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; |
| 586 |
INSERT INTO member VALUES (1); |
| 587 |
CREATE TABLE address ( |
| 588 |
id int AUTO_INCREMENT PRIMARY KEY, |
| 589 |
member_id int NOT NULL, |
| 590 |
KEY address_FI_1 (member_id), |
| 591 |
CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) |
| 592 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 593 |
) ENGINE=InnoDB; |
| 594 |
INSERT INTO address VALUES (2,1); |
| 595 |
CREATE TABLE payment_method ( |
| 596 |
id int AUTO_INCREMENT PRIMARY KEY, |
| 597 |
member_id int NOT NULL, |
| 598 |
cardholder_address_id int DEFAULT NULL, |
| 599 |
KEY payment_method_FI_1 (member_id), |
| 600 |
KEY payment_method_FI_2 (cardholder_address_id), |
| 601 |
CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 602 |
CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE |
| 603 |
) ENGINE=InnoDB; |
| 604 |
INSERT INTO payment_method VALUES (3,1,2); |
| 605 |
BEGIN; |
| 606 |
UPDATE member SET id=42; |
| 607 |
SELECT * FROM member; |
| 608 |
id |
| 609 |
42 |
| 610 |
SELECT * FROM address; |
| 611 |
id member_id |
| 612 |
2 42 |
| 613 |
SELECT * FROM payment_method; |
| 614 |
id member_id cardholder_address_id |
| 615 |
3 42 2 |
| 616 |
DELETE FROM member; |
| 617 |
COMMIT; |
| 618 |
SELECT * FROM member; |
| 619 |
id |
| 620 |
SELECT * FROM address; |
| 621 |
id member_id |
| 622 |
SELECT * FROM payment_method; |
| 623 |
id member_id cardholder_address_id |
| 624 |
DROP TABLE payment_method,address,member; |
| 625 |
# |
| 626 |
# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY |
| 627 |
# PRODUCE BROKEN TABLE (no bug in MariaDB) |
| 628 |
# |
| 629 |
create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; |
| 630 |
create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, |
| 631 |
foreign key(f1) references t1(f2) on update set NULL) |
| 632 |
engine=innodb; |
| 633 |
insert into t1 values(1, 1); |
| 634 |
insert into t2(f1) values(1); |
| 635 |
drop table t2, t1; |
| 636 |
SET FOREIGN_KEY_CHECKS=0; |
| 637 |
CREATE TABLE staff ( |
| 638 |
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 639 |
store_id TINYINT UNSIGNED NOT NULL, |
| 640 |
PRIMARY KEY (staff_id), |
| 641 |
KEY idx_fk_store_id (store_id), |
| 642 |
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 643 |
) ENGINE=InnoDB; |
| 644 |
CREATE TABLE store ( |
| 645 |
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 646 |
manager_staff_id TINYINT UNSIGNED NOT NULL, |
| 647 |
PRIMARY KEY (store_id), |
| 648 |
UNIQUE KEY idx_unique_manager (manager_staff_id), |
| 649 |
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE |
| 650 |
) ENGINE=InnoDB; |
| 651 |
LOCK TABLE staff WRITE; |
| 652 |
UNLOCK TABLES; |
| 653 |
DROP TABLES staff, store; |
| 654 |
SET FOREIGN_KEY_CHECKS=1; |
| 655 |
# |
| 656 |
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs |
| 657 |
# |
| 658 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 659 |
CREATE TABLE t2 (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a)) |
| 660 |
ENGINE=InnoDB; |
| 661 |
connection con1; |
| 662 |
INSERT INTO t1 SET a=1; |
| 663 |
BEGIN; |
| 664 |
DELETE FROM t1; |
| 665 |
connection default; |
| 666 |
INSERT INTO t2 SET a=1; |
| 667 |
connection con1; |
| 668 |
kill query @id; |
| 669 |
connection default; |
| 670 |
ERROR 70100: Query execution was interrupted |
| 671 |
connection con1; |
| 672 |
ROLLBACK; |
| 673 |
connection default; |
| 674 |
DROP TABLE t2,t1; |
| 675 |
# |
| 676 |
# MDEV-18272 InnoDB index corruption after failed DELETE CASCADE |
| 677 |
# |
| 678 |
CREATE TABLE t1 ( |
| 679 |
pk TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
| 680 |
a TINYINT UNSIGNED NOT NULL, b TINYINT UNSIGNED NOT NULL, KEY(b), |
| 681 |
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE |
| 682 |
) ENGINE=InnoDB; |
| 683 |
INSERT INTO t1 (a,b) VALUES |
| 684 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), |
| 685 |
(0,1),(0,1),(1,0); |
| 686 |
connection con1; |
| 687 |
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
| 688 |
connection default; |
| 689 |
DELETE IGNORE FROM t1 WHERE b = 1; |
| 690 |
Warnings: |
| 691 |
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again |
| 692 |
Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB |
| 693 |
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again |
| 694 |
Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB |
| 695 |
SELECT a FROM t1 FORCE INDEX(a); |
| 696 |
a |
| 697 |
0 |
| 698 |
0 |
| 699 |
0 |
| 700 |
0 |
| 701 |
0 |
| 702 |
0 |
| 703 |
0 |
| 704 |
0 |
| 705 |
0 |
| 706 |
0 |
| 707 |
0 |
| 708 |
0 |
| 709 |
0 |
| 710 |
0 |
| 711 |
1 |
| 712 |
SELECT * FROM t1; |
| 713 |
pk a b |
| 714 |
1 0 0 |
| 715 |
2 0 0 |
| 716 |
3 0 0 |
| 717 |
4 0 0 |
| 718 |
5 0 0 |
| 719 |
6 0 0 |
| 720 |
7 0 0 |
| 721 |
8 0 0 |
| 722 |
9 0 0 |
| 723 |
10 0 0 |
| 724 |
11 0 0 |
| 725 |
12 0 0 |
| 726 |
13 0 1 |
| 727 |
14 0 1 |
| 728 |
15 1 0 |
| 729 |
connection con1; |
| 730 |
COMMIT; |
| 731 |
connection default; |
| 732 |
InnoDB 0 transactions not purged |
| 733 |
CHECK TABLE t1; |
| 734 |
Table Op Msg_type Msg_text |
| 735 |
test.t1 check status OK |
| 736 |
DROP TABLE t1; |
| 737 |
# |
| 738 |
# MDEV-17187 table doesn't exist in engine after ALTER other tables |
| 739 |
# with CONSTRAINTs |
| 740 |
# |
| 741 |
call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t2` has or is referenced in foreign key constraints which are not compatible with the new table definition."); |
| 742 |
set foreign_key_checks=on; |
| 743 |
create table t1 (id int not null primary key) engine=innodb; |
| 744 |
create table t2 (id int not null primary key, fid int not null, |
| 745 |
CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb; |
| 746 |
insert into t1 values (1), (2), (3); |
| 747 |
insert into t2 values (1, 1), (2, 1), (3, 2); |
| 748 |
set foreign_key_checks=off; |
| 749 |
alter table t2 drop index fk_fid; |
| 750 |
set foreign_key_checks=on; |
| 751 |
delete from t1 where id=2; |
| 752 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) |
| 753 |
insert into t2 values(4, 99); |
| 754 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) |
| 755 |
select * from t1; |
| 756 |
id |
| 757 |
1 |
| 758 |
2 |
| 759 |
3 |
| 760 |
select * from t2; |
| 761 |
id fid |
| 762 |
1 1 |
| 763 |
2 1 |
| 764 |
3 2 |
| 765 |
set foreign_key_checks=off; |
| 766 |
delete from t1 where id=2; |
| 767 |
insert into t2 values(4, 99); |
| 768 |
set foreign_key_checks=on; |
| 769 |
select * from t1; |
| 770 |
id |
| 771 |
1 |
| 772 |
3 |
| 773 |
select * from t2; |
| 774 |
id fid |
| 775 |
1 1 |
| 776 |
2 1 |
| 777 |
3 2 |
| 778 |
4 99 |
| 779 |
show create table t1; |
| 780 |
Table Create Table |
| 781 |
t1 CREATE TABLE `t1` ( |
| 782 |
`id` int(11) NOT NULL, |
| 783 |
PRIMARY KEY (`id`) |
| 784 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 785 |
show create table t2; |
| 786 |
Table Create Table |
| 787 |
t2 CREATE TABLE `t2` ( |
| 788 |
`id` int(11) NOT NULL, |
| 789 |
`fid` int(11) NOT NULL, |
| 790 |
PRIMARY KEY (`id`), |
| 791 |
CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`) |
| 792 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 793 |
drop table t1,t2; |
| 794 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails |
| 795 |
drop table t1,t2; |
| 796 |
ERROR 42S02: Unknown table 'test.t2' |
| 797 |
# |
| 798 |
# MDEV-22934 Table disappear after two alter table command |
| 799 |
# |
| 800 |
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT, |
| 801 |
f2 INT NOT NULL, |
| 802 |
PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB; |
| 803 |
CREATE TABLE t2(f1 INT NOT NULL, |
| 804 |
f2 INT NOT NULL, f3 INT NOT NULL, |
| 805 |
PRIMARY KEY(f1, f2), UNIQUE KEY(f2), |
| 806 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE, |
| 807 |
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE |
| 808 |
) ENGINE=InnoDB; |
| 809 |
SET FOREIGN_KEY_CHECKS=0; |
| 810 |
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE; |
| 811 |
ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY; |
| 812 |
SHOW CREATE TABLE t2; |
| 813 |
Table Create Table |
| 814 |
t2 CREATE TABLE `t2` ( |
| 815 |
`f1` int(11) NOT NULL, |
| 816 |
`f2` int(11) NOT NULL, |
| 817 |
`f3` int(11) NOT NULL, |
| 818 |
PRIMARY KEY (`f3`) |
| 819 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 820 |
CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB; |
| 821 |
ERROR 42S01: Table 't2' already exists |
| 822 |
DROP TABLE t2, t1; |
| 823 |
# |
| 824 |
# MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt |
| 825 |
# to create unique key on virtual column |
| 826 |
# |
| 827 |
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b INT AS (a)) ENGINE=InnODB; |
| 828 |
INSERT INTO t1 (pk,a) VALUES (1,10),(2,10); |
| 829 |
ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED; |
| 830 |
ERROR 23000: Duplicate entry '10' for key 'ind9' |
| 831 |
SET FOREIGN_KEY_CHECKS= 0; |
| 832 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk); |
| 833 |
DROP TABLE t1; |
| 834 |
SET FOREIGN_KEY_CHECKS= 1; |
| 835 |
# |
| 836 |
# MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query |
| 837 |
# |
| 838 |
Parsing foreign keys 1... |
| 839 |
ERROR HY000: Can't create table `test`.`t0` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 840 |
Parsing foreign keys 2... |
| 841 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 842 |
Parsing foreign keys 3... |
| 843 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 844 |
Parsing foreign keys 4... |
| 845 |
# |
| 846 |
# MDEV-27583 InnoDB uses different constants for FK cascade |
| 847 |
# error message in SQL vs error log |
| 848 |
# |
| 849 |
CREATE TABLE t1 |
| 850 |
(a INT, b INT, KEY(b), |
| 851 |
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE) |
| 852 |
ENGINE=InnoDB; |
| 853 |
INSERT INTO t1 (a,b) VALUES |
| 854 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), |
| 855 |
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0); |
| 856 |
DELETE FROM t1 WHERE b = 1; |
| 857 |
ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB |
| 858 |
SHOW WARNINGS; |
| 859 |
Level Code Message |
| 860 |
Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again |
| 861 |
Error 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB |
| 862 |
DROP TABLE t1; |
| 863 |
FOUND 1 /InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*/ in mysqld.1.err |
| 864 |
# End of 10.2 tests |
| 865 |
# |
| 866 |
# MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN |
| 867 |
# |
| 868 |
SET NAMES utf8; |
| 869 |
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 870 |
CREATE DATABASE `db`; |
| 871 |
CREATE TABLE `db`.u ( |
| 872 |
a INT PRIMARY KEY, |
| 873 |
CONSTRAINT `††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††` |
| 874 |
FOREIGN KEY (a) REFERENCES test.t (a)) ENGINE=InnoDB; |
| 875 |
DROP TABLE `db`.u; |
| 876 |
DROP DATABASE `db`; |
| 877 |
DROP TABLE t; |
| 878 |
# End of 10.3 tests |
| 879 |
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), |
| 880 |
FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; |
| 881 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 882 |
# |
| 883 |
# MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col |
| 884 |
# |
| 885 |
CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c), |
| 886 |
FOREIGN KEY fx (b) REFERENCES t1 (c)) |
| 887 |
ENGINE=InnoDB; |
| 888 |
INSERT INTO t1 VALUES (1,0,10,10); |
| 889 |
ALTER TABLE t1 DROP a, ALGORITHM=INSTANT; |
| 890 |
SET FOREIGN_KEY_CHECKS= 0; |
| 891 |
DROP INDEX fx ON t1; |
| 892 |
INSERT INTO t1 VALUES (2,11,11); |
| 893 |
DROP TABLE t1; |
| 894 |
SET FOREIGN_KEY_CHECKS=DEFAULT; |
| 895 |
# |
| 896 |
# MDEV-32018 Allow the setting of Auto_increment on FK referenced columns |
| 897 |
# |
| 898 |
CREATE TABLE t1 ( |
| 899 |
id int unsigned NOT NULL PRIMARY KEY |
| 900 |
); |
| 901 |
CREATE TABLE t2 ( |
| 902 |
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, |
| 903 |
t1_id int unsigned DEFAULT NULL, |
| 904 |
CONSTRAINT FK_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id) |
| 905 |
); |
| 906 |
ALTER TABLE t1 MODIFY id INT unsigned AUTO_INCREMENT; |
| 907 |
DROP TABLE t1,t2; |
| 908 |
# |
| 909 |
# MDEV-31441 BLOB corruption on UPDATE of PRIMARY KEY with FOREIGN KEY |
| 910 |
# |
| 911 |
CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT) ENGINE=InnoDB; |
| 912 |
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1(pk)) |
| 913 |
ENGINE=InnoDB; |
| 914 |
SET @blob = REPEAT('A', @@innodb_page_size / 2); |
| 915 |
INSERT INTO t1 SET pk=1, t=@blob; |
| 916 |
INSERT INTO t2 SET pk=1; |
| 917 |
connection con1; |
| 918 |
BEGIN; |
| 919 |
DELETE FROM t2; |
| 920 |
connection default; |
| 921 |
UPDATE t1 SET pk=12; |
| 922 |
connection con1; |
| 923 |
COMMIT; |
| 924 |
disconnect con1; |
| 925 |
connection default; |
| 926 |
UPDATE t1 SET pk=1; |
| 927 |
SELECT pk,t=@blob FROM t1; |
| 928 |
pk t=@blob |
| 929 |
1 1 |
| 930 |
DROP TABLE t2, t1; |
| 931 |
# End of 10.4 tests |
| 932 |
# |
| 933 |
# MDEV-20729 Fix REFERENCES constraint in column definition |
| 934 |
# |
| 935 |
set default_storage_engine= innodb; |
| 936 |
create table t1 (x int primary key, y int unique); |
| 937 |
create table t2 (x int references t1(x), y int constraint fk references t1(y)); |
| 938 |
show create table t2; |
| 939 |
Table Create Table |
| 940 |
t2 CREATE TABLE `t2` ( |
| 941 |
`x` int(11) DEFAULT NULL, |
| 942 |
`y` int(11) DEFAULT NULL, |
| 943 |
KEY `x` (`x`), |
| 944 |
KEY `fk` (`y`), |
| 945 |
CONSTRAINT `fk` FOREIGN KEY (`y`) REFERENCES `t1` (`y`), |
| 946 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`) |
| 947 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 948 |
create table t3 (z int); |
| 949 |
alter table t3 add x int references t1(x), add y int constraint fk2 references t1(y); |
| 950 |
show create table t3; |
| 951 |
Table Create Table |
| 952 |
t3 CREATE TABLE `t3` ( |
| 953 |
`z` int(11) DEFAULT NULL, |
| 954 |
`x` int(11) DEFAULT NULL, |
| 955 |
`y` int(11) DEFAULT NULL, |
| 956 |
KEY `x` (`x`), |
| 957 |
KEY `fk2` (`y`), |
| 958 |
CONSTRAINT `fk2` FOREIGN KEY (`y`) REFERENCES `t1` (`y`), |
| 959 |
CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`) |
| 960 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 961 |
drop tables t3, t2, t1; |
| 962 |
create table t1 (id int primary key); |
| 963 |
create table t2 (id2 int references t1); |
| 964 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 965 |
create table t2 (id int references t1); |
| 966 |
show create table t2; |
| 967 |
Table Create Table |
| 968 |
t2 CREATE TABLE `t2` ( |
| 969 |
`id` int(11) DEFAULT NULL, |
| 970 |
KEY `id` (`id`), |
| 971 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) |
| 972 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 973 |
drop tables t2, t1; |
| 974 |
set default_storage_engine= default; |
| 975 |
# |
| 976 |
# MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func |
| 977 |
# |
| 978 |
SET FOREIGN_KEY_CHECKS=1; |
| 979 |
CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB; |
| 980 |
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b); |
| 981 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 982 |
SET FOREIGN_KEY_CHECKS=DEFAULT; |
| 983 |
DROP TABLE t1; |
| 984 |
# |
| 985 |
# MDEV-22602 Disable UPDATE CASCADE for SQL constraints |
| 986 |
# |
| 987 |
# TODO: enable them after MDEV-16417 is finished |
| 988 |
create or replace table t1 (a int primary key) engine=innodb; |
| 989 |
create or replace table t2 (a int, constraint foo check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 990 |
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `foo` |
| 991 |
create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 992 |
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `CONSTRAINT_1` |
| 993 |
create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb; |
| 994 |
create or replace table t2 ( |
| 995 |
a int, s date, e date, |
| 996 |
period for p (s, e), |
| 997 |
primary key (a, p without overlaps), |
| 998 |
foreign key (a, e, s) references t1 (f1, f3, f2) on delete cascade on update cascade) engine=innodb; |
| 999 |
ERROR HY000: Key `PRIMARY` cannot have WITHOUT OVERLAPS |
| 1000 |
create or replace table t1 (a varchar(4096) unique) engine=innodb; |
| 1001 |
create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb; |
| 1002 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 1003 |
drop table t1; |
| 1004 |
# |
| 1005 |
# MDEV-26824 Can't add foreign key with empty referenced columns list |
| 1006 |
# |
| 1007 |
create table t2(a int primary key) engine=innodb; |
| 1008 |
create table t1(a int primary key, b int) engine=innodb; |
| 1009 |
alter table t2 add foreign key(a) references t1(a, b); |
| 1010 |
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match |
| 1011 |
create or replace table t1(a tinyint primary key) engine innodb; |
| 1012 |
alter table t2 add foreign key(a) references t1; |
| 1013 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 1014 |
create or replace table t1(b int primary key) engine innodb; |
| 1015 |
alter table t2 add foreign key(a) references t1; |
| 1016 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 1017 |
create or replace table t1(a int primary key, b int) engine innodb; |
| 1018 |
alter table t2 add foreign key(a) references t1; |
| 1019 |
show create table t2; |
| 1020 |
Table Create Table |
| 1021 |
t2 CREATE TABLE `t2` ( |
| 1022 |
`a` int(11) NOT NULL, |
| 1023 |
PRIMARY KEY (`a`), |
| 1024 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) |
| 1025 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 1026 |
drop tables t2, t1; |
| 1027 |
# |
| 1028 |
# MDEV-29182 Assertion fld->field_no < table->n_v_def failed on cascade |
| 1029 |
# |
| 1030 |
CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(3), c INT AS (LENGTH(b)) VIRTUAL, |
| 1031 |
INDEX(c)) ENGINE=InnoDB; |
| 1032 |
CREATE TABLE t2(a INT REFERENCES t1(a) ON UPDATE CASCADE, |
| 1033 |
b INT GENERATED ALWAYS AS(a) VIRTUAL, INDEX(b)) ENGINE=InnoDB; |
| 1034 |
INSERT INTO t1 SET a=1,b='fu'; |
| 1035 |
INSERT INTO t2 SET a=1; |
| 1036 |
UPDATE t1 SET a=2,b='bar'; |
| 1037 |
SELECT * FROM t1; |
| 1038 |
a b c |
| 1039 |
2 bar 3 |
| 1040 |
SELECT * FROM t2; |
| 1041 |
a b |
| 1042 |
2 2 |
| 1043 |
DROP TABLE t2,t1; |
| 1044 |
# End of 10.5 tests |
| 1045 |
# |
| 1046 |
# MDEV-26554 Table-rebuilding DDL on parent table causes crash |
| 1047 |
# for INSERT into child table |
| 1048 |
# |
| 1049 |
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB; |
| 1050 |
CREATE TABLE child(a INT PRIMARY KEY REFERENCES parent(a)) ENGINE=InnoDB; |
| 1051 |
connect con1, localhost, root,,; |
| 1052 |
BEGIN; |
| 1053 |
INSERT INTO child SET a=1; |
| 1054 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)) |
| 1055 |
connection default; |
| 1056 |
TRUNCATE TABLE parent; |
| 1057 |
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`)) |
| 1058 |
DROP TABLE parent; |
| 1059 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails |
| 1060 |
SET innodb_lock_wait_timeout=0; |
| 1061 |
RENAME TABLE parent TO transparent; |
| 1062 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1063 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1064 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1065 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1066 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1067 |
SET innodb_lock_wait_timeout=0, foreign_key_checks=0; |
| 1068 |
TRUNCATE TABLE parent; |
| 1069 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1070 |
DROP TABLE parent; |
| 1071 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1072 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1073 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1074 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1075 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1076 |
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT; |
| 1077 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 1078 |
connection con1; |
| 1079 |
COMMIT; |
| 1080 |
connection default; |
| 1081 |
SET innodb_lock_wait_timeout=DEFAULT; |
| 1082 |
TRUNCATE TABLE parent; |
| 1083 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1084 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1085 |
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT; |
| 1086 |
SET foreign_key_checks=ON; |
| 1087 |
TRUNCATE TABLE parent; |
| 1088 |
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`)) |
| 1089 |
ALTER TABLE parent FORCE, ALGORITHM=COPY; |
| 1090 |
ALTER TABLE parent FORCE, ALGORITHM=INPLACE; |
| 1091 |
RENAME TABLE parent TO transparent; |
| 1092 |
DROP TABLE child, transparent; |
| 1093 |
# |
| 1094 |
# MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove |
| 1095 |
# or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table |
| 1096 |
# |
| 1097 |
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB; |
| 1098 |
INSERT INTO t1 VALUES (1); |
| 1099 |
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk)) |
| 1100 |
ENGINE=InnoDB; |
| 1101 |
connection con1; |
| 1102 |
SET FOREIGN_KEY_CHECKS=OFF; |
| 1103 |
CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB; |
| 1104 |
connection default; |
| 1105 |
INSERT INTO t2 VALUES (1); |
| 1106 |
connection con1; |
| 1107 |
connection default; |
| 1108 |
DROP TABLE IF EXISTS t2, t1; |
| 1109 |
# |
| 1110 |
# MDEV-30531 Corrupt index(es) on busy table when using FOREIGN KEY |
| 1111 |
# |
| 1112 |
CREATE TABLE collections ( |
| 1113 |
id int(11) unsigned NOT NULL AUTO_INCREMENT, |
| 1114 |
collectionhash varchar(255) NOT NULL DEFAULT '0', |
| 1115 |
PRIMARY KEY (id), |
| 1116 |
UNIQUE KEY ix_collection_collectionhash (collectionhash) |
| 1117 |
) ENGINE=InnoDB; |
| 1118 |
CREATE TABLE binaries ( |
| 1119 |
id bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
| 1120 |
collections_id int(11) unsigned NOT NULL DEFAULT 0, |
| 1121 |
binaryhash binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', |
| 1122 |
PRIMARY KEY (id), |
| 1123 |
UNIQUE KEY ix_binary_binaryhash (binaryhash), |
| 1124 |
CONSTRAINT FK_collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE |
| 1125 |
) ENGINE=InnoDB; |
| 1126 |
INSERT INTO collections (id) VALUES (NULL); |
| 1127 |
connection con1; |
| 1128 |
INSERT INTO binaries (id,collections_id) VALUES (NULL,1); |
| 1129 |
REPLACE INTO collections (id) VALUES (NULL); |
| 1130 |
connection default; |
| 1131 |
REPLACE INTO binaries (id) VALUES (NULL); |
| 1132 |
SET GLOBAL innodb_max_purge_lag_wait=0; |
| 1133 |
CHECK TABLE binaries, collections EXTENDED; |
| 1134 |
Table Op Msg_type Msg_text |
| 1135 |
test.binaries check status OK |
| 1136 |
test.collections check status OK |
| 1137 |
disconnect con1; |
| 1138 |
DROP TABLE binaries, collections; |
| 1139 |
CREATE SCHEMA `#mysql50##mysql50#d-b`; |
| 1140 |
CREATE TABLE `#mysql50##mysql50#d-b`.t1 (a INT PRIMARY KEY, b INT UNIQUE) engine=InnoDB; |
| 1141 |
USE `#mysql50##mysql50#d-b`; |
| 1142 |
CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE REFERENCES t1(b)) ENGINE=InnoDB; |
| 1143 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1144 |
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); |
| 1145 |
SHOW CREATE TABLE t2; |
| 1146 |
Table Create Table |
| 1147 |
t2 CREATE TABLE `t2` ( |
| 1148 |
`a` int(11) NOT NULL, |
| 1149 |
`b` int(11) DEFAULT NULL, |
| 1150 |
PRIMARY KEY (`a`), |
| 1151 |
UNIQUE KEY `b` (`b`), |
| 1152 |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`), |
| 1153 |
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) |
| 1154 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 1155 |
INSERT INTO t1 SET a=1; |
| 1156 |
INSERT INTO t2 SET a=1; |
| 1157 |
DELETE FROM t1; |
| 1158 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`#mysql50#d-b`.`t2`, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) |
| 1159 |
DELETE FROM t2; |
| 1160 |
DELETE FROM t1; |
| 1161 |
DROP DATABASE `#mysql50##mysql50#d-b`; |
| 1162 |
USE test; |
| 1163 |
# |
| 1164 |
# MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint |
| 1165 |
# |
| 1166 |
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB; |
| 1167 |
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 1168 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1169 |
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB; |
| 1170 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 1171 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 1172 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1173 |
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
| 1174 |
INSERT INTO t1 VALUES (1); |
| 1175 |
INSERT INTO t2 VALUES (1); |
| 1176 |
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)) |
| 1177 |
ALTER TABLE t1 ADD KEY(a), ALGORITHM=NOCOPY; |
| 1178 |
INSERT INTO t2 VALUES (1); |
| 1179 |
DROP INDEX b ON t2; |
| 1180 |
ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint |
| 1181 |
SET STATEMENT foreign_key_checks=0 FOR |
| 1182 |
DROP INDEX b ON t2; |
| 1183 |
DELETE FROM t2; |
| 1184 |
DELETE FROM t1; |
| 1185 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)) |
| 1186 |
ALTER TABLE t2 ADD KEY(b), ALGORITHM=NOCOPY; |
| 1187 |
DELETE FROM t1; |
| 1188 |
DROP TABLE t2, t1; |
| 1189 |
# |
| 1190 |
# MDEV-33167 ASAN errors after failing to load foreign key |
| 1191 |
# relation for the table |
| 1192 |
# |
| 1193 |
call mtr.add_suppression("InnoDB: Load table `test`.`t3` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again."); |
| 1194 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1195 |
CREATE TABLE t1(f1 VARCHAR(8), |
| 1196 |
FOREIGN KEY(f1) REFERENCES test.t3(f1))ENGINE=InnoDB; |
| 1197 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1198 |
CREATE TABLE t2(f1 VARCHAR(8), |
| 1199 |
FOREIGN KEY(f1) REFERENCES test.t3(f1)) |
| 1200 |
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; |
| 1201 |
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR |
| 1202 |
CREATE TABLE t3(f1 VARCHAR(8) PRIMARY KEY) |
| 1203 |
ENGINE=InnoDB DEFAULT CHARSET=latin1; |
| 1204 |
set GLOBAL innodb_fast_shutdown=0; |
| 1205 |
# restart |
| 1206 |
ALTER TABLE t2 FORCE; |
| 1207 |
DROP TABLE t2, t1, t3; |
| 1208 |
# |
| 1209 |
# MDEV-19194 ASAN use-after-poison in |
| 1210 |
# fk_prepare_copy_alter_table upon dropping FK |
| 1211 |
# |
| 1212 |
CREATE TABLE t1(f1 INT NOT NULL, KEY(f1))ENGINE=InnoDB; |
| 1213 |
CREATE TABLE t2(f1 INT NOT NULL, |
| 1214 |
FOREIGN KEY `f1`(f1) REFERENCES t1(f1))ENGINE=InnoDB; |
| 1215 |
ALTER TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1216 |
ERROR 42000: Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1217 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f0, DROP FOREIGN KEY f0, ALGORITHM=COPY; |
| 1218 |
ERROR 42000: Can't DROP FOREIGN KEY `f0`; check that it exists |
| 1219 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f0, ALGORITHM=COPY; |
| 1220 |
ERROR 42000: Can't DROP FOREIGN KEY `f0`; check that it exists |
| 1221 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1222 |
ERROR 42000: Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1223 |
ALTER IGNORE TABLE t2 DROP FOREIGN KEY f1, DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1224 |
Warnings: |
| 1225 |
Note 1091 Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1226 |
ALTER TABLE t2 DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1227 |
ERROR 42000: Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1228 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1229 |
Warnings: |
| 1230 |
Note 1091 Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1231 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, DROP FOREIGN KEY f1, ALGORITHM=COPY; |
| 1232 |
ERROR 42000: Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1233 |
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS f1, DROP FOREIGN KEY IF EXISTS f1, ALGORITHM=COPY; |
| 1234 |
Warnings: |
| 1235 |
Note 1091 Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1236 |
Note 1091 Can't DROP FOREIGN KEY `f1`; check that it exists |
| 1237 |
DROP TABLE t2, t1; |
| 1238 |
# End of 10.6 tests |
| 1239 |
CREATE TABLE t1 |
| 1240 |
( |
| 1241 |
f1 VARCHAR(32)BINARY NOT NULL, |
| 1242 |
f2 VARCHAR(32)BINARY NOT NULL, |
| 1243 |
PRIMARY KEY (f1), |
| 1244 |
INDEX(f2) |
| 1245 |
) ENGINE=InnoDB; |
| 1246 |
INSERT INTO t1 VALUES('MySQL', 'InnoDB'), ('MariaDB', 'NDB'); |
| 1247 |
CREATE TABLE t2 |
| 1248 |
( |
| 1249 |
f1 VARCHAR(32)BINARY NOT NULL, |
| 1250 |
f2 VARCHAR(255)BINARY NOT NULL, |
| 1251 |
f3 int, PRIMARY KEY (f1), INDEX(f1), INDEX(f2) |
| 1252 |
) ENGINE=InnoDB; |
| 1253 |
INSERT INTO t2 VALUES('MySQL', 'MySQL', 1), |
| 1254 |
('MariaDB', 'MariaDB', 1); |
| 1255 |
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t2 (f2); |
| 1256 |
ALTER TABLE t2 ADD FOREIGN KEY (f2) REFERENCES t2 (f2), |
| 1257 |
ADD UNIQUE INDEX(f3); |
| 1258 |
ERROR HY000: Cannot delete rows from table which is parent in a foreign key constraint 't1_ibfk_1' of table 't1' |
| 1259 |
drop table t1, t2; |
| 1260 |
# End of 10.11 tests |