Debian Codegraph @

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

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