1# 2# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW 3# DICT_CREATE_FOREIGN_CONSTR 4# 5create table t1 (f1 int primary key) engine=InnoDB; 6create table t2 (f1 int primary key, 7constraint c1 foreign key (f1) references t1(f1), 8constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; 9ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 10create table t2 (f1 int primary key, 11constraint c1 foreign key (f1) references t1(f1)) engine=innodb; 12alter table t2 add constraint c1 foreign key (f1) references t1(f1); 13ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") 14set foreign_key_checks = 0; 15alter table t2 add constraint c1 foreign key (f1) references t1(f1); 16ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/c1' 17drop table t2, t1; 18# 19# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN 20# NULL/NOT NULL MISMATCH 21# 22set foreign_key_checks = 1; 23show variables like 'foreign_key_checks'; 24Variable_name Value 25foreign_key_checks ON 26CREATE TABLE t1 27(a INT NOT NULL, 28b INT NOT NULL, 29INDEX idx(a)) ENGINE=InnoDB; 30CREATE TABLE t2 31(a INT KEY, 32b INT, 33INDEX ind(b), 34FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) 35ENGINE=InnoDB; 36show create table t1; 37Table Create Table 38t1 CREATE TABLE `t1` ( 39 `a` int(11) NOT NULL, 40 `b` int(11) NOT NULL, 41 KEY `idx` (`a`) 42) ENGINE=InnoDB DEFAULT CHARSET=latin1 43show create table t2; 44Table Create Table 45t2 CREATE TABLE `t2` ( 46 `a` int(11) NOT NULL, 47 `b` int(11) DEFAULT NULL, 48 PRIMARY KEY (`a`), 49 KEY `ind` (`b`), 50 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE 51) ENGINE=InnoDB DEFAULT CHARSET=latin1 52INSERT INTO t1 VALUES (1, 80); 53INSERT INTO t1 VALUES (2, 81); 54INSERT INTO t1 VALUES (3, 82); 55INSERT INTO t1 VALUES (4, 83); 56INSERT INTO t1 VALUES (5, 84); 57INSERT INTO t2 VALUES (51, 1); 58INSERT INTO t2 VALUES (52, 2); 59INSERT INTO t2 VALUES (53, 3); 60INSERT INTO t2 VALUES (54, 4); 61INSERT INTO t2 VALUES (55, 5); 62SELECT a, b FROM t1 ORDER BY a; 63a b 641 80 652 81 663 82 674 83 685 84 69SELECT a, b FROM t2 ORDER BY a; 70a b 7151 1 7252 2 7353 3 7454 4 7555 5 76INSERT INTO t2 VALUES (56, 6); 77ERROR 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) 78ALTER TABLE t1 CHANGE a id INT; 79SELECT id, b FROM t1 ORDER BY id; 80id b 811 80 822 81 833 82 844 83 855 84 86SELECT a, b FROM t2 ORDER BY a; 87a b 8851 1 8952 2 9053 3 9154 4 9255 5 93# Operations on child table 94INSERT INTO t2 VALUES (56, 6); 95ERROR 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) 96UPDATE t2 SET b = 99 WHERE a = 51; 97ERROR 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) 98DELETE FROM t2 WHERE a = 53; 99SELECT id, b FROM t1 ORDER BY id; 100id b 1011 80 1022 81 1033 82 1044 83 1055 84 106SELECT a, b FROM t2 ORDER BY a; 107a b 10851 1 10952 2 11054 4 11155 5 112# Operations on parent table 113DELETE FROM t1 WHERE id = 1; 114UPDATE t1 SET id = 50 WHERE id = 5; 115SELECT id, b FROM t1 ORDER BY id; 116id b 1172 81 1183 82 1194 83 12050 84 121SELECT a, b FROM t2 ORDER BY a; 122a b 12352 2 12454 4 12555 50 126DROP TABLE t2, t1; 127# 128# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART 129# base bug#24818604 [GR] 130# 131CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB; 132CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)) 133ENGINE=InnoDB; 134INSERT INTO t1 VALUES (1); 135INSERT INTO t2 VALUES (1); 136SELECT unique_constraint_name FROM information_schema.referential_constraints 137WHERE table_name = 't2'; 138unique_constraint_name 139PRIMARY 140# restart 141SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; 142SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; 143SELECT unique_constraint_name FROM information_schema.referential_constraints 144WHERE table_name = 't2'; 145unique_constraint_name 146PRIMARY 147SELECT * FROM t1; 148c1 1491 150SELECT unique_constraint_name FROM information_schema.referential_constraints 151WHERE table_name = 't2'; 152unique_constraint_name 153PRIMARY 154DROP TABLE t2; 155DROP TABLE t1; 156SET FOREIGN_KEY_CHECKS=0; 157CREATE TABLE staff ( 158staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 159store_id TINYINT UNSIGNED NOT NULL, 160PRIMARY KEY (staff_id), 161KEY idx_fk_store_id (store_id), 162CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE 163) ENGINE=InnoDB; 164CREATE TABLE store ( 165store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 166manager_staff_id TINYINT UNSIGNED NOT NULL, 167PRIMARY KEY (store_id), 168UNIQUE KEY idx_unique_manager (manager_staff_id), 169CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE 170) ENGINE=InnoDB; 171LOCK TABLE staff WRITE; 172UNLOCK TABLES; 173DROP TABLES staff, store; 174SET FOREIGN_KEY_CHECKS=1; 175# 176# MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX 177# 178CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 179CREATE DATABASE best default character set latin1; 180CREATE TABLE t3 (a INT PRIMARY KEY, 181CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; 182CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b), 183FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB; 184RENAME TABLE best.t2 TO test.t2; 185ERROR 42S01: Table 't2' already exists 186SHOW CREATE TABLE best.t2; 187Table Create Table 188t2 CREATE TABLE `t2` ( 189 `a` int(11) NOT NULL, 190 `b` text DEFAULT NULL, 191 PRIMARY KEY (`a`), 192 FULLTEXT KEY `b` (`b`), 193 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`) 194) ENGINE=InnoDB DEFAULT CHARSET=latin1 195DROP DATABASE best; 196# 197# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs 198# 199connect con1, localhost, root,,; 200INSERT INTO t1 SET a=1; 201BEGIN; 202DELETE FROM t1; 203connection default; 204INSERT INTO t3 SET a=1; 205connection con1; 206kill query @id; 207connection default; 208ERROR 70100: Query execution was interrupted 209connection con1; 210ROLLBACK; 211connection default; 212disconnect con1; 213DROP TABLE t3,t1; 214# 215# MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N 216# or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN 217# 218CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB; 219ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a); 220SET SESSION FOREIGN_KEY_CHECKS = OFF; 221ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL; 222ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY; 223ALTER TABLE t1 CHANGE COLUMN a b TIME; 224SET SESSION FOREIGN_KEY_CHECKS = ON; 225DROP TABLE t1; 226# 227# MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N 228# upon DROP FOREIGN KEY 229# 230CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 231CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY fk1 (b) REFERENCES t1 (a)) 232ENGINE=InnoDB; 233ALTER TABLE t2 DROP FOREIGN KEY fk1, DROP FOREIGN KEY fk1; 234DROP TABLE t2, t1; 235CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB; 236SET SESSION FOREIGN_KEY_CHECKS = OFF; 237ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); 238SET SESSION FOREIGN_KEY_CHECKS = ON; 239ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); 240Warnings: 241Warning 1088 failed to load FOREIGN KEY constraints 242ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); 243Warnings: 244Warning 1088 failed to load FOREIGN KEY constraints 245DROP TABLE t1; 246CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY) 247ENGINE=InnoDB; 248SET SESSION FOREIGN_KEY_CHECKS = OFF; 249ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); 250SET SESSION FOREIGN_KEY_CHECKS = ON; 251ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); 252Warnings: 253Warning 1088 failed to load FOREIGN KEY constraints 254ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); 255DROP TABLE t1; 256# 257# MDEV-18630 Conditional jump or move depends on uninitialised value 258# in ib_push_warning / dict_create_foreign_constraints_low 259# 260CREATE TABLE t1 (a INT) ENGINE=InnoDB; 261ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); 262ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 263SHOW WARNINGS; 264Level Code Message 265Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'. 266Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 267Warning 1215 Cannot add foreign key constraint for `t1` 268DROP TABLE t1; 269# 270# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error 271# 272CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB; 273CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB; 274ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f); 275ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1); 276DROP TABLE t1, t2; 277CREATE TABLE t1 (a INT, b INT, KEY idx(a)) ENGINE=InnoDB; 278SET FOREIGN_KEY_CHECKS= OFF; 279ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x); 280ALTER TABLE t1 DROP KEY idx; 281ALTER TABLE t1 CHANGE a c INT; 282DROP TABLE t1; 283CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB; 284ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1); 285ALTER TABLE t1 ADD COLUMN f INT; 286SET FOREIGN_KEY_CHECKS= OFF; 287ALTER TABLE t1 DROP KEY idx; 288ALTER TABLE t1 ADD KEY idx (f1); 289SET FOREIGN_KEY_CHECKS= ON; 290ALTER TABLE t1 DROP f3; 291ALTER TABLE t1 CHANGE f f3 INT; 292DROP TABLE t1; 293SET FOREIGN_KEY_CHECKS=1; 294# 295# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE 296# ADD FOREIGN KEY 297# 298CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, 299PRIMARY KEY (`department_id`)) engine=innodb; 300CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, 301`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb; 302CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb; 303ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES 304`people` (`people_id`); 305ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` 306(`people_id`); 307ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people` 308(`people_id`); 309drop table title, department, people; 310create table t1 (a int primary key, b int) engine=innodb; 311create table t2 (c int primary key, d int, 312foreign key (d) references t1 (a) on update cascade) engine=innodb; 313insert t1 values (1,1),(2,2),(3,3); 314insert t2 values (4,1),(5,2),(6,3); 315flush table t2 with read lock; 316connect con1,localhost,root; 317delete from t1 where a=2; 318ERROR 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) 319update t1 set a=10 where a=1; 320connection default; 321unlock tables; 322connection con1; 323connection default; 324lock table t2 write; 325connection con1; 326delete from t1 where a=2; 327connection default; 328unlock tables; 329connection con1; 330ERROR 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) 331connection default; 332unlock tables; 333disconnect con1; 334create user foo; 335grant select,update on test.t1 to foo; 336connect foo,localhost,foo; 337update t1 set a=30 where a=3; 338disconnect foo; 339connection default; 340select * from t2; 341c d 3425 2 3434 10 3446 30 345drop table t2, t1; 346drop user foo; 347# 348# MDEV-17595 - Server crashes in copy_data_between_tables or 349# Assertion `thd->transaction.stmt.is_empty() || 350# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' 351# fails in close_tables_for_reopen upon concurrent 352# ALTER TABLE and FLUSH 353# 354CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB; 355INSERT INTO t1 VALUES(1),(2); 356CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; 357INSERT INTO t2 VALUES(2); 358ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; 359DROP TABLE t2, t1; 360create table t1 (pk int primary key, data int) engine=innodb; 361insert t1 values (1,1),(2,2),(3,3); 362create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb; 363insert t2 values (1),(2); 364insert t2 values (10); 365ERROR 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`)) 366flush tables; 367flush status; 368update t1 set data=10 where pk+1>10; 369show status like '%opened_tab%'; 370Variable_name Value 371Opened_table_definitions 4 372Opened_tables 4 373flush tables; 374flush status; 375update t2 set t1_pk=11 where t1_pk+1>10; 376show status like '%opened_tab%'; 377Variable_name Value 378Opened_table_definitions 4 379Opened_tables 4 380flush tables; 381flush status; 382lock tables t1 write; 383show status like '%opened_tab%'; 384Variable_name Value 385Opened_table_definitions 2 386Opened_tables 2 387insert t1 values (4,4); 388show status like '%opened_tab%'; 389Variable_name Value 390Opened_table_definitions 5 391Opened_tables 5 392unlock tables; 393create function foo() returns int 394begin 395insert t1 values (5,5); 396return 5; 397end| 398flush tables; 399flush status; 400select foo(); 401foo() 4025 403show status like '%opened_tab%'; 404Variable_name Value 405Opened_table_definitions 5 406Opened_tables 5 407drop function foo; 408drop table t2, t1; 409CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk)) ENGINE=InnoDB; 410XA START 'xid'; 411INSERT INTO t1 VALUES (1,2); 412CREATE TABLE x AS SELECT * FROM t1; 413ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state 414connect con1,localhost,root,,test; 415SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 1; 416SET lock_wait_timeout=5; 417ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE; 418ERROR HY000: Lock wait timeout exceeded; try restarting transaction 419disconnect con1; 420connection default; 421XA END 'xid'; 422XA ROLLBACK 'xid'; 423DROP TABLE t1; 424CREATE TABLE t1 (pk INT PRIMARY KEY, 425f1 VARCHAR(10), f2 VARCHAR(10), 426f3 VARCHAR(10), f4 VARCHAR(10), 427f5 VARCHAR(10), f6 VARCHAR(10), 428f7 VARCHAR(10), f8 VARCHAR(10), 429INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4), 430INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB; 431INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb', 432'mariadb', 'mariadb', 'mariadb', 'mariadb'), 433(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb', 434'mariadb', 'mariadb', 'mariadb', 'mariadb'), 435(3, 'innodb', 'innodb', 'innodb', 'innodb', 436'innodb', 'innodb', 'innodb', 'innodb'); 437ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL; 438START TRANSACTION; 439DELETE FROM t1 where f1='mariadb'; 440SELECT * FROM t1; 441pk f1 f2 f3 f4 f5 f6 f7 f8 4422 NULL mariadb mariadb mariadb mariadb mariadb mariadb mariadb 4433 innodb innodb innodb innodb innodb innodb innodb innodb 444ROLLBACK; 445ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE; 446START TRANSACTION; 447DELETE FROM t1 where f3='mariadb'; 448SELECT * FROM t1; 449pk f1 f2 f3 f4 f5 f6 f7 f8 4503 innodb innodb innodb innodb innodb innodb innodb innodb 451ROLLBACK; 452ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL; 453UPDATE t1 SET f6='update'; 454ERROR 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) 455ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE; 456UPDATE t1 SET f6='cascade'; 457ERROR 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) 458DROP TABLE t1; 459# Start of 10.2 tests 460# 461# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint 462# 463CREATE TABLE users ( 464id int unsigned AUTO_INCREMENT PRIMARY KEY, 465name varchar(32) NOT NULL DEFAULT '' 466) ENGINE=InnoDB DEFAULT CHARSET=utf8; 467CREATE TABLE matchmaking_groups ( 468id bigint unsigned AUTO_INCREMENT PRIMARY KEY, 469host_user_id int unsigned NOT NULL UNIQUE, 470CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) 471ON DELETE CASCADE ON UPDATE CASCADE 472) ENGINE=InnoDB DEFAULT CHARSET=utf8; 473CREATE TABLE matchmaking_group_users ( 474matchmaking_group_id bigint unsigned NOT NULL, 475user_id int unsigned NOT NULL, 476PRIMARY KEY (matchmaking_group_id,user_id), 477UNIQUE KEY user_id (user_id), 478CONSTRAINT FOREIGN KEY (matchmaking_group_id) 479REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, 480CONSTRAINT FOREIGN KEY (user_id) 481REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE 482) ENGINE=InnoDB DEFAULT CHARSET=utf8; 483CREATE TABLE matchmaking_group_maps ( 484matchmaking_group_id bigint unsigned NOT NULL, 485map_id tinyint unsigned NOT NULL, 486PRIMARY KEY (matchmaking_group_id,map_id), 487CONSTRAINT FOREIGN KEY (matchmaking_group_id) 488REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE 489) ENGINE=InnoDB DEFAULT CHARSET=utf8; 490INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); 491INSERT INTO matchmaking_groups VALUES (10,1),(11,2); 492INSERT INTO matchmaking_group_users VALUES (10,1),(11,2); 493INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66); 494BEGIN; 495UPDATE users SET name = 'qux' WHERE id = 1; 496connect con1,localhost,root; 497connection con1; 498SET innodb_lock_wait_timeout= 1; 499DELETE FROM matchmaking_groups WHERE id = 10; 500connection default; 501COMMIT; 502SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); 503matchmaking_group_id user_id 504SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); 505matchmaking_group_id map_id 506SELECT * FROM users; 507id name 5081 qux 5092 bar 510DROP TABLE 511matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; 512# 513# MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout 514# 515CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; 516CREATE TABLE t2 ( 517id INT NOT NULL PRIMARY KEY, 518ref_id INT NOT NULL DEFAULT 0, 519f INT NULL, 520FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE 521) ENGINE=InnoDB; 522INSERT INTO t1 VALUES (1),(2); 523INSERT INTO t2 VALUES (1,1,10),(2,2,20); 524SHOW CREATE TABLE t2; 525Table Create Table 526t2 CREATE TABLE `t2` ( 527 `id` int(11) NOT NULL, 528 `ref_id` int(11) NOT NULL DEFAULT 0, 529 `f` int(11) DEFAULT NULL, 530 PRIMARY KEY (`id`), 531 KEY `ref_id` (`ref_id`), 532 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE 533) ENGINE=InnoDB DEFAULT CHARSET=latin1 534connection con1; 535BEGIN; 536UPDATE t2 SET f = 11 WHERE id = 1; 537connection default; 538SET innodb_lock_wait_timeout= 1; 539DELETE FROM t1 WHERE id = 1; 540ERROR HY000: Lock wait timeout exceeded; try restarting transaction 541connection con1; 542COMMIT; 543connection default; 544SELECT * FROM t2; 545id ref_id f 5461 1 11 5472 2 20 548DELETE FROM t1 WHERE id = 1; 549SELECT * FROM t2; 550id ref_id f 5512 2 20 552DROP TABLE t2, t1; 553# 554# MDEV-15199 Referential integrity broken in ON DELETE CASCADE 555# 556CREATE TABLE member (id int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; 557INSERT INTO member VALUES (1); 558CREATE TABLE address ( 559id int AUTO_INCREMENT PRIMARY KEY, 560member_id int NOT NULL, 561KEY address_FI_1 (member_id), 562CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) 563ON DELETE CASCADE ON UPDATE CASCADE 564) ENGINE=InnoDB; 565INSERT INTO address VALUES (2,1); 566CREATE TABLE payment_method ( 567id int AUTO_INCREMENT PRIMARY KEY, 568member_id int NOT NULL, 569cardholder_address_id int DEFAULT NULL, 570KEY payment_method_FI_1 (member_id), 571KEY payment_method_FI_2 (cardholder_address_id), 572CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, 573CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE 574) ENGINE=InnoDB; 575INSERT INTO payment_method VALUES (3,1,2); 576BEGIN; 577UPDATE member SET id=42; 578SELECT * FROM member; 579id 58042 581SELECT * FROM address; 582id member_id 5832 42 584SELECT * FROM payment_method; 585id member_id cardholder_address_id 5863 42 2 587DELETE FROM member; 588COMMIT; 589SELECT * FROM member; 590id 591SELECT * FROM address; 592id member_id 593SELECT * FROM payment_method; 594id member_id cardholder_address_id 595DROP TABLE payment_method,address,member; 596# 597# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY 598# PRODUCE BROKEN TABLE (no bug in MariaDB) 599# 600create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; 601create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, 602foreign key(f1) references t1(f2) on update set NULL) 603engine=innodb; 604insert into t1 values(1, 1); 605insert into t2(f1) values(1); 606drop table t2, t1; 607SET FOREIGN_KEY_CHECKS=0; 608CREATE TABLE staff ( 609staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 610store_id TINYINT UNSIGNED NOT NULL, 611PRIMARY KEY (staff_id), 612KEY idx_fk_store_id (store_id), 613CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE 614) ENGINE=InnoDB; 615CREATE TABLE store ( 616store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 617manager_staff_id TINYINT UNSIGNED NOT NULL, 618PRIMARY KEY (store_id), 619UNIQUE KEY idx_unique_manager (manager_staff_id), 620CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE 621) ENGINE=InnoDB; 622LOCK TABLE staff WRITE; 623UNLOCK TABLES; 624DROP TABLES staff, store; 625SET FOREIGN_KEY_CHECKS=1; 626# 627# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs 628# 629CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 630CREATE TABLE t2 (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a)) 631ENGINE=InnoDB; 632connection con1; 633INSERT INTO t1 SET a=1; 634BEGIN; 635DELETE FROM t1; 636connection default; 637INSERT INTO t2 SET a=1; 638connection con1; 639kill query @id; 640connection default; 641ERROR 70100: Query execution was interrupted 642connection con1; 643ROLLBACK; 644connection default; 645DROP TABLE t2,t1; 646# 647# MDEV-18272 InnoDB index corruption after failed DELETE CASCADE 648# 649CREATE TABLE t1 ( 650pk TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 651a TINYINT UNSIGNED NOT NULL, b TINYINT UNSIGNED NOT NULL, KEY(b), 652CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE 653) ENGINE=InnoDB; 654INSERT INTO t1 (a,b) VALUES 655(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 656(0,1),(0,1),(1,0); 657connection con1; 658START TRANSACTION WITH CONSISTENT SNAPSHOT; 659connection default; 660DELETE IGNORE FROM t1 WHERE b = 1; 661Warnings: 662Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again 663Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB 664Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again 665Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB 666SELECT a FROM t1 FORCE INDEX(a); 667a 6680 6690 6700 6710 6720 6730 6740 6750 6760 6770 6780 6790 6800 6810 6821 683SELECT * FROM t1; 684pk a b 6851 0 0 6862 0 0 6873 0 0 6884 0 0 6895 0 0 6906 0 0 6917 0 0 6928 0 0 6939 0 0 69410 0 0 69511 0 0 69612 0 0 69713 0 1 69814 0 1 69915 1 0 700disconnect con1; 701InnoDB 0 transactions not purged 702CHECK TABLE t1; 703Table Op Msg_type Msg_text 704test.t1 check status OK 705DROP TABLE t1; 706SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; 707# 708# MDEV-17187 table doesn't exist in engine after ALTER other tables 709# with CONSTRAINTs 710# 711call 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."); 712set foreign_key_checks=on; 713create table t1 (id int not null primary key) engine=innodb; 714create table t2 (id int not null primary key, fid int not null, 715CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb; 716insert into t1 values (1), (2), (3); 717insert into t2 values (1, 1), (2, 1), (3, 2); 718set foreign_key_checks=off; 719alter table t2 drop index fk_fid; 720set foreign_key_checks=on; 721delete from t1 where id=2; 722ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) 723insert into t2 values(4, 99); 724ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) 725select * from t1; 726id 7271 7282 7293 730select * from t2; 731id fid 7321 1 7332 1 7343 2 735set foreign_key_checks=off; 736delete from t1 where id=2; 737insert into t2 values(4, 99); 738set foreign_key_checks=on; 739select * from t1; 740id 7411 7423 743select * from t2; 744id fid 7451 1 7462 1 7473 2 7484 99 749show create table t1; 750Table Create Table 751t1 CREATE TABLE `t1` ( 752 `id` int(11) NOT NULL, 753 PRIMARY KEY (`id`) 754) ENGINE=InnoDB DEFAULT CHARSET=latin1 755show create table t2; 756Table Create Table 757t2 CREATE TABLE `t2` ( 758 `id` int(11) NOT NULL, 759 `fid` int(11) NOT NULL, 760 PRIMARY KEY (`id`), 761 CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`) 762) ENGINE=InnoDB DEFAULT CHARSET=latin1 763drop table t1,t2; 764ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails 765drop table t1,t2; 766ERROR 42S02: Unknown table 'test.t2' 767# 768# MDEV-22934 Table disappear after two alter table command 769# 770CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT, 771f2 INT NOT NULL, 772PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB; 773CREATE TABLE t2(f1 INT NOT NULL, 774f2 INT NOT NULL, f3 INT NOT NULL, 775PRIMARY KEY(f1, f2), UNIQUE KEY(f2), 776CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE, 777CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE 778) ENGINE=InnoDB; 779SET FOREIGN_KEY_CHECKS=0; 780ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE; 781ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY; 782SHOW CREATE TABLE t2; 783Table Create Table 784t2 CREATE TABLE `t2` ( 785 `f1` int(11) NOT NULL, 786 `f2` int(11) NOT NULL, 787 `f3` int(11) NOT NULL, 788 PRIMARY KEY (`f3`) 789) ENGINE=InnoDB DEFAULT CHARSET=latin1 790CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB; 791ERROR 42S01: Table 't2' already exists 792DROP TABLE t2, t1; 793# 794# MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt 795# to create unique key on virtual column 796# 797CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b INT AS (a)) ENGINE=InnODB; 798INSERT INTO t1 (pk,a) VALUES (1,10),(2,10); 799ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED; 800ERROR 23000: Duplicate entry '10' for key 'ind9' 801SET FOREIGN_KEY_CHECKS= 0; 802ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk); 803DROP TABLE t1; 804SET FOREIGN_KEY_CHECKS= 1; 805# 806# MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query 807# 808Parsing foreign keys 1... 809ERROR HY000: Can't create table `test`.`t0` (errno: 150 "Foreign key constraint is incorrectly formed") 810Parsing foreign keys 2... 811ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 812Parsing foreign keys 3... 813ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 814Parsing foreign keys 4... 815# End of 10.2 tests 816CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), 817FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; 818ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 819# 820# MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col 821# 822CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c), 823FOREIGN KEY fx (b) REFERENCES t1 (c)) 824ENGINE=InnoDB; 825INSERT INTO t1 VALUES (1,0,10,10); 826ALTER TABLE t1 DROP a, ALGORITHM=INSTANT; 827SET FOREIGN_KEY_CHECKS= 0; 828DROP INDEX fx ON t1; 829INSERT INTO t1 VALUES (2,11,11); 830DROP TABLE t1; 831SET FOREIGN_KEY_CHECKS=DEFAULT; 832# End of 10.4 tests 833