Debian Codegraph @

mysql-test/suite/innodb/t/innodb-fk.test

raw
1 --source include/have_innodb.inc
2 --source include/not_embedded.inc
3
4 --echo #
5 --echo # Bug #18806829 OPENING INNODB TABLES WITH MANY FOREIGN KEY
6 --echo # REFERENCES IS SLOW/CRASHES SEMAPHORE
7 --echo #
8
9 create table t1 (f1 int primary key) engine=innodb;
10 insert into t1 values (5);
11 insert into t1 values (2882);
12 insert into t1 values (10);
13
14 let $fk_tables = 120;
15
16 --disable_query_log
17 let $i = $fk_tables;
18 while ($i)
19 {
20 eval create table fk_$i (f1 int primary key,
21 constraint pc$i foreign key (f1) references t1(f1)
22 on delete cascade on update cascade) engine=innodb;
23 eval insert into fk_$i values (5);
24 eval insert into fk_$i values (2882);
25 eval insert into fk_$i values (10);
26 dec $i;
27 }
28 --enable_query_log
29
30 --source include/restart_mysqld.inc
31
32 update t1 set f1 = 28 where f1 = 2882;
33
34 select * from fk_120;
35 select * from fk_1;
36 select * from fk_50;
37
38 --disable_query_log
39 let $i = $fk_tables;
40 while ($i)
41 {
42 eval drop table fk_$i;
43 dec $i;
44 }
45 --enable_query_log
46
47 drop table t1;
48
49 --echo #
50 --echo # Check if restrict is working fine.
51 --echo #
52
53 create table t1 (f1 int primary key) engine=innodb;
54
55 let $fk_tables = 30;
56
57 --disable_query_log
58 let $i = $fk_tables;
59 while ($i)
60 {
61 eval create table fk_$i (f1 int primary key,
62 constraint pc$i foreign key (f1) references t1(f1)
63 on delete restrict on update restrict) engine=innodb;
64 eval insert into t1 values ($i);
65 eval insert into fk_$i values ($i);
66 dec $i;
67 }
68 --enable_query_log
69
70 --source include/restart_mysqld.inc
71
72 --error ER_ROW_IS_REFERENCED_2
73 delete from t1 where f1 = 29;
74 select * from fk_29;
75
76 --disable_query_log
77 let $i = $fk_tables;
78 while ($i)
79 {
80 eval drop table fk_$i;
81 dec $i;
82 }
83 --enable_query_log
84
85 drop table t1;
86
87 #
88 # MDEV-7672: Crash creating an InnoDB table with foreign keys
89 #
90
91 CREATE TABLE t1 (
92 id int(11) NOT NULL AUTO_INCREMENT,
93 f1 int(11) DEFAULT NULL,
94 PRIMARY KEY (id),
95 CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE
96 ) ENGINE=InnoDB;
97
98 --error 1005
99 CREATE TABLE t2 (
100 id int(11) NOT NULL AUTO_INCREMENT,
101 f2 int(11) NOT NULL,
102 f3 int(11) NOT NULL,
103 PRIMARY KEY (`id`),
104 CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE,
105 CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
106 ) ENGINE=InnoDB;
107
108 show warnings;
109
110 CREATE TABLE t2 (
111 id int(11) NOT NULL AUTO_INCREMENT,
112 f2 int(11) NOT NULL,
113 f3 int(11) NOT NULL,
114 PRIMARY KEY (`id`),
115 CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE
116 ) ENGINE=InnoDB;
117
118 --error 1005
119 ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE;
120
121 show warnings;
122
123 drop table t2;
124 drop table t1;
125
126 #
127 # MDEV-9142 :Adding Constraint with no database reference
128 # results in ERROR 1046 (3D000) at line 13: No database selected
129 #
130 CREATE DATABASE kg_test1;
131 CREATE DATABASE kg_test2;
132
133 CREATE TABLE `kg_test1`.`group` (
134 Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
136
137 CREATE TABLE `kg_test1`.`person` (
138 `Id` INT(11) NOT NULL AUTO_INCREMENT,
139 `Name` VARCHAR(50) NOT NULL,
140 PRIMARY KEY (`Id`),
141 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
142 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
143
144 show create table `kg_test1`.`person`;
145
146 --error 1005
147 CREATE TABLE `kg_test2`.`person2` (
148 `Id` INT(11) NOT NULL AUTO_INCREMENT,
149 `Name` VARCHAR(50) NOT NULL,
150 PRIMARY KEY (`Id`),
151 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
152 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
153
154 CREATE TABLE `kg_test2`.`person2` (
155 `Id` INT(11) NOT NULL AUTO_INCREMENT,
156 `Name` VARCHAR(50) NOT NULL,
157 PRIMARY KEY (`Id`),
158 CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
159 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
160
161 show create table `kg_test2`.`person2`;
162
163 SHOW WARNINGS;
164 DROP DATABASE kg_test2;
165 DROP DATABASE kg_test1;
166
167 #
168 # MDEV-7627: Some symbols in table name can cause to Error Code: 1050 when created FK
169 #
170
171 CREATE TABLE `#departaments` (
172 `id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
173 PRIMARY KEY (`id_depart`)
174 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
175
176 CREATE TABLE `#departaments_tree` (
177 `id_depart` INT(10) UNSIGNED NOT NULL,
178 `id_depart_in` INT(10) UNSIGNED NOT NULL,
179 PRIMARY KEY (`id_depart`,`id_depart_in`),
180 CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`)
181 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
182
183 ALTER TABLE `#departaments_tree`
184 ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`);
185
186 SHOW CREATE TABLE `#departaments_tree`;
187
188 DROP TABLE `#departaments_tree`;
189 DROP TABLE `#departaments`;
190
191 CREATE TABLE `boroda` (
192 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
193 `a` INT(11) UNSIGNED DEFAULT NULL,
194 `b` INT(11) UNSIGNED DEFAULT NULL,
195 PRIMARY KEY (`id`),
196 KEY `a` (`a`),
197 CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
198 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
199
200 ALTER TABLE `boroda`
201 ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`);
202
203 ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`;
204
205 RENAME TABLE `boroda` TO `#boroda`;
206
207 ALTER TABLE `#boroda`
208 ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
209
210 SHOW CREATE TABLE `#boroda`;
211 DROP TABLE `#boroda`;
212
213 CREATE TABLE `boroda` (
214 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
215 `a` INT(11) UNSIGNED DEFAULT NULL,
216 `b` INT(11) UNSIGNED DEFAULT NULL,
217 PRIMARY KEY (`id`),
218 KEY `a` (`a`),
219 CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
220 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
221
222 RENAME TABLE `boroda` TO `bor#oda`;
223
224 ALTER TABLE `bor#oda`
225 ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`);
226
227 SHOW CREATE TABLE `bor#oda`;
228 DROP TABLE `bor#oda`;
229
230 --echo #
231 --echo # MDEV-21127 Assertion `(size_t)(ptr - buf) < MAX_TEXT - 4' failed in key_text::key_text
232 --echo #
233 --error ER_CANT_CREATE_TABLE
234 CREATE TABLE t1 (
235 a012345678901234567890123456789012345678901 char(255),
236 b char(255),
237 FOREIGN KEY ( a012345678901234567890123456789012345678901, b ) REFERENCES tx (ax, bx)
238 ) ENGINE=InnoDB;
239
240 --error ER_CANT_CREATE_TABLE
241 CREATE TABLE t1 (
242 a012345678901234567 int,
243 b int,
244 c0123456789012345678 int,
245 FOREIGN KEY (a012345678901234567,c0123456789012345678,b) REFERENCES tx (x1,x2,x3)
246 ) ENGINE=InnoDB;
247
248 --echo #
249 --echo # MDEV-25642 InnoDB rename table copy DDL fails
250 --echo # while dropping the table
251 --echo #
252 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.");
253
254 CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
255 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;
256 ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
257 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
258 # Enable SET FOREIGN_KEY_CHECKS after fixing MDEV-25885
259 SET FOREIGN_KEY_CHECKS=0;
260 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
261 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;
262 DROP TABLE t1, tm1, t1_fk;