#
# Bug #18806829 OPENING INNODB TABLES WITH MANY FOREIGN KEY
# REFERENCES IS SLOW/CRASHES SEMAPHORE
#
create table t1 (f1 int primary key) engine=innodb;
insert into t1 values (5);
insert into t1 values (2882);
insert into t1 values (10);
# restart
update t1 set f1 = 28 where f1 = 2882;
select * from fk_120;
f1
5
10
28
select * from fk_1;
f1
5
10
28
select * from fk_50;
f1
5
10
28
drop table t1;
#
# Check if restrict is working fine.
#
create table t1 (f1 int primary key) engine=innodb;
# restart
delete from t1 where f1 = 29;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fk_29`, CONSTRAINT `pc29` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
select * from fk_29;
f1
29
drop table t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
f1 int(11) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
f2 int(11) NOT NULL,
f3 int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE,
CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
) ENGINE=InnoDB;
ERROR 42S02: Table 'test.t3' doesn't exist
show warnings;
Level	Code	Message
Error	1146	Table 'test.t3' doesn't exist
CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
f2 int(11) NOT NULL,
f3 int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE;
ERROR 42S02: Table 'test.t3' doesn't exist
show warnings;
Level	Code	Message
Error	1146	Table 'test.t3' doesn't exist
drop table t2;
drop table t1;
CREATE DATABASE kg_test1;
CREATE DATABASE kg_test2;
CREATE TABLE `kg_test1`.`group` (
Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `kg_test1`.`person` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`Id`),
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create table `kg_test1`.`person`;
Table	Create Table
person	CREATE TABLE `person` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`),
  CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `kg_test2`.`person2` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`Id`),
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
ERROR 42S02: Table 'kg_test2.group' doesn't exist
CREATE TABLE `kg_test2`.`person2` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`Id`),
CONSTRAINT `fk_person_group2` FOREIGN KEY (`Id`) REFERENCES  `kg_test1`.`group` (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create table `kg_test2`.`person2`;
Table	Create Table
person2	CREATE TABLE `person2` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`),
  CONSTRAINT `fk_person_group2` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SHOW WARNINGS;
Level	Code	Message
DROP DATABASE kg_test2;
DROP DATABASE kg_test1;
CREATE TABLE `#departaments` (
`id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id_depart`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `#departaments_tree` (
`id_depart` INT(10) UNSIGNED NOT NULL,
`id_depart_in` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id_depart`,`id_depart_in`),
CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
ALTER TABLE `#departaments_tree`
  ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`);
SHOW CREATE TABLE `#departaments_tree`;
Table	Create Table
#departaments_tree	CREATE TABLE `#departaments_tree` (
  `id_depart` int(10) unsigned NOT NULL,
  `id_depart_in` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_depart`,`id_depart_in`),
  KEY `fk_#departaments_tree` (`id_depart_in`),
  CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`),
  CONSTRAINT `fk_#departaments_tree` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments` (`id_depart`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP TABLE `#departaments_tree`;
DROP TABLE `#departaments`;
CREATE TABLE `boroda` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) UNSIGNED DEFAULT NULL,
`b` INT(11) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
ALTER TABLE `boroda`
  ADD FOREIGN KEY `boroda_ibfk_2` (`b`) REFERENCES `boroda`(`id`);
ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`;
RENAME TABLE `boroda` TO `#boroda`;
ALTER TABLE `#boroda`
ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
SHOW CREATE TABLE `#boroda`;
Table	Create Table
#boroda	CREATE TABLE `#boroda` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  `b` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `fk_#boroda` (`b`),
  CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `#boroda` (`id`),
  CONSTRAINT `fk_#boroda` FOREIGN KEY (`b`) REFERENCES `#boroda` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP TABLE `#boroda`;
CREATE TABLE `boroda` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) UNSIGNED DEFAULT NULL,
`b` INT(11) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
RENAME TABLE `boroda` TO `bor#oda`;
ALTER TABLE `bor#oda`
ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`);
SHOW CREATE TABLE `bor#oda`;
Table	Create Table
bor#oda	CREATE TABLE `bor#oda` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  `b` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `fk_bor#oda` (`b`),
  CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `bor#oda` (`id`),
  CONSTRAINT `fk_bor#oda` FOREIGN KEY (`b`) REFERENCES `bor#oda` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP TABLE `bor#oda`;
#
# MDEV-21127 Assertion `(size_t)(ptr - buf) < MAX_TEXT - 4' failed in key_text::key_text
#
create table tx (ax char(255) unique, bx char(255) unique) engine innodb;
CREATE TABLE t1 (
a012345678901234567890123456789012345678901 char(255),
b char(255),
FOREIGN KEY ( a012345678901234567890123456789012345678901, b ) REFERENCES tx (ax, bx)
) ENGINE=InnoDB;
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
CREATE TABLE t1 (
a012345678901234567 int,
b int,
c0123456789012345678 int,
FOREIGN KEY (a012345678901234567,c0123456789012345678,b) REFERENCES tx (x1,x2,x3)
) ENGINE=InnoDB;
ERROR 42000: Incorrect foreign key definition for 'x1': referenced field not found
drop table tx;