Debian Codegraph @

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

raw
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