Debian Codegraph @

mysql-test/suite/innodb/r/foreign-keys.result

raw
1 #
2 # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
3 # ADD FOREIGN KEY
4 #
5 CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
6 PRIMARY KEY (`department_id`)) engine=innodb;
7 CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
8 `title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
9 CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
10 ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
11 `people` (`people_id`);
12 ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
13 (`people_id`);
14 ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
15 (`people_id`);
16 drop table title, department, people;
17 create table t1 (a int primary key, b int) engine=innodb;
18 create table t2 (c int primary key, d int,
19 foreign key (d) references t1 (a) on update cascade) engine=innodb;
20 insert t1 values (1,1),(2,2),(3,3);
21 insert t2 values (4,1),(5,2),(6,3);
22 flush table t2 with read lock;
23 connect con1,localhost,root;
24 delete from t1 where a=2;
25 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
26 update t1 set a=10 where a=1;
27 connection default;
28 unlock tables;
29 connection con1;
30 connection default;
31 lock table t2 write;
32 connection con1;
33 delete from t1 where a=2;
34 connection default;
35 unlock tables;
36 connection con1;
37 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
38 connection default;
39 unlock tables;
40 disconnect con1;
41 create user foo;
42 grant select,update on test.t1 to foo;
43 connect foo,localhost,foo;
44 update t1 set a=30 where a=3;
45 disconnect foo;
46 connection default;
47 select * from t2;
48 c d
49 5 2
50 4 10
51 6 30
52 drop table t2, t1;
53 drop user foo;
54 create table t1 (f1 int primary key) engine=innodb;
55 create table t2 (f2 int primary key) engine=innodb;
56 create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb;
57 insert into t1 values (1),(2),(3),(4),(5);
58 insert into t2 values (1),(2),(3),(4),(5);
59 insert into t3 values (1),(2),(3),(4),(5);
60 connect con1,localhost,root;
61 set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2';
62 alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade;
63 connection default;
64 set debug_sync='before_execute_sql_command wait_for g1';
65 update t1 set f1 = f1 + 100000 limit 2;
66 connect con2,localhost,root;
67 kill query UPDATE;
68 disconnect con2;
69 connection default;
70 ERROR 70100: Query execution was interrupted
71 set debug_sync='now signal g2';
72 connection con1;
73 show create table t2;
74 Table Create Table
75 t2 CREATE TABLE `t2` (
76 `f2` int(11) NOT NULL,
77 PRIMARY KEY (`f2`),
78 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
80 disconnect con1;
81 connection default;
82 select * from t2 where f2 not in (select f1 from t1);
83 f2
84 select * from t3 where f3 not in (select f2 from t2);
85 f3
86 drop table t3;
87 drop table t2;
88 drop table t1;
89 set debug_sync='reset';
90 #
91 # MDEV-17595 - Server crashes in copy_data_between_tables or
92 # Assertion `thd->transaction.stmt.is_empty() ||
93 # (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
94 # fails in close_tables_for_reopen upon concurrent
95 # ALTER TABLE and FLUSH
96 #
97 CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
98 INSERT INTO t1 VALUES(1),(2);
99 CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
100 INSERT INTO t2 VALUES(2);
101 ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
102 DROP TABLE t2, t1;
103 #
104 # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name)
105 #
106 CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB;
107 CREATE TABLE t2 LIKE t1;
108 FLUSH TABLES;
109 SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go';
110 ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE;
111 connect con1, localhost, root;
112 SET debug_sync='now WAIT_FOR ready';
113 SET lock_wait_timeout=0;
114 UPDATE t2 SET pk=10 WHERE pk=1;
115 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
116 PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1';
117 DEALLOCATE PREPARE stmt;
118 SET debug_sync='now SIGNAL go';
119 connection default;
120 disconnect con1;
121 connection default;
122 SET debug_sync='reset';
123 SHOW OPEN TABLES FROM test;
124 Database Table In_use Name_locked
125 test t2 0 0
126 DROP TABLE t1, t2;
127 create table t1 (a int primary key, b int) engine=innodb;
128 create table t2 (c int primary key, d int,
129 foreign key (d) references t1 (a) on update cascade) engine=innodb;
130 insert t1 values (1,1),(2,2),(3,3);
131 insert t2 values (4,1),(5,2),(6,3);
132 flush table t2 with read lock;
133 connect con1,localhost,root;
134 delete from t1 where a=2;
135 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
136 update t1 set a=10 where a=1;
137 connection default;
138 unlock tables;
139 connection con1;
140 connection default;
141 lock table t2 write;
142 connection con1;
143 delete from t1 where a=2;
144 connection default;
145 unlock tables;
146 connection con1;
147 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
148 connection default;
149 unlock tables;
150 disconnect con1;
151 create user foo;
152 grant select,update on test.t1 to foo;
153 connect foo,localhost,foo;
154 update t1 set a=30 where a=3;
155 disconnect foo;
156 connection default;
157 select * from t2;
158 c d
159 5 2
160 4 10
161 6 30
162 drop table t2, t1;
163 drop user foo;
164 #
165 # MDEV-17187 table doesn't exist in engine after ALTER other tables
166 # with CONSTRAINTs
167 #
168 set foreign_key_checks=on;
169 create table t1 (id int not null primary key) engine=innodb;
170 create table t2 (id int not null primary key, fid int not null,
171 CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb;
172 insert into t1 values (1), (2), (3);
173 insert into t2 values (1, 1), (2, 1), (3, 2);
174 set foreign_key_checks=off;
175 alter table t2 drop index fk_fid;
176 set foreign_key_checks=on;
177 delete from t1 where id=2;
178 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
179 insert into t2 values(4, 99);
180 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
181 select * from t1;
182 id
183 1
184 2
185 3
186 select * from t2;
187 id fid
188 1 1
189 2 1
190 3 2
191 set foreign_key_checks=off;
192 delete from t1 where id=2;
193 insert into t2 values(4, 99);
194 set foreign_key_checks=on;
195 select * from t1;
196 id
197 1
198 3
199 select * from t2;
200 id fid
201 1 1
202 2 1
203 3 2
204 4 99
205 show create table t1;
206 Table Create Table
207 t1 CREATE TABLE `t1` (
208 `id` int(11) NOT NULL,
209 PRIMARY KEY (`id`)
210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
211 show create table t2;
212 Table Create Table
213 t2 CREATE TABLE `t2` (
214 `id` int(11) NOT NULL,
215 `fid` int(11) NOT NULL,
216 PRIMARY KEY (`id`),
217 CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
219 drop table t1,t2;
220 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
221 drop table t1,t2;
222 ERROR 42S02: Unknown table 'test.t2'
223 #
224 # MDEV-23470 InnoDB: Failing assertion: cmp < 0 in
225 # row_ins_check_foreign_constraint
226 #
227 CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY, f2 INT NOT NULL)ENGINE=InnoDB;
228 CREATE TABLE t2(f1 VARCHAR(100), f2 INT NOT NULL,
229 INDEX(f2))ENGINE=InnoDB;
230 INSERT INTO t1 VALUES(99, 2);
231 ALTER TABLE t2 ADD FOREIGN KEY(f2) REFERENCES t1(f1);
232 SET FOREIGN_KEY_CHECKS=0;
233 DROP INDEX f2 ON t2;
234 SET FOREIGN_KEY_CHECKS=1;
235 INSERT INTO t2 VALUES('G', 3);
236 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`))
237 DROP TABLE t2, t1;
238 SET FOREIGN_KEY_CHECKS=DEFAULT;
239 CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=1;
240 ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
241 SHOW WARNINGS;
242 Level Code Message
243 Warning 140 InnoDB: PAGE_COMPRESSED table can't have ROW_TYPE=COMPRESSED
244 Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options")
245 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
246 # End of 10.5 tests
247 #
248 # MDEV-35598 foreign key error is unnecessary truncated
249 #
250 set names utf8;
251 create table t1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш
252 (f1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш int not null primary key,
253 f2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш int not null
254 ) engine=innodb;
255 create table t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш
256 (f1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш varchar(100),
257 f2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш int not null,
258 index i2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш
259 (f2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш)
260 ) engine=innodb;
261 insert t1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш values(99, 2);
262 alter table t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш add foreign key(f2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш) references t1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш(f1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш);
263 insert t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш values('g', 3);
264 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш`, CONSTRAINT `t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш_ibfk_1` FOREIGN KEY (`f2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш`) REFERENCES `t1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш` (`f1яяяяяяяяяяььььььььььззззз
265 drop table t2яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш,
266 t1яяяяяяяяяяььььььььььззззззззззшшшшшшшшшш;
267 # End of 10.6 tests