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

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

--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
--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';

--source include/restart_mysqld.inc

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;
SET FOREIGN_KEY_CHECKS=DEFAULT;

LOCK TABLE staff WRITE;
UNLOCK TABLES;
DROP TABLES staff, store;
SET FOREIGN_KEY_CHECKS=1;

--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,,)
SET innodb_lock_wait_timeout= 1;
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= 1;
--error ER_LOCK_WAIT_TIMEOUT
DELETE FROM t1 WHERE id = 1;

--connection con1
COMMIT;
--disconnect con1

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

--source include/wait_until_count_sessions.inc