Debian Codegraph @

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

raw
1 drop table if exists t1,t2,t3,t4;
2 drop database if exists mysqltest;
3 CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
4 INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000));
5 UPDATE bug58912 SET a=REPEAT('a',7999);
6 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
7 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
8 select id, code, name from t1 order by id;
9 id code name
10 1 1 Tim
11 2 1 Monty
12 3 2 David
13 4 2 Erik
14 5 3 Sasha
15 6 3 Jeremy
16 7 4 Matt
17 update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
18 Warnings:
19 Warning 1062 Duplicate entry '8' for key 't1.PRIMARY'
20 select id, code, name from t1 order by id;
21 id code name
22 2 1 Monty
23 3 2 David
24 4 2 Erik
25 5 3 Sasha
26 6 3 Jeremy
27 7 4 Matt
28 8 1 Sinisa
29 update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
30 Warnings:
31 Warning 1062 Duplicate entry 'Ralph' for key 't1.name'
32 select id, code, name from t1 order by id;
33 id code name
34 3 2 David
35 4 2 Erik
36 5 3 Sasha
37 6 3 Jeremy
38 7 4 Matt
39 8 1 Sinisa
40 12 1 Ralph
41 drop table t1;
42 CREATE TABLE t1 (
43 id int(11) NOT NULL auto_increment,
44 parent_id int(11) DEFAULT '0' NOT NULL,
45 level tinyint(4) DEFAULT '0' NOT NULL,
46 PRIMARY KEY (id),
47 KEY parent_id (parent_id),
48 KEY level (level)
49 ) engine=innodb;
50 Warnings:
51 Warning 1681 Integer display width is deprecated and will be removed in a future release.
52 Warning 1681 Integer display width is deprecated and will be removed in a future release.
53 Warning 1681 Integer display width is deprecated and will be removed in a future release.
54 INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
55 update t1 set parent_id=parent_id+100;
56 select * from t1 where parent_id=102;
57 id parent_id level
58 8 102 2
59 9 102 2
60 15 102 2
61 update t1 set id=id+1000;
62 update t1 set id=1024 where id=1009;
63 Got one of the listed errors
64 select * from t1;
65 id parent_id level
66 1001 100 0
67 1002 101 1
68 1003 101 1
69 1004 101 1
70 1005 101 1
71 1006 101 1
72 1007 101 1
73 1008 102 2
74 1009 102 2
75 1015 102 2
76 1016 103 2
77 1017 103 2
78 1018 103 2
79 1019 103 2
80 1020 103 2
81 1021 104 2
82 1022 104 2
83 1024 104 2
84 1025 105 2
85 1026 105 2
86 1027 105 2
87 1028 105 2
88 1029 105 2
89 1030 105 2
90 1031 106 2
91 1032 106 2
92 1033 106 2
93 1034 106 2
94 1035 106 2
95 1036 107 2
96 1037 107 2
97 1038 107 2
98 1040 107 2
99 1157 100 0
100 1179 105 2
101 1183 104 2
102 1193 105 2
103 1202 107 2
104 1203 107 2
105 update ignore t1 set id=id+1;
106 Warnings:
107 Warning 1062 Duplicate entry '1002' for key 't1.PRIMARY'
108 Warning 1062 Duplicate entry '1003' for key 't1.PRIMARY'
109 Warning 1062 Duplicate entry '1004' for key 't1.PRIMARY'
110 Warning 1062 Duplicate entry '1005' for key 't1.PRIMARY'
111 Warning 1062 Duplicate entry '1006' for key 't1.PRIMARY'
112 Warning 1062 Duplicate entry '1007' for key 't1.PRIMARY'
113 Warning 1062 Duplicate entry '1008' for key 't1.PRIMARY'
114 Warning 1062 Duplicate entry '1009' for key 't1.PRIMARY'
115 Warning 1062 Duplicate entry '1016' for key 't1.PRIMARY'
116 Warning 1062 Duplicate entry '1017' for key 't1.PRIMARY'
117 Warning 1062 Duplicate entry '1018' for key 't1.PRIMARY'
118 Warning 1062 Duplicate entry '1019' for key 't1.PRIMARY'
119 Warning 1062 Duplicate entry '1020' for key 't1.PRIMARY'
120 Warning 1062 Duplicate entry '1021' for key 't1.PRIMARY'
121 Warning 1062 Duplicate entry '1022' for key 't1.PRIMARY'
122 Warning 1062 Duplicate entry '1025' for key 't1.PRIMARY'
123 Warning 1062 Duplicate entry '1026' for key 't1.PRIMARY'
124 Warning 1062 Duplicate entry '1027' for key 't1.PRIMARY'
125 Warning 1062 Duplicate entry '1028' for key 't1.PRIMARY'
126 Warning 1062 Duplicate entry '1029' for key 't1.PRIMARY'
127 Warning 1062 Duplicate entry '1030' for key 't1.PRIMARY'
128 Warning 1062 Duplicate entry '1031' for key 't1.PRIMARY'
129 Warning 1062 Duplicate entry '1032' for key 't1.PRIMARY'
130 Warning 1062 Duplicate entry '1033' for key 't1.PRIMARY'
131 Warning 1062 Duplicate entry '1034' for key 't1.PRIMARY'
132 Warning 1062 Duplicate entry '1035' for key 't1.PRIMARY'
133 Warning 1062 Duplicate entry '1036' for key 't1.PRIMARY'
134 Warning 1062 Duplicate entry '1037' for key 't1.PRIMARY'
135 Warning 1062 Duplicate entry '1038' for key 't1.PRIMARY'
136 Warning 1062 Duplicate entry '1203' for key 't1.PRIMARY'
137 select * from t1;
138 id parent_id level
139 1001 100 0
140 1002 101 1
141 1003 101 1
142 1004 101 1
143 1005 101 1
144 1006 101 1
145 1007 101 1
146 1008 102 2
147 1010 102 2
148 1015 102 2
149 1016 103 2
150 1017 103 2
151 1018 103 2
152 1019 103 2
153 1020 103 2
154 1021 104 2
155 1023 104 2
156 1024 104 2
157 1025 105 2
158 1026 105 2
159 1027 105 2
160 1028 105 2
161 1029 105 2
162 1030 105 2
163 1031 106 2
164 1032 106 2
165 1033 106 2
166 1034 106 2
167 1035 106 2
168 1036 107 2
169 1037 107 2
170 1039 107 2
171 1041 107 2
172 1158 100 0
173 1180 105 2
174 1184 104 2
175 1194 105 2
176 1202 107 2
177 1204 107 2
178 update ignore t1 set id=1023 where id=1010;
179 Warnings:
180 Warning 1062 Duplicate entry '1023' for key 't1.PRIMARY'
181 select * from t1 where parent_id=102;
182 id parent_id level
183 1008 102 2
184 1010 102 2
185 1015 102 2
186 explain select level from t1 where level=1;
187 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
188 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index
189 Warnings:
190 Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1)
191 explain select level,id from t1 where level=1;
192 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
193 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index
194 Warnings:
195 Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`level` = 1)
196 explain select level,id,parent_id from t1 where level=1;
197 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
198 1 SIMPLE t1 NULL ref level level 1 const # 100.00 NULL
199 Warnings:
200 Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`parent_id` AS `parent_id` from `test`.`t1` where (`test`.`t1`.`level` = 1)
201 select level,id from t1 where level=1;
202 level id
203 1 1002
204 1 1003
205 1 1004
206 1 1005
207 1 1006
208 1 1007
209 select level,id,parent_id from t1 where level=1;
210 level id parent_id
211 1 1002 101
212 1 1003 101
213 1 1004 101
214 1 1005 101
215 1 1006 101
216 1 1007 101
217 optimize table t1;
218 Table Op Msg_type Msg_text
219 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
220 test.t1 optimize status OK
221 show keys from t1;
222 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
223 t1 0 PRIMARY 1 id A # NULL NULL BTREE YES NULL
224 t1 1 parent_id 1 parent_id A # NULL NULL BTREE YES NULL
225 t1 1 level 1 level A # NULL NULL BTREE YES NULL
226 drop table t1;
227 CREATE TABLE t1 (
228 gesuchnr int(11) DEFAULT '0' NOT NULL,
229 benutzer_id int(11) DEFAULT '0' NOT NULL,
230 PRIMARY KEY (gesuchnr,benutzer_id)
231 ) engine=innodb;
232 Warnings:
233 Warning 1681 Integer display width is deprecated and will be removed in a future release.
234 Warning 1681 Integer display width is deprecated and will be removed in a future release.
235 replace into t1 (gesuchnr,benutzer_id) values (2,1);
236 replace into t1 (gesuchnr,benutzer_id) values (1,1);
237 replace into t1 (gesuchnr,benutzer_id) values (1,1);
238 select * from t1;
239 gesuchnr benutzer_id
240 1 1
241 2 1
242 drop table t1;
243 create table t1 (a int) engine=innodb;
244 insert into t1 values (1), (2);
245 optimize table t1;
246 Table Op Msg_type Msg_text
247 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
248 test.t1 optimize status OK
249 delete from t1 where a = 1;
250 select * from t1;
251 a
252 2
253 check table t1;
254 Table Op Msg_type Msg_text
255 test.t1 check status OK
256 drop table t1;
257 create table t1 (a int,b varchar(20)) engine=innodb;
258 insert into t1 values (1,""), (2,"testing");
259 delete from t1 where a = 1;
260 select * from t1;
261 a b
262 2 testing
263 create index skr on t1 (a);
264 insert into t1 values (3,""), (4,"testing");
265 analyze table t1;
266 Table Op Msg_type Msg_text
267 test.t1 analyze status OK
268 show keys from t1;
269 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
270 t1 1 skr 1 a A # NULL NULL YES BTREE YES NULL
271 drop table t1;
272 create table t1 (a int,b varchar(20),key(a)) engine=innodb;
273 insert into t1 values (1,""), (2,"testing");
274 select * from t1 where a = 1;
275 a b
276 1
277 drop table t1;
278 create table t1 (n int not null primary key) engine=innodb;
279 set autocommit=0;
280 insert into t1 values (4);
281 rollback;
282 select n, "after rollback" from t1;
283 n after rollback
284 insert into t1 values (4);
285 commit;
286 select n, "after commit" from t1;
287 n after commit
288 4 after commit
289 commit;
290 insert into t1 values (5);
291 insert into t1 values (4);
292 ERROR 23000: Duplicate entry '4' for key 't1.PRIMARY'
293 commit;
294 select n, "after commit" from t1;
295 n after commit
296 4 after commit
297 5 after commit
298 set autocommit=1;
299 insert into t1 values (6);
300 insert into t1 values (4);
301 ERROR 23000: Duplicate entry '4' for key 't1.PRIMARY'
302 select n from t1;
303 n
304 4
305 5
306 6
307 set autocommit=0;
308 begin;
309 savepoint `my_savepoint`;
310 insert into t1 values (7);
311 savepoint `savept2`;
312 insert into t1 values (3);
313 select n from t1;
314 n
315 3
316 4
317 5
318 6
319 7
320 savepoint savept3;
321 rollback to savepoint savept2;
322 rollback to savepoint savept3;
323 ERROR 42000: SAVEPOINT savept3 does not exist
324 rollback to savepoint savept2;
325 release savepoint `my_savepoint`;
326 select n from t1;
327 n
328 4
329 5
330 6
331 7
332 rollback to savepoint `my_savepoint`;
333 ERROR 42000: SAVEPOINT my_savepoint does not exist
334 rollback to savepoint savept2;
335 ERROR 42000: SAVEPOINT savept2 does not exist
336 insert into t1 values (8);
337 savepoint sv;
338 commit;
339 savepoint sv;
340 set autocommit=1;
341 rollback;
342 drop table t1;
343 create table t1 (n int not null primary key) engine=innodb;
344 start transaction;
345 insert into t1 values (4);
346 flush tables with read lock;
347 commit;
348 unlock tables;
349 commit;
350 select * from t1;
351 n
352 4
353 drop table t1;
354 create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
355 begin;
356 insert into t1 values(1,'hamdouni');
357 select id as afterbegin_id,nom as afterbegin_nom from t1;
358 afterbegin_id afterbegin_nom
359 1 hamdouni
360 rollback;
361 select id as afterrollback_id,nom as afterrollback_nom from t1;
362 afterrollback_id afterrollback_nom
363 set autocommit=0;
364 insert into t1 values(2,'mysql');
365 select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
366 afterautocommit0_id afterautocommit0_nom
367 2 mysql
368 rollback;
369 select id as afterrollback_id,nom as afterrollback_nom from t1;
370 afterrollback_id afterrollback_nom
371 set autocommit=1;
372 drop table t1;
373 CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
374 insert into t1 values ('pippo', 12);
375 insert into t1 values ('pippo', 12);
376 ERROR 23000: Duplicate entry 'pippo' for key 't1.PRIMARY'
377 delete from t1;
378 delete from t1 where id = 'pippo';
379 select * from t1;
380 id val
381 insert into t1 values ('pippo', 12);
382 set autocommit=0;
383 delete from t1;
384 rollback;
385 select * from t1;
386 id val
387 pippo 12
388 delete from t1;
389 commit;
390 select * from t1;
391 id val
392 drop table t1;
393 create table t1 (a integer) engine=innodb;
394 start transaction;
395 rename table t1 to t2;
396 create table t1 (b integer) engine=innodb;
397 insert into t1 values (1);
398 rollback;
399 drop table t1;
400 rename table t2 to t1;
401 drop table t1;
402 set autocommit=1;
403 CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
404 INSERT INTO t1 VALUES (1, 'Jochen');
405 select * from t1;
406 ID NAME
407 1 Jochen
408 drop table t1;
409 CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
410 set autocommit=0;
411 INSERT INTO t1 SET _userid='[email protected]';
412 COMMIT;
413 SELECT * FROM t1;
414 _userid
415 [email protected]
416 SELECT _userid FROM t1 WHERE _userid='[email protected]';
417 _userid
418 [email protected]
419 drop table t1;
420 set autocommit=1;
421 CREATE TABLE t1 (
422 user_id int(10) DEFAULT '0' NOT NULL,
423 name varchar(100),
424 phone varchar(100),
425 ref_email varchar(100) DEFAULT '' NOT NULL,
426 detail varchar(200),
427 PRIMARY KEY (user_id,ref_email)
428 )engine=innodb;
429 Warnings:
430 Warning 1681 Integer display width is deprecated and will be removed in a future release.
431 INSERT INTO t1 VALUES (10292,'sanjeev','29153373','[email protected]','xxx'),(10292,'shirish','2333604','[email protected]','ddsds'),(10292,'sonali','323232','[email protected]','filmstar');
432 select * from t1 where user_id=10292;
433 user_id name phone ref_email detail
434 10292 sanjeev 29153373 [email protected] xxx
435 10292 shirish 2333604 [email protected] ddsds
436 10292 sonali 323232 [email protected] filmstar
437 INSERT INTO t1 VALUES (10291,'sanjeev','29153373','[email protected]','xxx'),(10293,'shirish','2333604','[email protected]','ddsds');
438 select * from t1 where user_id=10292;
439 user_id name phone ref_email detail
440 10292 sanjeev 29153373 [email protected] xxx
441 10292 shirish 2333604 [email protected] ddsds
442 10292 sonali 323232 [email protected] filmstar
443 select * from t1 where user_id>=10292;
444 user_id name phone ref_email detail
445 10292 sanjeev 29153373 [email protected] xxx
446 10292 shirish 2333604 [email protected] ddsds
447 10292 sonali 323232 [email protected] filmstar
448 10293 shirish 2333604 [email protected] ddsds
449 select * from t1 where user_id>10292;
450 user_id name phone ref_email detail
451 10293 shirish 2333604 [email protected] ddsds
452 select * from t1 where user_id<10292;
453 user_id name phone ref_email detail
454 10291 sanjeev 29153373 [email protected] xxx
455 drop table t1;
456 CREATE TABLE t1 (a int not null, b int not null,c int not null,
457 key(a),primary key(a,b), unique(c),key(a),unique(b));
458 Warnings:
459 Warning 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
460 show index from t1;
461 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
462 t1 0 PRIMARY 1 a A # NULL NULL BTREE YES NULL
463 t1 0 PRIMARY 2 b A # NULL NULL BTREE YES NULL
464 t1 0 c 1 c A # NULL NULL BTREE YES NULL
465 t1 0 b 1 b A # NULL NULL BTREE YES NULL
466 t1 1 a 1 a A # NULL NULL BTREE YES NULL
467 t1 1 a_2 1 a A # NULL NULL BTREE YES NULL
468 drop table t1;
469 create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
470 alter table t1 engine=innodb;
471 insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
472 select * from t1;
473 col1 col2
474 1 1
475 2 3
476 3 4
477 4 4
478 5 2
479 update t1 set col2='7' where col1='4';
480 select * from t1;
481 col1 col2
482 1 1
483 2 3
484 3 4
485 4 7
486 5 2
487 alter table t1 add co3 int not null;
488 select * from t1;
489 col1 col2 co3
490 1 1 0
491 2 3 0
492 3 4 0
493 4 7 0
494 5 2 0
495 update t1 set col2='9' where col1='2';
496 select * from t1;
497 col1 col2 co3
498 1 1 0
499 2 9 0
500 3 4 0
501 4 7 0
502 5 2 0
503 drop table t1;
504 create table t1 (a int not null , b int, primary key (a)) engine = innodb;
505 create table t2 (a int not null , b int, primary key (a)) engine = myisam;
506 insert into t1 VALUES (1,3) , (2,3), (3,3);
507 select * from t1;
508 a b
509 1 3
510 2 3
511 3 3
512 insert into t2 select * from t1;
513 select * from t2;
514 a b
515 1 3
516 2 3
517 3 3
518 delete from t1 where b = 3;
519 select * from t1;
520 a b
521 insert into t1 select * from t2;
522 select * from t1;
523 a b
524 1 3
525 2 3
526 3 3
527 select * from t2;
528 a b
529 1 3
530 2 3
531 3 3
532 drop table t1,t2;
533 CREATE TABLE t1 (
534 user_name varchar(12),
535 password text,
536 subscribed char(1),
537 user_id int(11) DEFAULT '0' NOT NULL,
538 quota bigint(20),
539 weight double,
540 access_date date,
541 access_time time,
542 approved datetime,
543 dummy_primary_key int(11) NOT NULL auto_increment,
544 PRIMARY KEY (dummy_primary_key)
545 ) ENGINE=innodb;
546 Warnings:
547 Warning 1681 Integer display width is deprecated and will be removed in a future release.
548 Warning 1681 Integer display width is deprecated and will be removed in a future release.
549 Warning 1681 Integer display width is deprecated and will be removed in a future release.
550 INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
551 INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
552 INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
553 INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
554 INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
555 select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
556 user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key
557 user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1
558 user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2
559 user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3
560 user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4
561 user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5
562 drop table t1;
563 CREATE TABLE t1 (
564 id int(11) NOT NULL auto_increment,
565 parent_id int(11) DEFAULT '0' NOT NULL,
566 level tinyint(4) DEFAULT '0' NOT NULL,
567 KEY (id),
568 KEY parent_id (parent_id),
569 KEY level (level)
570 ) engine=innodb;
571 Warnings:
572 Warning 1681 Integer display width is deprecated and will be removed in a future release.
573 Warning 1681 Integer display width is deprecated and will be removed in a future release.
574 Warning 1681 Integer display width is deprecated and will be removed in a future release.
575 INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
576 INSERT INTO t1 values (179,5,2);
577 update t1 set parent_id=parent_id+100;
578 select * from t1 where parent_id=102;
579 id parent_id level
580 8 102 2
581 9 102 2
582 15 102 2
583 update t1 set id=id+1000;
584 update t1 set id=1024 where id=1009;
585 select * from t1;
586 id parent_id level
587 1001 100 0
588 1002 101 1
589 1003 101 1
590 1004 101 1
591 1005 101 1
592 1006 101 1
593 1007 101 1
594 1008 102 2
595 1015 102 2
596 1016 103 2
597 1017 103 2
598 1018 103 2
599 1019 103 2
600 1020 103 2
601 1021 104 2
602 1022 104 2
603 1024 102 2
604 1024 104 2
605 1025 105 2
606 1026 105 2
607 1027 105 2
608 1028 105 2
609 1029 105 2
610 1030 105 2
611 1031 106 2
612 1032 106 2
613 1033 106 2
614 1034 106 2
615 1035 106 2
616 1036 107 2
617 1037 107 2
618 1038 107 2
619 1040 107 2
620 1157 100 0
621 1179 105 2
622 1183 104 2
623 1193 105 2
624 1202 107 2
625 1203 107 2
626 update ignore t1 set id=id+1;
627 select * from t1;
628 id parent_id level
629 1002 100 0
630 1003 101 1
631 1004 101 1
632 1005 101 1
633 1006 101 1
634 1007 101 1
635 1008 101 1
636 1009 102 2
637 1016 102 2
638 1017 103 2
639 1018 103 2
640 1019 103 2
641 1020 103 2
642 1021 103 2
643 1022 104 2
644 1023 104 2
645 1025 102 2
646 1025 104 2
647 1026 105 2
648 1027 105 2
649 1028 105 2
650 1029 105 2
651 1030 105 2
652 1031 105 2
653 1032 106 2
654 1033 106 2
655 1034 106 2
656 1035 106 2
657 1036 106 2
658 1037 107 2
659 1038 107 2
660 1039 107 2
661 1041 107 2
662 1158 100 0
663 1180 105 2
664 1184 104 2
665 1194 105 2
666 1203 107 2
667 1204 107 2
668 update ignore t1 set id=1023 where id=1010;
669 select * from t1 where parent_id=102;
670 id parent_id level
671 1009 102 2
672 1025 102 2
673 1016 102 2
674 explain select level from t1 where level=1;
675 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
676 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index
677 Warnings:
678 Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1)
679 select level,id from t1 where level=1;
680 level id
681 1 1003
682 1 1004
683 1 1005
684 1 1006
685 1 1007
686 1 1008
687 select level,id,parent_id from t1 where level=1;
688 level id parent_id
689 1 1003 101
690 1 1004 101
691 1 1005 101
692 1 1006 101
693 1 1007 101
694 1 1008 101
695 select level,id from t1 where level=1 order by id;
696 level id
697 1 1003
698 1 1004
699 1 1005
700 1 1006
701 1 1007
702 1 1008
703 delete from t1 where level=1;
704 select * from t1;
705 id parent_id level
706 1002 100 0
707 1009 102 2
708 1016 102 2
709 1017 103 2
710 1018 103 2
711 1019 103 2
712 1020 103 2
713 1021 103 2
714 1022 104 2
715 1023 104 2
716 1025 102 2
717 1025 104 2
718 1026 105 2
719 1027 105 2
720 1028 105 2
721 1029 105 2
722 1030 105 2
723 1031 105 2
724 1032 106 2
725 1033 106 2
726 1034 106 2
727 1035 106 2
728 1036 106 2
729 1037 107 2
730 1038 107 2
731 1039 107 2
732 1041 107 2
733 1158 100 0
734 1180 105 2
735 1184 104 2
736 1194 105 2
737 1203 107 2
738 1204 107 2
739 drop table t1;
740 CREATE TABLE t1 (
741 sca_code char(6) NOT NULL,
742 cat_code char(6) NOT NULL,
743 sca_desc varchar(50),
744 lan_code char(2) NOT NULL,
745 sca_pic varchar(100),
746 sca_sdesc varchar(50),
747 sca_sch_desc varchar(16),
748 PRIMARY KEY (sca_code, cat_code, lan_code),
749 INDEX sca_pic (sca_pic)
750 ) engine = innodb ;
751 INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
752 select count(*) from t1 where sca_code = 'PD';
753 count(*)
754 1
755 select count(*) from t1 where sca_code <= 'PD';
756 count(*)
757 1
758 select count(*) from t1 where sca_pic is null;
759 count(*)
760 2
761 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
762 select count(*) from t1 where sca_code='PD' and sca_pic is null;
763 count(*)
764 1
765 select count(*) from t1 where cat_code='E';
766 count(*)
767 0
768 alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
769 select count(*) from t1 where sca_code='PD' and sca_pic is null;
770 count(*)
771 1
772 select count(*) from t1 where sca_pic >= 'n';
773 count(*)
774 1
775 select sca_pic from t1 where sca_pic is null;
776 sca_pic
777 NULL
778 NULL
779 update t1 set sca_pic="test" where sca_pic is null;
780 delete from t1 where sca_code='pd';
781 drop table t1;
782 set @a:=now();
783 CREATE TABLE t1 (a int not null, b timestamp not null default
784 current_timestamp on update current_timestamp, primary key (a)) engine=innodb;
785 insert into t1 (a) values(1),(2),(3);
786 select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
787 a
788 1
789 2
790 3
791 select a from t1 natural join t1 as t2 where b >= @a order by a;
792 a
793 1
794 2
795 3
796 update t1 set a=5 where a=1;
797 select a from t1;
798 a
799 2
800 3
801 5
802 drop table t1;
803 create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
804 insert into t1 values("hello",1),("world",2);
805 select * from t1 order by b desc;
806 a b
807 world 2
808 hello 1
809 optimize table t1;
810 Table Op Msg_type Msg_text
811 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
812 test.t1 optimize status OK
813 show keys from t1;
814 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
815 t1 0 PRIMARY 1 a A # NULL NULL BTREE YES NULL
816 drop table t1;
817 create table t1 (i int, j int ) ENGINE=innodb;
818 insert into t1 values (1,2);
819 select * from t1 where i=1 and j=2;
820 i j
821 1 2
822 create index ax1 on t1 (i,j);
823 select * from t1 where i=1 and j=2;
824 i j
825 1 2
826 drop table t1;
827 CREATE TABLE t1 (
828 a int3 unsigned NOT NULL,
829 b int1 unsigned NOT NULL,
830 UNIQUE (a, b)
831 ) ENGINE = innodb;
832 INSERT INTO t1 VALUES (1, 1);
833 SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
834 MIN(B) MAX(b)
835 1 1
836 drop table t1;
837 create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;
838 insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
839 explain select * from t1 where a > 0 and a < 50;
840 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
841 1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where
842 Warnings:
843 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`e` AS `e`,`test`.`t1`.`f` AS `f`,`test`.`t1`.`g` AS `g`,`test`.`t1`.`h` AS `h`,`test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j`,`test`.`t1`.`k` AS `k`,`test`.`t1`.`l` AS `l`,`test`.`t1`.`m` AS `m`,`test`.`t1`.`n` AS `n`,`test`.`t1`.`o` AS `o`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`q` AS `q`,`test`.`t1`.`r` AS `r`,`test`.`t1`.`s` AS `s`,`test`.`t1`.`t` AS `t`,`test`.`t1`.`u` AS `u`,`test`.`t1`.`v` AS `v`,`test`.`t1`.`w` AS `w`,`test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t1`.`z` AS `z`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`a3` AS `a3`,`test`.`t1`.`a4` AS `a4`,`test`.`t1`.`a5` AS `a5`,`test`.`t1`.`a6` AS `a6`,`test`.`t1`.`a7` AS `a7`,`test`.`t1`.`a8` AS `a8`,`test`.`t1`.`a9` AS `a9`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`b2` AS `b2`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`b4` AS `b4`,`test`.`t1`.`b5` AS `b5`,`test`.`t1`.`b6` AS `b6` from `test`.`t1` where ((`test`.`t1`.`a` > 0) and (`test`.`t1`.`a` < 50))
844 drop table t1;
845 create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
846 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
847 LOCK TABLES t1 WRITE;
848 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
849 ERROR 23000: Duplicate entry '1-1' for key 't1.PRIMARY'
850 select id from t1;
851 id
852 0
853 1
854 2
855 select id from t1;
856 id
857 0
858 1
859 2
860 UNLOCK TABLES;
861 DROP TABLE t1;
862 create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
863 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
864 LOCK TABLES t1 WRITE;
865 begin;
866 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
867 ERROR 23000: Duplicate entry '1-1' for key 't1.PRIMARY'
868 select id from t1;
869 id
870 0
871 1
872 2
873 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
874 Warnings:
875 Warning 1062 Duplicate entry '1-1' for key 't1.PRIMARY'
876 commit;
877 select id,id3 from t1;
878 id id3
879 0 0
880 1 1
881 2 2
882 100 2
883 UNLOCK TABLES;
884 DROP TABLE t1;
885 create table t1 (a char(20), unique (a(5))) engine=innodb;
886 drop table t1;
887 create table t1 (a char(20), index (a(5))) engine=innodb;
888 show create table t1;
889 Table Create Table
890 t1 CREATE TABLE `t1` (
891 `a` char(20) DEFAULT NULL,
892 KEY `a` (`a`(5))
893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
894 drop table t1;
895 create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
896 insert into t1 values (NULL),(NULL),(NULL);
897 delete from t1 where a=3;
898 insert into t1 values (NULL);
899 select * from t1;
900 a
901 1
902 2
903 4
904 alter table t1 add b int;
905 select * from t1;
906 a b
907 1 NULL
908 2 NULL
909 4 NULL
910 drop table t1;
911 create table t1
912 (
913 id int auto_increment primary key,
914 name varchar(32) not null,
915 value text not null,
916 uid int not null,
917 unique key(name,uid)
918 ) engine=innodb;
919 insert into t1 values (1,'one','one value',101),
920 (2,'two','two value',102),(3,'three','three value',103);
921 set insert_id=5;
922 replace into t1 (value,name,uid) values ('other value','two',102);
923 delete from t1 where uid=102;
924 set insert_id=5;
925 replace into t1 (value,name,uid) values ('other value','two',102);
926 set insert_id=6;
927 replace into t1 (value,name,uid) values ('other value','two',102);
928 select * from t1;
929 id name value uid
930 1 one one value 101
931 3 three three value 103
932 6 two other value 102
933 drop table t1;
934 create database mysqltest;
935 create table mysqltest.t1 (a int not null) engine= innodb;
936 insert into mysqltest.t1 values(1);
937 create table mysqltest.t2 (a int not null) engine= myisam;
938 insert into mysqltest.t2 values(1);
939 create table mysqltest.t3 (a int not null) engine= heap;
940 insert into mysqltest.t3 values(1);
941 commit;
942 drop database mysqltest;
943 show tables from mysqltest;
944 ERROR 42000: Unknown database 'mysqltest'
945 set autocommit=0;
946 create table t1 (a int not null) engine= innodb;
947 insert into t1 values(1),(2);
948 truncate table t1;
949 commit;
950 truncate table t1;
951 truncate table t1;
952 select * from t1;
953 a
954 insert into t1 values(1),(2);
955 delete from t1;
956 select * from t1;
957 a
958 commit;
959 drop table t1;
960 set autocommit=1;
961 create table t1 (a int not null) engine= innodb;
962 insert into t1 values(1),(2);
963 truncate table t1;
964 insert into t1 values(1),(2);
965 select * from t1;
966 a
967 1
968 2
969 truncate table t1;
970 insert into t1 values(1),(2);
971 delete from t1;
972 select * from t1;
973 a
974 drop table t1;
975 create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
976 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
977 explain select * from t1 order by a;
978 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
979 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 NULL
980 Warnings:
981 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`
982 explain select * from t1 order by b;
983 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
984 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort
985 Warnings:
986 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`b`
987 explain select * from t1 order by c;
988 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
989 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort
990 Warnings:
991 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`c`
992 explain select a from t1 order by a;
993 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
994 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index
995 Warnings:
996 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a`
997 explain select b from t1 order by b;
998 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
999 1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index
1000 Warnings:
1001 Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b`
1002 explain select a,b from t1 order by b;
1003 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1004 1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index
1005 Warnings:
1006 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b`
1007 explain select a,b from t1;
1008 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1009 1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index
1010 Warnings:
1011 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
1012 explain select a,b,c from t1;
1013 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1014 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1015 Warnings:
1016 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1`
1017 drop table t1;
1018 create table t1 (t int not null default 1, key (t)) engine=innodb;
1019 desc t1;
1020 Field Type Null Key Default Extra
1021 t int NO MUL 1
1022 drop table t1;
1023 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
1024 CREATE TABLE t1 (
1025 number bigint(20) NOT NULL default '0',
1026 cname char(15) NOT NULL default '',
1027 carrier_id smallint(6) NOT NULL default '0',
1028 privacy tinyint(4) NOT NULL default '0',
1029 last_mod_date timestamp NOT NULL,
1030 last_mod_id smallint(6) NOT NULL default '0',
1031 last_app_date timestamp NOT NULL,
1032 last_app_id smallint(6) default '-1',
1033 version smallint(6) NOT NULL default '0',
1034 assigned_scps int(11) default '0',
1035 status tinyint(4) default '0'
1036 ) ENGINE=InnoDB;
1037 Warnings:
1038 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1039 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1040 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1041 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1042 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1043 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1044 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1045 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1046 INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
1047 INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
1048 INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
1049 INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
1050 INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
1051 INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
1052 CREATE TABLE t2 (
1053 number bigint(20) NOT NULL default '0',
1054 cname char(15) NOT NULL default '',
1055 carrier_id smallint(6) NOT NULL default '0',
1056 privacy tinyint(4) NOT NULL default '0',
1057 last_mod_date timestamp NOT NULL,
1058 last_mod_id smallint(6) NOT NULL default '0',
1059 last_app_date timestamp NOT NULL,
1060 last_app_id smallint(6) default '-1',
1061 version smallint(6) NOT NULL default '0',
1062 assigned_scps int(11) default '0',
1063 status tinyint(4) default '0'
1064 ) ENGINE=InnoDB;
1065 Warnings:
1066 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1067 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1068 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1069 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1070 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1071 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1072 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1073 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1074 INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
1075 INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
1076 INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
1077 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
1078 select * from t1;
1079 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1080 302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0
1081 333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
1082 4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1
1083 6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
1084 650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1
1085 9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0
1086 select * from t2;
1087 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1088 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1089 4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1
1090 650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1
1091 9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0
1092 delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);
1093 select * from t1;
1094 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1095 6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
1096 333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
1097 select * from t2;
1098 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1099 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1100 select * from t2;
1101 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1102 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1103 drop table t1,t2;
1104 SET sql_mode = default;
1105 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
1106 BEGIN;
1107 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1108 SELECT @@transaction_isolation,@@global.transaction_isolation;
1109 @@transaction_isolation @@global.transaction_isolation
1110 SERIALIZABLE REPEATABLE-READ
1111 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
1112 select id, code, name from t1 order by id;
1113 id code name
1114 1 1 Tim
1115 2 1 Monty
1116 3 2 David
1117 COMMIT;
1118 BEGIN;
1119 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1120 insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
1121 select id, code, name from t1 order by id;
1122 id code name
1123 1 1 Tim
1124 2 1 Monty
1125 3 2 David
1126 4 2 Erik
1127 5 3 Sasha
1128 COMMIT;
1129 SET binlog_format='MIXED';
1130 Warnings:
1131 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
1132 BEGIN;
1133 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1134 insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
1135 select id, code, name from t1 order by id;
1136 id code name
1137 1 1 Tim
1138 2 1 Monty
1139 3 2 David
1140 4 2 Erik
1141 5 3 Sasha
1142 6 3 Jeremy
1143 7 4 Matt
1144 COMMIT;
1145 DROP TABLE t1;
1146 create table t1 (n int(10), d int(10)) engine=innodb;
1147 Warnings:
1148 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1149 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1150 create table t2 (n int(10), d int(10)) engine=innodb;
1151 Warnings:
1152 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1153 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1154 insert into t1 values(1,1),(1,2);
1155 insert into t2 values(1,10),(2,20);
1156 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
1157 select * from t1;
1158 n d
1159 1 10
1160 1 10
1161 select * from t2;
1162 n d
1163 1 30
1164 2 20
1165 drop table t1,t2;
1166 drop table if exists t1, t2;
1167 CREATE TABLE t1 (a int, PRIMARY KEY (a));
1168 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1169 create trigger trg_del_t2 after delete on t2 for each row
1170 insert into t1 values (1);
1171 insert into t1 values (1);
1172 insert into t2 values (1),(2);
1173 delete t2 from t2;
1174 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
1175 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1176 count(*)
1177 2
1178 drop table t1, t2;
1179 drop table if exists t1, t2;
1180 CREATE TABLE t1 (a int, PRIMARY KEY (a));
1181 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1182 create trigger trg_del_t2 after delete on t2 for each row
1183 insert into t1 values (1);
1184 insert into t1 values (1);
1185 insert into t2 values (1),(2);
1186 delete t2 from t2;
1187 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
1188 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1189 count(*)
1190 2
1191 drop table t1, t2;
1192 create table t1 (a int, b int) engine=innodb;
1193 insert into t1 values(20,null);
1194 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1195 t2.b=t3.a;
1196 b ifnull(t2.b,"this is null")
1197 NULL this is null
1198 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1199 t2.b=t3.a order by 1;
1200 b ifnull(t2.b,"this is null")
1201 NULL this is null
1202 insert into t1 values(10,null);
1203 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1204 t2.b=t3.a order by 1;
1205 b ifnull(t2.b,"this is null")
1206 NULL this is null
1207 NULL this is null
1208 drop table t1;
1209 create table t1 (a varchar(10) not null) engine=myisam;
1210 create table t2 (b varchar(10) not null unique) engine=innodb;
1211 select t1.a from t1,t2 where t1.a=t2.b;
1212 a
1213 drop table t1,t2;
1214 create table t1 (a int not null, b int, primary key (a)) engine = innodb;
1215 create table t2 (a int not null, b int, primary key (a)) engine = innodb;
1216 insert into t1 values (10, 20);
1217 insert into t2 values (10, 20);
1218 update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
1219 drop table t1,t2;
1220 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1221 CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;
1222 insert into t1 set id=1;
1223 insert into t2 set id=1, t1_id=1;
1224 delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
1225 select * from t1;
1226 id
1227 select * from t2;
1228 id t1_id
1229 drop table t2,t1;
1230 CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1231 CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
1232 INSERT INTO t1 VALUES(1);
1233 INSERT INTO t2 VALUES(1, 1);
1234 SELECT * from t1;
1235 id
1236 1
1237 UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
1238 SELECT * from t1;
1239 id
1240 2
1241 UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
1242 SELECT * from t1;
1243 id
1244 3
1245 DROP TABLE t1,t2;
1246 set autocommit=0;
1247 CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1248 CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1249 CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
1250 INSERT INTO t3 VALUES("my-test-1", "my-test-2");
1251 COMMIT;
1252 INSERT INTO t1 VALUES("this-key", "will disappear");
1253 INSERT INTO t2 VALUES("this-key", "will also disappear");
1254 DELETE FROM t3 WHERE id1="my-test-1";
1255 SELECT * FROM t1;
1256 id value
1257 this-key will disappear
1258 SELECT * FROM t2;
1259 id value
1260 this-key will also disappear
1261 SELECT * FROM t3;
1262 id1 id2
1263 ROLLBACK;
1264 SELECT * FROM t1;
1265 id value
1266 SELECT * FROM t2;
1267 id value
1268 SELECT * FROM t3;
1269 id1 id2
1270 my-test-1 my-test-2
1271 SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
1272 id1 id2
1273 my-test-1 my-test-2
1274 COMMIT;
1275 set autocommit=1;
1276 DROP TABLE t1,t2,t3;
1277 CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
1278 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1279 UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1280 SELECT * from t1;
1281 a b
1282 1 1
1283 102 2
1284 103 3
1285 4 4
1286 5 5
1287 6 6
1288 7 7
1289 8 8
1290 9 9
1291 drop table t1;
1292 CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
1293 CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1294 SET AUTOCOMMIT=0;
1295 INSERT INTO t1 ( B_ID ) VALUES ( 1 );
1296 INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
1297 ROLLBACK;
1298 Warnings:
1299 Warning 1196 Some non-transactional changed tables couldn't be rolled back
1300 SELECT * FROM t1;
1301 B_ID
1302 drop table t1,t2;
1303 create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
1304 insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
1305 select distinct parent,child from t1 order by parent;
1306 parent child
1307 0 4
1308 1 2
1309 1 3
1310 2 1
1311 drop table t1;
1312 create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1313 create table t2 (a int not null auto_increment primary key, b int);
1314 insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1315 insert into t2 (a) select b from t1;
1316 insert into t1 (b) select b from t2;
1317 insert into t2 (a) select b from t1;
1318 insert into t1 (a) select b from t2;
1319 insert into t2 (a) select b from t1;
1320 insert into t1 (a) select b from t2;
1321 insert into t2 (a) select b from t1;
1322 insert into t1 (a) select b from t2;
1323 insert into t2 (a) select b from t1;
1324 insert into t1 (a) select b from t2;
1325 select count(*) from t1;
1326 count(*)
1327 623
1328 explain select * from t1 where c between 1 and 2500;
1329 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1330 1 SIMPLE t1 NULL range c c 5 NULL # 100.00 Using index condition
1331 Warnings:
1332 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500)
1333 update t1 set c=a;
1334 explain select * from t1 where c between 1 and 2500;
1335 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1336 1 SIMPLE t1 NULL ALL c NULL NULL NULL # # Using where
1337 Warnings:
1338 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500)
1339 drop table t1,t2;
1340 create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1341 insert into t1 (id) values (null),(null),(null),(null),(null);
1342 update t1 set fk=69 where fk is null order by id limit 1;
1343 SELECT * from t1;
1344 id fk
1345 1 69
1346 2 NULL
1347 3 NULL
1348 4 NULL
1349 5 NULL
1350 drop table t1;
1351 create table t1 (a int not null, b int not null, key (a));
1352 insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
1353 SET @tmp=0;
1354 update t1 set b=(@tmp:=@tmp+1) order by a;
1355 Warnings:
1356 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
1357 update t1 set b=99 where a=1 order by b asc limit 1;
1358 update t1 set b=100 where a=1 order by b desc limit 2;
1359 update t1 set a=a+10+b where a=1 order by b;
1360 select * from t1 order by a,b;
1361 a b
1362 2 4
1363 2 5
1364 2 6
1365 3 7
1366 3 8
1367 3 9
1368 3 10
1369 3 11
1370 3 12
1371 13 2
1372 111 100
1373 111 100
1374 drop table t1;
1375 create table t1 ( c char(8) not null ) engine=innodb;
1376 insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
1377 insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
1378 alter table t1 add b char(8) not null;
1379 alter table t1 add a char(8) not null;
1380 alter table t1 add primary key (a,b,c);
1381 update t1 set a=c, b=c;
1382 create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;
1383 insert into t2 select * from t1;
1384 delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1385 drop table t1,t2;
1386 SET AUTOCOMMIT=1;
1387 create table t1 (a integer auto_increment primary key) engine=innodb;
1388 insert into t1 (a) values (NULL),(NULL);
1389 truncate table t1;
1390 insert into t1 (a) values (NULL),(NULL);
1391 SELECT * from t1;
1392 a
1393 1
1394 2
1395 drop table t1;
1396 CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1397 CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB;
1398 drop table t2,t1;
1399 create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1400 Warnings:
1401 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1402 insert into `t1`values ( 1 ) ;
1403 create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
1404 Warnings:
1405 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1406 insert into `t2`values ( 1 ) ;
1407 create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
1408 Warnings:
1409 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1410 insert into `t3`values ( 1 ) ;
1411 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1412 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
1413 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1414 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
1415 update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1416 ERROR 42S22: Unknown column 't1.id' in 'where clause'
1417 drop table t3,t2,t1;
1418 CREATE TABLE t1 (
1419 c1 VARCHAR(8), c2 VARCHAR(8),
1420 PRIMARY KEY (c1, c2)
1421 ) ENGINE=InnoDB;
1422 CREATE TABLE t2 (
1423 c0 INT PRIMARY KEY,
1424 c1 VARCHAR(8) UNIQUE,
1425 FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE
1426 ) ENGINE=InnoDB;
1427 INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue');
1428 INSERT INTO t2 VALUES (10, 'old'), (20, 'other');
1429 UPDATE t1 SET c1 = 'other' WHERE c1 = 'old';
1430 ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'c1'
1431 DROP TABLE t2,t1;
1432 create table t1(
1433 id int primary key,
1434 pid int,
1435 index(pid),
1436 foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1437 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1438 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1439 delete from t1 where id=0;
1440 ERROR HY000: Foreign key cascade delete/update exceeds max depth of 15.
1441 delete from t1 where id=15;
1442 delete from t1 where id=0;
1443 drop table t1;
1444 CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1445 Warnings:
1446 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1447 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1448 (stamp))ENGINE=InnoDB;
1449 Warnings:
1450 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1451 insert into t1 values (1),(2),(3);
1452 insert ignore into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1453 Warnings:
1454 Warning 1265 Data truncated for column 'stamp' at row 3
1455 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1456 '20020204120000' GROUP BY col1;
1457 col1
1458 1
1459 2
1460 3
1461 4
1462 drop table t1,t2;
1463 CREATE TABLE t1 (
1464 `id` int(10) unsigned NOT NULL auto_increment,
1465 `id_object` int(10) unsigned default '0',
1466 `id_version` int(10) unsigned NOT NULL default '1',
1467 `label` varchar(100) NOT NULL default '',
1468 `description` text,
1469 PRIMARY KEY (`id`),
1470 KEY `id_object` (`id_object`),
1471 KEY `id_version` (`id_version`)
1472 ) ENGINE=InnoDB;
1473 Warnings:
1474 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1475 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1476 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1477 INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);
1478 CREATE TABLE t2 (
1479 `id` int(10) unsigned NOT NULL auto_increment,
1480 `id_version` int(10) unsigned NOT NULL default '1',
1481 PRIMARY KEY (`id`),
1482 KEY `id_version` (`id_version`)
1483 ) ENGINE=InnoDB;
1484 Warnings:
1485 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1486 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1487 INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1488 SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1489 (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1490 ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object) ORDER BY 1;
1491 id label
1492 102 Le Pekin (Test)
1493 1794 Test de resto
1494 1822 Test 3
1495 3382 Test
1496 3524 Societe Test
1497 3525 Fournisseur Test
1498 drop table t1,t2;
1499 create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1500 create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1501 create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1502 insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1503 insert t2 select * from t1;
1504 insert t3 select * from t1;
1505 checksum table t1, t2, t3, t4 quick;
1506 Table Checksum
1507 test.t1 2948697075
1508 test.t2 NULL
1509 test.t3 NULL
1510 test.t4 NULL
1511 Warnings:
1512 Error 1146 Table 'test.t4' doesn't exist
1513 checksum table t1, t2, t3, t4;
1514 Table Checksum
1515 test.t1 2948697075
1516 test.t2 2948697075
1517 test.t3 2948697075
1518 test.t4 NULL
1519 Warnings:
1520 Error 1146 Table 'test.t4' doesn't exist
1521 checksum table t1, t2, t3, t4 extended;
1522 Table Checksum
1523 test.t1 2948697075
1524 test.t2 2948697075
1525 test.t3 2948697075
1526 test.t4 NULL
1527 Warnings:
1528 Error 1146 Table 'test.t4' doesn't exist
1529 drop table t1,t2,t3;
1530 create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1531 insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1532 select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1533 trim(name2)
1534 fff
1535 sss
1536 ttt
1537 first
1538 second
1539 third
1540 1
1541 2
1542 3
1543 drop table t1;
1544 create table t1 (a int) engine=innodb;
1545 create table t2 like t1;
1546 drop table t1,t2;
1547 create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1548 Warnings:
1549 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1550 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1551 create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1552 Warnings:
1553 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1554 show create table t1;
1555 Table Create Table
1556 t1 CREATE TABLE `t1` (
1557 `id` int NOT NULL,
1558 `id2` int NOT NULL,
1559 UNIQUE KEY `id` (`id`,`id2`)
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1561 show create table t2;
1562 Table Create Table
1563 t2 CREATE TABLE `t2` (
1564 `id` int NOT NULL,
1565 KEY `t1_id_fk` (`id`),
1566 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1568 create index id on t2 (id);
1569 show create table t2;
1570 Table Create Table
1571 t2 CREATE TABLE `t2` (
1572 `id` int NOT NULL,
1573 KEY `id` (`id`),
1574 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1575 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1576 create index id2 on t2 (id);
1577 Warnings:
1578 Warning 1831 Duplicate index 'id2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
1579 show create table t2;
1580 Table Create Table
1581 t2 CREATE TABLE `t2` (
1582 `id` int NOT NULL,
1583 KEY `id` (`id`),
1584 KEY `id2` (`id`),
1585 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1586 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1587 drop index id2 on t2;
1588 drop index id on t2;
1589 ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint
1590 show create table t2;
1591 Table Create Table
1592 t2 CREATE TABLE `t2` (
1593 `id` int NOT NULL,
1594 KEY `id` (`id`),
1595 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1596 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1597 drop table t2;
1598 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
1599 Warnings:
1600 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1601 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1602 show create table t2;
1603 Table Create Table
1604 t2 CREATE TABLE `t2` (
1605 `id` int NOT NULL,
1606 `id2` int NOT NULL,
1607 KEY `t1_id_fk` (`id`,`id2`),
1608 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1610 create unique index id on t2 (id,id2);
1611 show create table t2;
1612 Table Create Table
1613 t2 CREATE TABLE `t2` (
1614 `id` int NOT NULL,
1615 `id2` int NOT NULL,
1616 UNIQUE KEY `id` (`id`,`id2`),
1617 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1618 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1619 drop table t2;
1620 create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1621 Warnings:
1622 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1623 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1624 show create table t2;
1625 Table Create Table
1626 t2 CREATE TABLE `t2` (
1627 `id` int NOT NULL,
1628 `id2` int NOT NULL,
1629 UNIQUE KEY `id` (`id`,`id2`),
1630 KEY `t1_id_fk` (`id2`,`id`),
1631 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1633 drop table t2;
1634 create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
1635 Warnings:
1636 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1637 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1638 show create table t2;
1639 Table Create Table
1640 t2 CREATE TABLE `t2` (
1641 `id` int NOT NULL,
1642 `id2` int NOT NULL,
1643 UNIQUE KEY `id` (`id`,`id2`),
1644 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1646 drop table t2;
1647 create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1648 Warnings:
1649 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1650 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1651 show create table t2;
1652 Table Create Table
1653 t2 CREATE TABLE `t2` (
1654 `id` int NOT NULL,
1655 `id2` int NOT NULL,
1656 UNIQUE KEY `id` (`id`,`id2`),
1657 KEY `t1_id_fk` (`id2`,`id`),
1658 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1659 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1660 drop table t2;
1661 create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
1662 Warnings:
1663 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1664 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1665 show create table t2;
1666 Table Create Table
1667 t2 CREATE TABLE `t2` (
1668 `id` int NOT NULL AUTO_INCREMENT,
1669 `id2` int NOT NULL,
1670 PRIMARY KEY (`id`),
1671 KEY `id` (`id`,`id2`),
1672 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1674 drop table t2;
1675 create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
1676 Warnings:
1677 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1678 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1679 show create table t2;
1680 Table Create Table
1681 t2 CREATE TABLE `t2` (
1682 `id` int NOT NULL AUTO_INCREMENT,
1683 `id2` int NOT NULL,
1684 KEY `t1_id_fk` (`id`),
1685 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1686 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1687 alter table t2 add index id_test (id), add index id_test2 (id,id2);
1688 show create table t2;
1689 Table Create Table
1690 t2 CREATE TABLE `t2` (
1691 `id` int NOT NULL AUTO_INCREMENT,
1692 `id2` int NOT NULL,
1693 KEY `id_test` (`id`),
1694 KEY `id_test2` (`id`,`id2`),
1695 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1696 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1697 drop table t2;
1698 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
1699 ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1700 create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1701 show create table t2;
1702 Table Create Table
1703 t2 CREATE TABLE `t2` (
1704 `a` int NOT NULL AUTO_INCREMENT,
1705 `b` int DEFAULT NULL,
1706 PRIMARY KEY (`a`),
1707 UNIQUE KEY `b_2` (`b`),
1708 KEY `b` (`b`),
1709 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1711 drop table t2;
1712 create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;
1713 show create table t2;
1714 Table Create Table
1715 t2 CREATE TABLE `t2` (
1716 `a` int NOT NULL AUTO_INCREMENT,
1717 `b` int DEFAULT NULL,
1718 PRIMARY KEY (`a`),
1719 UNIQUE KEY `b` (`b`),
1720 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1721 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1723 drop table t2, t1;
1724 create table t1 (c char(10), index (c,c)) engine=innodb;
1725 ERROR 42S21: Duplicate column name 'c'
1726 create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1727 ERROR 42S21: Duplicate column name 'c1'
1728 create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1729 ERROR 42S21: Duplicate column name 'c1'
1730 create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1731 ERROR 42S21: Duplicate column name 'c1'
1732 create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1733 alter table t1 add key (c1,c1);
1734 ERROR 42S21: Duplicate column name 'c1'
1735 alter table t1 add key (c2,c1,c1);
1736 ERROR 42S21: Duplicate column name 'c1'
1737 alter table t1 add key (c1,c2,c1);
1738 ERROR 42S21: Duplicate column name 'c1'
1739 alter table t1 add key (c1,c1,c2);
1740 ERROR 42S21: Duplicate column name 'c1'
1741 drop table t1;
1742 CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1743 INSERT INTO t1 VALUES (1),(2),(3);
1744 CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),
1745 CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1746 INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1747 SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;
1748 a_id b_list
1749 1 1,2,3
1750 2 4,5
1751 3 NULL
1752 DROP TABLE t2;
1753 DROP TABLE t1;
1754 create temporary table t1 (a int) engine=innodb;
1755 insert into t1 values (4711);
1756 truncate t1;
1757 insert into t1 values (42);
1758 select * from t1;
1759 a
1760 42
1761 drop table t1;
1762 create table t1 (a int) engine=innodb;
1763 insert into t1 values (4711);
1764 truncate t1;
1765 insert into t1 values (42);
1766 select * from t1;
1767 a
1768 42
1769 drop table t1;
1770 create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;
1771 insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1772 analyze table t1;
1773 select * from t1 order by a,b,c,d;
1774 a b c d e
1775 1 1 a 1 1
1776 2 2 b 2 2
1777 3 3 ab 3 3
1778 explain select * from t1 order by a,b,c,d;
1779 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1780 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using filesort
1781 Warnings:
1782 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`e` AS `e` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`,`test`.`t1`.`d`
1783 drop table t1;
1784 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1785 insert into t1 values ('8', '6'), ('4', '7');
1786 select min(a) from t1;
1787 min(a)
1788 4
1789 select min(b) from t1 where a='8';
1790 min(b)
1791 6
1792 drop table t1;
1793 create table t1 (x bigint unsigned not null primary key) engine=innodb;
1794 insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1795 select * from t1;
1796 x
1797 18446744073709551600
1798 18446744073709551601
1799 select count(*) from t1 where x>0;
1800 count(*)
1801 2
1802 select count(*) from t1 where x=0;
1803 count(*)
1804 0
1805 select count(*) from t1 where x<0;
1806 count(*)
1807 0
1808 select count(*) from t1 where x < -16;
1809 count(*)
1810 0
1811 select count(*) from t1 where x = -16;
1812 count(*)
1813 0
1814 explain select count(*) from t1 where x > -16;
1815 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1816 1 SIMPLE t1 NULL index NULL PRIMARY 8 NULL 2 100.00 Using index
1817 Warnings:
1818 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where true
1819 select count(*) from t1 where x > -16;
1820 count(*)
1821 2
1822 select * from t1 where x > -16;
1823 x
1824 18446744073709551600
1825 18446744073709551601
1826 select count(*) from t1 where x = 18446744073709551601;
1827 count(*)
1828 1
1829 drop table t1;
1830 SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
1831 variable_value
1832 {checked_valid}
1833 SELECT variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
1834 variable_value
1835 {checked_valid}
1836 SELECT sum(variable_value) - @innodb_rows_deleted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_deleted';
1837 sum(variable_value) - @innodb_rows_deleted_orig
1838 1830
1839 SELECT sum(variable_value) - @innodb_rows_inserted_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_inserted';
1840 sum(variable_value) - @innodb_rows_inserted_orig
1841 2733
1842 SELECT sum(variable_value) - @innodb_rows_updated_orig FROM performance_schema.global_status WHERE LOWER(variable_name) like 'innodb%_rows_updated';
1843 sum(variable_value) - @innodb_rows_updated_orig
1844 2635
1845 SELECT variable_value - @innodb_row_lock_waits_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
1846 variable_value - @innodb_row_lock_waits_orig
1847 0
1848 SELECT variable_value - @innodb_row_lock_current_waits_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
1849 variable_value - @innodb_row_lock_current_waits_orig
1850 0
1851 SELECT variable_value - @innodb_row_lock_time_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
1852 variable_value - @innodb_row_lock_time_orig
1853 0
1854 SELECT variable_value - @innodb_row_lock_time_max_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
1855 variable_value - @innodb_row_lock_time_max_orig
1856 0
1857 SELECT variable_value - @innodb_row_lock_time_avg_orig FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
1858 variable_value - @innodb_row_lock_time_avg_orig
1859 0
1860 SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops;
1861 show variables like "innodb_sync_spin_loops";
1862 Variable_name Value
1863 innodb_sync_spin_loops 30
1864 set global innodb_sync_spin_loops=1000;
1865 show variables like "innodb_sync_spin_loops";
1866 Variable_name Value
1867 innodb_sync_spin_loops 1000
1868 set global innodb_sync_spin_loops=0;
1869 show variables like "innodb_sync_spin_loops";
1870 Variable_name Value
1871 innodb_sync_spin_loops 0
1872 set global innodb_sync_spin_loops=20;
1873 show variables like "innodb_sync_spin_loops";
1874 Variable_name Value
1875 innodb_sync_spin_loops 20
1876 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
1877 show variables like "innodb_thread_concurrency";
1878 Variable_name Value
1879 innodb_thread_concurrency 0
1880 set global innodb_thread_concurrency=1001;
1881 Warnings:
1882 Warning 1292 Truncated incorrect innodb_thread_concurrency value: '1001'
1883 Warning 1210 Current innodb_thread_concurrency is too big for safety of redo log files. Consider decreasing it or increasing innodb_redo_log_capacity.
1884 show variables like "innodb_thread_concurrency";
1885 Variable_name Value
1886 innodb_thread_concurrency 1000
1887 set global innodb_thread_concurrency=0;
1888 show variables like "innodb_thread_concurrency";
1889 Variable_name Value
1890 innodb_thread_concurrency 0
1891 set global innodb_thread_concurrency=16;
1892 show variables like "innodb_thread_concurrency";
1893 Variable_name Value
1894 innodb_thread_concurrency 16
1895 show variables like "innodb_concurrency_tickets";
1896 Variable_name Value
1897 innodb_concurrency_tickets 5000
1898 set global innodb_concurrency_tickets=1000;
1899 show variables like "innodb_concurrency_tickets";
1900 Variable_name Value
1901 innodb_concurrency_tickets 1000
1902 set global innodb_concurrency_tickets=0;
1903 Warnings:
1904 Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0'
1905 show variables like "innodb_concurrency_tickets";
1906 Variable_name Value
1907 innodb_concurrency_tickets 1
1908 set global innodb_concurrency_tickets=5000;
1909 show variables like "innodb_concurrency_tickets";
1910 Variable_name Value
1911 innodb_concurrency_tickets 5000
1912 show variables like "innodb_thread_sleep_delay";
1913 Variable_name Value
1914 innodb_thread_sleep_delay 10000
1915 set global innodb_thread_sleep_delay=100000;
1916 show variables like "innodb_thread_sleep_delay";
1917 Variable_name Value
1918 innodb_thread_sleep_delay 100000
1919 set global innodb_thread_sleep_delay=0;
1920 show variables like "innodb_thread_sleep_delay";
1921 Variable_name Value
1922 innodb_thread_sleep_delay 0
1923 set global innodb_thread_sleep_delay=10000;
1924 show variables like "innodb_thread_sleep_delay";
1925 Variable_name Value
1926 innodb_thread_sleep_delay 10000
1927 set default_storage_engine=INNODB;
1928 set session old_alter_table=1;
1929 drop table if exists t1,t2,t3;
1930 --- Testing varchar ---
1931 --- Testing varchar ---
1932 create table t1 (v varchar(10), c char(10), t text);
1933 insert into t1 values('+ ', '+ ', '+ ');
1934 set @a=repeat(' ',20);
1935 insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1936 Warnings:
1937 Note 1265 Data truncated for column 'v' at row 1
1938 select concat('*',v,'*',c,'*',t,'*') from t1;
1939 concat('*',v,'*',c,'*',t,'*')
1940 *+ *+*+ *
1941 *+ *+*+ *
1942 show create table t1;
1943 Table Create Table
1944 t1 CREATE TABLE `t1` (
1945 `v` varchar(10) DEFAULT NULL,
1946 `c` char(10) DEFAULT NULL,
1947 `t` text
1948 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1949 create table t2 like t1;
1950 show create table t2;
1951 Table Create Table
1952 t2 CREATE TABLE `t2` (
1953 `v` varchar(10) DEFAULT NULL,
1954 `c` char(10) DEFAULT NULL,
1955 `t` text
1956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1957 create table t3 select * from t1;
1958 show create table t3;
1959 Table Create Table
1960 t3 CREATE TABLE `t3` (
1961 `v` varchar(10) DEFAULT NULL,
1962 `c` char(10) DEFAULT NULL,
1963 `t` text
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1965 alter table t1 modify c varchar(10);
1966 show create table t1;
1967 Table Create Table
1968 t1 CREATE TABLE `t1` (
1969 `v` varchar(10) DEFAULT NULL,
1970 `c` varchar(10) DEFAULT NULL,
1971 `t` text
1972 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1973 alter table t1 modify v char(10);
1974 show create table t1;
1975 Table Create Table
1976 t1 CREATE TABLE `t1` (
1977 `v` char(10) DEFAULT NULL,
1978 `c` varchar(10) DEFAULT NULL,
1979 `t` text
1980 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1981 alter table t1 modify t varchar(10);
1982 Warnings:
1983 Note 1265 Data truncated for column 't' at row 2
1984 show create table t1;
1985 Table Create Table
1986 t1 CREATE TABLE `t1` (
1987 `v` char(10) DEFAULT NULL,
1988 `c` varchar(10) DEFAULT NULL,
1989 `t` varchar(10) DEFAULT NULL
1990 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1991 select concat('*',v,'*',c,'*',t,'*') from t1;
1992 concat('*',v,'*',c,'*',t,'*')
1993 *+*+*+ *
1994 *+*+*+ *
1995 drop table t1,t2,t3;
1996 create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)))
1997 stats_persistent=0 charset latin1;
1998 show create table t1;
1999 Table Create Table
2000 t1 CREATE TABLE `t1` (
2001 `v` varchar(10) DEFAULT NULL,
2002 `c` char(10) DEFAULT NULL,
2003 `t` text,
2004 KEY `v` (`v`),
2005 KEY `c` (`c`),
2006 KEY `t` (`t`(10))
2007 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
2008 select count(*) from t1;
2009 count(*)
2010 270
2011 insert into t1 values(concat('a','!'),concat('a','!'),concat('a','!'));
2012 select count(*) from t1 where v='a';
2013 count(*)
2014 10
2015 select count(*) from t1 where c='a';
2016 count(*)
2017 10
2018 select count(*) from t1 where t='a';
2019 count(*)
2020 10
2021 select count(*) from t1 where v='a ';
2022 count(*)
2023 10
2024 select count(*) from t1 where c='a ';
2025 count(*)
2026 10
2027 select count(*) from t1 where t='a ';
2028 count(*)
2029 10
2030 select count(*) from t1 where v between 'a' and 'a ';
2031 count(*)
2032 10
2033 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2034 count(*)
2035 10
2036 select count(*) from t1 where v like 'a%';
2037 count(*)
2038 11
2039 select count(*) from t1 where c like 'a%';
2040 count(*)
2041 11
2042 select count(*) from t1 where t like 'a%';
2043 count(*)
2044 11
2045 select count(*) from t1 where v like 'a %';
2046 count(*)
2047 9
2048 explain select count(*) from t1 where v='a ';
2049 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2050 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using index
2051 Warnings:
2052 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ')
2053 explain select count(*) from t1 where c='a ';
2054 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2055 1 SIMPLE t1 NULL ref c c 11 const # 100.00 Using index
2056 Warnings:
2057 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`c` = 'a ')
2058 explain select count(*) from t1 where t='a ';
2059 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2060 1 SIMPLE t1 NULL ref t t 13 const # 100.00 Using where
2061 Warnings:
2062 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`t` = 'a ')
2063 explain select count(*) from t1 where v like 'a%';
2064 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2065 1 SIMPLE t1 NULL range v v 13 NULL # 100.00 Using where; Using index
2066 Warnings:
2067 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%')
2068 explain select count(*) from t1 where v between 'a' and 'a ';
2069 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2070 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index
2071 Warnings:
2072 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ')
2073 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2074 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2075 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index
2076 Warnings:
2077 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n'))
2078 alter table t1 add unique(v);
2079 ERROR 23000: Duplicate entry '{ ' for key 't1.v_2'
2080 alter table t1 add key(v);
2081 Warnings:
2082 Warning 1831 Duplicate index 'v_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
2083 select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
2084 qq
2085 *a*a*a*
2086 *a *a*a *
2087 *a *a*a *
2088 *a *a*a *
2089 *a *a*a *
2090 *a *a*a *
2091 *a *a*a *
2092 *a *a*a *
2093 *a *a*a *
2094 *a *a*a *
2095 explain select * from t1 where v='a';
2096 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2097 1 SIMPLE t1 NULL ref v,v_2 # 13 const # 100.00 NULL
2098 Warnings:
2099 Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a')
2100 select TRIM(v),count(*) from t1 group by TRIM(v) order by TRIM(v) limit 10;
2101 TRIM(v) count(*)
2102 a 10
2103 a! 1
2104 b 10
2105 c 10
2106 d 10
2107 e 10
2108 f 10
2109 g 10
2110 h 10
2111 i 10
2112 select TRIM(v),count(t) from t1 group by TRIM(v) order by TRIM(v) limit 10;
2113 TRIM(v) count(t)
2114 a 10
2115 a! 1
2116 b 10
2117 c 10
2118 d 10
2119 e 10
2120 f 10
2121 g 10
2122 h 10
2123 i 10
2124 select TRIM(v),count(c) from t1 group by TRIM(v) order by TRIM(v) limit 10;
2125 TRIM(v) count(c)
2126 a 10
2127 a! 1
2128 b 10
2129 c 10
2130 d 10
2131 e 10
2132 f 10
2133 g 10
2134 h 10
2135 i 10
2136 select sql_big_result TRIM(v),count(t) from t1 group by TRIM(v) order by TRIM(v) limit 10;
2137 TRIM(v) count(t)
2138 a 10
2139 a! 1
2140 b 10
2141 c 10
2142 d 10
2143 e 10
2144 f 10
2145 g 10
2146 h 10
2147 i 10
2148 select sql_big_result TRIM(v),count(c) from t1 group by TRIM(v) order by TRIM(v) limit 10;
2149 TRIM(v) count(c)
2150 a 10
2151 a! 1
2152 b 10
2153 c 10
2154 d 10
2155 e 10
2156 f 10
2157 g 10
2158 h 10
2159 i 10
2160 select c,count(*) from t1 group by c order by c limit 10;
2161 c count(*)
2162 a 10
2163 a! 1
2164 b 10
2165 c 10
2166 d 10
2167 e 10
2168 f 10
2169 g 10
2170 h 10
2171 i 10
2172 select c,count(t) from t1 group by c order by c limit 10;
2173 c count(t)
2174 a 10
2175 a! 1
2176 b 10
2177 c 10
2178 d 10
2179 e 10
2180 f 10
2181 g 10
2182 h 10
2183 i 10
2184 select sql_big_result c,count(t) from t1 group by c limit 10;
2185 c count(t)
2186 a 10
2187 a! 1
2188 b 10
2189 c 10
2190 d 10
2191 e 10
2192 f 10
2193 g 10
2194 h 10
2195 i 10
2196 select TRIM(t),count(*) from t1 group by TRIM(t) order by TRIM(t) limit 10;
2197 TRIM(t) count(*)
2198 a 10
2199 a! 1
2200 b 10
2201 c 10
2202 d 10
2203 e 10
2204 f 10
2205 g 10
2206 h 10
2207 i 10
2208 select TRIM(t),count(t) from t1 group by TRIM(t) order by TRIM(t) limit 10;
2209 TRIM(t) count(t)
2210 a 10
2211 a! 1
2212 b 10
2213 c 10
2214 d 10
2215 e 10
2216 f 10
2217 g 10
2218 h 10
2219 i 10
2220 select sql_big_result TRIM(t),count(t) from t1 group by TRIM(t) order by TRIM(t) limit 10;
2221 TRIM(t) count(t)
2222 a 10
2223 a! 1
2224 b 10
2225 c 10
2226 d 10
2227 e 10
2228 f 10
2229 g 10
2230 h 10
2231 i 10
2232 alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
2233 show create table t1;
2234 Table Create Table
2235 t1 CREATE TABLE `t1` (
2236 `v` varchar(300) DEFAULT NULL,
2237 `c` char(10) DEFAULT NULL,
2238 `t` text,
2239 KEY `c` (`c`),
2240 KEY `t` (`t`(10)),
2241 KEY `v` (`v`)
2242 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
2243 select count(*) from t1 where v='a';
2244 count(*)
2245 10
2246 select count(*) from t1 where v='a ';
2247 count(*)
2248 10
2249 select count(*) from t1 where v between 'a' and 'a ';
2250 count(*)
2251 10
2252 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2253 count(*)
2254 10
2255 select count(*) from t1 where v like 'a%';
2256 count(*)
2257 11
2258 select count(*) from t1 where v like 'a %';
2259 count(*)
2260 9
2261 explain select count(*) from t1 where v='a ';
2262 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2263 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using index
2264 Warnings:
2265 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ')
2266 explain select count(*) from t1 where v like 'a%';
2267 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2268 1 SIMPLE t1 NULL range v v 303 NULL # 100.00 Using where; Using index
2269 Warnings:
2270 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%')
2271 explain select count(*) from t1 where v between 'a' and 'a ';
2272 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2273 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index
2274 Warnings:
2275 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ')
2276 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2277 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2278 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index
2279 Warnings:
2280 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n'))
2281 explain select * from t1 where v='a';
2282 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2283 1 SIMPLE t1 NULL ref v v 303 const # 100.00 NULL
2284 Warnings:
2285 Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a')
2286 select v,count(*) from t1 group by v order by v limit 10;
2287 v count(*)
2288 a 10
2289 a! 1
2290 b 10
2291 c 10
2292 d 10
2293 e 10
2294 f 10
2295 g 10
2296 h 10
2297 i 10
2298 select TRIM(v),count(t) from t1 group by v order by v limit 10;
2299 TRIM(v) count(t)
2300 a 10
2301 a! 1
2302 b 10
2303 c 10
2304 d 10
2305 e 10
2306 f 10
2307 g 10
2308 h 10
2309 i 10
2310 select sql_big_result TRIM(v),count(t) from t1 group by v order by v limit 10;
2311 TRIM(v) count(t)
2312 a 10
2313 a! 1
2314 b 10
2315 c 10
2316 d 10
2317 e 10
2318 f 10
2319 g 10
2320 h 10
2321 i 10
2322 alter table t1 drop key v, add key v (v(30));
2323 show create table t1;
2324 Table Create Table
2325 t1 CREATE TABLE `t1` (
2326 `v` varchar(300) DEFAULT NULL,
2327 `c` char(10) DEFAULT NULL,
2328 `t` text,
2329 KEY `c` (`c`),
2330 KEY `t` (`t`(10)),
2331 KEY `v` (`v`(30))
2332 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
2333 select count(*) from t1 where v='a';
2334 count(*)
2335 10
2336 select count(*) from t1 where v='a ';
2337 count(*)
2338 10
2339 select count(*) from t1 where v between 'a' and 'a ';
2340 count(*)
2341 10
2342 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2343 count(*)
2344 10
2345 select count(*) from t1 where v like 'a%';
2346 count(*)
2347 11
2348 select count(*) from t1 where v like 'a %';
2349 count(*)
2350 9
2351 explain select count(*) from t1 where v='a ';
2352 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2353 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where
2354 Warnings:
2355 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ')
2356 explain select count(*) from t1 where v like 'a%';
2357 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2358 1 SIMPLE t1 NULL range v v 33 NULL # 100.00 Using where; Using index
2359 Warnings:
2360 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%')
2361 explain select count(*) from t1 where v between 'a' and 'a ';
2362 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2363 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where
2364 Warnings:
2365 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ')
2366 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2367 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2368 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where
2369 Warnings:
2370 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n'))
2371 explain select * from t1 where v='a';
2372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2373 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where
2374 Warnings:
2375 Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a')
2376 select TRIM(v),count(*) from t1 group by v order by v limit 10;
2377 TRIM(v) count(*)
2378 a 10
2379 a! 1
2380 b 10
2381 c 10
2382 d 10
2383 e 10
2384 f 10
2385 g 10
2386 h 10
2387 i 10
2388 select TRIM(v),count(t) from t1 group by v order by v limit 10;
2389 TRIM(v) count(t)
2390 a 10
2391 a! 1
2392 b 10
2393 c 10
2394 d 10
2395 e 10
2396 f 10
2397 g 10
2398 h 10
2399 i 10
2400 select sql_big_result TRIM(v),count(t) from t1 group by v order by v limit 10;
2401 TRIM(v) count(t)
2402 a 10
2403 a! 1
2404 b 10
2405 c 10
2406 d 10
2407 e 10
2408 f 10
2409 g 10
2410 h 10
2411 i 10
2412 alter table t1 modify v varchar(600), drop key v, add key v (v);
2413 show create table t1;
2414 Table Create Table
2415 t1 CREATE TABLE `t1` (
2416 `v` varchar(600) DEFAULT NULL,
2417 `c` char(10) DEFAULT NULL,
2418 `t` text,
2419 KEY `c` (`c`),
2420 KEY `t` (`t`(10)),
2421 KEY `v` (`v`)
2422 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
2423 select TRIM(v),count(*) from t1 group by v order by v limit 10;
2424 TRIM(v) count(*)
2425 a 10
2426 a! 1
2427 b 10
2428 c 10
2429 d 10
2430 e 10
2431 f 10
2432 g 10
2433 h 10
2434 i 10
2435 select TRIM(v),count(t) from t1 group by v order by v limit 10;
2436 TRIM(v) count(t)
2437 a 10
2438 a! 1
2439 b 10
2440 c 10
2441 d 10
2442 e 10
2443 f 10
2444 g 10
2445 h 10
2446 i 10
2447 select sql_big_result TRIM(v),count(t) from t1 group by v order by v limit 10;
2448 TRIM(v) count(t)
2449 a 10
2450 a! 1
2451 b 10
2452 c 10
2453 d 10
2454 e 10
2455 f 10
2456 g 10
2457 h 10
2458 i 10
2459 drop table t1;
2460 SET NAMES utf8mb4;
2461 create table t1 (a char(10), unique (a));
2462 insert into t1 values ('a ');
2463 insert into t1 values ('a ');
2464 ERROR 23000: Duplicate entry 'a' for key 't1.a'
2465 alter table t1 modify a varchar(10);
2466 insert into t1 values ('a'),('á'),('à'),('å');
2467 ERROR 23000: Duplicate entry 'a' for key 't1.a'
2468 insert into t1 values ('á');
2469 ERROR 23000: Duplicate entry 'á' for key 't1.a'
2470 insert into t1 values ('A');
2471 ERROR 23000: Duplicate entry 'A' for key 't1.a'
2472 insert into t1 values ('Ã¥');
2473 ERROR 23000: Duplicate entry 'Ã¥' for key 't1.a'
2474 update t1 set a='a ' where a like 'a%';
2475 select concat(a,'.') from t1;
2476 concat(a,'.')
2477 a .
2478 update t1 set a='abc ' where a like 'a ';
2479 select concat(a,'.') from t1;
2480 concat(a,'.')
2481 a .
2482 update t1 set a='a ' where a like 'a %';
2483 select concat(a,'.') from t1;
2484 concat(a,'.')
2485 a .
2486 update t1 set a='a ' where a like 'a ';
2487 select concat(a,'.') from t1;
2488 concat(a,'.')
2489 a .
2490 drop table t1;
2491 SET NAMES DEFAULT;
2492 create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2493 show create table t1;
2494 Table Create Table
2495 t1 CREATE TABLE `t1` (
2496 `v` varchar(10) DEFAULT NULL,
2497 `c` char(10) DEFAULT NULL,
2498 `t` text,
2499 KEY `v` (`v`(5)),
2500 KEY `c` (`c`(5)),
2501 KEY `t` (`t`(5))
2502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2503 drop table t1;
2504 create table t1 (v char(10) character set utf8);
2505 Warnings:
2506 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.
2507 show create table t1;
2508 Table Create Table
2509 t1 CREATE TABLE `t1` (
2510 `v` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
2511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2512 drop table t1;
2513 create table t1 (v varchar(10), c char(10)) row_format=fixed;
2514 Warnings:
2515 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
2516 show create table t1;
2517 Table Create Table
2518 t1 CREATE TABLE `t1` (
2519 `v` varchar(10) DEFAULT NULL,
2520 `c` char(10) DEFAULT NULL
2521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2522 insert into t1 values('a','a'),('a ','a ');
2523 select concat('*',v,'*',c,'*') from t1;
2524 concat('*',v,'*',c,'*')
2525 *a*a*
2526 *a *a*
2527 drop table t1;
2528 create table t1 (v varchar(65530), key(v(10))) charset latin1;
2529 insert into t1 values(repeat('a',65530));
2530 select length(v) from t1 where v=repeat('a',65530);
2531 length(v)
2532 65530
2533 drop table t1;
2534 create table t1(a int, b varchar(12), key ba(b, a));
2535 insert into t1 values (1, 'A'), (20, NULL);
2536 explain select * from t1 where a=20 and b is null;
2537 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2538 1 SIMPLE t1 NULL ref ba ba 56 const,const 1 100.00 Using where; Using index
2539 Warnings:
2540 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 20) and (`test`.`t1`.`b` is null))
2541 select * from t1 where a=20 and b is null;
2542 a b
2543 20 NULL
2544 drop table t1;
2545 set session old_alter_table=0;
2546 SET sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
2547 Warnings:
2548 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2549 create table t1 (v varchar(65530), key(v)) charset latin1 ROW_FORMAT=COMPACT;
2550 Got one of the listed errors
2551 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
2552 create table t1 (v varchar(65536));
2553 Warnings:
2554 Note 1246 Converting column 'v' from VARCHAR to TEXT
2555 show create table t1;
2556 Table Create Table
2557 t1 CREATE TABLE `t1` (
2558 `v` mediumtext
2559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2560 drop table t1;
2561 create table t1 (v varchar(65530) character set utf8);
2562 Warnings:
2563 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.
2564 Note 1246 Converting column 'v' from VARCHAR to TEXT
2565 show create table t1;
2566 Table Create Table
2567 t1 CREATE TABLE `t1` (
2568 `v` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci
2569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2570 drop table t1;
2571 SET sql_mode = default;
2572 set default_storage_engine=MyISAM;
2573 create table t1 (v varchar(16384)) charset latin1 engine=innodb;
2574 drop table t1;
2575 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2576 insert into t1 values ('8', '6'), ('4', '7');
2577 select min(a) from t1;
2578 min(a)
2579 4
2580 select min(b) from t1 where a='8';
2581 min(b)
2582 6
2583 drop table t1;
2584 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2585 Warnings:
2586 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2587 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2588 insert into t1 (b) values (1);
2589 replace into t1 (b) values (2), (1), (3);
2590 select * from t1;
2591 a b
2592 2 2
2593 3 1
2594 4 3
2595 truncate table t1;
2596 insert into t1 (b) values (1);
2597 replace into t1 (b) values (2);
2598 replace into t1 (b) values (1);
2599 replace into t1 (b) values (3);
2600 select * from t1;
2601 a b
2602 2 2
2603 3 1
2604 4 3
2605 drop table t1;
2606 create table t1 (rowid int not null auto_increment, val int not null,primary
2607 key (rowid), unique(val)) engine=innodb;
2608 replace into t1 (val) values ('1'),('2');
2609 replace into t1 (val) values ('1'),('2');
2610 insert into t1 (val) values ('1'),('2');
2611 ERROR 23000: Duplicate entry '1' for key 't1.val'
2612 select * from t1;
2613 rowid val
2614 3 1
2615 4 2
2616 drop table t1;
2617 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2618 insert into t1 (val) values (1);
2619 update t1 set a=2 where a=1;
2620 insert into t1 (val) values (3);
2621 select * from t1;
2622 a val
2623 2 1
2624 3 3
2625 insert into t1 values (2, 2);
2626 ERROR 23000: Duplicate entry '2' for key 't1.PRIMARY'
2627 select * from t1;
2628 a val
2629 2 1
2630 3 3
2631 drop table t1;
2632 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2633 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2634 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2635 GRADE
2636 252
2637 SELECT GRADE FROM t1 WHERE GRADE= 151;
2638 GRADE
2639 151
2640 DROP TABLE t1;
2641 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
2642 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
2643 insert into t2 values ('aa','cc');
2644 insert into t1 values ('aa','bb'),('aa','cc');
2645 delete t1 from t1,t2 where f1=f3 and f4='cc';
2646 select * from t1;
2647 f1 f2
2648 drop table t1,t2;
2649 CREATE TABLE t1 (
2650 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2651 ) ENGINE=InnoDB;
2652 INSERT INTO t1 (id) VALUES (NULL);
2653 SELECT * FROM t1;
2654 id
2655 1
2656 TRUNCATE t1;
2657 INSERT INTO t1 (id) VALUES (NULL);
2658 SELECT * FROM t1;
2659 id
2660 1
2661 DELETE FROM t1;
2662 TRUNCATE t1;
2663 INSERT INTO t1 (id) VALUES (NULL);
2664 SELECT * FROM t1;
2665 id
2666 1
2667 DROP TABLE t1;
2668 CREATE TABLE t1
2669 (
2670 id INT PRIMARY KEY
2671 ) ENGINE=InnoDB;
2672 CREATE TEMPORARY TABLE t2
2673 (
2674 id INT NOT NULL PRIMARY KEY,
2675 b INT,
2676 FOREIGN KEY (b) REFERENCES test.t1(id)
2677 ) ENGINE=InnoDB;
2678 ERROR HY000: Cannot add foreign key constraint
2679 DROP TABLE t1;
2680 create table t1 (col1 varchar(2000), index (col1(767)))
2681 character set = latin1 engine = innodb;
2682 create table t2 (col1 char(255), index (col1))
2683 character set = latin1 engine = innodb;
2684 create table t3 (col1 binary(255), index (col1))
2685 character set = latin1 engine = innodb;
2686 create table t4 (col1 varchar(767), index (col1))
2687 character set = latin1 engine = innodb;
2688 create table t5 (col1 varchar(767) primary key)
2689 character set = latin1 engine = innodb;
2690 create table t6 (col1 varbinary(767) primary key)
2691 character set = latin1 engine = innodb;
2692 create table t7 (col1 text, index(col1(767)))
2693 character set = latin1 engine = innodb;
2694 create table t8 (col1 blob, index(col1(767)))
2695 character set = latin1 engine = innodb;
2696 drop table t1, t2, t3, t4, t5, t6, t7, t8;
2697 CREATE TABLE t1
2698 (
2699 id INT PRIMARY KEY
2700 ) ENGINE=InnoDB;
2701 CREATE TABLE t2
2702 (
2703 v INT,
2704 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2705 ) ENGINE=InnoDB;
2706 INSERT INTO t2 VALUES(2);
2707 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2708 INSERT INTO t1 VALUES(1);
2709 INSERT INTO t2 VALUES(1);
2710 DELETE FROM t1 WHERE id = 1;
2711 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2712 DROP TABLE t1;
2713 ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'c1' on table 't2'.
2714 SET FOREIGN_KEY_CHECKS=0;
2715 DROP TABLE t1;
2716 SET FOREIGN_KEY_CHECKS=1;
2717 INSERT INTO t2 VALUES(3);
2718 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2719 DROP TABLE t2;
2720 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2721 insert into t1 values (1),(2);
2722 set autocommit=0;
2723 checksum table t1;
2724 Table Checksum
2725 test.t1 1531596814
2726 insert into t1 values(3);
2727 checksum table t1;
2728 Table Checksum
2729 test.t1 1531596814
2730 commit;
2731 checksum table t1;
2732 Table Checksum
2733 test.t1 2050879373
2734 commit;
2735 drop table t1;
2736 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2737 insert into t1 values (1),(2);
2738 set autocommit=1;
2739 checksum table t1;
2740 Table Checksum
2741 test.t1 1531596814
2742 set autocommit=1;
2743 insert into t1 values(3);
2744 checksum table t1;
2745 Table Checksum
2746 test.t1 2050879373
2747 drop table t1;
2748 set foreign_key_checks=0;
2749 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2750 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2751 ERROR HY000: Referencing column 'b' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
2752 set foreign_key_checks=1;
2753 drop table t2;
2754 set foreign_key_checks=0;
2755 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2756 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
2757 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
2758 set foreign_key_checks=1;
2759 drop table t1;
2760 set foreign_key_checks=0;
2761 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2762 create table t1(a varchar(10) primary key) engine = innodb;
2763 alter table t1 modify column a int;
2764 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
2765 set foreign_key_checks=1;
2766 drop table t2,t1;
2767 set foreign_key_checks=0;
2768 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2769 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2770 alter table t1 convert to character set utf8;
2771 Warnings:
2772 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.
2773 set foreign_key_checks=1;
2774 drop table t2,t1;
2775 set foreign_key_checks=0;
2776 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2777 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
2778 Warnings:
2779 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.
2780 rename table t3 to t1;
2781 ERROR HY000: Referencing column 'a' and referenced column 'a' in foreign key constraint 't2_ibfk_1' are incompatible.
2782 set foreign_key_checks=1;
2783 drop table t2,t3;
2784 create table t1(a int primary key) row_format=redundant engine=innodb;
2785 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2786 create table t3(a int primary key) row_format=compact engine=innodb;
2787 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2788 insert into t1 values(1);
2789 insert into t3 values(1);
2790 insert into t2 values(2);
2791 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`))
2792 insert into t4 values(2);
2793 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`))
2794 insert into t2 values(1);
2795 insert into t4 values(1);
2796 update t1 set a=2;
2797 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`))
2798 update t2 set a=2;
2799 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`))
2800 update t3 set a=2;
2801 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`))
2802 update t4 set a=2;
2803 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`))
2804 truncate t1;
2805 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
2806 truncate t3;
2807 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1`)
2808 truncate t2;
2809 truncate t4;
2810 truncate t1;
2811 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
2812 truncate t3;
2813 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1`)
2814 drop table t4,t3,t2,t1;
2815 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
2816 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2817 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
2818 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
2819 Warnings:
2820 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
2821 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
2822 Warnings:
2823 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
2824 insert into t1 values (0x41),(0x4120),(0x4100);
2825 insert into t2 values (0x41),(0x4120),(0x4100);
2826 ERROR 23000: Duplicate entry 'A' for key 't2.PRIMARY'
2827 insert into t2 values (0x41),(0x4120);
2828 insert into t3 values (0x41),(0x4120),(0x4100);
2829 ERROR 23000: Duplicate entry 'A ' for key 't3.PRIMARY'
2830 insert into t3 values (0x41),(0x4100);
2831 insert into t4 values (0x41),(0x4120),(0x4100);
2832 ERROR 23000: Duplicate entry 'A' for key 't4.PRIMARY'
2833 insert into t4 values (0x41),(0x4100);
2834 select hex(s1) from t1;
2835 hex(s1)
2836 41
2837 4100
2838 4120
2839 select hex(s1) from t2;
2840 hex(s1)
2841 4100
2842 4120
2843 select hex(s1) from t3;
2844 hex(s1)
2845 4100
2846 41
2847 select hex(s1) from t4;
2848 hex(s1)
2849 4100
2850 41
2851 drop table t1,t2,t3,t4;
2852 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2853 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2854 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2855 insert into t2 values(0x42);
2856 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)
2857 insert into t2 values(0x41);
2858 select hex(s1) from t2;
2859 hex(s1)
2860 4100
2861 update t1 set s1=0x123456 where a=2;
2862 select hex(s1) from t2;
2863 hex(s1)
2864 4100
2865 update t1 set s1=0x12 where a=1;
2866 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)
2867 update t1 set s1=0x12345678 where a=1;
2868 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)
2869 update t1 set s1=0x123457 where a=1;
2870 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)
2871 update t1 set s1=0x1220 where a=1;
2872 select hex(s1) from t2;
2873 hex(s1)
2874 1220
2875 update t1 set s1=0x1200 where a=1;
2876 select hex(s1) from t2;
2877 hex(s1)
2878 1200
2879 update t1 set s1=0x4200 where a=1;
2880 select hex(s1) from t2;
2881 hex(s1)
2882 4200
2883 delete from t1 where a=1;
2884 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)
2885 delete from t1 where a=2;
2886 update t2 set s1=0x4120;
2887 delete from t1;
2888 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)
2889 delete from t1 where a!=3;
2890 select a,hex(s1) from t1;
2891 a hex(s1)
2892 3 4120
2893 select hex(s1) from t2;
2894 hex(s1)
2895 4120
2896 drop table t2,t1;
2897 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
2898 Warnings:
2899 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
2900 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2901 Warnings:
2902 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
2903 insert into t1 values(1,0x4100),(2,0x41);
2904 insert into t2 values(0x41);
2905 select hex(s1) from t2;
2906 hex(s1)
2907 41
2908 update t1 set s1=0x1234 where a=1;
2909 select hex(s1) from t2;
2910 hex(s1)
2911 41
2912 update t1 set s1=0x12 where a=2;
2913 select hex(s1) from t2;
2914 hex(s1)
2915 12
2916 delete from t1 where a=1;
2917 delete from t1 where a=2;
2918 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)
2919 select a,hex(s1) from t1;
2920 a hex(s1)
2921 2 12
2922 select hex(s1) from t2;
2923 hex(s1)
2924 12
2925 drop table t2,t1;
2926 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2927 CREATE TABLE t2(a INT) ENGINE=InnoDB;
2928 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2929 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2930 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2931 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2932 SHOW CREATE TABLE t2;
2933 Table Create Table
2934 t2 CREATE TABLE `t2` (
2935 `a` int DEFAULT NULL,
2936 KEY `t2_ibfk_0` (`a`)
2937 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2938 DROP TABLE t2,t1;
2939 SET sql_mode = default;
2940 CREATE TABLE t1 (
2941 field1 varchar(8) NOT NULL DEFAULT '',
2942 field2 varchar(8) NOT NULL DEFAULT '',
2943 PRIMARY KEY (field1, field2)
2944 ) ENGINE=InnoDB;
2945 CREATE TABLE t2 (
2946 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2947 FOREIGN KEY (field1) REFERENCES t1 (field1)
2948 ON DELETE CASCADE ON UPDATE CASCADE
2949 ) ENGINE=InnoDB;
2950 INSERT INTO t1 VALUES ('old', 'somevalu');
2951 INSERT INTO t1 VALUES ('other', 'anyvalue');
2952 INSERT INTO t2 VALUES ('old');
2953 INSERT INTO t2 VALUES ('other');
2954 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2955 ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'PRIMARY'
2956 DROP TABLE t2;
2957 DROP TABLE t1;
2958 create table t1 (
2959 c1 bigint not null,
2960 c2 bigint not null,
2961 primary key (c1),
2962 unique key (c2)
2963 ) engine=innodb;
2964 create table t2 (
2965 c1 bigint not null,
2966 primary key (c1)
2967 ) engine=innodb;
2968 alter table t1 add constraint c2_fk foreign key (c2)
2969 references t2(c1) on delete cascade;
2970 show create table t1;
2971 Table Create Table
2972 t1 CREATE TABLE `t1` (
2973 `c1` bigint NOT NULL,
2974 `c2` bigint NOT NULL,
2975 PRIMARY KEY (`c1`),
2976 UNIQUE KEY `c2` (`c2`),
2977 CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2978 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2979 alter table t1 drop foreign key c2_fk;
2980 show create table t1;
2981 Table Create Table
2982 t1 CREATE TABLE `t1` (
2983 `c1` bigint NOT NULL,
2984 `c2` bigint NOT NULL,
2985 PRIMARY KEY (`c1`),
2986 UNIQUE KEY `c2` (`c2`)
2987 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2988 drop table t1, t2;
2989 create table t1(a date) engine=innodb;
2990 create table t2(a date, key(a)) engine=innodb;
2991 insert into t1 values('2005-10-01');
2992 insert into t2 values('2005-10-01');
2993 select * from t1, t2
2994 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2995 a a
2996 2005-10-01 2005-10-01
2997 drop table t1, t2;
2998 create table t1 (id int not null, f_id int not null, f int not null,
2999 primary key(f_id, id)) engine=innodb;
3000 create table t2 (id int not null,s_id int not null,s varchar(200),
3001 primary key(id)) engine=innodb;
3002 INSERT INTO t1 VALUES (8, 1, 3);
3003 INSERT INTO t1 VALUES (1, 2, 1);
3004 INSERT INTO t2 VALUES (1, 0, '');
3005 INSERT INTO t2 VALUES (8, 1, '');
3006 commit;
3007 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
3008 WHERE mm.id IS NULL;
3009 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
3010 where mm.id is null lock in share mode;
3011 id f_id f
3012 drop table t1,t2;
3013 create table t1(a int not null, b int, primary key(a)) engine=innodb;
3014 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
3015 commit;
3016 SET binlog_format='MIXED';
3017 Warnings:
3018 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3019 set autocommit = 0;
3020 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3021 update t1 set b = 5 where b = 1;
3022 SET binlog_format='MIXED';
3023 Warnings:
3024 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3025 set autocommit = 0;
3026 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3027 select * from t1 where a = 7 and b = 3 for update;
3028 a b
3029 7 3
3030 commit;
3031 commit;
3032 drop table t1;
3033 create table t1(a int not null, b int, primary key(a)) engine=innodb;
3034 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
3035 commit;
3036 set autocommit = 0;
3037 select * from t1 lock in share mode;
3038 a b
3039 1 1
3040 2 2
3041 3 1
3042 4 2
3043 5 1
3044 6 2
3045 update t1 set b = 5 where b = 1;
3046 set autocommit = 0;
3047 select * from t1 where a = 2 and b = 2 for update;
3048 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3049 commit;
3050 commit;
3051 drop table t1;
3052 create table t1(a int not null, b int, primary key(a)) engine=innodb;
3053 insert into t1 values (1,2),(5,3),(4,2);
3054 create table t2(d int not null, e int, primary key(d)) engine=innodb;
3055 insert into t2 values (8,6),(12,1),(3,1);
3056 commit;
3057 set autocommit = 0;
3058 select * from t2 for update;
3059 d e
3060 3 1
3061 8 6
3062 12 1
3063 SET binlog_format='MIXED';
3064 Warnings:
3065 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3066 set autocommit = 0;
3067 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3068 insert into t1 select * from t2;
3069 update t1 set b = (select e from t2 where a = d);
3070 create table t3(d int not null, e int, primary key(d)) engine=innodb
3071 select * from t2;
3072 commit;
3073 commit;
3074 drop table t1, t2, t3;
3075 create table t1(a int not null, b int, primary key(a)) engine=innodb;
3076 insert into t1 values (1,2),(5,3),(4,2);
3077 create table t2(a int not null, b int, primary key(a)) engine=innodb;
3078 insert into t2 values (8,6),(12,1),(3,1);
3079 create table t3(d int not null, b int, primary key(d)) engine=innodb;
3080 insert into t3 values (8,6),(12,1),(3,1);
3081 create table t5(a int not null, b int, primary key(a)) engine=innodb;
3082 insert into t5 values (1,2),(5,3),(4,2);
3083 create table t6(d int not null, e int, primary key(d)) engine=innodb;
3084 insert into t6 values (8,6),(12,1),(3,1);
3085 create table t8(a int not null, b int, primary key(a)) engine=innodb;
3086 insert into t8 values (1,2),(5,3),(4,2);
3087 create table t9(d int not null, e int, primary key(d)) engine=innodb;
3088 insert into t9 values (8,6),(12,1),(3,1);
3089 commit;
3090 set autocommit = 0;
3091 select * from t2 for update;
3092 a b
3093 3 1
3094 8 6
3095 12 1
3096 SET binlog_format='MIXED';
3097 Warnings:
3098 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3099 set autocommit = 0;
3100 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3101 insert into t1 select * from t2;
3102 SET binlog_format='MIXED';
3103 Warnings:
3104 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3105 set autocommit = 0;
3106 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3107 update t3 set b = (select b from t2 where a = d);
3108 SET binlog_format='MIXED';
3109 Warnings:
3110 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3111 set autocommit = 0;
3112 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3113 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
3114 SET binlog_format='MIXED';
3115 Warnings:
3116 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3117 set autocommit = 0;
3118 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3119 insert into t5 (select * from t2 lock in share mode);
3120 SET binlog_format='MIXED';
3121 Warnings:
3122 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3123 set autocommit = 0;
3124 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3125 update t6 set e = (select b from t2 where a = d lock in share mode);
3126 SET binlog_format='MIXED';
3127 Warnings:
3128 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3129 set autocommit = 0;
3130 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3131 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
3132 SET binlog_format='MIXED';
3133 Warnings:
3134 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3135 set autocommit = 0;
3136 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3137 insert into t8 (select * from t2 for update);
3138 SET binlog_format='MIXED';
3139 Warnings:
3140 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3141 set autocommit = 0;
3142 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3143 update t9 set e = (select b from t2 where a = d for update);
3144 SET binlog_format='MIXED';
3145 Warnings:
3146 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
3147 set autocommit = 0;
3148 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3149 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
3150 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3151 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3152 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3153 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3154 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3155 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3156 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3157 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3158 ERROR HY000: Can't update table 't2' while 't10' is being created.
3159 commit;
3160 drop table t1, t2, t3, t5, t6, t8, t9;
3161 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
3162 ERROR 42000: Incorrect column name 'DB_ROW_ID'
3163 CREATE TABLE t1 (
3164 a BIGINT(20) NOT NULL,
3165 PRIMARY KEY (a)
3166 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3167 Warnings:
3168 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3169 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.
3170 CREATE TABLE t2 (
3171 a BIGINT(20) NOT NULL,
3172 b VARCHAR(128) NOT NULL,
3173 c TEXT NOT NULL,
3174 PRIMARY KEY (a,b),
3175 KEY idx_t2_b_c (b,c(100)),
3176 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
3177 ON DELETE CASCADE
3178 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3179 Warnings:
3180 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3181 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.
3182 INSERT INTO t1 VALUES (1);
3183 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
3184 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
3185 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
3186 INSERT INTO t2 VALUES (1, 'customer_over', '1');
3187 SELECT * FROM t2 WHERE b = 'customer_over';
3188 a b c
3189 1 customer_over 1
3190 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
3191 a b c
3192 1 customer_over 1
3193 Warnings:
3194 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
3195 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
3196 a
3197 1
3198 /* Bang: Empty result set, above was expected: */
3199 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3200 a
3201 1
3202 Warnings:
3203 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
3204 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3205 a
3206 1
3207 Warnings:
3208 Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
3209 drop table t2, t1;
3210 CREATE TABLE t1 ( a int ) ENGINE=innodb;
3211 BEGIN;
3212 INSERT INTO t1 VALUES (1);
3213 OPTIMIZE TABLE t1;
3214 Table Op Msg_type Msg_text
3215 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3216 test.t1 optimize status OK
3217 DROP TABLE t1;
3218 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
3219 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
3220 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
3221 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
3222 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
3223 DELETE CASCADE ON UPDATE CASCADE;
3224 SHOW CREATE TABLE t2;
3225 Table Create Table
3226 t2 CREATE TABLE `t2` (
3227 `id` int NOT NULL,
3228 `f` int NOT NULL,
3229 PRIMARY KEY (`id`),
3230 KEY `f` (`f`),
3231 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
3232 CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
3233 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3234 DROP TABLE t2, t1;
3235 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
3236 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
3237 INSERT INTO t1 VALUES (1);
3238 INSERT INTO t2 VALUES (1);
3239 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
3240 ALTER TABLE t2 MODIFY a INT NOT NULL;
3241 ERROR HY000: Column 'a' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
3242 DELETE FROM t1;
3243 DROP TABLE t2,t1;
3244 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
3245 ENGINE=InnoDB;
3246 Warnings:
3247 Warning 3778 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
3248 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
3249 DELETE FROM t1;
3250 INSERT INTO t1 VALUES ('DDD');
3251 SELECT * FROM t1;
3252 a
3253 DDD
3254 DROP TABLE t1;
3255 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
3256 AUTO_INCREMENT=42;
3257 INSERT INTO t1 VALUES (0),(347),(0);
3258 SELECT * FROM t1;
3259 id
3260 42
3261 347
3262 348
3263 SHOW CREATE TABLE t1;
3264 Table Create Table
3265 t1 CREATE TABLE `t1` (
3266 `id` int NOT NULL AUTO_INCREMENT,
3267 PRIMARY KEY (`id`)
3268 ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3269 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
3270 INSERT INTO t2 VALUES(42),(347),(348);
3271 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
3272 SHOW CREATE TABLE t1;
3273 Table Create Table
3274 t1 CREATE TABLE `t1` (
3275 `id` int NOT NULL AUTO_INCREMENT,
3276 PRIMARY KEY (`id`),
3277 CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
3278 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3279 DROP TABLE t1,t2;
3280 SET innodb_strict_mode=ON;
3281 CREATE TABLE t1 (
3282 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
3283 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
3284 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
3285 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
3286 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
3287 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
3288 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
3289 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
3290 ) charset latin1 ENGINE = InnoDB;
3291 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.
3292 SET innodb_strict_mode=OFF;
3293 DROP TABLE IF EXISTS t1;
3294 Warnings:
3295 Note 1051 Unknown table 'test.t1'
3296 CREATE TABLE t1(
3297 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
3298 ) ENGINE=InnoDB;
3299 Warnings:
3300 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3301 INSERT INTO t1 VALUES(-10);
3302 SELECT * FROM t1;
3303 id
3304 -10
3305 INSERT INTO t1 VALUES(NULL);
3306 SELECT * FROM t1;
3307 id
3308 -10
3309 1
3310 DROP TABLE t1;
3311 create table t1 (i int, j int) engine=innodb;
3312 insert into t1 (i, j) values (1, 1), (2, 2);
3313 update t1 set j = 2;
3314 affected rows: 1
3315 info: Rows matched: 2 Changed: 1 Warnings: 0
3316 drop table t1;
3317 create table t1 (id int) comment='this is a comment' engine=innodb;
3318 analyze table t1;
3319 Table Op Msg_type Msg_text
3320 test.t1 analyze status OK
3321 select table_comment, data_free > 0 as data_free_is_set
3322 from information_schema.tables
3323 where table_schema='test' and table_name = 't1';
3324 TABLE_COMMENT data_free_is_set
3325 this is a comment 1
3326 drop table t1;
3327 CREATE TABLE t1 (
3328 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3329 c2 VARCHAR(128) NOT NULL,
3330 PRIMARY KEY(c1)
3331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
3332 Warnings:
3333 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3334 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.
3335 CREATE TABLE t2 (
3336 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3337 c2 INT(10) UNSIGNED DEFAULT NULL,
3338 PRIMARY KEY(c1)
3339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
3340 Warnings:
3341 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3342 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3343 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.
3344 ANALYZE TABLE t2;
3345 Table Op Msg_type Msg_text
3346 test.t2 analyze status OK
3347 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3348 AUTO_INCREMENT
3349 200
3350 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
3351 ANALYZE TABLE t2;
3352 Table Op Msg_type Msg_text
3353 test.t2 analyze status OK
3354 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3355 AUTO_INCREMENT
3356 200
3357 DROP TABLE t2;
3358 DROP TABLE t1;
3359 CREATE TABLE t1 (c1 int default NULL,
3360 c2 int default NULL
3361 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
3362 TRUNCATE TABLE t1;
3363 affected rows: 0
3364 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
3365 affected rows: 5
3366 info: Records: 5 Duplicates: 0 Warnings: 0
3367 TRUNCATE TABLE t1;
3368 affected rows: 0
3369 DROP TABLE t1;
3370 Variable_name Value
3371 Handler_update 10
3372 Variable_name Value
3373 Handler_delete 0
3374 Variable_name Value
3375 Handler_update 11
3376 Variable_name Value
3377 Handler_delete 1
3378 DROP TABLE bug58912;
3379 create table t1 (f1 integer primary key) engine=innodb;
3380 flush status;
3381 show status like "handler_read_key";
3382 Variable_name Value
3383 Handler_read_key 0
3384 select f1 from t1;
3385 f1
3386 show status like "handler_read_key";
3387 Variable_name Value
3388 Handler_read_key 1
3389 drop table t1;
3390 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
3391 CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB;
3392 START TRANSACTION READ ONLY;
3393 INSERT INTO t2 VALUES(0);
3394 INSERT INTO t1 VALUES(0);
3395 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3396 ROLLBACK;
3397 SELECT * FROM t1;
3398 c1
3399 SELECT * FROM t2;
3400 c1
3401 START TRANSACTION READ ONLY;
3402 INSERT INTO t1 VALUES(0);
3403 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3404 INSERT INTO t2 VALUES(1);
3405 COMMIT;
3406 SET TRANSACTION READ ONLY;
3407 START TRANSACTION;
3408 INSERT INTO t2 VALUES(3);
3409 INSERT INTO t1 VALUES(0);
3410 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3411 COMMIT;
3412 SELECT * FROM t1;
3413 c1
3414 SELECT * FROM t2;
3415 c1
3416 1
3417 3
3418 DROP TABLE t2;
3419 CREATE TEMPORARY TABLE t2 (
3420 c1 INT AUTO_INCREMENT PRIMARY KEY,
3421 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
3422 SHOW CREATE TABLE t2;
3423 Table Create Table
3424 t2 CREATE TEMPORARY TABLE `t2` (
3425 `c1` int NOT NULL AUTO_INCREMENT,
3426 `c2` int DEFAULT NULL,
3427 PRIMARY KEY (`c1`),
3428 KEY `idx` (`c2`)
3429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3430 START TRANSACTION READ ONLY;
3431 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
3432 INSERT INTO t1 VALUES(0);
3433 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3434 ROLLBACK;
3435 SELECT * FROM t1;
3436 c1
3437 SELECT * FROM t2;
3438 c1 c2
3439 START TRANSACTION READ ONLY;
3440 INSERT INTO t1 VALUES(0);
3441 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3442 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
3443 COMMIT;
3444 SET TRANSACTION READ ONLY;
3445 START TRANSACTION;
3446 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
3447 INSERT INTO t1 VALUES(0);
3448 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
3449 COMMIT;
3450 SHOW CREATE TABLE t2;
3451 Table Create Table
3452 t2 CREATE TEMPORARY TABLE `t2` (
3453 `c1` int NOT NULL AUTO_INCREMENT,
3454 `c2` int DEFAULT NULL,
3455 PRIMARY KEY (`c1`),
3456 KEY `idx` (`c2`)
3457 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3458 SELECT * FROM t1;
3459 c1
3460 SELECT * FROM t2;
3461 c1 c2
3462 4 1
3463 5 2
3464 6 3
3465 7 1
3466 8 2
3467 9 3
3468 DROP TABLE t1;
3469 DROP TABLE t2;
3470 count_alloc WHERE event_name like 'memory/innodb/other'
3471 count_alloc
3472 0
3473 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
3474 Warnings:
3475 Warning 1287 '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release.
3476 SET GLOBAL log_bin_trust_function_creators = 1;
3477 Warnings:
3478 Warning 1287 '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release.
3479 create table t1 (col1 integer primary key, col2 integer) engine=innodb;
3480 insert t1 values (1,100);
3481 create function f1 () returns integer begin
3482 declare var1 int;
3483 select col2 into var1 from t1 where col1=1 for update;
3484 return var1;
3485 end|
3486 start transaction;
3487 select f1();
3488 f1()
3489 100
3490 update t1 set col2=0 where col1=1;
3491 select * from t1;
3492 col1 col2
3493 1 100
3494 rollback;
3495 rollback;
3496 drop table t1;
3497 drop function f1;
3498 SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
3499 Warnings:
3500 Warning 1287 '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release.