Debian Codegraph @

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

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