SET GLOBAL innodb_stats_persistent = 0;
#
# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
# DICT_CREATE_FOREIGN_CONSTR
#
create table t1 (f1 int primary key) engine=InnoDB;
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1),
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update")
set foreign_key_checks = 0;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/c1'
drop table t2, t1;
#
# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
# NULL/NOT NULL MISMATCH
#
set foreign_key_checks = 1;
show variables like 'foreign_key_checks';
Variable_name	Value
foreign_key_checks	ON
CREATE TABLE t1
(a INT NOT NULL,
b INT NOT NULL,
INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ind` (`b`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES (1, 80);
INSERT INTO t1 VALUES (2, 81);
INSERT INTO t1 VALUES (3, 82);
INSERT INTO t1 VALUES (4, 83);
INSERT INTO t1 VALUES (5, 84);
INSERT INTO t2 VALUES (51, 1);
INSERT INTO t2 VALUES (52, 2);
INSERT INTO t2 VALUES (53, 3);
INSERT INTO t2 VALUES (54, 4);
INSERT INTO t2 VALUES (55, 5);
SELECT a, b FROM t1 ORDER BY a;
a	b
1	80
2	81
3	82
4	83
5	84
SELECT a, b FROM t2 ORDER BY a;
a	b
51	1
52	2
53	3
54	4
55	5
INSERT INTO t2 VALUES (56, 6);
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)
ALTER TABLE t1 CHANGE a id INT;
SELECT id, b FROM t1 ORDER BY id;
id	b
1	80
2	81
3	82
4	83
5	84
SELECT a, b FROM t2 ORDER BY a;
a	b
51	1
52	2
53	3
54	4
55	5
# Operations on child table
INSERT INTO t2 VALUES (56, 6);
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)
UPDATE t2 SET b = 99 WHERE a = 51;
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)
DELETE FROM t2 WHERE a = 53;
SELECT id, b FROM t1 ORDER BY id;
id	b
1	80
2	81
3	82
4	83
5	84
SELECT a, b FROM t2 ORDER BY a;
a	b
51	1
52	2
54	4
55	5
# Operations on parent table
DELETE FROM t1 WHERE id = 1;
UPDATE t1 SET id = 50 WHERE id = 5;
SELECT id, b FROM t1 ORDER BY id;
id	b
2	81
3	82
4	83
50	84
SELECT a, b FROM t2 ORDER BY a;
a	b
52	2
54	4
55	50
DROP TABLE t2, t1;
#
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
# base bug#24818604 [GR]
#
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1))
ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
#
# MDEV-28317 Assertion failure on rollback of FOREIGN KEY operation
#
SET foreign_key_checks=0;
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE child(a INT,FOREIGN KEY(a) REFERENCES parent(a) ON DELETE CASCADE)
ENGINE=InnoDB;
INSERT INTO child VALUES(1);
ALTER TABLE child DROP INDEX a;
connect incomplete, localhost, root,,;
BEGIN;
DELETE FROM child;
connection default;
INSERT INTO parent SET a=0;
FLUSH TABLES;
# restart
disconnect incomplete;
SET GLOBAL innodb_stats_persistent = 0;
INSERT INTO child SET a=0;
INSERT INTO child SET a=1;
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)
DELETE FROM parent;
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)
ALTER TABLE child ADD INDEX(a);
DELETE FROM parent;
DROP TABLE child,parent;
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
SELECT * FROM t1;
c1
1
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
DROP TABLE t2;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY  (staff_id),
KEY idx_fk_store_id (store_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY  (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
LOCK TABLE staff WRITE;
UNLOCK TABLES;
DROP TABLES staff, store;
SET FOREIGN_KEY_CHECKS=1;
#
# MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE DATABASE best default character set latin1;
CREATE TABLE t3 (a INT PRIMARY KEY,
CONSTRAINT t2_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
CREATE TABLE best.t2 (a INT PRIMARY KEY, b TEXT, FULLTEXT INDEX(b),
FOREIGN KEY (a) REFERENCES test.t1(a)) ENGINE=InnoDB;
RENAME TABLE best.t2 TO test.t2;
ERROR 42S01: Table 't2' already exists
SHOW CREATE TABLE best.t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` text DEFAULT NULL,
  PRIMARY KEY (`a`),
  FULLTEXT KEY `b` (`b`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP DATABASE best;
#
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
#
connect  con1, localhost, root,,;
INSERT INTO t1 SET a=1;
BEGIN;
DELETE FROM t1;
connection default;
INSERT INTO t3 SET a=1;
connection con1;
kill query @id;
connection default;
ERROR 70100: Query execution was interrupted
connection con1;
ROLLBACK;
connection default;
disconnect con1;
DROP TABLE t3,t1;
#
# MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
# or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN
#
CREATE TABLE t1 (a INT, UNIQUE(a), KEY(a)) ENGINE=InnoDB;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a);
SET SESSION FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL;
ERROR HY000: Cannot change column 'a': used in a foreign key constraint 't1_ibfk_1' of table 'test.t1'
ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE t1 CHANGE COLUMN a b TIME;
ERROR 0A000: ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE
ALTER TABLE t1 CHANGE COLUMN a b TIME, DROP FOREIGN KEY t1_ibfk_1;
SET SESSION FOREIGN_KEY_CHECKS = ON;
DROP TABLE t1;
#
# MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
# upon DROP FOREIGN KEY
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY fk1 (b) REFERENCES t1 (a))
ENGINE=InnoDB;
ALTER TABLE t2 DROP FOREIGN KEY fk1, DROP FOREIGN KEY fk1;
DROP TABLE t2, t1;
CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
SET SESSION FOREIGN_KEY_CHECKS = ON;
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
Warnings:
Note	1831	Duplicate index `ft2`. This is deprecated and will be disallowed in a future release
DROP TABLE t1;
CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY)
ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
SET SESSION FOREIGN_KEY_CHECKS = ON;
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
Warnings:
Warning	1088	failed to load FOREIGN KEY constraints
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
Warnings:
Note	1831	Duplicate index `ft2`. This is deprecated and will be disallowed in a future release
DROP TABLE t1;
#
# MDEV-18630 Conditional jump or move depends on uninitialised value
# in ib_push_warning / dict_create_foreign_constraints_low
#
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW WARNINGS;
Level	Code	Message
Warning	150	Alter  table `test`.`t1` with foreign key (a) constraint failed. Referenced table `test`.`t2` not found in the data dictionary.
Error	1005	Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning	1215	Cannot add foreign key constraint for `t1`
DROP TABLE t1;
#
# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
#
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1);
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT, b INT, KEY idx(a)) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS= OFF;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x);
ALTER TABLE t1 DROP KEY idx;
ALTER TABLE t1 CHANGE a c INT;
DROP TABLE t1;
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB;
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1);
ALTER TABLE t1 ADD COLUMN f INT;
SET FOREIGN_KEY_CHECKS= OFF;
ALTER TABLE t1 DROP KEY idx;
ALTER TABLE t1 ADD KEY idx (f1);
SET FOREIGN_KEY_CHECKS= ON;
ALTER TABLE t1 DROP f3;
ALTER TABLE t1 CHANGE f f3 INT;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=1;
#
# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
# ADD FOREIGN KEY
#
CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
PRIMARY KEY (`department_id`)) engine=innodb;
CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
`people` (`people_id`);
ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
(`people_id`);
ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
(`people_id`);
drop table title, department, people;
create table t1 (a int primary key, b int) engine=innodb;
create table t2 (c int primary key, d int,
foreign key (d) references t1 (a) on update cascade) engine=innodb;
insert t1 values (1,1),(2,2),(3,3);
insert t2 values (4,1),(5,2),(6,3);
flush table t2 with read lock;
connect  con1,localhost,root;
delete from t1 where a=2;
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)
update t1 set a=10 where a=1;
connection default;
unlock tables;
connection con1;
connection default;
lock table t2 write;
connection con1;
delete from t1 where a=2;
connection default;
unlock tables;
connection con1;
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)
connection default;
unlock tables;
disconnect con1;
create user foo;
grant select,update on test.t1 to foo;
connect foo,localhost,foo;
update t1 set a=30 where a=3;
disconnect foo;
connection default;
select * from t2;
c	d
5	2
4	10
6	30
drop table t2, t1;
drop user foo;
#
# MDEV-17595 - Server crashes in copy_data_between_tables or
#              Assertion `thd->transaction.stmt.is_empty() ||
#              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
#              fails in close_tables_for_reopen upon concurrent
#              ALTER TABLE and FLUSH
#
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1),(2);
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
INSERT INTO t2 VALUES(2);
ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
DROP TABLE t2, t1;
create table t1 (pk int primary key, data int) engine=innodb;
insert t1 values (1,1),(2,2),(3,3);
create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
insert t2 values (1),(2);
insert t2 values (10);
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`))
flush tables;
flush status;
update t1 set data=10 where pk+1>10;
show status like '%opened_tab%';
Variable_name	Value
Opened_table_definitions	4
Opened_tables	4
flush tables;
flush status;
update t2 set t1_pk=11 where t1_pk+1>10;
show status like '%opened_tab%';
Variable_name	Value
Opened_table_definitions	4
Opened_tables	4
flush tables;
flush status;
lock tables t1 write;
show status like '%opened_tab%';
Variable_name	Value
Opened_table_definitions	2
Opened_tables	2
insert t1 values (4,4);
show status like '%opened_tab%';
Variable_name	Value
Opened_table_definitions	5
Opened_tables	5
unlock tables;
create function foo() returns int
begin
insert t1 values (5,5);
return 5;
end|
flush tables;
flush status;
select foo();
foo()
5
show status like '%opened_tab%';
Variable_name	Value
Opened_table_definitions	5
Opened_tables	5
drop function foo;
drop table t2, t1;
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk)) ENGINE=InnoDB;
XA START 'xid';
INSERT INTO t1 VALUES (1,2);
CREATE TABLE x AS SELECT * FROM t1;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
connect  con1,localhost,root,,test;
SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 0;
SET lock_wait_timeout=2;
ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
disconnect con1;
connection default;
XA END 'xid';
XA ROLLBACK 'xid';
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY,
f1 VARCHAR(10), f2 VARCHAR(10),
f3 VARCHAR(10), f4 VARCHAR(10),
f5 VARCHAR(10), f6 VARCHAR(10),
f7 VARCHAR(10), f8 VARCHAR(10),
INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
'mariadb', 'mariadb', 'mariadb', 'mariadb'),
(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
'mariadb', 'mariadb', 'mariadb', 'mariadb'),
(3, 'innodb', 'innodb', 'innodb', 'innodb',
'innodb', 'innodb', 'innodb', 'innodb');
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
START TRANSACTION;
DELETE FROM t1 where f1='mariadb';
SELECT * FROM t1;
pk	f1	f2	f3	f4	f5	f6	f7	f8
2	NULL	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb
3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
ROLLBACK;
ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
START TRANSACTION;
DELETE FROM t1 where f3='mariadb';
SELECT * FROM t1;
pk	f1	f2	f3	f4	f5	f6	f7	f8
3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
ROLLBACK;
ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
UPDATE t1 SET f6='update';
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)
ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
UPDATE t1 SET f6='cascade';
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)
DROP TABLE t1;
# Start of 10.2 tests
#
# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
#
CREATE TABLE users (
id int unsigned AUTO_INCREMENT PRIMARY KEY,
name varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE matchmaking_groups (
id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
host_user_id int unsigned NOT NULL UNIQUE,
CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE matchmaking_group_users (
matchmaking_group_id bigint unsigned NOT NULL,
user_id int unsigned NOT NULL,
PRIMARY KEY (matchmaking_group_id,user_id),
UNIQUE KEY user_id (user_id),
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE matchmaking_group_maps (
matchmaking_group_id bigint unsigned NOT NULL,
map_id tinyint unsigned NOT NULL,
PRIMARY KEY (matchmaking_group_id,map_id),
CONSTRAINT FOREIGN KEY (matchmaking_group_id)
REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
BEGIN;
UPDATE users SET name = 'qux' WHERE id = 1;
connect  con1,localhost,root;
connection con1;
SET innodb_lock_wait_timeout= 0;
DELETE FROM matchmaking_groups WHERE id = 10;
connection default;
COMMIT;
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
matchmaking_group_id	user_id
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
matchmaking_group_id	map_id
SELECT * FROM users;
id	name
1	qux
2	bar
DROP TABLE
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
#
# MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout
#
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (
id INT NOT NULL PRIMARY KEY,
ref_id INT NOT NULL DEFAULT 0,
f INT NULL,
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1,1,10),(2,2,20);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `ref_id` int(11) NOT NULL DEFAULT 0,
  `f` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ref_id` (`ref_id`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection con1;
BEGIN;
UPDATE t2 SET f = 11 WHERE id = 1;
connection default;
SET innodb_lock_wait_timeout= 0;
DELETE FROM t1 WHERE id = 1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET innodb_lock_wait_timeout= 1;
connection con1;
COMMIT;
connection default;
SELECT * FROM t2;
id	ref_id	f
1	1	11
2	2	20
DELETE FROM t1 WHERE id = 1;
SELECT * FROM t2;
id	ref_id	f
2	2	20
DROP TABLE t2, t1;
#
# MDEV-15199 Referential integrity broken in ON DELETE CASCADE
#
CREATE TABLE member (id int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO member VALUES (1);
CREATE TABLE address (
id int AUTO_INCREMENT PRIMARY KEY,
member_id int NOT NULL,
KEY address_FI_1 (member_id),
CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO address VALUES (2,1);
CREATE TABLE payment_method (
id int AUTO_INCREMENT PRIMARY KEY,
member_id int NOT NULL,
cardholder_address_id int DEFAULT NULL,
KEY payment_method_FI_1 (member_id),
KEY payment_method_FI_2 (cardholder_address_id),
CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO payment_method VALUES (3,1,2);
BEGIN;
UPDATE member SET id=42;
SELECT * FROM member;
id
42
SELECT * FROM address;
id	member_id
2	42
SELECT * FROM payment_method;
id	member_id	cardholder_address_id
3	42	2
DELETE FROM member;
COMMIT;
SELECT * FROM member;
id
SELECT * FROM address;
id	member_id
SELECT * FROM payment_method;
id	member_id	cardholder_address_id
DROP TABLE payment_method,address,member;
#
# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY
# PRODUCE BROKEN TABLE (no bug in MariaDB)
#
create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb;
create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored,
foreign key(f1) references t1(f2) on update set NULL)
engine=innodb;
insert into t1 values(1, 1);
insert into t2(f1) values(1);
drop table t2, t1;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY  (staff_id),
KEY idx_fk_store_id (store_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY  (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
LOCK TABLE staff WRITE;
UNLOCK TABLES;
DROP TABLES staff, store;
SET FOREIGN_KEY_CHECKS=1;
#
# MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a))
ENGINE=InnoDB;
connection con1;
INSERT INTO t1 SET a=1;
BEGIN;
DELETE FROM t1;
connection default;
INSERT INTO t2 SET a=1;
connection con1;
kill query @id;
connection default;
ERROR 70100: Query execution was interrupted
connection con1;
ROLLBACK;
connection default;
DROP TABLE t2,t1;
#
# MDEV-18272 InnoDB index corruption after failed DELETE CASCADE
#
CREATE TABLE t1 (
pk TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
a TINYINT UNSIGNED NOT NULL, b TINYINT UNSIGNED NOT NULL, KEY(b),
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO t1 (a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,1),(0,1),(1,0);
connection con1;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
DELETE IGNORE FROM t1 WHERE b = 1;
Warnings:
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
Warning	1296	Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
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
Warning	1296	Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
SELECT a FROM t1 FORCE INDEX(a);
a
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
SELECT * FROM t1;
pk	a	b
1	0	0
2	0	0
3	0	0
4	0	0
5	0	0
6	0	0
7	0	0
8	0	0
9	0	0
10	0	0
11	0	0
12	0	0
13	0	1
14	0	1
15	1	0
connection con1;
COMMIT;
connection default;
InnoDB		0 transactions not purged
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
#
# MDEV-17187 table doesn't exist in engine after ALTER other tables
# with CONSTRAINTs
#
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.");
set foreign_key_checks=on;
create table t1 (id int not null primary key) engine=innodb;
create table t2 (id int not null primary key, fid int not null,
CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb;
insert into t1 values (1), (2), (3);
insert into t2 values (1, 1), (2, 1), (3, 2);
set foreign_key_checks=off;
alter table t2 drop index fk_fid;
set foreign_key_checks=on;
delete from t1 where id=2;
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`))
insert into t2 values(4, 99);
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`))
select * from t1;
id
1
2
3
select * from t2;
id	fid
1	1
2	1
3	2
set foreign_key_checks=off;
delete from t1 where id=2;
insert into t2 values(4, 99);
set foreign_key_checks=on;
select * from t1;
id
1
3
select * from t2;
id	fid
1	1
2	1
3	2
4	99
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1,t2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
drop table t1,t2;
ERROR 42S02: Unknown table 'test.t2'
#
# MDEV-22934 Table disappear after two alter table command
#
CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
f2 INT NOT NULL,
PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB;
CREATE TABLE t2(f1 INT NOT NULL,
f2 INT NOT NULL, f3 INT NOT NULL,
PRIMARY KEY(f1, f2), UNIQUE KEY(f2),
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE,
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE;
ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  `f3` int(11) NOT NULL,
  PRIMARY KEY (`f3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB;
ERROR 42S01: Table 't2' already exists
DROP TABLE t2, t1;
#
# MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt
# to create unique key on virtual column
#
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b INT AS (a)) ENGINE=InnODB;
INSERT INTO t1 (pk,a) VALUES (1,10),(2,10);
ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED;
ERROR 23000: Duplicate entry '10' for key 'ind9'
SET FOREIGN_KEY_CHECKS= 0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk);
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS= 1;
#
# MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query
#
Parsing foreign keys 1...
ERROR HY000: Can't create table `test`.`t0` (errno: 150 "Foreign key constraint is incorrectly formed")
Parsing foreign keys 2...
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Parsing foreign keys 3...
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Parsing foreign keys 4...
#
# MDEV-27583 InnoDB uses different constants for FK cascade
#            error message in SQL vs error log
#
CREATE TABLE t1
(a INT, b INT, KEY(b),
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE)
ENGINE=InnoDB;
INSERT INTO t1 (a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0);
DELETE FROM t1 WHERE b = 1;
ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
SHOW WARNINGS;
Level	Code	Message
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
Error	1296	Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
DROP TABLE t1;
FOUND 1 /InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*/ in mysqld.1.err
# End of 10.2 tests
#
# MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN
#
SET NAMES utf8;
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE DATABASE `db`;
CREATE TABLE `db`.u (
a INT PRIMARY KEY,
CONSTRAINT `††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††`
  FOREIGN KEY (a) REFERENCES test.t (a)) ENGINE=InnoDB;
DROP TABLE `db`.u;
DROP DATABASE `db`;
DROP TABLE t;
# End of 10.3 tests
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)),
FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB;
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
#
# MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col
#
CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c),
FOREIGN KEY fx (b) REFERENCES t1 (c))
ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,0,10,10);
ALTER TABLE t1 DROP a, ALGORITHM=INSTANT;
SET FOREIGN_KEY_CHECKS= 0;
DROP INDEX fx ON t1;
INSERT INTO t1 VALUES (2,11,11);
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=DEFAULT;
#
# MDEV-32018 Allow the setting of Auto_increment on FK referenced columns
#
CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY KEY
);
CREATE TABLE t2 (
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
t1_id int unsigned DEFAULT NULL,
CONSTRAINT FK_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (id)
);
ALTER TABLE t1 MODIFY id INT unsigned AUTO_INCREMENT;
DROP TABLE t1,t2;
#
# MDEV-31441 BLOB corruption on UPDATE of PRIMARY KEY with FOREIGN KEY
#
CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT) ENGINE=InnoDB;
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1(pk))
ENGINE=InnoDB;
SET @blob = REPEAT('A', @@innodb_page_size / 2);
INSERT INTO t1 SET pk=1, t=@blob;
INSERT INTO t2 SET pk=1;
connection con1;
BEGIN;
DELETE FROM t2;
connection default;
UPDATE t1 SET pk=12;
connection con1;
COMMIT;
disconnect con1;
connection default;
UPDATE t1 SET pk=1;
SELECT pk,t=@blob FROM t1;
pk	t=@blob
1	1
DROP TABLE t2, t1;
# End of 10.4 tests
#
# MDEV-20729 Fix REFERENCES constraint in column definition
#
set default_storage_engine= innodb;
create table t1 (x int primary key, y int unique);
create table t2 (x int references t1(x), y int constraint fk references t1(y));
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL,
  KEY `x` (`x`),
  KEY `fk` (`y`),
  CONSTRAINT `fk` FOREIGN KEY (`y`) REFERENCES `t1` (`y`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
create table t3 (z int);
alter table t3 add x int references t1(x), add y int constraint fk2 references t1(y);
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `z` int(11) DEFAULT NULL,
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL,
  KEY `x` (`x`),
  KEY `fk2` (`y`),
  CONSTRAINT `fk2` FOREIGN KEY (`y`) REFERENCES `t1` (`y`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop tables t3, t2, t1;
create table t1 (id int primary key);
create table t2 (id2 int references t1);
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
create table t2 (id int references t1);
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop tables t2, t1;
set default_storage_engine= default;
#
# MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func
#
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
SET FOREIGN_KEY_CHECKS=DEFAULT;
DROP TABLE t1;
#
# MDEV-22602 Disable UPDATE CASCADE for SQL constraints
#
# TODO: enable them after MDEV-16417 is finished
create or replace table t1 (a int primary key) engine=innodb;
create or replace table t2 (a int, constraint foo check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `foo`
create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;
ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `CONSTRAINT_1`
create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb;
create or replace table t2 (
a int, s date, e date,
period for p (s, e),
primary key (a, p without overlaps),
foreign key (a, e, s) references t1 (f1, f3, f2) on delete cascade on update cascade) engine=innodb;
ERROR HY000: Key `PRIMARY` cannot have WITHOUT OVERLAPS
create or replace table t1 (a varchar(4096) unique) engine=innodb;
create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
drop table t1;
#
# MDEV-26824 Can't add foreign key with empty referenced columns list
#
create table t2(a int primary key) engine=innodb;
create table t1(a int primary key, b int) engine=innodb;
alter table t2 add foreign key(a) references t1(a, b);
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
create or replace table t1(a tinyint primary key) engine innodb;
alter table t2 add foreign key(a) references t1;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
create or replace table t1(b int primary key) engine innodb;
alter table t2 add foreign key(a) references t1;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
create or replace table t1(a int primary key, b int) engine innodb;
alter table t2 add foreign key(a) references t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop tables t2, t1;
#
# MDEV-29182 Assertion fld->field_no < table->n_v_def failed on cascade
#
CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(3), c INT AS (LENGTH(b)) VIRTUAL,
INDEX(c)) ENGINE=InnoDB;
CREATE TABLE t2(a INT REFERENCES t1(a) ON UPDATE CASCADE,
b INT GENERATED ALWAYS AS(a) VIRTUAL, INDEX(b)) ENGINE=InnoDB;
INSERT INTO t1 SET a=1,b='fu';
INSERT INTO t2 SET a=1;
UPDATE t1 SET a=2,b='bar';
SELECT * FROM t1;
a	b	c
2	bar	3
SELECT * FROM t2;
a	b
2	2
DROP TABLE t2,t1;
# End of 10.5 tests
#
# MDEV-26554 Table-rebuilding DDL on parent table causes crash
#            for INSERT into child table
#
CREATE TABLE parent(a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE child(a INT PRIMARY KEY REFERENCES parent(a)) ENGINE=InnoDB;
connect  con1, localhost, root,,;
BEGIN;
INSERT INTO child SET a=1;
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`))
connection default;
TRUNCATE TABLE parent;
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`))
DROP TABLE parent;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
SET innodb_lock_wait_timeout=0;
RENAME TABLE parent TO transparent;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE parent FORCE, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET innodb_lock_wait_timeout=0, foreign_key_checks=0;
TRUNCATE TABLE parent;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DROP TABLE parent;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE parent FORCE, ALGORITHM=COPY;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection con1;
COMMIT;
connection default;
SET innodb_lock_wait_timeout=DEFAULT;
TRUNCATE TABLE parent;
ALTER TABLE parent FORCE, ALGORITHM=COPY;
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
SET foreign_key_checks=ON;
TRUNCATE TABLE parent;
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`))
ALTER TABLE parent FORCE, ALGORITHM=COPY;
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
RENAME TABLE parent TO transparent;
DROP TABLE child, transparent;
#
# MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove
# or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table
#
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t1(pk))
ENGINE=InnoDB;
connection con1;
SET FOREIGN_KEY_CHECKS=OFF;
CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB;
connection default;
INSERT INTO t2 VALUES (1);
connection con1;
connection default;
DROP TABLE IF EXISTS t2, t1;
#
# MDEV-30531 Corrupt index(es) on busy table when using FOREIGN KEY
#
CREATE TABLE collections (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
collectionhash varchar(255) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
UNIQUE KEY ix_collection_collectionhash (collectionhash)
) ENGINE=InnoDB;
CREATE TABLE binaries (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
collections_id int(11) unsigned NOT NULL DEFAULT 0,
binaryhash binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
PRIMARY KEY (id),
UNIQUE KEY ix_binary_binaryhash (binaryhash),
CONSTRAINT FK_collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO collections (id) VALUES (NULL);
connection con1;
INSERT INTO binaries (id,collections_id) VALUES (NULL,1);
REPLACE INTO collections (id) VALUES (NULL);
connection default;
REPLACE INTO binaries (id) VALUES (NULL);
SET GLOBAL innodb_max_purge_lag_wait=0;
CHECK TABLE binaries, collections EXTENDED;
Table	Op	Msg_type	Msg_text
test.binaries	check	status	OK
test.collections	check	status	OK
disconnect con1;
DROP TABLE binaries, collections;
CREATE SCHEMA `#mysql50##mysql50#d-b`;
CREATE TABLE `#mysql50##mysql50#d-b`.t1 (a INT PRIMARY KEY, b INT UNIQUE) engine=InnoDB;
USE `#mysql50##mysql50#d-b`;
CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE REFERENCES t1(b)) ENGINE=InnoDB;
SET STATEMENT foreign_key_checks=0 FOR
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`),
  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 SET a=1;
INSERT INTO t2 SET a=1;
DELETE FROM t1;
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`))
DELETE FROM t2;
DELETE FROM t1;
DROP DATABASE `#mysql50##mysql50#d-b`;
USE test;
#
# MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint
#
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
SET STATEMENT foreign_key_checks=0 FOR
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
SET STATEMENT foreign_key_checks=0 FOR
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
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`))
ALTER TABLE t1 ADD KEY(a), ALGORITHM=NOCOPY;
INSERT INTO t2 VALUES (1);
DROP INDEX b ON t2;
ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
SET STATEMENT foreign_key_checks=0 FOR
DROP INDEX b ON t2;
DELETE FROM t2;
DELETE FROM t1;
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`))
ALTER TABLE t2 ADD KEY(b), ALGORITHM=NOCOPY;
DELETE FROM t1;
DROP TABLE t2, t1;
#
# MDEV-33167 ASAN errors after failing to load foreign key
#   relation for the table
#
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.");
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
CREATE TABLE t1(f1 VARCHAR(8),
FOREIGN KEY(f1) REFERENCES test.t3(f1))ENGINE=InnoDB;
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
CREATE TABLE t2(f1 VARCHAR(8),
FOREIGN KEY(f1) REFERENCES test.t3(f1))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
SET STATEMENT FOREIGN_KEY_CHECKS = 0 FOR
CREATE TABLE t3(f1 VARCHAR(8) PRIMARY KEY)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
set GLOBAL innodb_fast_shutdown=0;
# restart
ALTER TABLE t2 FORCE;
DROP TABLE t2, t1, t3;
# End of 10.6 tests
CREATE TABLE t1
(
f1 VARCHAR(32)BINARY NOT NULL,
f2 VARCHAR(32)BINARY NOT NULL,
PRIMARY KEY (f1),
INDEX(f2)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES('MySQL', 'InnoDB'), ('MariaDB', 'NDB');
CREATE TABLE t2
(
f1 VARCHAR(32)BINARY NOT NULL,
f2 VARCHAR(255)BINARY NOT NULL,
f3 int, PRIMARY KEY (f1), INDEX(f1), INDEX(f2)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES('MySQL', 'MySQL', 1),
('MariaDB', 'MariaDB', 1);
ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t2 (f2);
ALTER TABLE t2 ADD FOREIGN KEY (f2) REFERENCES t2 (f2),
ADD UNIQUE INDEX(f3);
ERROR HY000: Cannot delete rows from table which is parent in a foreign key constraint 't1_ibfk_1' of table 't1'
drop table t1, t2;
# End of 10.11 tests