1
drop table if exists t1,t2,t3,t4;
2
drop database if exists mysqltest;
3
create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
4
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
5
select id, code, name from t1 order by id;
14
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
15
select id, code, name from t1 order by id;
24
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
25
select id, code, name from t1 order by id;
36
id int NOT NULL auto_increment,
37
parent_id int DEFAULT '0' NOT NULL,
38
level int DEFAULT '0' NOT NULL,
40
KEY parent_id (parent_id),
43
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);
44
update t1 set parent_id=parent_id+100;
45
select * from t1 where parent_id=102;
50
update t1 set id=id+1000;
51
update t1 set id=1024 where id=1009;
52
Got one of the listed errors
94
update ignore t1 set id=id+1;
136
update ignore t1 set id=1023 where id=1010;
137
select * from t1 where parent_id=102;
142
explain select level from t1 where level=1;
143
id select_type table type possible_keys key key_len ref rows Extra
144
1 SIMPLE t1 ref level level 4 const # Using index
145
explain select level,id from t1 where level=1;
146
id select_type table type possible_keys key key_len ref rows Extra
147
1 SIMPLE t1 ref level level 4 const # Using index
148
explain select level,id,parent_id from t1 where level=1;
149
id select_type table type possible_keys key key_len ref rows Extra
150
1 SIMPLE t1 ref level level 4 const #
151
select level,id from t1 where level=1;
159
select level,id,parent_id from t1 where level=1;
167
alter table t1 ENGINE=innodb;
169
Table Unique Key_name Seq_in_index Column_name
171
t1 FALSE parent_id 1 parent_id
172
t1 FALSE level 1 level
175
gesuchnr int DEFAULT '0' NOT NULL,
176
benutzer_id int DEFAULT '0' NOT NULL,
177
PRIMARY KEY (gesuchnr,benutzer_id)
179
replace into t1 (gesuchnr,benutzer_id) values (2,1);
180
replace into t1 (gesuchnr,benutzer_id) values (1,1);
181
replace into t1 (gesuchnr,benutzer_id) values (1,1);
187
create table t1 (a int) engine=innodb;
188
insert into t1 values (1), (2);
189
alter table t1 engine=innodb;
190
delete from t1 where a = 1;
195
Table Op Msg_type Msg_text
196
test.t1 check status OK
198
create table t1 (a int,b varchar(20)) engine=innodb;
199
insert into t1 values (1,""), (2,"testing");
200
delete from t1 where a = 1;
204
create index skr on t1 (a);
205
insert into t1 values (3,""), (4,"testing");
207
Table Op Msg_type Msg_text
208
test.t1 analyze status OK
210
Table Unique Key_name Seq_in_index Column_name
213
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
214
insert into t1 values (1,""), (2,"testing");
215
select * from t1 where a = 1;
219
create table t1 (n int not null primary key) engine=innodb;
221
insert into t1 values (4);
223
select n, "after rollback" from t1;
225
insert into t1 values (4);
227
select n, "after commit" from t1;
231
insert into t1 values (5);
232
insert into t1 values (4);
233
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
235
select n, "after commit" from t1;
240
insert into t1 values (6);
241
insert into t1 values (4);
242
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
250
savepoint `my_savepoint`;
251
insert into t1 values (7);
253
insert into t1 values (3);
262
rollback to savepoint savept2;
263
rollback to savepoint savept3;
264
ERROR 42000: SAVEPOINT savept3 does not exist
265
rollback to savepoint savept2;
266
release savepoint `my_savepoint`;
273
rollback to savepoint `my_savepoint`;
274
ERROR 42000: SAVEPOINT my_savepoint does not exist
275
rollback to savepoint savept2;
276
insert into t1 values (8);
283
create table t1 (n int not null primary key) engine=innodb;
285
insert into t1 values (4);
286
flush tables with read lock;
294
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
296
insert into t1 values(1,'hamdouni');
297
select id as afterbegin_id,nom as afterbegin_nom from t1;
298
afterbegin_id afterbegin_nom
301
select id as afterrollback_id,nom as afterrollback_nom from t1;
302
afterrollback_id afterrollback_nom
304
insert into t1 values(2,'mysql');
305
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
306
afterautocommit0_id afterautocommit0_nom
309
select id as afterrollback_id,nom as afterrollback_nom from t1;
310
afterrollback_id afterrollback_nom
313
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
314
insert into t1 values ('pippo', 12);
315
insert into t1 values ('pippo', 12);
316
ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
318
delete from t1 where id = 'pippo';
321
insert into t1 values ('pippo', 12);
333
create table t1 (a integer) engine=innodb;
335
rename table t1 to t2;
336
create table t1 (b integer) engine=innodb;
337
insert into t1 values (1);
340
rename table t2 to t1;
343
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
344
INSERT INTO t1 VALUES (1, 'Jochen');
349
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
351
INSERT INTO t1 SET _userid='marc@anyware.co.uk';
356
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
362
user_id int DEFAULT '0' NOT NULL,
365
ref_email varchar(100) DEFAULT '' NOT NULL,
367
PRIMARY KEY (user_id,ref_email)
369
INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
370
select * from t1 where user_id=10292;
371
user_id name phone ref_email detail
372
10292 sanjeev 29153373 sansh777@hotmail.com xxx
373
10292 shirish 2333604 shirish@yahoo.com ddsds
374
10292 sonali 323232 sonali@bolly.com filmstar
375
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
376
select * from t1 where user_id=10292;
377
user_id name phone ref_email detail
378
10292 sanjeev 29153373 sansh777@hotmail.com xxx
379
10292 shirish 2333604 shirish@yahoo.com ddsds
380
10292 sonali 323232 sonali@bolly.com filmstar
381
select * from t1 where user_id>=10292;
382
user_id name phone ref_email detail
383
10292 sanjeev 29153373 sansh777@hotmail.com xxx
384
10292 shirish 2333604 shirish@yahoo.com ddsds
385
10292 sonali 323232 sonali@bolly.com filmstar
386
10293 shirish 2333604 shirish@yahoo.com ddsds
387
select * from t1 where user_id>10292;
388
user_id name phone ref_email detail
389
10293 shirish 2333604 shirish@yahoo.com ddsds
390
select * from t1 where user_id<10292;
391
user_id name phone ref_email detail
392
10291 sanjeev 29153373 sansh777@hotmail.com xxx
394
CREATE TABLE t1 (a int not null, b int not null,c int not null,
395
key(a),primary key(a,b), unique(c),key(a),unique(b));
397
Table Unique Key_name Seq_in_index Column_name
405
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
406
alter table t1 engine=innodb;
407
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
415
update t1 set col2='7' where col1='4';
423
alter table t1 add co3 int not null;
431
update t1 set col2='9' where col1='2';
440
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
441
create TEMPORARY table t2 (a int not null , b int, primary key (a)) engine = myisam;
442
insert into t1 VALUES (1,3) , (2,3), (3,3);
448
insert into t2 select * from t1;
454
delete from t1 where b = 3;
457
insert into t1 select * from t2;
470
user_name varchar(12),
473
user_id int DEFAULT '0' NOT NULL,
478
dummy_primary_key int NOT NULL auto_increment,
479
PRIMARY KEY (dummy_primary_key)
481
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);
482
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);
483
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);
484
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);
485
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);
486
select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;
487
user_name password subscribed user_id quota weight access_date approved dummy_primary_key
488
user_0 somepassword N 0 0 0 2000-09-07 2000-09-07 23:06:59 1
489
user_1 somepassword Y 1 1 1 2000-09-07 2000-09-07 23:06:59 2
490
user_2 somepassword N 2 2 1.4142135623731 2000-09-07 2000-09-07 23:06:59 3
491
user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 2000-09-07 23:06:59 4
492
user_4 somepassword N 4 4 2 2000-09-07 2000-09-07 23:06:59 5
495
id int NOT NULL auto_increment,
496
parent_id int DEFAULT '0' NOT NULL,
497
level int DEFAULT '0' NOT NULL,
499
KEY parent_id (parent_id),
502
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);
503
INSERT INTO t1 values (179,5,2);
504
update t1 set parent_id=parent_id+100;
505
select * from t1 where parent_id=102;
510
update t1 set id=id+1000;
511
update t1 set id=1024 where id=1009;
553
update ignore t1 set id=id+1;
595
update ignore t1 set id=1023 where id=1010;
596
select * from t1 where parent_id=102;
601
explain select level from t1 where level=1;
602
id select_type table type possible_keys key key_len ref rows Extra
603
1 SIMPLE t1 ref level level 4 const # Using index
604
select level,id from t1 where level=1;
612
select level,id,parent_id from t1 where level=1;
620
select level,id from t1 where level=1 order by id;
628
delete from t1 where level=1;
666
sca_code char(6) NOT NULL,
667
cat_code char(6) NOT NULL,
668
sca_desc varchar(50),
669
lan_code char(2) NOT NULL,
670
sca_pic varchar(100),
671
sca_sdesc varchar(50),
672
sca_sch_desc varchar(16),
673
PRIMARY KEY (sca_code, cat_code, lan_code),
674
INDEX sca_pic (sca_pic)
676
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');
677
select count(*) from t1 where sca_code = 'PD';
680
select count(*) from t1 where sca_code <= 'PD';
683
select count(*) from t1 where sca_pic is null;
686
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
687
select count(*) from t1 where sca_code='PD' and sca_pic is null;
690
select count(*) from t1 where cat_code='E';
693
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
694
select count(*) from t1 where sca_code='PD' and sca_pic is null;
697
select count(*) from t1 where sca_pic >= 'n';
700
select sca_pic from t1 where sca_pic is null;
704
update t1 set sca_pic="test" where sca_pic is null;
705
delete from t1 where sca_code='pd';
708
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
709
insert into t1 (a) values(1),(2),(3);
710
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
715
select a from t1 natural join t1 as t2 where b >= @a order by a;
720
update t1 set a=5 where a=1;
727
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
728
insert into t1 values("hello",1),("world",2);
729
select * from t1 order by b desc;
733
alter table t1 engine=innodb;
735
Table Unique Key_name Seq_in_index Column_name
738
create table t1 (i int, j int ) ENGINE=innodb;
739
insert into t1 values (1,2);
740
select * from t1 where i=1 and j=2;
743
create index ax1 on t1 (i,j);
744
select * from t1 where i=1 and j=2;
753
INSERT INTO t1 VALUES (1, 1);
754
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
758
CREATE TABLE t1 (a int NOT NULL) engine=innodb;
759
INSERT INTO t1 VALUES (1);
764
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;
765
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);
766
explain select * from t1 where a > 0 and a < 50;
767
id select_type table type possible_keys key key_len ref rows Extra
768
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where
770
create table t1 (a char(20), unique (a(5))) engine=innodb;
772
create table t1 (a char(20), index (a(5))) engine=innodb;
773
show create table t1;
775
t1 CREATE TABLE `t1` (
776
`a` varchar(20) DEFAULT NULL,
780
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
781
insert into t1 values (NULL),(NULL),(NULL);
782
delete from t1 where a=3;
783
insert into t1 values (NULL);
789
alter table t1 add b int;
798
id int auto_increment primary key,
799
name varchar(32) not null,
804
insert into t1 values (1,'one','one value',101),
805
(2,'two','two value',102),(3,'three','three value',103);
806
replace into t1 (value,name,uid) values ('other value','two',102);
807
delete from t1 where uid=102;
808
replace into t1 (value,name,uid) values ('other value','two',102);
809
replace into t1 (value,name,uid) values ('other value','two',102);
813
3 three three value 103
814
6 two other value 102
816
create database mysqltest;
817
create table mysqltest.t1 (a int not null) engine= innodb;
818
insert into mysqltest.t1 values(1);
819
create TEMPORARY table mysqltest.t2 (a int not null) engine= myisam;
820
insert into mysqltest.t2 values(1);
821
create temporary table mysqltest.t3 (a int not null) engine= MEMORY;
822
insert into mysqltest.t3 values(1);
824
drop database mysqltest;
825
show tables from mysqltest;
826
ERROR 42000: Unknown database 'mysqltest'
828
create table t1 (a int not null) engine= innodb;
829
insert into t1 values(1),(2);
836
insert into t1 values(1),(2);
843
create table t1 (a int not null) engine= innodb;
844
insert into t1 values(1),(2);
846
insert into t1 values(1),(2);
852
insert into t1 values(1),(2);
857
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
858
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
859
explain select * from t1 order by a;
860
id select_type table type possible_keys key key_len ref rows Extra
861
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
862
explain select * from t1 order by b;
863
id select_type table type possible_keys key key_len ref rows Extra
864
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
865
explain select * from t1 order by c;
866
id select_type table type possible_keys key key_len ref rows Extra
867
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
868
explain select a from t1 order by a;
869
id select_type table type possible_keys key key_len ref rows Extra
870
1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index
871
explain select b from t1 order by b;
872
id select_type table type possible_keys key key_len ref rows Extra
873
1 SIMPLE t1 index NULL b 4 NULL # Using index
874
explain select a,b from t1 order by b;
875
id select_type table type possible_keys key key_len ref rows Extra
876
1 SIMPLE t1 index NULL b 4 NULL # Using index
877
explain select a,b from t1;
878
id select_type table type possible_keys key key_len ref rows Extra
879
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
880
explain select a,b,c from t1;
881
id select_type table type possible_keys key key_len ref rows Extra
882
1 SIMPLE t1 ALL NULL NULL NULL NULL #
884
create table t1 (t int not null default 1, key (t)) engine=innodb;
886
Field Type Null Default Default_is_NULL On_Update
887
t INTEGER FALSE 1 FALSE
889
create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
891
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
892
SELECT @@tx_isolation,@@global.tx_isolation;
893
@@tx_isolation @@global.tx_isolation
894
SERIALIZABLE REPEATABLE-READ
895
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
896
select id, code, name from t1 order by id;
903
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
904
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
905
select id, code, name from t1 order by id;
914
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
915
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
916
select id, code, name from t1 order by id;
927
create table t1 (a int, b int) engine=innodb;
928
insert into t1 values(20,null);
929
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
931
b ifnull(t2.b,"this is null")
933
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
934
t2.b=t3.a order by 1;
935
b ifnull(t2.b,"this is null")
937
insert into t1 values(10,null);
938
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
939
t2.b=t3.a order by 1;
940
b ifnull(t2.b,"this is null")
944
create TEMPORARY table t1 (a varchar(10) not null) engine=myisam;
945
create table t2 (b varchar(10) not null unique) engine=innodb;
946
select t1.a from t1,t2 where t1.a=t2.b;
949
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
950
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
951
insert into t1 values (10, 20);
952
insert into t2 values (10, 20);
955
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
956
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
957
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
958
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
960
INSERT INTO t1 VALUES("this-key", "will disappear");
961
INSERT INTO t2 VALUES("this-key", "will also disappear");
962
DELETE FROM t3 WHERE id1="my-test-1";
965
this-key will disappear
968
this-key will also disappear
979
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
985
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
986
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
987
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1000
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1001
create table t2 (a int not null auto_increment primary key, b int);
1002
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1003
insert into t2 (a) select b from t1;
1004
insert into t1 (b) select b from t2;
1005
insert into t2 (a) select b from t1;
1006
insert into t1 (a) select b from t2;
1007
insert into t2 (a) select b from t1;
1008
insert into t1 (a) select b from t2;
1009
insert into t2 (a) select b from t1;
1010
insert into t1 (a) select b from t2;
1011
insert into t2 (a) select b from t1;
1012
insert into t1 (a) select b from t2;
1013
select count(*) from t1;
1016
explain select * from t1 where c between 1 and 2500;
1017
id select_type table type possible_keys key key_len ref rows Extra
1018
1 SIMPLE t1 range c c 5 NULL # Using where
1020
explain select * from t1 where c between 1 and 2500;
1021
id select_type table type possible_keys key key_len ref rows Extra
1022
1 SIMPLE t1 ALL c NULL NULL NULL # Using where
1024
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1025
insert into t1 (id) values (null),(null),(null),(null),(null);
1026
update t1 set fk=69 where fk is null order by id limit 1;
1035
create table t1 (a int not null, b int not null, key (a));
1036
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);
1038
update t1 set b=(@tmp:=@tmp+1) order by a;
1039
update t1 set b=99 where a=1 order by b asc limit 1;
1040
update t1 set b=100 where a=1 order by b desc limit 2;
1041
update t1 set a=a+10+b where a=1 order by b;
1042
select * from t1 order by a,b;
1058
create table t1 (a integer auto_increment primary key) engine=innodb;
1059
insert into t1 (a) values (NULL),(NULL);
1061
insert into t1 (a) values (NULL),(NULL);
1067
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1068
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;
1074
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1075
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1076
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1077
delete from t1 where id=0;
1078
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t1` (`id`) ON DELETE CASCADE)
1079
delete from t1 where id=15;
1080
delete from t1 where id=0;
1082
CREATE TABLE t1 (col1 int) ENGINE=InnoDB;
1083
CREATE TABLE t2 (col1 int, stamp TIMESTAMP,INDEX stamp_idx (stamp)) ENGINE=InnoDB;
1084
insert into t1 values (1),(2),(3);
1085
insert into t2 values (1, 20020204110000),(2, 20020204110001),(4,20020204110002 ),(5,20020204110003);
1086
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1087
'20020204120000' GROUP BY col1;
1096
`id` int NOT NULL auto_increment,
1097
`id_object` int default '0',
1098
`id_version` int NOT NULL default '1',
1099
`label` varchar(100) NOT NULL default '',
1102
KEY `id_object` (`id_object`),
1103
KEY `id_version` (`id_version`)
1105
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);
1107
`id` int NOT NULL auto_increment,
1108
`id_version` int NOT NULL default '1',
1110
KEY `id_version` (`id_version`)
1112
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1113
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1114
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1115
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1122
3525 Fournisseur Test
1124
create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=myisam;
1125
create table t2 (a int, b varchar(200), c text not null) engine=innodb;
1126
create table t3 (a int, b varchar(200), c text not null) engine=innodb;
1127
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1128
insert t2 select * from t1;
1129
insert t3 select * from t1;
1130
checksum table t1, t2, t3, t4;
1137
Error 1146 Table 'test.t4' doesn't exist
1138
checksum table t1, t2, t3, t4;
1145
Error 1146 Table 'test.t4' doesn't exist
1146
checksum table t1, t2, t3, t4;
1153
Error 1146 Table 'test.t4' doesn't exist
1154
drop table t1,t2,t3;
1155
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1156
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1157
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1169
create table t1 (a int) engine=innodb;
1170
create table t2 like t1;
1172
create table t1 (id int not null, id2 int not null, unique (id,id2)) engine=innodb;
1173
create table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1174
show create table t1;
1176
t1 CREATE TABLE `t1` (
1179
UNIQUE KEY `id` (`id`,`id2`)
1181
show create table t2;
1183
t2 CREATE TABLE `t2` (
1185
KEY `t1_id_fk` (`id`),
1186
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1188
create index id on t2 (id);
1189
show create table t2;
1191
t2 CREATE TABLE `t2` (
1194
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1196
create index id2 on t2 (id);
1197
show create table t2;
1199
t2 CREATE TABLE `t2` (
1203
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1205
drop index id2 on t2;
1206
drop index id on t2;
1207
Got one of the listed errors
1208
show create table t2;
1210
t2 CREATE TABLE `t2` (
1213
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1216
create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
1217
show create table t2;
1219
t2 CREATE TABLE `t2` (
1222
KEY `t1_id_fk` (`id`,`id2`),
1223
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1225
create unique index id on t2 (id,id2);
1226
show create table t2;
1228
t2 CREATE TABLE `t2` (
1231
UNIQUE KEY `id` (`id`,`id2`),
1232
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1235
create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1236
show create table t2;
1238
t2 CREATE TABLE `t2` (
1241
UNIQUE KEY `id` (`id`,`id2`),
1242
KEY `t1_id_fk` (`id2`,`id`),
1243
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1246
create table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
1247
show create table t2;
1249
t2 CREATE TABLE `t2` (
1252
UNIQUE KEY `id` (`id`,`id2`),
1253
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1256
create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1257
show create table t2;
1259
t2 CREATE TABLE `t2` (
1262
UNIQUE KEY `id` (`id`,`id2`),
1263
KEY `t1_id_fk` (`id2`,`id`),
1264
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1267
create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
1268
show create table t2;
1270
t2 CREATE TABLE `t2` (
1271
`id` int NOT NULL AUTO_INCREMENT,
1274
KEY `id` (`id`,`id2`),
1275
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1278
create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
1279
show create table t2;
1281
t2 CREATE TABLE `t2` (
1282
`id` int NOT NULL AUTO_INCREMENT,
1284
KEY `t1_id_fk` (`id`),
1285
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1287
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1288
show create table t2;
1290
t2 CREATE TABLE `t2` (
1291
`id` int NOT NULL AUTO_INCREMENT,
1293
KEY `id_test` (`id`),
1294
KEY `id_test2` (`id`,`id2`),
1295
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1298
create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
1299
ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1300
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1301
show create table t2;
1303
t2 CREATE TABLE `t2` (
1304
`a` int NOT NULL AUTO_INCREMENT,
1305
`b` int DEFAULT NULL,
1307
UNIQUE KEY `b_2` (`b`),
1309
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1312
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;
1313
show create table t2;
1315
t2 CREATE TABLE `t2` (
1316
`a` int NOT NULL AUTO_INCREMENT,
1317
`b` int DEFAULT NULL,
1319
UNIQUE KEY `b` (`b`),
1320
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1321
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1324
create table t1 (c char(10), index (c,c)) engine=innodb;
1325
ERROR 42S21: Duplicate column name 'c'
1326
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1327
ERROR 42S21: Duplicate column name 'c1'
1328
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1329
ERROR 42S21: Duplicate column name 'c1'
1330
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1331
ERROR 42S21: Duplicate column name 'c1'
1332
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1333
alter table t1 add key (c1,c1);
1334
ERROR 42S21: Duplicate column name 'c1'
1335
alter table t1 add key (c2,c1,c1);
1336
ERROR 42S21: Duplicate column name 'c1'
1337
alter table t1 add key (c1,c2,c1);
1338
ERROR 42S21: Duplicate column name 'c1'
1339
alter table t1 add key (c1,c1,c2);
1340
ERROR 42S21: Duplicate column name 'c1'
1342
create table t1(a int, b int) engine=innodb;
1343
insert into t1 values ('1111', '3333');
1344
select distinct concat(a, b) from t1;
1348
CREATE TABLE t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB;
1349
INSERT INTO t1 VALUES (1),(2),(3);
1350
CREATE TABLE t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),
1351
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB;
1352
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1353
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;
1360
create temporary table t1 (a int) engine=innodb;
1361
insert into t1 values (4711);
1363
insert into t1 values (42);
1368
create table t1 (a int) engine=innodb;
1369
insert into t1 values (4711);
1371
insert into t1 values (42);
1376
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;
1377
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1378
select * from t1 order by a,b,c,d;
1383
explain select * from t1 order by a,b,c,d;
1384
id select_type table type possible_keys key key_len ref rows Extra
1385
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1387
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1388
insert into t1 values ('8', '6'), ('4', '7');
1389
select min(a) from t1;
1392
select min(b) from t1 where a='8';
1396
create table t1 (x bigint not null primary key) engine=innodb;
1397
insert into t1(x) values (0x0ffffffffffffff0),(0x0ffffffffffffff1);
1402
select count(*) from t1 where x>0;
1405
select count(*) from t1 where x=0;
1408
select count(*) from t1 where x<0;
1411
select count(*) from t1 where x < -16;
1414
select count(*) from t1 where x = -16;
1417
explain select count(*) from t1 where x > -16;
1418
id select_type table type possible_keys key key_len ref rows Extra
1419
1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index
1420
select count(*) from t1 where x > -16;
1423
select * from t1 where x > -16;
1427
select count(*) from t1 where x = 1152921504606846961;
1431
show status like "Innodb_buffer_pool_pages_total";
1433
show status like "Innodb_page_size";
1435
show status like "Innodb_rows_deleted";
1437
show status like "Innodb_rows_inserted";
1439
show status like "Innodb_rows_updated";
1441
show status like "Innodb_row_lock_waits";
1443
show status like "Innodb_row_lock_current_waits";
1445
show status like "Innodb_row_lock_time";
1447
show status like "Innodb_row_lock_time_max";
1449
show status like "Innodb_row_lock_time_avg";
1451
show variables like "innodb_sync_spin_loops";
1453
innodb_sync_spin_loops 30
1454
set global innodb_sync_spin_loops=1000;
1455
show variables like "innodb_sync_spin_loops";
1457
innodb_sync_spin_loops 1000
1458
set global innodb_sync_spin_loops=0;
1459
show variables like "innodb_sync_spin_loops";
1461
innodb_sync_spin_loops 0
1462
set global innodb_sync_spin_loops=20;
1463
show variables like "innodb_sync_spin_loops";
1465
innodb_sync_spin_loops 20
1466
show variables like "innodb_thread_concurrency";
1468
innodb_thread_concurrency 0
1469
set global innodb_thread_concurrency=1001;
1471
Error 1292 Truncated incorrect thread_concurrency value: '1001'
1472
show variables like "innodb_thread_concurrency";
1474
innodb_thread_concurrency 1000
1475
set global innodb_thread_concurrency=0;
1476
show variables like "innodb_thread_concurrency";
1478
innodb_thread_concurrency 0
1479
set global innodb_thread_concurrency=16;
1480
show variables like "innodb_thread_concurrency";
1482
innodb_thread_concurrency 16
1483
show variables like "innodb_concurrency_tickets";
1485
innodb_concurrency_tickets 500
1486
set global innodb_concurrency_tickets=1000;
1487
show variables like "innodb_concurrency_tickets";
1489
innodb_concurrency_tickets 1000
1490
set global innodb_concurrency_tickets=0;
1492
Error 1292 Truncated incorrect concurrency_tickets value: '0'
1493
show variables like "innodb_concurrency_tickets";
1495
innodb_concurrency_tickets 1
1496
set global innodb_concurrency_tickets=500;
1497
show variables like "innodb_concurrency_tickets";
1499
innodb_concurrency_tickets 500
1500
show variables like "innodb_thread_sleep_delay";
1502
innodb_thread_sleep_delay 10000
1503
set global innodb_thread_sleep_delay=100000;
1504
show variables like "innodb_thread_sleep_delay";
1506
innodb_thread_sleep_delay 100000
1507
set global innodb_thread_sleep_delay=0;
1508
show variables like "innodb_thread_sleep_delay";
1510
innodb_thread_sleep_delay 0
1511
set global innodb_thread_sleep_delay=10000;
1512
show variables like "innodb_thread_sleep_delay";
1514
innodb_thread_sleep_delay 10000
1515
set storage_engine=INNODB;
1516
drop table if exists t1,t2,t3;
1517
--- Testing varchar ---
1518
--- Testing varchar ---
1519
create table t1 (v varchar(10), c char(10), t text);
1520
insert into t1 values('+ ', '+ ', '+ ');
1521
set @a=repeat(' ',20);
1522
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1524
Note 1265 Data truncated for column 'v' at row 1
1525
Note 1265 Data truncated for column 'c' at row 1
1526
select concat('*',v,'*',c,'*',t,'*') from t1;
1527
concat('*',v,'*',c,'*',t,'*')
1530
show create table t1;
1532
t1 CREATE TABLE `t1` (
1533
`v` varchar(10) DEFAULT NULL,
1534
`c` varchar(10) DEFAULT NULL,
1537
create table t2 like t1;
1538
show create table t2;
1540
t2 CREATE TABLE `t2` (
1541
`v` varchar(10) DEFAULT NULL,
1542
`c` varchar(10) DEFAULT NULL,
1545
create table t3 select * from t1;
1546
show create table t3;
1548
t3 CREATE TABLE `t3` (
1549
`v` varchar(10) DEFAULT NULL,
1550
`c` varchar(10) DEFAULT NULL,
1553
alter table t1 modify c varchar(10);
1554
show create table t1;
1556
t1 CREATE TABLE `t1` (
1557
`v` varchar(10) DEFAULT NULL,
1558
`c` varchar(10) DEFAULT NULL,
1561
alter table t1 modify v char(10);
1562
show create table t1;
1564
t1 CREATE TABLE `t1` (
1565
`v` varchar(10) DEFAULT NULL,
1566
`c` varchar(10) DEFAULT NULL,
1569
alter table t1 modify t varchar(10);
1571
Note 1265 Data truncated for column 't' at row 2
1572
show create table t1;
1574
t1 CREATE TABLE `t1` (
1575
`v` varchar(10) DEFAULT NULL,
1576
`c` varchar(10) DEFAULT NULL,
1577
`t` varchar(10) DEFAULT NULL
1579
select concat('*',v,'*',c,'*',t,'*') from t1;
1580
concat('*',v,'*',c,'*',t,'*')
1583
drop table t1,t2,t3;
1584
create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1585
show create table t1;
1587
t1 CREATE TABLE `t1` (
1588
`v` varchar(10) DEFAULT NULL,
1589
`c` varchar(10) DEFAULT NULL,
1595
select count(*) from t1;
1598
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1599
select count(*) from t1 where v='a';
1602
select count(*) from t1 where c='a';
1605
select count(*) from t1 where t='a';
1608
select count(*) from t1 where v='a ';
1611
select count(*) from t1 where c='a ';
1614
select count(*) from t1 where t='a ';
1617
select count(*) from t1 where v between 'a' and 'a ';
1620
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1623
select count(*) from t1 where v like 'a%';
1626
select count(*) from t1 where c like 'a%';
1629
select count(*) from t1 where t like 'a%';
1632
select count(*) from t1 where v like 'a %';
1635
explain select count(*) from t1 where v='a ';
1636
id select_type table type possible_keys key key_len ref rows Extra
1637
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1638
explain select count(*) from t1 where c='a ';
1639
id select_type table type possible_keys key key_len ref rows Extra
1640
1 SIMPLE t1 ref c c 43 const # Using where; Using index
1641
explain select count(*) from t1 where t='a ';
1642
id select_type table type possible_keys key key_len ref rows Extra
1643
1 SIMPLE t1 ref t t 43 const # Using where
1644
explain select count(*) from t1 where v like 'a%';
1645
id select_type table type possible_keys key key_len ref rows Extra
1646
1 SIMPLE t1 range v v 43 NULL # Using where; Using index
1647
explain select count(*) from t1 where v between 'a' and 'a ';
1648
id select_type table type possible_keys key key_len ref rows Extra
1649
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1650
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1651
id select_type table type possible_keys key key_len ref rows Extra
1652
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1653
alter table t1 add unique(v);
1654
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
1655
alter table t1 add key(v);
1656
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1668
explain select * from t1 where v='a';
1669
id select_type table type possible_keys key key_len ref rows Extra
1670
1 SIMPLE t1 ref v,v_2 # 43 const # Using where
1671
select v,count(*) from t1 group by v limit 10;
1683
select v,count(t) from t1 group by v limit 10;
1695
select v,count(c) from t1 group by v limit 10;
1707
select sql_big_result v,count(t) from t1 group by v limit 10;
1719
select sql_big_result v,count(c) from t1 group by v limit 10;
1731
select c,count(*) from t1 group by c limit 10;
1743
select c,count(t) from t1 group by c limit 10;
1755
select sql_big_result c,count(t) from t1 group by c limit 10;
1767
select t,count(*) from t1 group by t limit 10;
1779
select t,count(t) from t1 group by t limit 10;
1791
select sql_big_result t,count(t) from t1 group by t limit 10;
1803
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
1805
Warning 1071 Specified key was too long; max key length is 767 bytes
1806
show create table t1;
1808
t1 CREATE TABLE `t1` (
1809
`v` varchar(300) DEFAULT NULL,
1810
`c` varchar(10) DEFAULT NULL,
1816
select count(*) from t1 where v='a';
1819
select count(*) from t1 where v='a ';
1822
select count(*) from t1 where v between 'a' and 'a ';
1825
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1828
select count(*) from t1 where v like 'a%';
1831
select count(*) from t1 where v like 'a %';
1834
explain select count(*) from t1 where v='a ';
1835
id select_type table type possible_keys key key_len ref rows Extra
1836
1 SIMPLE t1 ref v v 767 const # Using where
1837
explain select count(*) from t1 where v like 'a%';
1838
id select_type table type possible_keys key key_len ref rows Extra
1839
1 SIMPLE t1 range v v 767 NULL # Using where
1840
explain select count(*) from t1 where v between 'a' and 'a ';
1841
id select_type table type possible_keys key key_len ref rows Extra
1842
1 SIMPLE t1 ref v v 767 const # Using where
1843
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1844
id select_type table type possible_keys key key_len ref rows Extra
1845
1 SIMPLE t1 ref v v 767 const # Using where
1846
explain select * 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 767 const # Using where
1849
select v,count(*) from t1 group by v limit 10;
1861
select v,count(t) from t1 group by v limit 10;
1873
select sql_big_result v,count(t) from t1 group by v limit 10;
1885
alter table t1 drop key v, add key v (v(30));
1886
show create table t1;
1888
t1 CREATE TABLE `t1` (
1889
`v` varchar(300) DEFAULT NULL,
1890
`c` varchar(10) DEFAULT NULL,
1896
select count(*) from t1 where v='a';
1899
select count(*) from t1 where v='a ';
1902
select count(*) from t1 where v between 'a' and 'a ';
1905
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1908
select count(*) from t1 where v like 'a%';
1911
select count(*) from t1 where v like 'a %';
1914
explain select count(*) from t1 where v='a ';
1915
id select_type table type possible_keys key key_len ref rows Extra
1916
1 SIMPLE t1 ref v v 123 const # Using where
1917
explain select count(*) from t1 where v like 'a%';
1918
id select_type table type possible_keys key key_len ref rows Extra
1919
1 SIMPLE t1 range v v 123 NULL # Using where
1920
explain select count(*) from t1 where v between 'a' and 'a ';
1921
id select_type table type possible_keys key key_len ref rows Extra
1922
1 SIMPLE t1 ref v v 123 const # Using where
1923
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1924
id select_type table type possible_keys key key_len ref rows Extra
1925
1 SIMPLE t1 ref v v 123 const # Using where
1926
explain select * from t1 where v='a';
1927
id select_type table type possible_keys key key_len ref rows Extra
1928
1 SIMPLE t1 ref v v 123 const # Using where
1929
select v,count(*) from t1 group by v limit 10;
1941
select v,count(t) from t1 group by v limit 10;
1953
select sql_big_result v,count(t) from t1 group by v limit 10;
1965
alter table t1 modify v varchar(600), drop key v, add key v (v);
1967
Warning 1071 Specified key was too long; max key length is 767 bytes
1968
show create table t1;
1970
t1 CREATE TABLE `t1` (
1971
`v` varchar(600) DEFAULT NULL,
1972
`c` varchar(10) DEFAULT NULL,
1978
select v,count(*) from t1 group by v limit 10;
1990
select v,count(t) from t1 group by v limit 10;
2002
select sql_big_result v,count(t) from t1 group by v limit 10;
2015
create table t1 (a char(10), unique (a));
2016
insert into t1 values ('a ');
2017
insert into t1 values ('a ');
2018
ERROR 23000: Duplicate entry 'a ' for key 'a'
2019
alter table t1 modify a varchar(10);
2020
insert into t1 values ('a '),('a '),('a '),('a ');
2021
ERROR 23000: Duplicate entry 'a ' for key 'a'
2022
insert into t1 values ('a ');
2023
ERROR 23000: Duplicate entry 'a ' for key 'a'
2024
insert into t1 values ('a ');
2025
ERROR 23000: Duplicate entry 'a ' for key 'a'
2026
insert into t1 values ('a ');
2027
ERROR 23000: Duplicate entry 'a ' for key 'a'
2028
update t1 set a='a ' where a like 'a%';
2029
select concat(a,'.') from t1;
2032
update t1 set a='abc ' where a like 'a ';
2033
select concat(a,'.') from t1;
2036
update t1 set a='a ' where a like 'a %';
2037
select concat(a,'.') from t1;
2040
update t1 set a='a ' where a like 'a ';
2041
select concat(a,'.') from t1;
2045
create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2046
show create table t1;
2048
t1 CREATE TABLE `t1` (
2049
`v` varchar(10) DEFAULT NULL,
2050
`c` varchar(10) DEFAULT NULL,
2057
create table t1 (v char(10));
2058
show create table t1;
2060
t1 CREATE TABLE `t1` (
2061
`v` varchar(10) DEFAULT NULL
2064
create table t1 (v varchar(10), c char(10));
2065
show create table t1;
2067
t1 CREATE TABLE `t1` (
2068
`v` varchar(10) DEFAULT NULL,
2069
`c` varchar(10) DEFAULT NULL
2071
insert into t1 values('a','a'),('a ','a ');
2072
select concat('*',v,'*',c,'*') from t1;
2073
concat('*',v,'*',c,'*')
2077
create table t1(a int, b varchar(12), key ba(b, a));
2078
insert into t1 values (1, 'A'), (20, NULL);
2079
explain select * from t1 where a=20 and b is null;
2080
id select_type table type possible_keys key key_len ref rows Extra
2081
1 SIMPLE t1 ref ba ba 56 const,const 1 Using where; Using index
2082
select * from t1 where a=20 and b is null;
2086
create table t1 (v varchar(16383), key(v));
2088
Warning 1071 Specified key was too long; max key length is 767 bytes
2090
create table t1 (v varchar(16383));
2091
show create table t1;
2093
t1 CREATE TABLE `t1` (
2094
`v` varchar(16383) DEFAULT NULL
2097
create table t1 (v varchar(16383));
2098
show create table t1;
2100
t1 CREATE TABLE `t1` (
2101
`v` varchar(16383) DEFAULT NULL
2104
set storage_engine=InnoDB;
2105
create table t1 (v varchar(16383)) engine=innodb;
2107
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2108
insert into t1 values ('8', '6'), ('4', '7');
2109
select min(a) from t1;
2112
select min(b) from t1 where a='8';
2116
CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2117
insert into t1 (b) values (1);
2118
replace into t1 (b) values (2), (1), (3);
2125
insert into t1 (b) values (1);
2126
replace into t1 (b) values (2);
2127
replace into t1 (b) values (1);
2128
replace into t1 (b) values (3);
2135
create table t1 (rowid int not null auto_increment, val int not null,primary
2136
key (rowid), unique(val)) engine=innodb;
2137
replace into t1 (val) values ('1'),('2');
2138
replace into t1 (val) values ('1'),('2');
2139
insert into t1 (val) values ('1'),('2');
2140
ERROR 23000: Duplicate entry '1' for key 'val'
2146
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2147
insert into t1 (val) values (1);
2148
update t1 set a=2 where a=1;
2149
insert into t1 (val) values (1);
2150
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
2155
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2156
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2157
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2160
SELECT GRADE FROM t1 WHERE GRADE= 151;
2165
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2168
id INTEGER NOT NULL,
2169
FOREIGN KEY (id) REFERENCES t1 (id)
2171
INSERT INTO t1 (id) VALUES (NULL);
2176
INSERT INTO t1 (id) VALUES (NULL);
2182
INSERT INTO t1 (id) VALUES (NULL);
2191
CREATE TEMPORARY TABLE t2
2193
id INT NOT NULL PRIMARY KEY,
2195
FOREIGN KEY (b) REFERENCES test.t1(id)
2197
Got one of the listed errors
2199
create table t1 (col1 varchar(2000), index (col1(767)))
2202
Warning 1071 Specified key was too long; max key length is 767 bytes
2203
create table t2 (col1 char(255), index (col1))
2206
Warning 1071 Specified key was too long; max key length is 767 bytes
2207
create table t4 (col1 varchar(767), index (col1))
2210
Warning 1071 Specified key was too long; max key length is 767 bytes
2211
create table t5 (col1 varchar(190) primary key)
2213
create table t6 (col1 varbinary(254) primary key)
2215
create table t7 (col1 text, index(col1(767)))
2218
Warning 1071 Specified key was too long; max key length is 767 bytes
2219
create table t8 (col1 blob, index(col1(767)))
2221
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
2224
Warning 1071 Specified key was too long; max key length is 767 bytes
2225
Warning 1071 Specified key was too long; max key length is 767 bytes
2226
show create table t9;
2228
t9 CREATE TABLE `t9` (
2229
`col1` varchar(512) DEFAULT NULL,
2230
`col2` varchar(512) DEFAULT NULL,
2231
KEY `col1` (`col1`(191),`col2`(191))
2233
drop table t1, t2, t4, t5, t6, t7, t8, t9;
2234
create table t1 (col1 varchar(768), index(col1))
2237
Warning 1071 Specified key was too long; max key length is 767 bytes
2238
create table t2 (col1 varbinary(768), index(col1))
2241
Warning 1071 Specified key was too long; max key length is 767 bytes
2242
create table t3 (col1 text, index(col1(768)))
2245
Warning 1071 Specified key was too long; max key length is 767 bytes
2246
create table t4 (col1 blob, index(col1(768)))
2249
Warning 1071 Specified key was too long; max key length is 767 bytes
2250
show create table t1;
2252
t1 CREATE TABLE `t1` (
2253
`col1` varchar(768) DEFAULT NULL,
2254
KEY `col1` (`col1`(191))
2256
drop table t1, t2, t3, t4;
2257
create table t1 (col1 varchar(768) primary key)
2259
ERROR 42000: Specified key was too long; max key length is 767 bytes
2260
create table t2 (col1 varbinary(768) primary key)
2262
ERROR 42000: Specified key was too long; max key length is 767 bytes
2263
create table t3 (col1 text, primary key(col1(768)))
2265
ERROR 42000: Specified key was too long; max key length is 767 bytes
2266
create table t4 (col1 blob, primary key(col1(768)))
2268
ERROR 42000: Specified key was too long; max key length is 767 bytes
2276
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2278
INSERT INTO t2 VALUES(2);
2279
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2280
INSERT INTO t1 VALUES(1);
2281
INSERT INTO t2 VALUES(1);
2282
DELETE FROM t1 WHERE id = 1;
2283
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2285
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
2286
SET FOREIGN_KEY_CHECKS=0;
2288
SET FOREIGN_KEY_CHECKS=1;
2289
INSERT INTO t2 VALUES(3);
2290
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2292
create table t1(a int not null) engine=innodb;
2293
insert into t1 values (1),(2);
2298
insert into t1 values(3);
2308
create table t1(a int not null) engine=innodb;
2309
insert into t1 values (1),(2);
2315
insert into t1 values(3);
2320
set foreign_key_checks=0;
2321
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2322
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2323
ERROR HY000: Can't create table 'test.t1' (errno: 150)
2324
set foreign_key_checks=1;
2326
set foreign_key_checks=0;
2327
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2328
create table t1(a varchar(10) primary key) engine = innodb;
2329
alter table t1 modify column a int;
2330
Got one of the listed errors
2331
set foreign_key_checks=1;
2333
create table t1(a int primary key) row_format=redundant engine=innodb;
2334
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2335
create table t3(a int primary key) row_format=compact engine=innodb;
2336
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2337
insert into t1 values(1);
2338
insert into t3 values(1);
2339
insert into t2 values(2);
2340
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`))
2341
insert into t4 values(2);
2342
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`))
2343
insert into t2 values(1);
2344
insert into t4 values(1);
2346
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`))
2348
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`))
2350
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`))
2352
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`))
2354
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`))
2356
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`))
2361
drop table t4,t3,t2,t1;
2362
create table t1 (a varchar(255),
2366
key (a,b,c,d)) engine=innodb;
2368
Warning 1071 Specified key was too long; max key length is 767 bytes
2369
Warning 1071 Specified key was too long; max key length is 767 bytes
2370
Warning 1071 Specified key was too long; max key length is 767 bytes
2371
Warning 1071 Specified key was too long; max key length is 767 bytes
2373
create table t1 (a varchar(255),
2378
key (a,b,c,d,e)) engine=innodb;
2379
ERROR 42000: Specified key was too long; max key length is 3500 bytes
2380
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2381
create table t3 (s1 varchar(2) ,primary key (s1)) engine=innodb;
2382
create table t4 (s1 char(2) ,primary key (s1)) engine=innodb;
2383
insert into t1 values (0x41),(0x4120),(0x4100);
2384
insert into t3 values (0x41),(0x4120),(0x4100);
2385
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2386
insert into t3 values (0x41),(0x4100);
2387
insert into t4 values (0x41),(0x4120),(0x4100);
2388
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2389
insert into t4 values (0x41),(0x4100);
2390
select hex(s1) from t1;
2395
select hex(s1) from t3;
2399
select hex(s1) from t4;
2403
drop table t1,t3,t4;
2404
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2405
create table t2 (s1 varbinary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2406
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2407
insert into t2 values(0x42);
2408
insert into t2 values(0x41);
2409
select hex(s1) from t2;
2413
update t1 set s1=0x123456 where a=2;
2414
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)
2415
select hex(s1) from t2;
2419
update t1 set s1=0x12 where a=1;
2420
update t1 set s1=0x12345678 where a=1;
2421
ERROR 22001: Data too long for column 's1' at row 1
2422
update t1 set s1=0x123457 where a=1;
2423
update t1 set s1=0x1220 where a=1;
2424
select hex(s1) from t2;
2428
update t1 set s1=0x1200 where a=1;
2429
select hex(s1) from t2;
2433
update t1 set s1=0x4200 where a=1;
2434
select hex(s1) from t2;
2438
delete from t1 where a=1;
2439
update t2 set s1=0x4120;
2441
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)
2442
delete from t1 where a!=3;
2443
select a,hex(s1) from t1;
2446
select hex(s1) from t2;
2451
create table t1 (a int primary key,s1 varchar(2) not null unique) engine=innodb;
2452
create table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2453
insert into t1 values(1,0x4100),(2,0x41);
2454
insert into t2 values(0x41);
2455
select hex(s1) from t2;
2458
update t1 set s1=0x1234 where a=1;
2459
select hex(s1) from t2;
2462
update t1 set s1=0x12 where a=2;
2463
select hex(s1) from t2;
2466
delete from t1 where a=1;
2467
delete from t1 where a=2;
2468
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)
2469
select a,hex(s1) from t1;
2472
select hex(s1) from t2;
2476
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2477
CREATE TABLE t2(a INT) ENGINE=InnoDB;
2478
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2479
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2480
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2481
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2482
SHOW CREATE TABLE t2;
2484
t2 CREATE TABLE `t2` (
2485
`a` int DEFAULT NULL,
2486
KEY `t2_ibfk_0` (`a`)
2490
field1 varchar(8) NOT NULL DEFAULT '',
2491
field2 varchar(8) NOT NULL DEFAULT '',
2492
PRIMARY KEY (field1, field2)
2495
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2496
FOREIGN KEY (field1) REFERENCES t1 (field1)
2497
ON DELETE CASCADE ON UPDATE CASCADE
2499
INSERT INTO t1 VALUES ('old', 'somevalu');
2500
INSERT INTO t1 VALUES ('other', 'anyvalue');
2501
INSERT INTO t2 VALUES ('old');
2502
INSERT INTO t2 VALUES ('other');
2503
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2504
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
2517
alter table t1 add constraint c2_fk foreign key (c2)
2518
references t2(c1) on delete cascade;
2519
show create table t1;
2521
t1 CREATE TABLE `t1` (
2522
`c1` bigint NOT NULL,
2523
`c2` bigint NOT NULL,
2525
UNIQUE KEY `c2` (`c2`),
2526
CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2528
alter table t1 drop foreign key c2_fk;
2529
show create table t1;
2531
t1 CREATE TABLE `t1` (
2532
`c1` bigint NOT NULL,
2533
`c2` bigint NOT NULL,
2535
UNIQUE KEY `c2` (`c2`)
2538
create table t1(a date) engine=innodb;
2539
create table t2(a date, key(a)) engine=innodb;
2540
insert into t1 values('2005-10-01');
2541
insert into t2 values('2005-10-01');
2542
select * from t1, t2
2543
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2545
2005-10-01 2005-10-01
2547
create table t1 (id int not null, f_id int not null, f int not null,
2548
primary key(f_id, id)) engine=innodb;
2549
create table t2 (id int not null,s_id int not null,s varchar(200),
2550
primary key(id)) engine=innodb;
2551
INSERT INTO t1 VALUES (8, 1, 3);
2552
INSERT INTO t1 VALUES (1, 2, 1);
2553
INSERT INTO t2 VALUES (1, 0, '');
2554
INSERT INTO t2 VALUES (8, 1, '');
2556
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2557
where mm.id is null lock in share mode;
2560
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2561
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2564
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2565
update t1 set b = 5 where b = 1;
2567
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2568
select * from t1 where a = 7 and b = 3 for update;
2574
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2575
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2578
select * from t1 lock in share mode;
2586
update t1 set b = 5 where b = 1;
2588
select * from t1 where a = 2 and b = 2 for update;
2589
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2593
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2594
insert into t1 values (1,2),(5,3),(4,2);
2595
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2596
insert into t2 values (8,6),(12,1),(3,1);
2599
select * from t2 for update;
2605
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2606
insert into t1 select * from t2;
2607
update t1 set b = (select e from t2 where a = d);
2608
create table t3(d int not null, e int, primary key(d)) engine=innodb
2612
drop table t1, t2, t3;
2613
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2614
insert into t1 values (1,2),(5,3),(4,2);
2615
create table t2(a int not null, b int, primary key(a)) engine=innodb;
2616
insert into t2 values (8,6),(12,1),(3,1);
2617
create table t3(d int not null, b int, primary key(d)) engine=innodb;
2618
insert into t3 values (8,6),(12,1),(3,1);
2619
create table t5(a int not null, b int, primary key(a)) engine=innodb;
2620
insert into t5 values (1,2),(5,3),(4,2);
2621
create table t6(d int not null, e int, primary key(d)) engine=innodb;
2622
insert into t6 values (8,6),(12,1),(3,1);
2623
create table t8(a int not null, b int, primary key(a)) engine=innodb;
2624
insert into t8 values (1,2),(5,3),(4,2);
2625
create table t9(d int not null, e int, primary key(d)) engine=innodb;
2626
insert into t9 values (8,6),(12,1),(3,1);
2629
select * from t2 for update;
2635
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2636
insert into t1 select * from t2;
2638
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2639
update t3 set b = (select b from t2 where a = d);
2641
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2642
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2644
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2645
insert into t5 (select * from t2 lock in share mode);
2647
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2648
update t6 set e = (select b from t2 where a = d lock in share mode);
2650
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2651
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2653
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2654
insert into t8 (select * from t2 for update);
2656
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2657
update t9 set e = (select b from t2 where a = d for update);
2659
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2660
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2661
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2662
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2663
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2664
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2665
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2666
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2667
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2668
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2669
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2671
drop table t1, t2, t3, t5, t6, t8, t9;
2672
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2673
ERROR HY000: Can't create table 'test.t1' (errno: -1)
2680
b VARCHAR(128) NOT NULL,
2683
KEY idx_t2_b_c (b,c(200)),
2684
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2688
Warning 1071 Specified key was too long; max key length is 767 bytes
2689
INSERT INTO t1 VALUES (1);
2690
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2691
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2692
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2693
INSERT INTO t2 VALUES (1, 'customer_over', '1');
2694
SELECT * FROM t2 WHERE b = 'customer_over';
2697
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2700
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2703
/* Bang: Empty result set, above was expected: */
2704
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2707
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2711
CREATE TABLE t1 ( a int ) ENGINE=innodb;
2713
INSERT INTO t1 VALUES (1);
2714
ALTER TABLE t1 ENGINE=innodb;
2716
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2717
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2718
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2719
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2720
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2721
DELETE CASCADE ON UPDATE CASCADE;
2722
SHOW CREATE TABLE t2;
2724
t2 CREATE TABLE `t2` (
2729
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
2730
CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2733
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2734
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2735
INSERT INTO t1 VALUES (1);
2736
INSERT INTO t2 VALUES (1);
2737
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2738
ALTER TABLE t2 MODIFY a INT NOT NULL;
2739
ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
2742
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2744
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2746
INSERT INTO t1 VALUES ('DDD');
2751
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2753
INSERT INTO t1 VALUES (NULL),(347),(NULL);
2759
SHOW CREATE TABLE t1;
2761
t1 CREATE TABLE `t1` (
2762
`id` int NOT NULL AUTO_INCREMENT,
2765
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2766
INSERT INTO t2 VALUES(42),(347),(348);
2767
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2768
SHOW CREATE TABLE t1;
2770
t1 CREATE TABLE `t1` (
2771
`id` int NOT NULL AUTO_INCREMENT,
2773
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
2776
DROP TABLE IF EXISTS t1;
2778
Note 1051 Unknown table 't1'
2780
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
2782
INSERT INTO t1 VALUES(-10);
2786
INSERT INTO t1 VALUES(NULL);