Debian Codegraph @

mysql-test/suite/innodb/r/innodb-fk.result

raw
1 #
2 # Bug #18806829 OPENING INNODB TABLES WITH MANY FOREIGN KEY
3 # REFERENCES IS SLOW/CRASHES SEMAPHORE
4 #
5 create table t1 (f1 int primary key) engine=innodb;
6 insert into t1 values (5);
7 insert into t1 values (2882);
8 insert into t1 values (10);
9 # restart
10 update t1 set f1 = 28 where f1 = 2882;
11 select * from fk_120;
12 f1
13 5
14 10
15 28
16 select * from fk_1;
17 f1
18 5
19 10
20 28
21 select * from fk_50;
22 f1
23 5
24 10
25 28
26 drop table t1;
27 #
28 # Check if restrict is working fine.
29 #
30 create table t1 (f1 int primary key) engine=innodb;
31 # restart
32 delete from t1 where f1 = 29;
33 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`))
34 select * from fk_29;
35 f1
36 29
37 drop table t1;
38 CREATE TABLE t1 (
39 id int(11) NOT NULL AUTO_INCREMENT,
40 f1 int(11) DEFAULT NULL,
41 PRIMARY KEY (id),
42 CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE
43 ) ENGINE=InnoDB;
44 CREATE TABLE t2 (
45 id int(11) NOT NULL AUTO_INCREMENT,
46 f2 int(11) NOT NULL,
47 f3 int(11) NOT NULL,
48 PRIMARY KEY (`id`),
49 CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE,
50 CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
51 ) ENGINE=InnoDB;
52 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
53 show warnings;
54 Level Code Message
55 Warning 150 Create table `test`.`t2` with foreign key `fk3` constraint failed. Referenced table `test`.`t3` not found in the data dictionary.
56 Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
57 Warning 1215 Cannot add foreign key constraint for `t2`
58 CREATE TABLE t2 (
59 id int(11) NOT NULL AUTO_INCREMENT,
60 f2 int(11) NOT NULL,
61 f3 int(11) NOT NULL,
62 PRIMARY KEY (`id`),
63 CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE
64 ) ENGINE=InnoDB;
65 ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE;
66 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
67 show warnings;
68 Level Code Message
69 Warning 150 Alter table `test`.`t2` with foreign key `fk3` constraint failed. Referenced table `test`.`t3` not found in the data dictionary.
70 Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
71 Warning 1215 Cannot add foreign key constraint for `t2`
72 drop table t2;
73 drop table t1;
74 CREATE DATABASE kg_test1;
75 CREATE DATABASE kg_test2;
76 CREATE TABLE `kg_test1`.`group` (
77 Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
79 CREATE TABLE `kg_test1`.`person` (
80 `Id` INT(11) NOT NULL AUTO_INCREMENT,
81 `Name` VARCHAR(50) NOT NULL,
82 PRIMARY KEY (`Id`),
83 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
84 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
85 show create table `kg_test1`.`person`;
86 Table Create Table
87 person CREATE TABLE `person` (
88 `Id` int(11) NOT NULL AUTO_INCREMENT,
89 `Name` varchar(50) NOT NULL,
90 PRIMARY KEY (`Id`),
91 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
92 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
93 CREATE TABLE `kg_test2`.`person2` (
94 `Id` INT(11) NOT NULL AUTO_INCREMENT,
95 `Name` VARCHAR(50) NOT NULL,
96 PRIMARY KEY (`Id`),
97 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
98 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
99 ERROR HY000: Can't create table `kg_test2`.`person2` (errno: 150 "Foreign key constraint is incorrectly formed")
100 CREATE TABLE `kg_test2`.`person2` (
101 `Id` INT(11) NOT NULL AUTO_INCREMENT,
102 `Name` VARCHAR(50) NOT NULL,
103 PRIMARY KEY (`Id`),
104 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
105 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
106 show create table `kg_test2`.`person2`;
107 Table Create Table
108 person2 CREATE TABLE `person2` (
109 `Id` int(11) NOT NULL AUTO_INCREMENT,
110 `Name` varchar(50) NOT NULL,
111 PRIMARY KEY (`Id`),
112 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
114 SHOW WARNINGS;
115 Level Code Message
116 DROP DATABASE kg_test2;
117 DROP DATABASE kg_test1;
118 CREATE TABLE `#departaments` (
119 `id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
120 PRIMARY KEY (`id_depart`)
121 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
122 CREATE TABLE `#departaments_tree` (
123 `id_depart` INT(10) UNSIGNED NOT NULL,
124 `id_depart_in` INT(10) UNSIGNED NOT NULL,
125 PRIMARY KEY (`id_depart`,`id_depart_in`),
126 CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`)
127 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
128 ALTER TABLE `#departaments_tree`
129 ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`);
130 SHOW CREATE TABLE `#departaments_tree`;
131 Table Create Table
132 #departaments_tree CREATE TABLE `#departaments_tree` (
133 `id_depart` int(10) unsigned NOT NULL,
134 `id_depart_in` int(10) unsigned NOT NULL,
135 PRIMARY KEY (`id_depart`,`id_depart_in`),
136 KEY `id_depart_in` (`id_depart_in`),
137 CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`),
138 CONSTRAINT `#departaments_tree_ibfk_2` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments` (`id_depart`)
139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
140 DROP TABLE `#departaments_tree`;
141 DROP TABLE `#departaments`;
142 CREATE TABLE `boroda` (
143 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
144 `a` INT(11) UNSIGNED DEFAULT NULL,
145 `b` INT(11) UNSIGNED DEFAULT NULL,
146 PRIMARY KEY (`id`),
147 KEY `a` (`a`),
148 CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
149 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
150 ALTER TABLE `boroda`
151 ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`);
152 ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`;
153 RENAME TABLE `boroda` TO `#boroda`;
154 ALTER TABLE `#boroda`
155 ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
156 SHOW CREATE TABLE `#boroda`;
157 Table Create Table
158 #boroda CREATE TABLE `#boroda` (
159 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
160 `a` int(11) unsigned DEFAULT NULL,
161 `b` int(11) unsigned DEFAULT NULL,
162 PRIMARY KEY (`id`),
163 KEY `a` (`a`),
164 KEY `b` (`b`),
165 CONSTRAINT `#boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `#boroda` (`id`),
166 CONSTRAINT `#boroda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `#boroda` (`id`)
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
168 DROP TABLE `#boroda`;
169 CREATE TABLE `boroda` (
170 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
171 `a` INT(11) UNSIGNED DEFAULT NULL,
172 `b` INT(11) UNSIGNED DEFAULT NULL,
173 PRIMARY KEY (`id`),
174 KEY `a` (`a`),
175 CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
176 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
177 RENAME TABLE `boroda` TO `bor#oda`;
178 ALTER TABLE `bor#oda`
179 ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`);
180 SHOW CREATE TABLE `bor#oda`;
181 Table Create Table
182 bor#oda CREATE TABLE `bor#oda` (
183 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
184 `a` int(11) unsigned DEFAULT NULL,
185 `b` int(11) unsigned DEFAULT NULL,
186 PRIMARY KEY (`id`),
187 KEY `a` (`a`),
188 KEY `b` (`b`),
189 CONSTRAINT `bor#oda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `bor#oda` (`id`),
190 CONSTRAINT `bor#oda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `bor#oda` (`id`)
191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
192 DROP TABLE `bor#oda`;
193 #
194 # MDEV-21127 Assertion `(size_t)(ptr - buf) < MAX_TEXT - 4' failed in key_text::key_text
195 #
196 CREATE TABLE t1 (
197 a012345678901234567890123456789012345678901 char(255),
198 b char(255),
199 FOREIGN KEY ( a012345678901234567890123456789012345678901, b ) REFERENCES tx (ax, bx)
200 ) ENGINE=InnoDB;
201 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
202 CREATE TABLE t1 (
203 a012345678901234567 int,
204 b int,
205 c0123456789012345678 int,
206 FOREIGN KEY (a012345678901234567,c0123456789012345678,b) REFERENCES tx (x1,x2,x3)
207 ) ENGINE=InnoDB;
208 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
209 #
210 # MDEV-25642 InnoDB rename table copy DDL fails
211 # while dropping the table
212 #
213 call mtr.add_suppression("InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.");
214 CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
215 CREATE TABLE t1_fk (a VARCHAR(40), KEY a (a), FULLTEXT KEY(a), CONSTRAINT fk FOREIGN KEY(a) REFERENCES t1 (a) ON UPDATE CASCADE) ENGINE=InnoDB;
216 ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
217 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
218 ID FOR_NAME REF_NAME N_COLS TYPE
219 test/fk test/t1_fk test/t1 1 4
220 SET FOREIGN_KEY_CHECKS=0;
221 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
222 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;
223 DROP TABLE t1, tm1, t1_fk;