--source include/have_innodb.inc
--source include/count_sessions.inc
--source include/default_charset.inc

--disable_query_log
call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
call mtr.add_suppression("Invalid \\(old\\?\\) table or database name '#mysql50#d-b'");
--enable_query_log

SET GLOBAL innodb_stats_persistent = 0;

--echo #
--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
--echo # DICT_CREATE_FOREIGN_CONSTR
--echo #

create table t1 (f1 int primary key) engine=InnoDB;
--error ER_CANT_CREATE_TABLE
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;
create table t2 (f1 int primary key,
   constraint c1 foreign key (f1) references t1(f1)) engine=innodb;

--error ER_CANT_CREATE_TABLE
alter table t2 add constraint c1 foreign key (f1) references t1(f1);

set foreign_key_checks = 0;
--error ER_DUP_CONSTRAINT_NAME
alter table t2 add constraint c1 foreign key (f1) references t1(f1);

drop table t2, t1;

--echo #
--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
--echo # NULL/NOT NULL MISMATCH
--echo #

set foreign_key_checks = 1;
show variables like 'foreign_key_checks';

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;
show create table t2;

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;
SELECT a, b FROM t2 ORDER BY a;

--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES (56, 6);

ALTER TABLE t1 CHANGE a id INT;

SELECT id, b FROM t1 ORDER BY id;
SELECT a, b FROM t2 ORDER BY a;

--echo # Operations on child table
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES (56, 6);
--error ER_NO_REFERENCED_ROW_2
UPDATE t2 SET b = 99 WHERE a = 51;
DELETE FROM t2 WHERE a = 53;
SELECT id, b FROM t1 ORDER BY id;
SELECT a, b FROM t2 ORDER BY a;

--echo # 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;
SELECT a, b FROM t2 ORDER BY a;

DROP TABLE t2, t1;

--echo #
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
--echo # base bug#24818604 [GR]
--echo #

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';

--echo #
--echo # MDEV-28317 Assertion failure on rollback of FOREIGN KEY operation
--echo #

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;

--let $shutdown_timeout=0
--source include/restart_mysqld.inc
--let $shutdown_timeout=
disconnect incomplete;

SET GLOBAL innodb_stats_persistent = 0;

INSERT INTO child SET a=0;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO child SET a=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM parent;
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';

SELECT * FROM t1;

SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';

DROP TABLE t2;
DROP TABLE t1;

#
# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE
#
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;

--echo #
--echo # MDEV-17531 Crash in RENAME TABLE with FOREIGN KEY and FULLTEXT INDEX
--echo #

--disable_query_log
call mtr.add_suppression("InnoDB: Table rename might cause two FOREIGN KEY");
call mtr.add_suppression("InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\.");
--enable_query_log

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;
--replace_regex /Table '.*t2'/Table 't2'/
--error ER_TABLE_EXISTS_ERROR
RENAME TABLE best.t2 TO test.t2;
SHOW CREATE TABLE best.t2;
DROP DATABASE best;

--echo #
--echo # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
--echo #
connect (con1, localhost, root,,);
INSERT INTO t1 SET a=1;
BEGIN;
DELETE FROM t1;

connection default;
let $ID= `SELECT @id := CONNECTION_ID()`;
send INSERT INTO t3 SET a=1;

connection con1;
# Check that the above SELECT is blocked
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = 'update' and info = 'INSERT INTO t3 SET a=1';
--source include/wait_condition.inc
let $ignore= `SELECT @id := $ID`;
kill query @id;

connection default;
--error ER_QUERY_INTERRUPTED
reap;

connection con1;
ROLLBACK;
connection default;
disconnect con1;

DROP TABLE t3,t1;

--echo #
--echo # MDEV-18222 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
--echo # or ASAN heap-use-after-free in dict_foreign_remove_from_cache upon CHANGE COLUMN
--echo #
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;
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
ALTER TABLE t1 CHANGE COLUMN a a TIME NOT NULL;
ALTER TABLE t1 ADD pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 CHANGE COLUMN a b TIME;
ALTER TABLE t1 CHANGE COLUMN a b TIME, DROP FOREIGN KEY t1_ibfk_1;
SET SESSION FOREIGN_KEY_CHECKS = ON;
DROP TABLE t1;

--echo #
--echo # MDEV-18256 InnoDB: Failing assertion: heap->magic_n == MEM_BLOCK_MAGIC_N
--echo # upon DROP FOREIGN KEY
--echo #
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);
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);
ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
DROP TABLE t1;

--echo #
--echo # MDEV-18630 Conditional jump or move depends on uninitialised value
--echo # in ib_push_warning / dict_create_foreign_constraints_low
--echo #
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
--error ER_CANT_CREATE_TABLE
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
SHOW WARNINGS;
DROP TABLE t1;

--echo #
--echo # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
--echo #
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;

# MDEV-19092 Server crash when renaming the column when
# FOREIGN_KEY_CHECKS is disabled
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;
# Cleanup
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;
# Cleanup
DROP TABLE t1;

SET FOREIGN_KEY_CHECKS=1;

--echo #
--echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
--echo # ADD FOREIGN KEY
--echo #

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;

#
# FK and prelocking:
# child table accesses (reads and writes) wait for locks.
#
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; # this takes MDL_SHARED_NO_WRITE
connect (con1,localhost,root);
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a=2;
send update t1 set a=10 where a=1;
connection default;
let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
source include/wait_condition.inc;
unlock tables;
connection con1;
reap;
connection default;
lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE
connection con1;
send delete from t1 where a=2;
connection default;
let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
source include/wait_condition.inc;
unlock tables;
connection con1;
--error ER_ROW_IS_REFERENCED_2
reap;
connection default;
unlock tables;
disconnect con1;

# but privileges should not be checked
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;
drop table t2, t1;
drop user foo;

--echo #
--echo # MDEV-17595 - Server crashes in copy_data_between_tables or
--echo #              Assertion `thd->transaction.stmt.is_empty() ||
--echo #              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
--echo #              fails in close_tables_for_reopen upon concurrent
--echo #              ALTER TABLE and FLUSH
--echo #
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;

#
# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
#

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);
error ER_NO_REFERENCED_ROW_2;
insert t2 values (10);
flush tables;
flush status;
# with ON UPDATE RESTRICT child tables are not opened
update t1 set data=10 where pk+1>10;
show status like '%opened_tab%';
flush tables;
flush status;
# neither are parent tables
update t2 set t1_pk=11 where t1_pk+1>10;
show status like '%opened_tab%';
# under LOCK TABLES
flush tables;
flush status;
lock tables t1 write;
show status like '%opened_tab%';
insert t1 values (4,4);
show status like '%opened_tab%';
unlock tables;
delimiter |;
create function foo() returns int
begin
  insert t1 values (5,5);
  return 5;
end|
delimiter ;|
flush tables;
flush status;
--disable_ps2_protocol
select foo();
--enable_ps2_protocol
show status like '%opened_tab%';
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);
--error ER_XAER_RMFAIL
CREATE TABLE x AS SELECT * FROM t1;
--connect (con1,localhost,root,,test)
SET foreign_key_checks= OFF, innodb_lock_wait_timeout= 0;
SET lock_wait_timeout=2;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;# Cleanup
--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;
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;
ROLLBACK;

ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
--error ER_ROW_IS_REFERENCED_2
UPDATE t1 SET f6='update';

ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
--error ER_ROW_IS_REFERENCED_2
UPDATE t1 SET f6='cascade';
DROP TABLE t1;
#
# End of 10.1 tests
#

--echo # Start of 10.2 tests

--echo #
--echo # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
--echo #

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;
--sorted_result
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM users;

DROP TABLE
matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;

--echo #
--echo # MDEV-13331 FK DELETE CASCADE does not honor innodb_lock_wait_timeout
--echo #

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;

--connection con1
BEGIN;
UPDATE t2 SET f = 11 WHERE id = 1;

--connection default
SET innodb_lock_wait_timeout= 0;
--error ER_LOCK_WAIT_TIMEOUT
DELETE FROM t1 WHERE id = 1;
SET innodb_lock_wait_timeout= 1;

--connection con1
COMMIT;

--connection default
SELECT * FROM t2;
DELETE FROM t1 WHERE id = 1;
SELECT * FROM t2;
DROP TABLE t2, t1;

--echo #
--echo # MDEV-15199 Referential integrity broken in ON DELETE CASCADE
--echo #

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;
SELECT * FROM address;
SELECT * FROM payment_method;
DELETE FROM member;
COMMIT;
SELECT * FROM member;
SELECT * FROM address;
SELECT * FROM payment_method;

DROP TABLE payment_method,address,member;

--echo #
--echo # Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY
--echo # PRODUCE BROKEN TABLE (no bug in MariaDB)
--echo #
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;

#
# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE
#
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;

--echo #
--echo # MDEV-17541 KILL QUERY during lock wait in FOREIGN KEY check hangs
--echo #

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;
let $ID= `SELECT @id := CONNECTION_ID()`;
send INSERT INTO t2 SET a=1;

connection con1;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = 'update' and info = 'INSERT INTO t2 SET a=1';
--source include/wait_condition.inc
let $ignore= `SELECT @id := $ID`;
kill query @id;

connection default;
--error ER_QUERY_INTERRUPTED
reap;

connection con1;
ROLLBACK;
connection default;

DROP TABLE t2,t1;

--echo #
--echo # MDEV-18272 InnoDB index corruption after failed DELETE CASCADE
--echo #
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;

SELECT a FROM t1 FORCE INDEX(a);
# This would wrongly return the empty result if
# the "goto rollback_to_savept" in row_mysql_handle_errors() is reverted.
SELECT * FROM t1;
# Allow purge to continue by closing the read view.
connection con1;
COMMIT;
connection default;

# Wait for purge. With the fix reverted, the server would crash here.
--source include/wait_all_purged.inc
CHECK TABLE t1;
DROP TABLE t1;

--echo #
--echo # MDEV-17187 table doesn't exist in engine after ALTER other tables
--echo # with CONSTRAINTs
--echo #

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;

--error ER_ROW_IS_REFERENCED_2
delete from t1 where id=2;
--error ER_NO_REFERENCED_ROW_2
insert into t2 values(4, 99);

select * from t1;
select * from t2;

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;
select * from t2;

show create table t1;
show create table t2;

# Optional: test DROP TABLE without any prior ha_innobase::open().
# This was tested manually, but it would cause --embedded to skip the test,
# and the restart would significantly increase the running time.
# --source include/restart_mysqld.inc

--error ER_ROW_IS_REFERENCED_2
drop table t1,t2;
--error ER_BAD_TABLE_ERROR
drop table t1,t2;

--echo #
--echo # MDEV-22934 Table disappear after two alter table command
--echo #
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;
--error ER_TABLE_EXISTS_ERROR
CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB;
DROP TABLE t2, t1;

--echo #
--echo # MDEV-23685 SIGSEGV on ADD FOREIGN KEY after failed attempt
--echo # to create unique key on virtual column
--echo #
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);
--error ER_DUP_ENTRY
ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED;
SET FOREIGN_KEY_CHECKS= 0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk);
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS= 1;

--echo #
--echo # MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query
--echo #
let $constr_prefix= aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
let $fk_ref=        xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
let $fk_field=      yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy;
let $constr_count= 200; # each 100 constrs is 1 sec of test execution
let $i= 0;

while ($i < $constr_count)
{
  let $p= $constr_prefix$i;
  let $constr= CONSTRAINT $p FOREIGN KEY ($fk_field) REFERENCES t1($fk_ref) ON UPDATE SET NULL;
  if ($i)
  {
    let $constrs= $constrs, $constr;
  }
  if (!$i)
  {
    let $constrs= $constr;
  }
  inc $i;
}
--disable_query_log
--echo Parsing foreign keys 1...
--error ER_CANT_CREATE_TABLE
eval create table t0($fk_field int, $constrs) engine innodb;
--echo Parsing foreign keys 2...
--error ER_CANT_CREATE_TABLE
eval create table t1($fk_field int, $constrs) engine innodb;
--echo Parsing foreign keys 3...
--error ER_CANT_CREATE_TABLE
eval create table t1($fk_ref int, $fk_field int, $constrs) engine innodb;
--echo Parsing foreign keys 4...
eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine innodb;
drop table t1;
--enable_query_log

--echo #
--echo # MDEV-27583 InnoDB uses different constants for FK cascade
--echo #            error message in SQL vs error log
--echo #

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);

--error ER_GET_ERRMSG
DELETE FROM t1 WHERE b = 1;
SHOW WARNINGS;
DROP TABLE t1;

let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let SEARCH_PATTERN= InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*;
-- source include/search_pattern_in_file.inc

--echo # End of 10.2 tests

--echo #
--echo # MDEV-28980 InnoDB: Failing assertion: len <= MAX_TABLE_NAME_LEN
--echo #

SET NAMES utf8;
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;
# The maximum identifier length is 64 characters.
# my_charset_filename will expand some characters to 5 characters,
# e.g., # to @0023.
# Many operating systems (such as Linux) or file systems
# limit the path component length to 255 bytes, such as 51*5 characters.
# The bug was repeated with a shorter length, which we will use here,
# to avoid exceeding MAX_PATH on Microsoft Windows.
let $db=##########################;
--replace_result $db db
eval CREATE DATABASE `$db`;
--replace_result $db db
eval CREATE TABLE `$db`.u (
  a INT PRIMARY KEY,
  CONSTRAINT `††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††`
  FOREIGN KEY (a) REFERENCES test.t (a)) ENGINE=InnoDB;
--replace_result $db db
eval DROP TABLE `$db`.u;
--replace_result $db db
eval DROP DATABASE `$db`;
DROP TABLE t;

--echo # End of 10.3 tests

# MDEV-21792 Server aborts upon attempt to create foreign key on spatial field
# Fail to create foreign key for spatial fields
--error ER_CANT_CREATE_TABLE
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)),
		 FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB;

--echo #
--echo # MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col
--echo #
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;

--echo #
--echo # MDEV-32018 Allow the setting of Auto_increment on FK referenced columns
--echo #

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;

--echo #
--echo # MDEV-31441 BLOB corruption on UPDATE of PRIMARY KEY with FOREIGN KEY
--echo #

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
# The following will be blocked by a FOREIGN KEY check on pk=1 in t2.
--send
UPDATE t1 SET pk=12;
--connection con1
let $wait_condition=
SELECT count(*) > 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state='Updating';
--source include/wait_condition.inc
COMMIT;
--disconnect con1
--connection default
--reap
UPDATE t1 SET pk=1;
SELECT pk,t=@blob FROM t1;
DROP TABLE t2, t1;

--echo # End of 10.4 tests

--echo #
--echo # MDEV-20729 Fix REFERENCES constraint in column definition
--echo #
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;
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;
drop tables t3, t2, t1;

create table t1 (id int primary key);
--error ER_CANT_CREATE_TABLE
create table t2 (id2 int references t1);
create table t2 (id int references t1);
show create table t2;
drop tables t2, t1;

set default_storage_engine= default;

--echo #
--echo # MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func
--echo #
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB;
--error ER_CANT_CREATE_TABLE
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b);
SET FOREIGN_KEY_CHECKS=DEFAULT;

# Cleanup
DROP TABLE t1;

--echo #
--echo # MDEV-22602 Disable UPDATE CASCADE for SQL constraints
--echo #
--echo # TODO: enable them after MDEV-16417 is finished
create or replace table t1 (a int primary key) engine=innodb;
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
create or replace table t2 (a int, constraint foo check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb;

create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb;
--error ER_KEY_CANT_HAVE_WITHOUT_OVERLAPS
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;

# FK on long unique is already disabled
create or replace table t1 (a varchar(4096) unique) engine=innodb;
--error ER_CANT_CREATE_TABLE
create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb;

drop table t1;

--echo #
--echo # MDEV-26824 Can't add foreign key with empty referenced columns list
--echo #
create table t2(a int primary key) engine=innodb;
create table t1(a int primary key, b int) engine=innodb;
--error ER_WRONG_FK_DEF
alter table t2 add foreign key(a) references t1(a, b);
create or replace table t1(a tinyint primary key) engine innodb;
--error ER_CANT_CREATE_TABLE
alter table t2 add foreign key(a) references t1;
create or replace table t1(b int primary key) engine innodb;
--error ER_CANT_CREATE_TABLE
alter table t2 add foreign key(a) references t1;
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;
drop tables t2, t1;


--echo #
--echo # MDEV-29182 Assertion fld->field_no < table->n_v_def failed on cascade
--echo #
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;
SELECT * FROM t2;
DROP TABLE t2,t1;

--echo # End of 10.5 tests

--echo #
--echo # MDEV-26554 Table-rebuilding DDL on parent table causes crash
--echo #            for INSERT into child table
--echo #

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;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO child SET a=1;
connection default;
--error ER_TRUNCATE_ILLEGAL_FK
TRUNCATE TABLE parent;
--error ER_ROW_IS_REFERENCED_2
DROP TABLE parent;
SET innodb_lock_wait_timeout=0;
--error ER_LOCK_WAIT_TIMEOUT
RENAME TABLE parent TO transparent;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent FORCE, ALGORITHM=COPY;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
SET innodb_lock_wait_timeout=0, foreign_key_checks=0;
--error ER_LOCK_WAIT_TIMEOUT
TRUNCATE TABLE parent;
--error ER_LOCK_WAIT_TIMEOUT
DROP TABLE parent;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent FORCE, ALGORITHM=COPY;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE parent ADD COLUMN b INT, ALGORITHM=INSTANT;
connection con1;
COMMIT;
connection default;
# Restore the timeout to avoid occasional races with purge.
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;
--error ER_TRUNCATE_ILLEGAL_FK
TRUNCATE TABLE parent;
ALTER TABLE parent FORCE, ALGORITHM=COPY;
ALTER TABLE parent FORCE, ALGORITHM=INPLACE;
RENAME TABLE parent TO transparent;
DROP TABLE child, transparent;

--echo #
--echo # MDEV-26217 Failing assertion: list.count > 0 in ut_list_remove
--echo # or Assertion `lock->trx == this' failed in dberr_t trx_t::drop_table
--echo #

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;
--send
CREATE OR REPLACE TABLE t1 (b INT) ENGINE=InnoDB;

--connection default
--error 0,ER_NO_REFERENCED_ROW_2,ER_LOCK_DEADLOCK
INSERT INTO t2 VALUES (1);

--connection con1
--error 0,ER_CANT_CREATE_TABLE
--reap

# Cleanup
--connection default
--disable_warnings
DROP TABLE IF EXISTS t2, t1;
--enable_warnings

--echo #
--echo # MDEV-30531 Corrupt index(es) on busy table when using FOREIGN KEY
--echo #

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);
--send
  REPLACE INTO collections (id) VALUES (NULL);

--connection default
--error 0,ER_LOCK_DEADLOCK,ER_NO_REFERENCED_ROW_2
REPLACE INTO binaries (id) VALUES (NULL);

SET GLOBAL innodb_max_purge_lag_wait=0;
CHECK TABLE binaries, collections EXTENDED;

--disconnect con1

# Cleanup
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;
INSERT INTO t1 SET a=1;
INSERT INTO t2 SET a=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1;
DELETE FROM t2;
DELETE FROM t1;
DROP DATABASE `#mysql50##mysql50#d-b`;
USE test;

--echo #
--echo # MDEV-35962 CREATE INDEX fails to heal a FOREIGN KEY constraint
--echo #

--error ER_CANT_CREATE_TABLE
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
SET STATEMENT foreign_key_checks=0 FOR
CREATE TABLE t2 (b INT, FOREIGN KEY (b) REFERENCES t1(a)) ENGINE=InnoDB;
--error ER_CANT_CREATE_TABLE
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
SET STATEMENT foreign_key_checks=0 FOR
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES (1);
ALTER TABLE t1 ADD KEY(a), ALGORITHM=NOCOPY;
INSERT INTO t2 VALUES (1);
--error ER_DROP_INDEX_FK
DROP INDEX b ON t2;
SET STATEMENT foreign_key_checks=0 FOR
DROP INDEX b ON t2;
DELETE FROM t2;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1;
ALTER TABLE t2 ADD KEY(b), ALGORITHM=NOCOPY;
DELETE FROM t1;
DROP TABLE t2, t1;

--echo #
--echo # MDEV-33167 ASAN errors after failing to load foreign key
--echo #   relation for the table
--echo #
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;
--let $shutdown_timeout=
--source include/restart_mysqld.inc
# Error encountered while loading the foreign key
# constraint for t3. t1 wasn't loaded into memory yet
# t2 failed to find index for foreign key relation
ALTER TABLE t2 FORCE;
DROP TABLE t2, t1, t3;

--echo # 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);
# MDEV-33927 TODO: change the warning message
--error ER_FK_CANNOT_DELETE_PARENT
ALTER TABLE t2 ADD FOREIGN KEY (f2) REFERENCES t2 (f2),
ADD UNIQUE INDEX(f3);
drop table t1, t2;

--echo # End of 10.11 tests