Debian Codegraph @

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

raw
1 drop table if exists t1,t2,t3,t4;
2 drop database if exists mysqltest;
3 create table t1 (v varchar(16384)) charset latin1 engine=innodb;
4 drop table t1;
5 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
6 insert into t1 values ('8', '6'), ('4', '7');
7 select min(a) from t1;
8 min(a)
9 4
10 select min(b) from t1 where a='8';
11 min(b)
12 6
13 drop table t1;
14 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
15 Warnings:
16 Warning 1681 Integer display width is deprecated and will be removed in a future release.
17 Warning 1681 Integer display width is deprecated and will be removed in a future release.
18 insert into t1 (b) values (1);
19 replace into t1 (b) values (2), (1), (3);
20 select * from t1;
21 a b
22 2 2
23 3 1
24 4 3
25 truncate table t1;
26 insert into t1 (b) values (1);
27 replace into t1 (b) values (2);
28 replace into t1 (b) values (1);
29 replace into t1 (b) values (3);
30 select * from t1;
31 a b
32 2 2
33 3 1
34 4 3
35 drop table t1;
36 create table t1 (rowid int not null auto_increment, val int not null,primary
37 key (rowid), unique(val)) engine=innodb;
38 replace into t1 (val) values ('1'),('2');
39 replace into t1 (val) values ('1'),('2');
40 insert into t1 (val) values ('1'),('2');
41 ERROR 23000: Duplicate entry '1' for key 't1.val'
42 select * from t1;
43 rowid val
44 3 1
45 4 2
46 drop table t1;
47 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
48 insert into t1 (val) values (1);
49 update t1 set a=2 where a=1;
50 insert into t1 values (2, 2);
51 ERROR 23000: Duplicate entry '2' for key 't1.PRIMARY'
52 insert into t1 (val) value (3);
53 select * from t1;
54 a val
55 2 1
56 3 3
57 drop table t1;
58 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
59 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
60 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
61 GRADE
62 252
63 SELECT GRADE FROM t1 WHERE GRADE= 151;
64 GRADE
65 151
66 DROP TABLE t1;
67 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
68 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
69 insert into t2 values ('aa','cc');
70 insert into t1 values ('aa','bb'),('aa','cc');
71 delete t1 from t1,t2 where f1=f3 and f4='cc';
72 select * from t1;
73 f1 f2
74 drop table t1,t2;
75 CREATE TABLE t1 (
76 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
77 ) ENGINE=InnoDB;
78 CREATE TABLE t2 (
79 id INTEGER NOT NULL,
80 FOREIGN KEY (id) REFERENCES t1 (id)
81 ) ENGINE=InnoDB;
82 INSERT INTO t1 (id) VALUES (NULL);
83 SELECT * FROM t1;
84 id
85 1
86 TRUNCATE t1;
87 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
88 INSERT INTO t1 (id) VALUES (NULL);
89 SELECT * FROM t1;
90 id
91 1
92 2
93 DELETE FROM t1;
94 TRUNCATE t1;
95 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
96 INSERT INTO t1 (id) VALUES (NULL);
97 SELECT * FROM t1;
98 id
99 3
100 DROP TABLE t2, t1;
101 CREATE TABLE t1
102 (
103 id INT PRIMARY KEY
104 ) ENGINE=InnoDB;
105 CREATE TEMPORARY TABLE t2
106 (
107 id INT NOT NULL PRIMARY KEY,
108 b INT,
109 FOREIGN KEY (b) REFERENCES test.t1(id)
110 ) ENGINE=InnoDB;
111 ERROR HY000: Cannot add foreign key constraint
112 DROP TABLE t1;
113 create table t1 (col1 varchar(2000), index (col1(767)))
114 character set = latin1 engine = innodb;
115 create table t2 (col1 char(255), index (col1))
116 character set = latin1 engine = innodb;
117 create table t3 (col1 binary(255), index (col1))
118 character set = latin1 engine = innodb;
119 create table t4 (col1 varchar(767), index (col1))
120 character set = latin1 engine = innodb;
121 create table t5 (col1 varchar(767) primary key)
122 character set = latin1 engine = innodb;
123 create table t6 (col1 varbinary(767) primary key)
124 character set = latin1 engine = innodb;
125 create table t7 (col1 text, index(col1(767)))
126 character set = latin1 engine = innodb;
127 create table t8 (col1 blob, index(col1(767)))
128 character set = latin1 engine = innodb;
129 drop table t1, t2, t3, t4, t5, t6, t7, t8;
130 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
131 SET GLOBAL innodb_default_row_format=compact;
132 create table t1 (col1 varchar(768) primary key)
133 character set = latin1 engine = innodb;
134 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
135 create table t2 (col1 varbinary(768) primary key)
136 character set = latin1 engine = innodb;
137 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
138 create table t3 (col1 text, primary key(col1(768)))
139 character set = latin1 engine = innodb;
140 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
141 create table t4 (col1 blob, primary key(col1(768)))
142 character set = latin1 engine = innodb;
143 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
144 SET GLOBAL innodb_default_row_format=dynamic;
145 create table t1 (col1 varchar(768) primary key)
146 character set = latin1 engine = innodb;
147 drop table t1;
148 create table t2 (col1 varbinary(768) primary key)
149 character set = latin1 engine = innodb;
150 drop table t2;
151 create table t3 (col1 text, primary key(col1(768)))
152 character set = latin1 engine = innodb;
153 drop table t3;
154 create table t4 (col1 blob, primary key(col1(768)))
155 character set = latin1 engine = innodb;
156 drop table t4;
157 SET GLOBAL innodb_default_row_format=default;
158 SET sql_mode = default;
159 SET GLOBAL innodb_default_row_format=compact;
160 create table t1 (col1 varchar(768) primary key)
161 character set = latin1 engine = innodb;
162 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
163 create table t2 (col1 varbinary(768) primary key)
164 character set = latin1 engine = innodb;
165 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
166 create table t3 (col1 text, primary key(col1(768)))
167 character set = latin1 engine = innodb;
168 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
169 create table t4 (col1 blob, primary key(col1(768)))
170 character set = latin1 engine = innodb;
171 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
172 SET GLOBAL innodb_default_row_format=default;
173 CREATE TABLE t1
174 (
175 id INT PRIMARY KEY
176 ) ENGINE=InnoDB;
177 CREATE TABLE t2
178 (
179 v INT,
180 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
181 ) ENGINE=InnoDB;
182 INSERT INTO t2 VALUES(2);
183 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
184 INSERT INTO t1 VALUES(1);
185 INSERT INTO t2 VALUES(1);
186 DELETE FROM t1 WHERE id = 1;
187 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
188 DROP TABLE t1;
189 ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'c1' on table 't2'.
190 SET FOREIGN_KEY_CHECKS=0;
191 DROP TABLE t1;
192 SET FOREIGN_KEY_CHECKS=1;
193 INSERT INTO t2 VALUES(3);
194 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
195 DROP TABLE t2;
196 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
197 insert into t1 values (1),(2);
198 set autocommit=0;
199 checksum table t1;
200 Table Checksum
201 test.t1 1531596814
202 insert into t1 values(3);
203 checksum table t1;
204 Table Checksum
205 test.t1 1531596814
206 commit;
207 checksum table t1;
208 Table Checksum
209 test.t1 2050879373
210 commit;
211 drop table t1;
212 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
213 insert into t1 values (1),(2);
214 set autocommit=1;
215 checksum table t1;
216 Table Checksum
217 test.t1 1531596814
218 set autocommit=1;
219 insert into t1 values(3);
220 checksum table t1;
221 Table Checksum
222 test.t1 2050879373
223 drop table t1;
224 set foreign_key_checks=0;
225 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
226 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
227 ERROR HY000: Referencing column 'b' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
228 set foreign_key_checks=1;
229 drop table t2;
230 set foreign_key_checks=0;
231 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
232 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
233 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
234 set foreign_key_checks=1;
235 drop table t1;
236 set foreign_key_checks=0;
237 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
238 create table t1(a varchar(10) primary key) engine = innodb;
239 alter table t1 modify column a int;
240 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
241 set foreign_key_checks=1;
242 drop table t2,t1;
243 set foreign_key_checks=0;
244 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
245 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
246 alter table t1 convert to character set utf8;
247 Warnings:
248 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
249 set foreign_key_checks=1;
250 drop table t2,t1;
251 set foreign_key_checks=0;
252 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
253 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
254 Warnings:
255 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
256 rename table t3 to t1;
257 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
258 set foreign_key_checks=1;
259 drop table t2,t3;
260 create table t1(a int primary key) row_format=redundant engine=innodb;
261 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
262 create table t3(a int primary key) row_format=compact engine=innodb;
263 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
264 insert into t1 values(1);
265 insert into t3 values(1);
266 insert into t2 values(2);
267 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
268 insert into t4 values(2);
269 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
270 insert into t2 values(1);
271 insert into t4 values(1);
272 update t1 set a=2;
273 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
274 update t2 set a=2;
275 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
276 update t3 set a=2;
277 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
278 update t4 set a=2;
279 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
280 truncate t1;
281 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
282 truncate t3;
283 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1`)
284 truncate t2;
285 truncate t4;
286 truncate t1;
287 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
288 truncate t3;
289 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1`)
290 drop table t4,t3,t2,t1;
291 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
292 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
293 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
294 Warnings:
295 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
296 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
297 Warnings:
298 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
299 insert into t1 values (0x41),(0x4120),(0x4100);
300 insert into t2 values (0x41),(0x4120),(0x4100);
301 ERROR 23000: Duplicate entry 'A' for key 't2.PRIMARY'
302 insert into t2 values (0x41),(0x4120);
303 insert into t3 values (0x41),(0x4120),(0x4100);
304 ERROR 23000: Duplicate entry 'A ' for key 't3.PRIMARY'
305 insert into t3 values (0x41),(0x4100);
306 insert into t4 values (0x41),(0x4120),(0x4100);
307 ERROR 23000: Duplicate entry 'A' for key 't4.PRIMARY'
308 insert into t4 values (0x41),(0x4100);
309 select hex(s1) from t1;
310 hex(s1)
311 41
312 4100
313 4120
314 select hex(s1) from t2;
315 hex(s1)
316 4100
317 4120
318 select hex(s1) from t3;
319 hex(s1)
320 4100
321 41
322 select hex(s1) from t4;
323 hex(s1)
324 4100
325 41
326 drop table t1,t2,t3,t4;
327 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
328 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
329 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
330 insert into t2 values(0x42);
331 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
332 insert into t2 values(0x41);
333 select hex(s1) from t2;
334 hex(s1)
335 4100
336 update t1 set s1=0x123456 where a=2;
337 select hex(s1) from t2;
338 hex(s1)
339 4100
340 update t1 set s1=0x12 where a=1;
341 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
342 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
343 update t1 set s1=0x12345678 where a=1;
344 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
345 SET sql_mode = default;
346 update t1 set s1=0x123457 where a=1;
347 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
348 update t1 set s1=0x1220 where a=1;
349 select hex(s1) from t2;
350 hex(s1)
351 1220
352 update t1 set s1=0x1200 where a=1;
353 select hex(s1) from t2;
354 hex(s1)
355 1200
356 update t1 set s1=0x4200 where a=1;
357 select hex(s1) from t2;
358 hex(s1)
359 4200
360 delete from t1 where a=1;
361 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
362 delete from t1 where a=2;
363 update t2 set s1=0x4120;
364 delete from t1;
365 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
366 delete from t1 where a!=3;
367 select a,hex(s1) from t1;
368 a hex(s1)
369 3 4120
370 select hex(s1) from t2;
371 hex(s1)
372 4120
373 drop table t2,t1;
374 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
375 Warnings:
376 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
377 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
378 Warnings:
379 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
380 insert into t1 values(1,0x4100),(2,0x41);
381 insert into t2 values(0x41);
382 select hex(s1) from t2;
383 hex(s1)
384 41
385 update t1 set s1=0x1234 where a=1;
386 select hex(s1) from t2;
387 hex(s1)
388 41
389 update t1 set s1=0x12 where a=2;
390 select hex(s1) from t2;
391 hex(s1)
392 12
393 delete from t1 where a=1;
394 delete from t1 where a=2;
395 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
396 select a,hex(s1) from t1;
397 a hex(s1)
398 2 12
399 select hex(s1) from t2;
400 hex(s1)
401 12
402 drop table t2,t1;
403 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
404 CREATE TABLE t2(a INT) ENGINE=InnoDB;
405 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
406 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
407 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
408 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
409 SHOW CREATE TABLE t2;
410 Table Create Table
411 t2 CREATE TABLE `t2` (
412 `a` int DEFAULT NULL,
413 KEY `t2_ibfk_0` (`a`)
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
415 DROP TABLE t2,t1;
416 CREATE TABLE t1 (
417 field1 varchar(8) NOT NULL DEFAULT '',
418 field2 varchar(8) NOT NULL DEFAULT '',
419 PRIMARY KEY (field1, field2)
420 ) ENGINE=InnoDB;
421 CREATE TABLE t2 (
422 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
423 FOREIGN KEY (field1) REFERENCES t1 (field1)
424 ON DELETE CASCADE ON UPDATE CASCADE
425 ) ENGINE=InnoDB;
426 INSERT INTO t1 VALUES ('old', 'somevalu');
427 INSERT INTO t1 VALUES ('other', 'anyvalue');
428 INSERT INTO t2 VALUES ('old');
429 INSERT INTO t2 VALUES ('other');
430 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
431 ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'PRIMARY'
432 DROP TABLE t2;
433 DROP TABLE t1;
434 create table t1 (
435 c1 bigint not null,
436 c2 bigint not null,
437 primary key (c1),
438 unique key (c2)
439 ) engine=innodb;
440 create table t2 (
441 c1 bigint not null,
442 primary key (c1)
443 ) engine=innodb;
444 alter table t1 add constraint c2_fk foreign key (c2)
445 references t2(c1) on delete cascade;
446 show create table t1;
447 Table Create Table
448 t1 CREATE TABLE `t1` (
449 `c1` bigint NOT NULL,
450 `c2` bigint NOT NULL,
451 PRIMARY KEY (`c1`),
452 UNIQUE KEY `c2` (`c2`),
453 CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
454 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
455 alter table t1 drop foreign key c2_fk;
456 show create table t1;
457 Table Create Table
458 t1 CREATE TABLE `t1` (
459 `c1` bigint NOT NULL,
460 `c2` bigint NOT NULL,
461 PRIMARY KEY (`c1`),
462 UNIQUE KEY `c2` (`c2`)
463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
464 drop table t1, t2;
465 create table t1(a date) engine=innodb;
466 create table t2(a date, key(a)) engine=innodb;
467 insert into t1 values('2005-10-01');
468 insert into t2 values('2005-10-01');
469 select * from t1, t2
470 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
471 a a
472 2005-10-01 2005-10-01
473 drop table t1, t2;
474 create table t1 (id int not null, f_id int not null, f int not null,
475 primary key(f_id, id)) engine=innodb;
476 create table t2 (id int not null,s_id int not null,s varchar(200),
477 primary key(id)) engine=innodb;
478 INSERT INTO t1 VALUES (8, 1, 3);
479 INSERT INTO t1 VALUES (1, 2, 1);
480 INSERT INTO t2 VALUES (1, 0, '');
481 INSERT INTO t2 VALUES (8, 1, '');
482 commit;
483 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
484 WHERE mm.id IS NULL;
485 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
486 where mm.id is null lock in share mode;
487 id f_id f
488 drop table t1,t2;
489 create table t1(a int not null, b int, primary key(a)) engine=innodb;
490 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
491 commit;
492 SET binlog_format='MIXED';
493 Warnings:
494 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
495 set autocommit = 0;
496 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
497 update t1 set b = 5 where b = 1;
498 SET binlog_format='MIXED';
499 Warnings:
500 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
501 set autocommit = 0;
502 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
503 select * from t1 where a = 7 and b = 3 for update;
504 a b
505 7 3
506 commit;
507 commit;
508 drop table t1;
509 create table t1(a int not null, b int, primary key(a)) engine=innodb;
510 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
511 commit;
512 set autocommit = 0;
513 select * from t1 lock in share mode;
514 a b
515 1 1
516 2 2
517 3 1
518 4 2
519 5 1
520 6 2
521 update t1 set b = 5 where b = 1;
522 set autocommit = 0;
523 select * from t1 where a = 2 and b = 2 for update;
524 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
525 commit;
526 commit;
527 drop table t1;
528 create table t1(a int not null, b int, primary key(a)) engine=innodb;
529 insert into t1 values (1,2),(5,3),(4,2);
530 create table t2(d int not null, e int, primary key(d)) engine=innodb;
531 insert into t2 values (8,6),(12,1),(3,1);
532 commit;
533 set autocommit = 0;
534 select * from t2 for update;
535 d e
536 3 1
537 8 6
538 12 1
539 SET binlog_format='MIXED';
540 Warnings:
541 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
542 set autocommit = 0;
543 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
544 insert into t1 select * from t2;
545 update t1 set b = (select e from t2 where a = d);
546 create table t3(d int not null, e int, primary key(d)) engine=innodb
547 select * from t2;
548 commit;
549 commit;
550 drop table t1, t2, t3;
551 create table t1(a int not null, b int, primary key(a)) engine=innodb;
552 insert into t1 values (1,2),(5,3),(4,2);
553 create table t2(a int not null, b int, primary key(a)) engine=innodb;
554 insert into t2 values (8,6),(12,1),(3,1);
555 create table t3(d int not null, b int, primary key(d)) engine=innodb;
556 insert into t3 values (8,6),(12,1),(3,1);
557 create table t5(a int not null, b int, primary key(a)) engine=innodb;
558 insert into t5 values (1,2),(5,3),(4,2);
559 create table t6(d int not null, e int, primary key(d)) engine=innodb;
560 insert into t6 values (8,6),(12,1),(3,1);
561 create table t8(a int not null, b int, primary key(a)) engine=innodb;
562 insert into t8 values (1,2),(5,3),(4,2);
563 create table t9(d int not null, e int, primary key(d)) engine=innodb;
564 insert into t9 values (8,6),(12,1),(3,1);
565 commit;
566 set autocommit = 0;
567 select * from t2 for update;
568 a b
569 3 1
570 8 6
571 12 1
572 SET binlog_format='MIXED';
573 Warnings:
574 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
575 set autocommit = 0;
576 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
577 insert into t1 select * from t2;
578 SET binlog_format='MIXED';
579 Warnings:
580 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
581 set autocommit = 0;
582 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
583 update t3 set b = (select b from t2 where a = d);
584 SET binlog_format='MIXED';
585 Warnings:
586 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
587 set autocommit = 0;
588 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
589 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
590 SET binlog_format='MIXED';
591 Warnings:
592 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
593 set autocommit = 0;
594 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
595 insert into t5 (select * from t2 lock in share mode);
596 SET binlog_format='MIXED';
597 Warnings:
598 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
599 set autocommit = 0;
600 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
601 update t6 set e = (select b from t2 where a = d lock in share mode);
602 SET binlog_format='MIXED';
603 Warnings:
604 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
605 set autocommit = 0;
606 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
607 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
608 SET binlog_format='MIXED';
609 Warnings:
610 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
611 set autocommit = 0;
612 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
613 insert into t8 (select * from t2 for update);
614 SET binlog_format='MIXED';
615 Warnings:
616 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
617 set autocommit = 0;
618 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
619 update t9 set e = (select b from t2 where a = d for update);
620 SET binlog_format='MIXED';
621 Warnings:
622 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
623 set autocommit = 0;
624 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
625 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
626 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
627 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
628 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
629 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
630 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
631 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
632 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
633 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
634 ERROR HY000: Can't update table 't2' while 't10' is being created.
635 commit;
636 drop table t1, t2, t3, t5, t6, t8, t9;
637 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
638 ERROR 42000: Incorrect column name 'DB_ROW_ID'
639 CREATE TABLE t1 (
640 a BIGINT(20) NOT NULL,
641 PRIMARY KEY (a)
642 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
643 Warnings:
644 Warning 1681 Integer display width is deprecated and will be removed in a future release.
645 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
646 CREATE TABLE t2 (
647 a BIGINT(20) NOT NULL,
648 b VARCHAR(128) NOT NULL,
649 c TEXT NOT NULL,
650 PRIMARY KEY (a,b),
651 KEY idx_t2_b_c (b,c(100)),
652 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
653 ON DELETE CASCADE
654 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
655 Warnings:
656 Warning 1681 Integer display width is deprecated and will be removed in a future release.
657 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
658 INSERT INTO t1 VALUES (1);
659 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
660 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
661 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
662 INSERT INTO t2 VALUES (1, 'customer_over', '1');
663 SELECT * FROM t2 WHERE b = 'customer_over';
664 a b c
665 1 customer_over 1
666 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
667 a b c
668 1 customer_over 1
669 Warnings:
670 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
671 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
672 a
673 1
674 /* Bang: Empty result set, above was expected: */
675 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
676 a
677 1
678 Warnings:
679 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
680 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
681 a
682 1
683 Warnings:
684 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
685 drop table t2, t1;
686 CREATE TABLE t1 ( a int ) ENGINE=innodb;
687 BEGIN;
688 INSERT INTO t1 VALUES (1);
689 OPTIMIZE TABLE t1;
690 Table Op Msg_type Msg_text
691 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
692 test.t1 optimize status OK
693 DROP TABLE t1;
694 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
695 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
696 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
697 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
698 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
699 DELETE CASCADE ON UPDATE CASCADE;
700 SHOW CREATE TABLE t2;
701 Table Create Table
702 t2 CREATE TABLE `t2` (
703 `id` int NOT NULL,
704 `f` int NOT NULL,
705 PRIMARY KEY (`id`),
706 KEY `f` (`f`),
707 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
708 CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
710 DROP TABLE t2, t1;
711 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
712 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
713 INSERT INTO t1 VALUES (1);
714 INSERT INTO t2 VALUES (1);
715 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
716 ALTER TABLE t2 MODIFY a INT NOT NULL;
717 ERROR HY000: Column 'a' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
718 DELETE FROM t1;
719 DROP TABLE t2,t1;
720 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
721 ENGINE=InnoDB;
722 Warnings:
723 Warning 3778 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
724 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
725 DELETE FROM t1;
726 INSERT INTO t1 VALUES ('DDD');
727 SELECT * FROM t1;
728 a
729 DDD
730 DROP TABLE t1;
731 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
732 AUTO_INCREMENT=42;
733 INSERT INTO t1 VALUES (0),(347),(0);
734 SELECT * FROM t1;
735 id
736 42
737 347
738 348
739 SHOW CREATE TABLE t1;
740 Table Create Table
741 t1 CREATE TABLE `t1` (
742 `id` int NOT NULL AUTO_INCREMENT,
743 PRIMARY KEY (`id`)
744 ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
745 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
746 INSERT INTO t2 VALUES(42),(347),(348);
747 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
748 SHOW CREATE TABLE t1;
749 Table Create Table
750 t1 CREATE TABLE `t1` (
751 `id` int NOT NULL AUTO_INCREMENT,
752 PRIMARY KEY (`id`),
753 CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
754 ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
755 DROP TABLE t1,t2;
756 SET innodb_strict_mode=ON;
757 CREATE TABLE t1 (
758 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
759 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
760 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
761 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
762 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
763 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
764 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
765 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
766 ) ENGINE = InnoDB charset latin1;
767 ERROR 42000: Row size too large (> {checked_valid}). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
768 SET innodb_strict_mode=OFF;
769 DROP TABLE IF EXISTS t1;
770 Warnings:
771 Note 1051 Unknown table 'test.t1'
772 CREATE TABLE t1(
773 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
774 ) ENGINE=InnoDB;
775 Warnings:
776 Warning 1681 Integer display width is deprecated and will be removed in a future release.
777 INSERT INTO t1 VALUES(-10);
778 SELECT * FROM t1;
779 id
780 -10
781 INSERT INTO t1 VALUES(NULL);
782 SELECT * FROM t1;
783 id
784 -10
785 1
786 DROP TABLE t1;
787 create table t1 (i int, j int) engine=innodb;
788 insert into t1 (i, j) values (1, 1), (2, 2);
789 update t1 set j = 2;
790 affected rows: 1
791 info: Rows matched: 2 Changed: 1 Warnings: 0
792 drop table t1;
793 set @save_innodb_file_per_table= @@global.innodb_file_per_table;
794 set @@global.innodb_file_per_table=0;
795 create table t1 (id int) comment='this is a comment' engine=innodb;
796 ANALYZE TABLE t1;
797 Table Op Msg_type Msg_text
798 test.t1 analyze status OK
799 select table_comment, data_free > 0 as data_free_is_set
800 from information_schema.tables
801 where table_schema='test' and table_name = 't1';
802 TABLE_COMMENT data_free_is_set
803 this is a comment 1
804 drop table t1;
805 set @@global.innodb_file_per_table= @save_innodb_file_per_table;
806 CREATE TABLE t1 (
807 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
808 c2 VARCHAR(128) NOT NULL,
809 PRIMARY KEY(c1)
810 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
811 Warnings:
812 Warning 1681 Integer display width is deprecated and will be removed in a future release.
813 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
814 CREATE TABLE t2 (
815 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
816 c2 INT(10) UNSIGNED DEFAULT NULL,
817 PRIMARY KEY(c1)
818 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
819 Warnings:
820 Warning 1681 Integer display width is deprecated and will be removed in a future release.
821 Warning 1681 Integer display width is deprecated and will be removed in a future release.
822 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
823 ANALYZE TABLE t2;
824 Table Op Msg_type Msg_text
825 test.t2 analyze status OK
826 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
827 AUTO_INCREMENT
828 200
829 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
830 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
831 AUTO_INCREMENT
832 200
833 DROP TABLE t2;
834 DROP TABLE t1;
835 CREATE TABLE t1 (c1 int default NULL,
836 c2 int default NULL
837 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
838 TRUNCATE TABLE t1;
839 affected rows: 0
840 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
841 affected rows: 5
842 info: Records: 5 Duplicates: 0 Warnings: 0
843 TRUNCATE TABLE t1;
844 affected rows: 0
845 DROP TABLE t1;
846 Variable_name Value
847 Handler_update 10
848 Variable_name Value
849 Handler_delete 0
850 Variable_name Value
851 Handler_update 11
852 Variable_name Value
853 Handler_delete 1
854 #
855 # Bug#27041288: ASSERTION FAILURE:
856 # ROW0SEL.CC:3955:PREBUILT->MYSQL_PREFIX_LEN <= RECORD_BUFFER..
857 #
858 CREATE TABLE t(a INT PRIMARY KEY, b CHAR(0), c CHAR(1));
859 INSERT INTO t VALUES (1, '', 'a'), (2, '', 'b');
860 SELECT * FROM t;
861 a b c
862 1 a
863 2 b
864 DROP TABLE t;
865 count_alloc WHERE event_name like 'memory/innodb/other'
866 count_alloc
867 0