1
drop table if exists t1,t2,t3,t4;
2
drop database if exists mysqltest;
3
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;
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(11) NOT NULL auto_increment,
37
parent_id int(11) DEFAULT '0' NOT NULL,
38
level tinyint(4) 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 1 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 1 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 1 const #
151
select level,id from t1 where level=1;
159
select level,id,parent_id from t1 where level=1;
168
Table Op Msg_type Msg_text
169
test.t1 optimize status OK
171
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
172
t1 0 PRIMARY 1 id A # NULL NULL BTREE
173
t1 1 parent_id 1 parent_id A # NULL NULL BTREE
174
t1 1 level 1 level A # NULL NULL BTREE
177
gesuchnr int(11) DEFAULT '0' NOT NULL,
178
benutzer_id int(11) DEFAULT '0' NOT NULL,
179
PRIMARY KEY (gesuchnr,benutzer_id)
181
replace into t1 (gesuchnr,benutzer_id) values (2,1);
182
replace into t1 (gesuchnr,benutzer_id) values (1,1);
183
replace into t1 (gesuchnr,benutzer_id) values (1,1);
189
create table t1 (a int) engine=innodb;
190
insert into t1 values (1), (2);
192
Table Op Msg_type Msg_text
193
test.t1 optimize status OK
194
delete from t1 where a = 1;
199
Table Op Msg_type Msg_text
200
test.t1 check status OK
202
create table t1 (a int,b varchar(20)) engine=innodb;
203
insert into t1 values (1,""), (2,"testing");
204
delete from t1 where a = 1;
208
create index skr on t1 (a);
209
insert into t1 values (3,""), (4,"testing");
211
Table Op Msg_type Msg_text
212
test.t1 analyze status OK
214
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
215
t1 1 skr 1 a A # NULL NULL YES BTREE
217
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
218
insert into t1 values (1,""), (2,"testing");
219
select * from t1 where a = 1;
223
create table t1 (n int not null primary key) engine=innodb;
225
insert into t1 values (4);
227
select n, "after rollback" from t1;
229
insert into t1 values (4);
231
select n, "after commit" from t1;
235
insert into t1 values (5);
236
insert into t1 values (4);
237
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
239
select n, "after commit" from t1;
244
insert into t1 values (6);
245
insert into t1 values (4);
246
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
254
savepoint `my_savepoint`;
255
insert into t1 values (7);
257
insert into t1 values (3);
266
rollback to savepoint savept2;
267
rollback to savepoint savept3;
268
ERROR 42000: SAVEPOINT savept3 does not exist
269
rollback to savepoint savept2;
270
release savepoint `my_savepoint`;
277
rollback to savepoint `my_savepoint`;
278
ERROR 42000: SAVEPOINT my_savepoint does not exist
279
rollback to savepoint savept2;
280
ERROR 42000: SAVEPOINT savept2 does not exist
281
insert into t1 values (8);
288
create table t1 (n int not null primary key) engine=innodb;
290
insert into t1 values (4);
291
flush tables with read lock;
299
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
301
insert into t1 values(1,'hamdouni');
302
select id as afterbegin_id,nom as afterbegin_nom from t1;
303
afterbegin_id afterbegin_nom
306
select id as afterrollback_id,nom as afterrollback_nom from t1;
307
afterrollback_id afterrollback_nom
309
insert into t1 values(2,'mysql');
310
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
311
afterautocommit0_id afterautocommit0_nom
314
select id as afterrollback_id,nom as afterrollback_nom from t1;
315
afterrollback_id afterrollback_nom
318
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
319
insert into t1 values ('pippo', 12);
320
insert into t1 values ('pippo', 12);
321
ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
323
delete from t1 where id = 'pippo';
326
insert into t1 values ('pippo', 12);
338
create table t1 (a integer) engine=innodb;
340
rename table t1 to t2;
341
create table t1 (b integer) engine=innodb;
342
insert into t1 values (1);
345
rename table t2 to t1;
348
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
349
INSERT INTO t1 VALUES (1, 'Jochen');
354
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
356
INSERT INTO t1 SET _userid='marc@anyware.co.uk';
361
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
367
user_id int(10) DEFAULT '0' NOT NULL,
370
ref_email varchar(100) DEFAULT '' NOT NULL,
372
PRIMARY KEY (user_id,ref_email)
374
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');
375
select * from t1 where user_id=10292;
376
user_id name phone ref_email detail
377
10292 sanjeev 29153373 sansh777@hotmail.com xxx
378
10292 shirish 2333604 shirish@yahoo.com ddsds
379
10292 sonali 323232 sonali@bolly.com filmstar
380
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
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
select * from t1 where user_id>=10292;
387
user_id name phone ref_email detail
388
10292 sanjeev 29153373 sansh777@hotmail.com xxx
389
10292 shirish 2333604 shirish@yahoo.com ddsds
390
10292 sonali 323232 sonali@bolly.com filmstar
391
10293 shirish 2333604 shirish@yahoo.com ddsds
392
select * from t1 where user_id>10292;
393
user_id name phone ref_email detail
394
10293 shirish 2333604 shirish@yahoo.com ddsds
395
select * from t1 where user_id<10292;
396
user_id name phone ref_email detail
397
10291 sanjeev 29153373 sansh777@hotmail.com xxx
399
CREATE TABLE t1 (a int not null, b int not null,c int not null,
400
key(a),primary key(a,b), unique(c),key(a),unique(b));
402
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
403
t1 0 PRIMARY 1 a A # NULL NULL BTREE
404
t1 0 PRIMARY 2 b A # NULL NULL BTREE
405
t1 0 c 1 c A # NULL NULL BTREE
406
t1 0 b 1 b A # NULL NULL BTREE
407
t1 1 a 1 a A # NULL NULL BTREE
408
t1 1 a_2 1 a A # NULL NULL BTREE
410
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
411
alter table t1 engine=innodb;
412
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
420
update t1 set col2='7' where col1='4';
428
alter table t1 add co3 int not null;
436
update t1 set col2='9' where col1='2';
445
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
446
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
447
insert into t1 VALUES (1,3) , (2,3), (3,3);
453
insert into t2 select * from t1;
459
delete from t1 where b = 3;
462
insert into t1 select * from t2;
475
user_name varchar(12),
478
user_id int(11) DEFAULT '0' NOT NULL,
484
dummy_primary_key int(11) NOT NULL auto_increment,
485
PRIMARY KEY (dummy_primary_key)
487
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
488
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
489
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);
490
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);
491
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
492
select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
493
user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key
494
user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1
495
user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2
496
user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3
497
user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4
498
user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5
501
id int(11) NOT NULL auto_increment,
502
parent_id int(11) DEFAULT '0' NOT NULL,
503
level tinyint(4) DEFAULT '0' NOT NULL,
505
KEY parent_id (parent_id),
508
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);
509
INSERT INTO t1 values (179,5,2);
510
update t1 set parent_id=parent_id+100;
511
select * from t1 where parent_id=102;
516
update t1 set id=id+1000;
517
update t1 set id=1024 where id=1009;
559
update ignore t1 set id=id+1;
601
update ignore t1 set id=1023 where id=1010;
602
select * from t1 where parent_id=102;
607
explain select level from t1 where level=1;
608
id select_type table type possible_keys key key_len ref rows Extra
609
1 SIMPLE t1 ref level level 1 const # Using index
610
select level,id from t1 where level=1;
618
select level,id,parent_id from t1 where level=1;
626
select level,id from t1 where level=1 order by id;
634
delete from t1 where level=1;
672
sca_code char(6) NOT NULL,
673
cat_code char(6) NOT NULL,
674
sca_desc varchar(50),
675
lan_code char(2) NOT NULL,
676
sca_pic varchar(100),
677
sca_sdesc varchar(50),
678
sca_sch_desc varchar(16),
679
PRIMARY KEY (sca_code, cat_code, lan_code),
680
INDEX sca_pic (sca_pic)
682
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');
683
select count(*) from t1 where sca_code = 'PD';
686
select count(*) from t1 where sca_code <= 'PD';
689
select count(*) from t1 where sca_pic is null;
692
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
693
select count(*) from t1 where sca_code='PD' and sca_pic is null;
696
select count(*) from t1 where cat_code='E';
699
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
700
select count(*) from t1 where sca_code='PD' and sca_pic is null;
703
select count(*) from t1 where sca_pic >= 'n';
706
select sca_pic from t1 where sca_pic is null;
710
update t1 set sca_pic="test" where sca_pic is null;
711
delete from t1 where sca_code='pd';
714
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
715
insert into t1 (a) values(1),(2),(3);
716
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
721
select a from t1 natural join t1 as t2 where b >= @a order by a;
726
update t1 set a=5 where a=1;
733
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
734
insert into t1 values("hello",1),("world",2);
735
select * from t1 order by b desc;
740
Table Op Msg_type Msg_text
741
test.t1 optimize status OK
743
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
744
t1 0 PRIMARY 1 a A # NULL NULL BTREE
746
create table t1 (i int, j int ) ENGINE=innodb;
747
insert into t1 values (1,2);
748
select * from t1 where i=1 and j=2;
751
create index ax1 on t1 (i,j);
752
select * from t1 where i=1 and j=2;
757
a int3 unsigned NOT NULL,
758
b int1 unsigned NOT NULL,
761
INSERT INTO t1 VALUES (1, 1);
762
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
766
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
767
INSERT INTO t1 VALUES (1);
772
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;
773
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);
774
explain select * from t1 where a > 0 and a < 50;
775
id select_type table type possible_keys key key_len ref rows Extra
776
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where
778
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;
779
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
780
LOCK TABLES t1 WRITE;
781
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
782
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
795
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;
796
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
797
LOCK TABLES t1 WRITE;
799
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
800
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
806
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
808
select id,id3 from t1;
816
create table t1 (a char(20), unique (a(5))) engine=innodb;
818
create table t1 (a char(20), index (a(5))) engine=innodb;
819
show create table t1;
821
t1 CREATE TABLE `t1` (
822
`a` char(20) DEFAULT NULL,
824
) ENGINE=InnoDB DEFAULT CHARSET=latin1
826
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
827
insert into t1 values (NULL),(NULL),(NULL);
828
delete from t1 where a=3;
829
insert into t1 values (NULL);
835
alter table t1 add b int;
844
id int auto_increment primary key,
845
name varchar(32) not null,
850
insert into t1 values (1,'one','one value',101),
851
(2,'two','two value',102),(3,'three','three value',103);
853
replace into t1 (value,name,uid) values ('other value','two',102);
854
delete from t1 where uid=102;
856
replace into t1 (value,name,uid) values ('other value','two',102);
858
replace into t1 (value,name,uid) values ('other value','two',102);
862
3 three three value 103
863
6 two other value 102
865
create database mysqltest;
866
create table mysqltest.t1 (a int not null) engine= innodb;
867
insert into mysqltest.t1 values(1);
868
create table mysqltest.t2 (a int not null) engine= myisam;
869
insert into mysqltest.t2 values(1);
870
create table mysqltest.t3 (a int not null) engine= heap;
871
insert into mysqltest.t3 values(1);
873
drop database mysqltest;
874
show tables from mysqltest;
875
ERROR 42000: Unknown database 'mysqltest'
877
create table t1 (a int not null) engine= innodb;
878
insert into t1 values(1),(2);
885
insert into t1 values(1),(2);
892
create table t1 (a int not null) engine= innodb;
893
insert into t1 values(1),(2);
895
insert into t1 values(1),(2);
901
insert into t1 values(1),(2);
906
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
907
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
908
explain select * from t1 order by a;
909
id select_type table type possible_keys key key_len ref rows Extra
910
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
911
explain select * from t1 order by b;
912
id select_type table type possible_keys key key_len ref rows Extra
913
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
914
explain select * from t1 order by c;
915
id select_type table type possible_keys key key_len ref rows Extra
916
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
917
explain select a from t1 order by a;
918
id select_type table type possible_keys key key_len ref rows Extra
919
1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index
920
explain select b from t1 order by b;
921
id select_type table type possible_keys key key_len ref rows Extra
922
1 SIMPLE t1 index NULL b 4 NULL # Using index
923
explain select a,b from t1 order by b;
924
id select_type table type possible_keys key key_len ref rows Extra
925
1 SIMPLE t1 index NULL b 4 NULL # Using index
926
explain select a,b from t1;
927
id select_type table type possible_keys key key_len ref rows Extra
928
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
929
explain select a,b,c from t1;
930
id select_type table type possible_keys key key_len ref rows Extra
931
1 SIMPLE t1 ALL NULL NULL NULL NULL #
933
create table t1 (t int not null default 1, key (t)) engine=innodb;
935
Field Type Null Key Default Extra
939
number bigint(20) NOT NULL default '0',
940
cname char(15) NOT NULL default '',
941
carrier_id smallint(6) NOT NULL default '0',
942
privacy tinyint(4) NOT NULL default '0',
943
last_mod_date timestamp NOT NULL,
944
last_mod_id smallint(6) NOT NULL default '0',
945
last_app_date timestamp NOT NULL,
946
last_app_id smallint(6) default '-1',
947
version smallint(6) NOT NULL default '0',
948
assigned_scps int(11) default '0',
949
status tinyint(4) default '0'
951
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
952
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
953
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
954
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
955
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
956
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
958
number bigint(20) NOT NULL default '0',
959
cname char(15) NOT NULL default '',
960
carrier_id smallint(6) NOT NULL default '0',
961
privacy tinyint(4) NOT NULL default '0',
962
last_mod_date timestamp NOT NULL,
963
last_mod_id smallint(6) NOT NULL default '0',
964
last_app_date timestamp NOT NULL,
965
last_app_id smallint(6) default '-1',
966
version smallint(6) NOT NULL default '0',
967
assigned_scps int(11) default '0',
968
status tinyint(4) default '0'
970
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
971
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
972
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
973
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
975
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
976
4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1
977
9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0
978
650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1
979
302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0
980
6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
981
333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
983
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
984
4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1
985
9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0
986
650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1
987
333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
988
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);
990
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
991
6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
992
333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
994
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
995
333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
997
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
998
333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1000
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;
1002
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1003
SELECT @@tx_isolation,@@global.tx_isolation;
1004
@@tx_isolation @@global.tx_isolation
1005
SERIALIZABLE REPEATABLE-READ
1006
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
1007
select id, code, name from t1 order by id;
1014
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1015
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
1016
select id, code, name from t1 order by id;
1025
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1026
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
1027
select id, code, name from t1 order by id;
1038
create table t1 (n int(10), d int(10)) engine=innodb;
1039
create table t2 (n int(10), d int(10)) engine=innodb;
1040
insert into t1 values(1,1),(1,2);
1041
insert into t2 values(1,10),(2,20);
1042
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
1052
drop table if exists t1, t2;
1053
CREATE TABLE t1 (a int, PRIMARY KEY (a));
1054
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1055
create trigger trg_del_t2 after delete on t2 for each row
1056
insert into t1 values (1);
1057
insert into t1 values (1);
1058
insert into t2 values (1),(2);
1060
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1061
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1065
create table t1 (a int, b int) engine=innodb;
1066
insert into t1 values(20,null);
1067
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1069
b ifnull(t2.b,"this is null")
1071
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1072
t2.b=t3.a order by 1;
1073
b ifnull(t2.b,"this is null")
1075
insert into t1 values(10,null);
1076
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1077
t2.b=t3.a order by 1;
1078
b ifnull(t2.b,"this is null")
1082
create table t1 (a varchar(10) not null) engine=myisam;
1083
create table t2 (b varchar(10) not null unique) engine=innodb;
1084
select t1.a from t1,t2 where t1.a=t2.b;
1087
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
1088
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
1089
insert into t1 values (10, 20);
1090
insert into t2 values (10, 20);
1091
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
1093
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1094
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;
1095
insert into t1 set id=1;
1096
insert into t2 set id=1, t1_id=1;
1097
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
1103
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1104
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
1105
INSERT INTO t1 VALUES(1);
1106
INSERT INTO t2 VALUES(1, 1);
1110
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
1114
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
1120
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1121
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1122
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
1123
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
1125
INSERT INTO t1 VALUES("this-key", "will disappear");
1126
INSERT INTO t2 VALUES("this-key", "will also disappear");
1127
DELETE FROM t3 WHERE id1="my-test-1";
1130
this-key will disappear
1133
this-key will also disappear
1144
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
1149
DROP TABLE t1,t2,t3;
1150
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
1151
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1152
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1165
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
1166
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
1167
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);
1168
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1169
update t1,t2 set t1.a=t1.a+100;
1184
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
1199
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
1214
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
1241
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
1242
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1244
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
1245
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
1248
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1252
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
1253
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
1254
select distinct parent,child from t1 order by parent;
1261
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1262
create table t2 (a int not null auto_increment primary key, b int);
1263
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1264
insert into t2 (a) select b from t1;
1265
insert into t1 (b) select b from t2;
1266
insert into t2 (a) select b from t1;
1267
insert into t1 (a) select b from t2;
1268
insert into t2 (a) select b from t1;
1269
insert into t1 (a) select b from t2;
1270
insert into t2 (a) select b from t1;
1271
insert into t1 (a) select b from t2;
1272
insert into t2 (a) select b from t1;
1273
insert into t1 (a) select b from t2;
1274
select count(*) from t1;
1277
explain select * from t1 where c between 1 and 2500;
1278
id select_type table type possible_keys key key_len ref rows Extra
1279
1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR
1281
explain select * from t1 where c between 1 and 2500;
1282
id select_type table type possible_keys key key_len ref rows Extra
1283
1 SIMPLE t1 ALL c NULL NULL NULL # Using where
1285
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1286
insert into t1 (id) values (null),(null),(null),(null),(null);
1287
update t1 set fk=69 where fk is null order by id limit 1;
1296
create table t1 (a int not null, b int not null, key (a));
1297
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);
1299
update t1 set b=(@tmp:=@tmp+1) order by a;
1300
update t1 set b=99 where a=1 order by b asc limit 1;
1301
update t1 set b=100 where a=1 order by b desc limit 2;
1302
update t1 set a=a+10+b where a=1 order by b;
1303
select * from t1 order by a,b;
1318
create table t1 ( c char(8) not null ) engine=innodb;
1319
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
1320
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
1321
alter table t1 add b char(8) not null;
1322
alter table t1 add a char(8) not null;
1323
alter table t1 add primary key (a,b,c);
1324
update t1 set a=c, b=c;
1325
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;
1326
insert into t2 select * from t1;
1327
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1330
create table t1 (a integer auto_increment primary key) engine=innodb;
1331
insert into t1 (a) values (NULL),(NULL);
1333
insert into t1 (a) values (NULL),(NULL);
1339
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1340
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;
1342
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1343
insert into `t1`values ( 1 ) ;
1344
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;
1345
insert into `t2`values ( 1 ) ;
1346
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;
1347
insert into `t3`values ( 1 ) ;
1348
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1349
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`))
1350
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;
1351
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`))
1352
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1353
ERROR 42S22: Unknown column 't1.id' in 'where clause'
1354
drop table t3,t2,t1;
1359
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1360
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1361
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1362
delete from t1 where id=0;
1363
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)
1364
delete from t1 where id=15;
1365
delete from t1 where id=0;
1367
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1368
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1369
(stamp))ENGINE=InnoDB;
1370
insert into t1 values (1),(2),(3);
1371
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1373
Warning 1265 Data truncated for column 'stamp' at row 3
1374
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1375
'20020204120000' GROUP BY col1;
1383
`id` int(10) unsigned NOT NULL auto_increment,
1384
`id_object` int(10) unsigned default '0',
1385
`id_version` int(10) unsigned NOT NULL default '1',
1386
`label` varchar(100) NOT NULL default '',
1389
KEY `id_object` (`id_object`),
1390
KEY `id_version` (`id_version`)
1392
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);
1394
`id` int(10) unsigned NOT NULL auto_increment,
1395
`id_version` int(10) unsigned NOT NULL default '1',
1397
KEY `id_version` (`id_version`)
1399
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1400
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1401
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1402
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1409
3525 Fournisseur Test
1411
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1412
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1413
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1414
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1415
insert t2 select * from t1;
1416
insert t3 select * from t1;
1417
checksum table t1, t2, t3, t4 quick;
1424
Error 1146 Table 'test.t4' doesn't exist
1425
checksum table t1, t2, t3, t4;
1432
Error 1146 Table 'test.t4' doesn't exist
1433
checksum table t1, t2, t3, t4 extended;
1440
Error 1146 Table 'test.t4' doesn't exist
1441
drop table t1,t2,t3;
1442
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1443
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1444
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1456
create table t1 (a int) engine=innodb;
1457
create table t2 like t1;
1459
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1460
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1461
show create table t1;
1463
t1 CREATE TABLE `t1` (
1464
`id` int(11) NOT NULL,
1465
`id2` int(11) NOT NULL,
1466
UNIQUE KEY `id` (`id`,`id2`)
1467
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1468
show create table t2;
1470
t2 CREATE TABLE `t2` (
1471
`id` int(11) NOT NULL,
1472
KEY `t1_id_fk` (`id`),
1473
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1474
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1475
create index id on t2 (id);
1476
show create table t2;
1478
t2 CREATE TABLE `t2` (
1479
`id` int(11) NOT NULL,
1481
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1482
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1483
create index id2 on t2 (id);
1484
show create table t2;
1486
t2 CREATE TABLE `t2` (
1487
`id` int(11) NOT NULL,
1490
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1491
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1492
drop index id2 on t2;
1493
drop index id on t2;
1494
Got one of the listed errors
1495
show create table t2;
1497
t2 CREATE TABLE `t2` (
1498
`id` int(11) NOT NULL,
1500
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1501
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1503
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;
1504
show create table t2;
1506
t2 CREATE TABLE `t2` (
1507
`id` int(11) NOT NULL,
1508
`id2` int(11) NOT NULL,
1509
KEY `t1_id_fk` (`id`,`id2`),
1510
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1511
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1512
create unique index id on t2 (id,id2);
1513
show create table t2;
1515
t2 CREATE TABLE `t2` (
1516
`id` int(11) NOT NULL,
1517
`id2` int(11) NOT NULL,
1518
UNIQUE KEY `id` (`id`,`id2`),
1519
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1520
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1522
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;
1523
show create table t2;
1525
t2 CREATE TABLE `t2` (
1526
`id` int(11) NOT NULL,
1527
`id2` int(11) NOT NULL,
1528
UNIQUE KEY `id` (`id`,`id2`),
1529
KEY `t1_id_fk` (`id2`,`id`),
1530
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1531
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1533
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;
1534
show create table t2;
1536
t2 CREATE TABLE `t2` (
1537
`id` int(11) NOT NULL,
1538
`id2` int(11) NOT NULL,
1539
UNIQUE KEY `id` (`id`,`id2`),
1540
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1541
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1543
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;
1544
show create table t2;
1546
t2 CREATE TABLE `t2` (
1547
`id` int(11) NOT NULL,
1548
`id2` int(11) NOT NULL,
1549
UNIQUE KEY `id` (`id`,`id2`),
1550
KEY `t1_id_fk` (`id2`,`id`),
1551
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1552
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1554
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;
1555
show create table t2;
1557
t2 CREATE TABLE `t2` (
1558
`id` int(11) NOT NULL AUTO_INCREMENT,
1559
`id2` int(11) NOT NULL,
1561
KEY `id` (`id`,`id2`),
1562
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1563
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1565
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;
1566
show create table t2;
1568
t2 CREATE TABLE `t2` (
1569
`id` int(11) NOT NULL AUTO_INCREMENT,
1570
`id2` int(11) NOT NULL,
1571
KEY `t1_id_fk` (`id`),
1572
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1573
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1574
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1575
show create table t2;
1577
t2 CREATE TABLE `t2` (
1578
`id` int(11) NOT NULL AUTO_INCREMENT,
1579
`id2` int(11) NOT NULL,
1580
KEY `id_test` (`id`),
1581
KEY `id_test2` (`id`,`id2`),
1582
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1583
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1585
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;
1586
ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1587
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1588
show create table t2;
1590
t2 CREATE TABLE `t2` (
1591
`a` int(11) NOT NULL AUTO_INCREMENT,
1592
`b` int(11) DEFAULT NULL,
1594
UNIQUE KEY `b_2` (`b`),
1596
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1597
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1599
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;
1600
show create table t2;
1602
t2 CREATE TABLE `t2` (
1603
`a` int(11) NOT NULL AUTO_INCREMENT,
1604
`b` int(11) DEFAULT NULL,
1606
UNIQUE KEY `b` (`b`),
1607
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1608
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1609
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1611
create table t1 (c char(10), index (c,c)) engine=innodb;
1612
ERROR 42S21: Duplicate column name 'c'
1613
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1614
ERROR 42S21: Duplicate column name 'c1'
1615
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1616
ERROR 42S21: Duplicate column name 'c1'
1617
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1618
ERROR 42S21: Duplicate column name 'c1'
1619
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1620
alter table t1 add key (c1,c1);
1621
ERROR 42S21: Duplicate column name 'c1'
1622
alter table t1 add key (c2,c1,c1);
1623
ERROR 42S21: Duplicate column name 'c1'
1624
alter table t1 add key (c1,c2,c1);
1625
ERROR 42S21: Duplicate column name 'c1'
1626
alter table t1 add key (c1,c1,c2);
1627
ERROR 42S21: Duplicate column name 'c1'
1629
create table t1(a int(1) , b int(1)) engine=innodb;
1630
insert into t1 values ('1111', '3333');
1631
select distinct concat(a, b) from t1;
1635
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1636
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1637
ERROR HY000: The used table type doesn't support FULLTEXT indexes
1639
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1640
INSERT INTO t1 VALUES (1),(2),(3);
1641
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),
1642
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1643
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1644
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;
1651
create temporary table t1 (a int) engine=innodb;
1652
insert into t1 values (4711);
1654
insert into t1 values (42);
1659
create table t1 (a int) engine=innodb;
1660
insert into t1 values (4711);
1662
insert into t1 values (42);
1667
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;
1668
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1669
select * from t1 order by a,b,c,d;
1674
explain select * from t1 order by a,b,c,d;
1675
id select_type table type possible_keys key key_len ref rows Extra
1676
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1678
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1679
insert into t1 values ('8', '6'), ('4', '7');
1680
select min(a) from t1;
1683
select min(b) from t1 where a='8';
1687
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1688
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1691
18446744073709551600
1692
18446744073709551601
1693
select count(*) from t1 where x>0;
1696
select count(*) from t1 where x=0;
1699
select count(*) from t1 where x<0;
1702
select count(*) from t1 where x < -16;
1705
select count(*) from t1 where x = -16;
1708
explain select count(*) from t1 where x > -16;
1709
id select_type table type possible_keys key key_len ref rows Extra
1710
1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
1711
select count(*) from t1 where x > -16;
1714
select * from t1 where x > -16;
1716
18446744073709551600
1717
18446744073709551601
1718
select count(*) from t1 where x = 18446744073709551601;
1722
show status like "Innodb_buffer_pool_pages_total";
1724
Innodb_buffer_pool_pages_total 512
1725
show status like "Innodb_page_size";
1727
Innodb_page_size 16384
1728
show status like "Innodb_rows_deleted";
1730
Innodb_rows_deleted 70
1731
show status like "Innodb_rows_inserted";
1733
Innodb_rows_inserted 1082
1734
show status like "Innodb_rows_updated";
1736
Innodb_rows_updated 885
1737
show status like "Innodb_row_lock_waits";
1739
Innodb_row_lock_waits 0
1740
show status like "Innodb_row_lock_current_waits";
1742
Innodb_row_lock_current_waits 0
1743
show status like "Innodb_row_lock_time";
1745
Innodb_row_lock_time 0
1746
show status like "Innodb_row_lock_time_max";
1748
Innodb_row_lock_time_max 0
1749
show status like "Innodb_row_lock_time_avg";
1751
Innodb_row_lock_time_avg 0
1752
show variables like "innodb_sync_spin_loops";
1754
innodb_sync_spin_loops 20
1755
set global innodb_sync_spin_loops=1000;
1756
show variables like "innodb_sync_spin_loops";
1758
innodb_sync_spin_loops 1000
1759
set global innodb_sync_spin_loops=0;
1760
show variables like "innodb_sync_spin_loops";
1762
innodb_sync_spin_loops 0
1763
set global innodb_sync_spin_loops=20;
1764
show variables like "innodb_sync_spin_loops";
1766
innodb_sync_spin_loops 20
1767
show variables like "innodb_thread_concurrency";
1769
innodb_thread_concurrency 8
1770
set global innodb_thread_concurrency=1001;
1772
Warning 1292 Truncated incorrect thread_concurrency value: '1001'
1773
show variables like "innodb_thread_concurrency";
1775
innodb_thread_concurrency 1000
1776
set global innodb_thread_concurrency=0;
1777
show variables like "innodb_thread_concurrency";
1779
innodb_thread_concurrency 0
1780
set global innodb_thread_concurrency=16;
1781
show variables like "innodb_thread_concurrency";
1783
innodb_thread_concurrency 16
1784
show variables like "innodb_concurrency_tickets";
1786
innodb_concurrency_tickets 500
1787
set global innodb_concurrency_tickets=1000;
1788
show variables like "innodb_concurrency_tickets";
1790
innodb_concurrency_tickets 1000
1791
set global innodb_concurrency_tickets=0;
1793
Warning 1292 Truncated incorrect concurrency_tickets value: '0'
1794
show variables like "innodb_concurrency_tickets";
1796
innodb_concurrency_tickets 1
1797
set global innodb_concurrency_tickets=500;
1798
show variables like "innodb_concurrency_tickets";
1800
innodb_concurrency_tickets 500
1801
show variables like "innodb_thread_sleep_delay";
1803
innodb_thread_sleep_delay 10000
1804
set global innodb_thread_sleep_delay=100000;
1805
show variables like "innodb_thread_sleep_delay";
1807
innodb_thread_sleep_delay 100000
1808
set global innodb_thread_sleep_delay=0;
1809
show variables like "innodb_thread_sleep_delay";
1811
innodb_thread_sleep_delay 0
1812
set global innodb_thread_sleep_delay=10000;
1813
show variables like "innodb_thread_sleep_delay";
1815
innodb_thread_sleep_delay 10000
1816
set storage_engine=INNODB;
1817
drop table if exists t1,t2,t3;
1818
--- Testing varchar ---
1819
--- Testing varchar ---
1820
create table t1 (v varchar(10), c char(10), t text);
1821
insert into t1 values('+ ', '+ ', '+ ');
1822
set @a=repeat(' ',20);
1823
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1825
Note 1265 Data truncated for column 'v' at row 1
1826
Note 1265 Data truncated for column 'c' at row 1
1827
select concat('*',v,'*',c,'*',t,'*') from t1;
1828
concat('*',v,'*',c,'*',t,'*')
1831
show create table t1;
1833
t1 CREATE TABLE `t1` (
1834
`v` varchar(10) DEFAULT NULL,
1835
`c` char(10) DEFAULT NULL,
1837
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1838
create table t2 like t1;
1839
show create table t2;
1841
t2 CREATE TABLE `t2` (
1842
`v` varchar(10) DEFAULT NULL,
1843
`c` char(10) DEFAULT NULL,
1845
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1846
create table t3 select * from t1;
1847
show create table t3;
1849
t3 CREATE TABLE `t3` (
1850
`v` varchar(10) DEFAULT NULL,
1851
`c` char(10) DEFAULT NULL,
1853
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1854
alter table t1 modify c varchar(10);
1855
show create table t1;
1857
t1 CREATE TABLE `t1` (
1858
`v` varchar(10) DEFAULT NULL,
1859
`c` varchar(10) DEFAULT NULL,
1861
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1862
alter table t1 modify v char(10);
1863
show create table t1;
1865
t1 CREATE TABLE `t1` (
1866
`v` char(10) DEFAULT NULL,
1867
`c` varchar(10) DEFAULT NULL,
1869
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1870
alter table t1 modify t varchar(10);
1872
Note 1265 Data truncated for column 't' at row 2
1873
show create table t1;
1875
t1 CREATE TABLE `t1` (
1876
`v` char(10) DEFAULT NULL,
1877
`c` varchar(10) DEFAULT NULL,
1878
`t` varchar(10) DEFAULT NULL
1879
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1880
select concat('*',v,'*',c,'*',t,'*') from t1;
1881
concat('*',v,'*',c,'*',t,'*')
1884
drop table t1,t2,t3;
1885
create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1886
show create table t1;
1888
t1 CREATE TABLE `t1` (
1889
`v` varchar(10) DEFAULT NULL,
1890
`c` char(10) DEFAULT NULL,
1895
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1896
select count(*) from t1;
1899
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1900
select count(*) from t1 where v='a';
1903
select count(*) from t1 where c='a';
1906
select count(*) from t1 where t='a';
1909
select count(*) from t1 where v='a ';
1912
select count(*) from t1 where c='a ';
1915
select count(*) from t1 where t='a ';
1918
select count(*) from t1 where v between 'a' and 'a ';
1921
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1924
select count(*) from t1 where v like 'a%';
1927
select count(*) from t1 where c like 'a%';
1930
select count(*) from t1 where t like 'a%';
1933
select count(*) from t1 where v like 'a %';
1936
explain select count(*) from t1 where v='a ';
1937
id select_type table type possible_keys key key_len ref rows Extra
1938
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1939
explain select count(*) from t1 where c='a ';
1940
id select_type table type possible_keys key key_len ref rows Extra
1941
1 SIMPLE t1 ref c c 11 const # Using where; Using index
1942
explain select count(*) from t1 where t='a ';
1943
id select_type table type possible_keys key key_len ref rows Extra
1944
1 SIMPLE t1 ref t t 13 const # Using where
1945
explain select count(*) from t1 where v like 'a%';
1946
id select_type table type possible_keys key key_len ref rows Extra
1947
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
1948
explain select count(*) from t1 where v between 'a' and 'a ';
1949
id select_type table type possible_keys key key_len ref rows Extra
1950
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1951
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1952
id select_type table type possible_keys key key_len ref rows Extra
1953
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1954
alter table t1 add unique(v);
1955
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
1956
alter table t1 add key(v);
1957
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1969
explain select * from t1 where v='a';
1970
id select_type table type possible_keys key key_len ref rows Extra
1971
1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition
1972
select v,count(*) from t1 group by v limit 10;
1984
select v,count(t) from t1 group by v limit 10;
1996
select v,count(c) from t1 group by v limit 10;
2008
select sql_big_result v,count(t) from t1 group by v limit 10;
2020
select sql_big_result v,count(c) from t1 group by v limit 10;
2032
select c,count(*) from t1 group by c limit 10;
2044
select c,count(t) from t1 group by c limit 10;
2056
select sql_big_result c,count(t) from t1 group by c limit 10;
2068
select t,count(*) from t1 group by t limit 10;
2080
select t,count(t) from t1 group by t limit 10;
2092
select sql_big_result t,count(t) from t1 group by t limit 10;
2104
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
2105
show create table t1;
2107
t1 CREATE TABLE `t1` (
2108
`v` varchar(300) DEFAULT NULL,
2109
`c` char(10) DEFAULT NULL,
2114
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2115
select count(*) from t1 where v='a';
2118
select count(*) from t1 where v='a ';
2121
select count(*) from t1 where v between 'a' and 'a ';
2124
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2127
select count(*) from t1 where v like 'a%';
2130
select count(*) from t1 where v like 'a %';
2133
explain select count(*) from t1 where v='a ';
2134
id select_type table type possible_keys key key_len ref rows Extra
2135
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2136
explain select count(*) from t1 where v like 'a%';
2137
id select_type table type possible_keys key key_len ref rows Extra
2138
1 SIMPLE t1 range v v 303 NULL # Using where; Using index
2139
explain select count(*) from t1 where v between 'a' and 'a ';
2140
id select_type table type possible_keys key key_len ref rows Extra
2141
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2142
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2143
id select_type table type possible_keys key key_len ref rows Extra
2144
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2145
explain select * from t1 where v='a';
2146
id select_type table type possible_keys key key_len ref rows Extra
2147
1 SIMPLE t1 ref v v 303 const # Using index condition
2148
select v,count(*) from t1 group by v limit 10;
2160
select v,count(t) from t1 group by v limit 10;
2172
select sql_big_result v,count(t) from t1 group by v limit 10;
2184
alter table t1 drop key v, add key v (v(30));
2185
show create table t1;
2187
t1 CREATE TABLE `t1` (
2188
`v` varchar(300) DEFAULT NULL,
2189
`c` char(10) DEFAULT NULL,
2194
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2195
select count(*) from t1 where v='a';
2198
select count(*) from t1 where v='a ';
2201
select count(*) from t1 where v between 'a' and 'a ';
2204
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2207
select count(*) from t1 where v like 'a%';
2210
select count(*) from t1 where v like 'a %';
2213
explain select count(*) from t1 where v='a ';
2214
id select_type table type possible_keys key key_len ref rows Extra
2215
1 SIMPLE t1 ref v v 33 const # Using where
2216
explain select count(*) from t1 where v like 'a%';
2217
id select_type table type possible_keys key key_len ref rows Extra
2218
1 SIMPLE t1 range v v 33 NULL # Using where
2219
explain select count(*) from t1 where v between 'a' and 'a ';
2220
id select_type table type possible_keys key key_len ref rows Extra
2221
1 SIMPLE t1 ref v v 33 const # Using where
2222
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2223
id select_type table type possible_keys key key_len ref rows Extra
2224
1 SIMPLE t1 ref v v 33 const # Using where
2225
explain select * from t1 where v='a';
2226
id select_type table type possible_keys key key_len ref rows Extra
2227
1 SIMPLE t1 ref v v 33 const # Using where
2228
select v,count(*) from t1 group by v limit 10;
2240
select v,count(t) from t1 group by v limit 10;
2252
select sql_big_result v,count(t) from t1 group by v limit 10;
2264
alter table t1 modify v varchar(600), drop key v, add key v (v);
2265
show create table t1;
2267
t1 CREATE TABLE `t1` (
2268
`v` varchar(600) DEFAULT NULL,
2269
`c` char(10) DEFAULT NULL,
2274
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2275
select v,count(*) from t1 group by v limit 10;
2287
select v,count(t) from t1 group by v limit 10;
2299
select sql_big_result v,count(t) from t1 group by v limit 10;
2312
create table t1 (a char(10), unique (a));
2313
insert into t1 values ('a ');
2314
insert into t1 values ('a ');
2315
ERROR 23000: Duplicate entry 'a' for key 'a'
2316
alter table t1 modify a varchar(10);
2317
insert into t1 values ('a '),('a '),('a '),('a ');
2318
ERROR 23000: Duplicate entry 'a ' for key 'a'
2319
insert into t1 values ('a ');
2320
ERROR 23000: Duplicate entry 'a ' for key 'a'
2321
insert into t1 values ('a ');
2322
ERROR 23000: Duplicate entry 'a ' for key 'a'
2323
insert into t1 values ('a ');
2324
ERROR 23000: Duplicate entry 'a ' for key 'a'
2325
update t1 set a='a ' where a like 'a%';
2326
select concat(a,'.') from t1;
2329
update t1 set a='abc ' where a like 'a ';
2330
select concat(a,'.') from t1;
2333
update t1 set a='a ' where a like 'a %';
2334
select concat(a,'.') from t1;
2337
update t1 set a='a ' where a like 'a ';
2338
select concat(a,'.') from t1;
2342
create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2343
show create table t1;
2345
t1 CREATE TABLE `t1` (
2346
`v` varchar(10) DEFAULT NULL,
2347
`c` char(10) DEFAULT NULL,
2352
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2354
create table t1 (v char(10) character set utf8);
2355
show create table t1;
2357
t1 CREATE TABLE `t1` (
2358
`v` char(10) CHARACTER SET utf8 DEFAULT NULL
2359
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2361
create table t1 (v varchar(10), c char(10)) row_format=fixed;
2362
show create table t1;
2364
t1 CREATE TABLE `t1` (
2365
`v` varchar(10) DEFAULT NULL,
2366
`c` char(10) DEFAULT NULL
2367
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
2368
insert into t1 values('a','a'),('a ','a ');
2369
select concat('*',v,'*',c,'*') from t1;
2370
concat('*',v,'*',c,'*')
2374
create table t1 (v varchar(65530), key(v(10)));
2375
insert into t1 values(repeat('a',65530));
2376
select length(v) from t1 where v=repeat('a',65530);
2380
create table t1(a int, b varchar(12), key ba(b, a));
2381
insert into t1 values (1, 'A'), (20, NULL);
2382
explain select * from t1 where a=20 and b is null;
2383
id select_type table type possible_keys key key_len ref rows Extra
2384
1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index
2385
select * from t1 where a=20 and b is null;
2389
create table t1 (v varchar(65530), key(v));
2391
Warning 1071 Specified key was too long; max key length is 767 bytes
2393
create table t1 (v varchar(65536));
2395
Note 1246 Converting column 'v' from VARCHAR to TEXT
2396
show create table t1;
2398
t1 CREATE TABLE `t1` (
2400
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2402
create table t1 (v varchar(65530) character set utf8);
2404
Note 1246 Converting column 'v' from VARCHAR to TEXT
2405
show create table t1;
2407
t1 CREATE TABLE `t1` (
2408
`v` mediumtext CHARACTER SET utf8
2409
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2411
set storage_engine=MyISAM;
2412
create table t1 (v varchar(16384)) engine=innodb;
2414
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2415
insert into t1 values ('8', '6'), ('4', '7');
2416
select min(a) from t1;
2419
select min(b) from t1 where a='8';
2423
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2424
insert into t1 (b) values (1);
2425
replace into t1 (b) values (2), (1), (3);
2432
insert into t1 (b) values (1);
2433
replace into t1 (b) values (2);
2434
replace into t1 (b) values (1);
2435
replace into t1 (b) values (3);
2442
create table t1 (rowid int not null auto_increment, val int not null,primary
2443
key (rowid), unique(val)) engine=innodb;
2444
replace into t1 (val) values ('1'),('2');
2445
replace into t1 (val) values ('1'),('2');
2446
insert into t1 (val) values ('1'),('2');
2447
ERROR 23000: Duplicate entry '1' for key 'val'
2453
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2454
insert into t1 (val) values (1);
2455
update t1 set a=2 where a=1;
2456
insert into t1 (val) values (1);
2457
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
2462
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2463
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2464
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2467
SELECT GRADE FROM t1 WHERE GRADE= 151;
2471
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
2472
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
2473
insert into t2 values ('aa','cc');
2474
insert into t1 values ('aa','bb'),('aa','cc');
2475
delete t1 from t1,t2 where f1=f3 and f4='cc';
2480
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2483
id INTEGER NOT NULL,
2484
FOREIGN KEY (id) REFERENCES t1 (id)
2486
INSERT INTO t1 (id) VALUES (NULL);
2491
INSERT INTO t1 (id) VALUES (NULL);
2497
INSERT INTO t1 (id) VALUES (NULL);
2506
CREATE TEMPORARY TABLE t2
2508
id INT NOT NULL PRIMARY KEY,
2510
FOREIGN KEY (b) REFERENCES test.t1(id)
2512
Got one of the listed errors
2514
create table t1 (col1 varchar(2000), index (col1(767)))
2515
character set = latin1 engine = innodb;
2516
create table t2 (col1 char(255), index (col1))
2517
character set = latin1 engine = innodb;
2518
create table t3 (col1 binary(255), index (col1))
2519
character set = latin1 engine = innodb;
2520
create table t4 (col1 varchar(767), index (col1))
2521
character set = latin1 engine = innodb;
2522
create table t5 (col1 varchar(767) primary key)
2523
character set = latin1 engine = innodb;
2524
create table t6 (col1 varbinary(767) primary key)
2525
character set = latin1 engine = innodb;
2526
create table t7 (col1 text, index(col1(767)))
2527
character set = latin1 engine = innodb;
2528
create table t8 (col1 blob, index(col1(767)))
2529
character set = latin1 engine = innodb;
2530
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
2531
character set = latin1 engine = innodb;
2532
show create table t9;
2534
t9 CREATE TABLE `t9` (
2535
`col1` varchar(512) DEFAULT NULL,
2536
`col2` varchar(512) DEFAULT NULL,
2537
KEY `col1` (`col1`,`col2`)
2538
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2539
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
2540
create table t1 (col1 varchar(768), index(col1))
2541
character set = latin1 engine = innodb;
2543
Warning 1071 Specified key was too long; max key length is 767 bytes
2544
create table t2 (col1 varbinary(768), index(col1))
2545
character set = latin1 engine = innodb;
2547
Warning 1071 Specified key was too long; max key length is 767 bytes
2548
create table t3 (col1 text, index(col1(768)))
2549
character set = latin1 engine = innodb;
2551
Warning 1071 Specified key was too long; max key length is 767 bytes
2552
create table t4 (col1 blob, index(col1(768)))
2553
character set = latin1 engine = innodb;
2555
Warning 1071 Specified key was too long; max key length is 767 bytes
2556
show create table t1;
2558
t1 CREATE TABLE `t1` (
2559
`col1` varchar(768) DEFAULT NULL,
2560
KEY `col1` (`col1`(767))
2561
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2562
drop table t1, t2, t3, t4;
2563
create table t1 (col1 varchar(768) primary key)
2564
character set = latin1 engine = innodb;
2565
ERROR 42000: Specified key was too long; max key length is 767 bytes
2566
create table t2 (col1 varbinary(768) primary key)
2567
character set = latin1 engine = innodb;
2568
ERROR 42000: Specified key was too long; max key length is 767 bytes
2569
create table t3 (col1 text, primary key(col1(768)))
2570
character set = latin1 engine = innodb;
2571
ERROR 42000: Specified key was too long; max key length is 767 bytes
2572
create table t4 (col1 blob, primary key(col1(768)))
2573
character set = latin1 engine = innodb;
2574
ERROR 42000: Specified key was too long; max key length is 767 bytes
2582
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2584
INSERT INTO t2 VALUES(2);
2585
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2586
INSERT INTO t1 VALUES(1);
2587
INSERT INTO t2 VALUES(1);
2588
DELETE FROM t1 WHERE id = 1;
2589
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2591
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
2592
SET FOREIGN_KEY_CHECKS=0;
2594
SET FOREIGN_KEY_CHECKS=1;
2595
INSERT INTO t2 VALUES(3);
2596
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2598
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2599
insert into t1 values (1),(2);
2604
insert into t1 values(3);
2614
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2615
insert into t1 values (1),(2);
2621
insert into t1 values(3);
2626
set foreign_key_checks=0;
2627
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2628
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2629
ERROR HY000: Can't create table 'test.t1' (errno: 150)
2630
set foreign_key_checks=1;
2632
set foreign_key_checks=0;
2633
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2634
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
2635
ERROR HY000: Can't create table 'test.t2' (errno: 150)
2636
set foreign_key_checks=1;
2638
set foreign_key_checks=0;
2639
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2640
create table t1(a varchar(10) primary key) engine = innodb;
2641
alter table t1 modify column a int;
2642
Got one of the listed errors
2643
set foreign_key_checks=1;
2645
set foreign_key_checks=0;
2646
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2647
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2648
alter table t1 convert to character set utf8;
2649
set foreign_key_checks=1;
2651
set foreign_key_checks=0;
2652
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2653
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
2654
rename table t3 to t1;
2655
ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150)
2656
set foreign_key_checks=1;
2658
create table t1(a int primary key) row_format=redundant engine=innodb;
2659
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2660
create table t3(a int primary key) row_format=compact engine=innodb;
2661
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2662
insert into t1 values(1);
2663
insert into t3 values(1);
2664
insert into t2 values(2);
2665
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`))
2666
insert into t4 values(2);
2667
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`))
2668
insert into t2 values(1);
2669
insert into t4 values(1);
2671
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`))
2673
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`))
2675
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`))
2677
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`))
2679
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`))
2681
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`))
2686
drop table t4,t3,t2,t1;
2687
create table t1 (a varchar(255) character set utf8,
2688
b varchar(255) character set utf8,
2689
c varchar(255) character set utf8,
2690
d varchar(255) character set utf8,
2691
key (a,b,c,d)) engine=innodb;
2693
create table t1 (a varchar(255) character set utf8,
2694
b varchar(255) character set utf8,
2695
c varchar(255) character set utf8,
2696
d varchar(255) character set utf8,
2697
e varchar(255) character set utf8,
2698
key (a,b,c,d,e)) engine=innodb;
2699
ERROR 42000: Specified key was too long; max key length is 3072 bytes
2700
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2701
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
2702
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
2703
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
2704
insert into t1 values (0x41),(0x4120),(0x4100);
2705
insert into t2 values (0x41),(0x4120),(0x4100);
2706
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2707
insert into t2 values (0x41),(0x4120);
2708
insert into t3 values (0x41),(0x4120),(0x4100);
2709
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2710
insert into t3 values (0x41),(0x4100);
2711
insert into t4 values (0x41),(0x4120),(0x4100);
2712
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2713
insert into t4 values (0x41),(0x4100);
2714
select hex(s1) from t1;
2719
select hex(s1) from t2;
2723
select hex(s1) from t3;
2727
select hex(s1) from t4;
2731
drop table t1,t2,t3,t4;
2732
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2733
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2734
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2735
insert into t2 values(0x42);
2736
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)
2737
insert into t2 values(0x41);
2738
select hex(s1) from t2;
2741
update t1 set s1=0x123456 where a=2;
2742
select hex(s1) from t2;
2745
update t1 set s1=0x12 where a=1;
2746
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)
2747
update t1 set s1=0x12345678 where a=1;
2748
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)
2749
update t1 set s1=0x123457 where a=1;
2750
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)
2751
update t1 set s1=0x1220 where a=1;
2752
select hex(s1) from t2;
2755
update t1 set s1=0x1200 where a=1;
2756
select hex(s1) from t2;
2759
update t1 set s1=0x4200 where a=1;
2760
select hex(s1) from t2;
2763
delete from t1 where a=1;
2764
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)
2765
delete from t1 where a=2;
2766
update t2 set s1=0x4120;
2768
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)
2769
delete from t1 where a!=3;
2770
select a,hex(s1) from t1;
2773
select hex(s1) from t2;
2777
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
2778
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2779
insert into t1 values(1,0x4100),(2,0x41);
2780
insert into t2 values(0x41);
2781
select hex(s1) from t2;
2784
update t1 set s1=0x1234 where a=1;
2785
select hex(s1) from t2;
2788
update t1 set s1=0x12 where a=2;
2789
select hex(s1) from t2;
2792
delete from t1 where a=1;
2793
delete from t1 where a=2;
2794
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)
2795
select a,hex(s1) from t1;
2798
select hex(s1) from t2;
2802
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2803
CREATE TABLE t2(a INT) ENGINE=InnoDB;
2804
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2805
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2806
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2807
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2808
SHOW CREATE TABLE t2;
2810
t2 CREATE TABLE `t2` (
2811
`a` int(11) DEFAULT NULL,
2812
KEY `t2_ibfk_0` (`a`)
2813
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2815
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2816
insert into t1(a) values (1),(2),(3);
2819
update t1 set b = 5 where a = 2;
2820
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
2822
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
2823
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
2824
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
2825
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
2826
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
2831
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2832
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2833
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2834
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2835
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2836
insert into t1(a) values (1),(2),(3);
2837
insert into t2(a) values (1),(2),(3);
2838
insert into t3(a) values (1),(2),(3);
2839
insert into t4(a) values (1),(2),(3);
2840
insert into t3(a) values (5),(7),(8);
2841
insert into t4(a) values (5),(7),(8);
2842
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
2843
create trigger t1t before insert on t1 for each row begin
2844
INSERT INTO t2 SET a = NEW.a;
2846
create trigger t2t before insert on t2 for each row begin
2847
DELETE FROM t3 WHERE a = NEW.a;
2849
create trigger t3t before delete on t3 for each row begin
2850
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
2852
create trigger t4t before update on t4 for each row begin
2853
UPDATE t5 SET b = b + 1 where a = NEW.a;
2857
update t1 set b = b + 5 where a = 1;
2858
update t2 set b = b + 5 where a = 1;
2859
update t3 set b = b + 5 where a = 1;
2860
update t4 set b = b + 5 where a = 1;
2861
insert into t5(a) values(20);
2863
insert into t1(a) values(7);
2864
insert into t2(a) values(8);
2865
delete from t2 where a = 3;
2866
update t4 set b = b + 1 where a = 3;
2872
drop table t1, t2, t3, t4, t5;
2874
field1 varchar(8) NOT NULL DEFAULT '',
2875
field2 varchar(8) NOT NULL DEFAULT '',
2876
PRIMARY KEY (field1, field2)
2879
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2880
FOREIGN KEY (field1) REFERENCES t1 (field1)
2881
ON DELETE CASCADE ON UPDATE CASCADE
2883
INSERT INTO t1 VALUES ('old', 'somevalu');
2884
INSERT INTO t1 VALUES ('other', 'anyvalue');
2885
INSERT INTO t2 VALUES ('old');
2886
INSERT INTO t2 VALUES ('other');
2887
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2888
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
2901
alter table t1 add constraint c2_fk foreign key (c2)
2902
references t2(c1) on delete cascade;
2903
show create table t1;
2905
t1 CREATE TABLE `t1` (
2906
`c1` bigint(20) NOT NULL,
2907
`c2` bigint(20) NOT NULL,
2909
UNIQUE KEY `c2` (`c2`),
2910
CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2911
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2912
alter table t1 drop foreign key c2_fk;
2913
show create table t1;
2915
t1 CREATE TABLE `t1` (
2916
`c1` bigint(20) NOT NULL,
2917
`c2` bigint(20) NOT NULL,
2919
UNIQUE KEY `c2` (`c2`)
2920
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2922
create table t1(a date) engine=innodb;
2923
create table t2(a date, key(a)) engine=innodb;
2924
insert into t1 values('2005-10-01');
2925
insert into t2 values('2005-10-01');
2926
select * from t1, t2
2927
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2929
2005-10-01 2005-10-01
2931
create table t1 (id int not null, f_id int not null, f int not null,
2932
primary key(f_id, id)) engine=innodb;
2933
create table t2 (id int not null,s_id int not null,s varchar(200),
2934
primary key(id)) engine=innodb;
2935
INSERT INTO t1 VALUES (8, 1, 3);
2936
INSERT INTO t1 VALUES (1, 2, 1);
2937
INSERT INTO t2 VALUES (1, 0, '');
2938
INSERT INTO t2 VALUES (8, 1, '');
2940
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2941
WHERE mm.id IS NULL;
2942
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2943
where mm.id is null lock in share mode;
2946
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2947
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2950
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2951
update t1 set b = 5 where b = 1;
2953
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2954
select * from t1 where a = 7 and b = 3 for update;
2960
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2961
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2964
select * from t1 lock in share mode;
2972
update t1 set b = 5 where b = 1;
2974
select * from t1 where a = 2 and b = 2 for update;
2975
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2979
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2980
insert into t1 values (1,2),(5,3),(4,2);
2981
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2982
insert into t2 values (8,6),(12,1),(3,1);
2985
select * from t2 for update;
2991
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2992
insert into t1 select * from t2;
2993
update t1 set b = (select e from t2 where a = d);
2994
create table t3(d int not null, e int, primary key(d)) engine=innodb
2998
drop table t1, t2, t3;
2999
create table t1(a int not null, b int, primary key(a)) engine=innodb;
3000
insert into t1 values (1,2),(5,3),(4,2);
3001
create table t2(a int not null, b int, primary key(a)) engine=innodb;
3002
insert into t2 values (8,6),(12,1),(3,1);
3003
create table t3(d int not null, b int, primary key(d)) engine=innodb;
3004
insert into t3 values (8,6),(12,1),(3,1);
3005
create table t5(a int not null, b int, primary key(a)) engine=innodb;
3006
insert into t5 values (1,2),(5,3),(4,2);
3007
create table t6(d int not null, e int, primary key(d)) engine=innodb;
3008
insert into t6 values (8,6),(12,1),(3,1);
3009
create table t8(a int not null, b int, primary key(a)) engine=innodb;
3010
insert into t8 values (1,2),(5,3),(4,2);
3011
create table t9(d int not null, e int, primary key(d)) engine=innodb;
3012
insert into t9 values (8,6),(12,1),(3,1);
3015
select * from t2 for update;
3021
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3022
insert into t1 select * from t2;
3024
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3025
update t3 set b = (select b from t2 where a = d);
3027
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3028
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
3030
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3031
insert into t5 (select * from t2 lock in share mode);
3033
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3034
update t6 set e = (select b from t2 where a = d lock in share mode);
3036
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3037
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
3039
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3040
insert into t8 (select * from t2 for update);
3042
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3043
update t9 set e = (select b from t2 where a = d for update);
3045
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3046
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
3047
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3048
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3049
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3050
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3051
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3052
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3053
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3054
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3055
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3057
drop table t1, t2, t3, t5, t6, t8, t9;
3058
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
3059
ERROR HY000: Can't create table 'test.t1' (errno: -1)
3061
a BIGINT(20) NOT NULL,
3063
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3065
a BIGINT(20) NOT NULL,
3066
b VARCHAR(128) NOT NULL,
3069
KEY idx_t2_b_c (b,c(200)),
3070
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
3072
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3073
INSERT INTO t1 VALUES (1);
3074
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
3075
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
3076
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
3077
INSERT INTO t2 VALUES (1, 'customer_over', '1');
3078
SELECT * FROM t2 WHERE b = 'customer_over';
3081
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
3084
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
3087
/* Bang: Empty result set, above was expected: */
3088
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3091
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3095
CREATE TABLE t1 ( a int ) ENGINE=innodb;
3097
INSERT INTO t1 VALUES (1);
3099
Table Op Msg_type Msg_text
3100
test.t1 optimize status OK
3102
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
3103
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
3104
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
3105
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
3106
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
3107
DELETE CASCADE ON UPDATE CASCADE;
3108
SHOW CREATE TABLE t2;
3110
t2 CREATE TABLE `t2` (
3111
`id` int(11) NOT NULL,
3112
`f` int(11) NOT NULL,
3115
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
3116
CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
3117
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3119
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
3120
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
3121
INSERT INTO t1 VALUES (1);
3122
INSERT INTO t2 VALUES (1);
3123
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
3124
ALTER TABLE t2 MODIFY a INT NOT NULL;
3125
ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
3128
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
3130
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
3132
INSERT INTO t1 VALUES ('DDD');
3137
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
3139
INSERT INTO t1 VALUES (0),(347),(0);
3145
SHOW CREATE TABLE t1;
3147
t1 CREATE TABLE `t1` (
3148
`id` int(11) NOT NULL AUTO_INCREMENT,
3150
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3151
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
3152
INSERT INTO t2 VALUES(42),(347),(348);
3153
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
3154
SHOW CREATE TABLE t1;
3156
t1 CREATE TABLE `t1` (
3157
`id` int(11) NOT NULL AUTO_INCREMENT,
3159
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
3160
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3163
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
3164
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
3165
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
3166
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
3167
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
3168
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
3169
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
3170
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
3172
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
3173
DROP TABLE IF EXISTS t1;
3175
Note 1051 Unknown table 't1'
3177
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
3179
INSERT INTO t1 VALUES(-10);
3183
INSERT INTO t1 VALUES(NULL);
3189
SET TX_ISOLATION='read-committed';
3191
DROP TABLE IF EXISTS t1, t2;
3193
Note 1051 Unknown table 't1'
3194
Note 1051 Unknown table 't2'
3195
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
3196
CREATE TABLE t2 LIKE t1;
3199
SET TX_ISOLATION='read-committed';
3201
INSERT INTO t1 VALUES (1);
3203
SELECT * FROM t1 WHERE a=1;
3206
SET TX_ISOLATION='read-committed';
3210
SET TX_ISOLATION='read-committed';
3212
INSERT INTO t1 VALUES (2);
3214
SELECT * FROM t1 WHERE a=2;
3217
SELECT * FROM t1 WHERE a=2;
added
removed
mysql-test/r/innodb.result
