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 note Table does not support optimize, doing recreate + analyze instead
170
test.t1 optimize status OK
172
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
173
t1 0 PRIMARY 1 id A # NULL NULL BTREE
174
t1 1 parent_id 1 parent_id A # NULL NULL BTREE
175
t1 1 level 1 level A # NULL NULL BTREE
178
gesuchnr int(11) DEFAULT '0' NOT NULL,
179
benutzer_id int(11) DEFAULT '0' NOT NULL,
180
PRIMARY KEY (gesuchnr,benutzer_id)
182
replace into t1 (gesuchnr,benutzer_id) values (2,1);
183
replace into t1 (gesuchnr,benutzer_id) values (1,1);
184
replace into t1 (gesuchnr,benutzer_id) values (1,1);
190
create table t1 (a int) engine=innodb;
191
insert into t1 values (1), (2);
193
Table Op Msg_type Msg_text
194
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
195
test.t1 optimize status OK
196
delete from t1 where a = 1;
201
Table Op Msg_type Msg_text
202
test.t1 check status OK
204
create table t1 (a int,b varchar(20)) engine=innodb;
205
insert into t1 values (1,""), (2,"testing");
206
delete from t1 where a = 1;
210
create index skr on t1 (a);
211
insert into t1 values (3,""), (4,"testing");
213
Table Op Msg_type Msg_text
214
test.t1 analyze status OK
216
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
217
t1 1 skr 1 a A # NULL NULL YES BTREE
219
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
220
insert into t1 values (1,""), (2,"testing");
221
select * from t1 where a = 1;
225
create table t1 (n int not null primary key) engine=innodb;
227
insert into t1 values (4);
229
select n, "after rollback" from t1;
231
insert into t1 values (4);
233
select n, "after commit" from t1;
237
insert into t1 values (5);
238
insert into t1 values (4);
239
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
241
select n, "after commit" from t1;
246
insert into t1 values (6);
247
insert into t1 values (4);
248
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
256
savepoint `my_savepoint`;
257
insert into t1 values (7);
259
insert into t1 values (3);
268
rollback to savepoint savept2;
269
rollback to savepoint savept3;
270
ERROR 42000: SAVEPOINT savept3 does not exist
271
rollback to savepoint savept2;
272
release savepoint `my_savepoint`;
279
rollback to savepoint `my_savepoint`;
280
ERROR 42000: SAVEPOINT my_savepoint does not exist
281
rollback to savepoint savept2;
282
ERROR 42000: SAVEPOINT savept2 does not exist
283
insert into t1 values (8);
290
create table t1 (n int not null primary key) engine=innodb;
292
insert into t1 values (4);
293
flush tables with read lock;
301
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
303
insert into t1 values(1,'hamdouni');
304
select id as afterbegin_id,nom as afterbegin_nom from t1;
305
afterbegin_id afterbegin_nom
308
select id as afterrollback_id,nom as afterrollback_nom from t1;
309
afterrollback_id afterrollback_nom
311
insert into t1 values(2,'mysql');
312
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
313
afterautocommit0_id afterautocommit0_nom
316
select id as afterrollback_id,nom as afterrollback_nom from t1;
317
afterrollback_id afterrollback_nom
320
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
321
insert into t1 values ('pippo', 12);
322
insert into t1 values ('pippo', 12);
323
ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
325
delete from t1 where id = 'pippo';
328
insert into t1 values ('pippo', 12);
340
create table t1 (a integer) engine=innodb;
342
rename table t1 to t2;
343
create table t1 (b integer) engine=innodb;
344
insert into t1 values (1);
347
rename table t2 to t1;
350
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
351
INSERT INTO t1 VALUES (1, 'Jochen');
356
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
358
INSERT INTO t1 SET _userid='marc@anyware.co.uk';
363
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
369
user_id int(10) DEFAULT '0' NOT NULL,
372
ref_email varchar(100) DEFAULT '' NOT NULL,
374
PRIMARY KEY (user_id,ref_email)
376
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');
377
select * from t1 where user_id=10292;
378
user_id name phone ref_email detail
379
10292 sanjeev 29153373 sansh777@hotmail.com xxx
380
10292 shirish 2333604 shirish@yahoo.com ddsds
381
10292 sonali 323232 sonali@bolly.com filmstar
382
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
383
select * from t1 where user_id=10292;
384
user_id name phone ref_email detail
385
10292 sanjeev 29153373 sansh777@hotmail.com xxx
386
10292 shirish 2333604 shirish@yahoo.com ddsds
387
10292 sonali 323232 sonali@bolly.com filmstar
388
select * from t1 where user_id>=10292;
389
user_id name phone ref_email detail
390
10292 sanjeev 29153373 sansh777@hotmail.com xxx
391
10292 shirish 2333604 shirish@yahoo.com ddsds
392
10292 sonali 323232 sonali@bolly.com filmstar
393
10293 shirish 2333604 shirish@yahoo.com ddsds
394
select * from t1 where user_id>10292;
395
user_id name phone ref_email detail
396
10293 shirish 2333604 shirish@yahoo.com ddsds
397
select * from t1 where user_id<10292;
398
user_id name phone ref_email detail
399
10291 sanjeev 29153373 sansh777@hotmail.com xxx
401
CREATE TABLE t1 (a int not null, b int not null,c int not null,
402
key(a),primary key(a,b), unique(c),key(a),unique(b));
404
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
405
t1 0 PRIMARY 1 a A # NULL NULL BTREE
406
t1 0 PRIMARY 2 b A # NULL NULL BTREE
407
t1 0 c 1 c A # NULL NULL BTREE
408
t1 0 b 1 b A # NULL NULL BTREE
409
t1 1 a 1 a A # NULL NULL BTREE
410
t1 1 a_2 1 a A # NULL NULL BTREE
412
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
413
alter table t1 engine=innodb;
414
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
422
update t1 set col2='7' where col1='4';
430
alter table t1 add co3 int not null;
438
update t1 set col2='9' where col1='2';
447
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
448
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
449
insert into t1 VALUES (1,3) , (2,3), (3,3);
455
insert into t2 select * from t1;
461
delete from t1 where b = 3;
464
insert into t1 select * from t2;
477
user_name varchar(12),
480
user_id int(11) DEFAULT '0' NOT NULL,
486
dummy_primary_key int(11) NOT NULL auto_increment,
487
PRIMARY KEY (dummy_primary_key)
489
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
490
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
491
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);
492
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);
493
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
494
select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
495
user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key
496
user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1
497
user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2
498
user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3
499
user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4
500
user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5
503
id int(11) NOT NULL auto_increment,
504
parent_id int(11) DEFAULT '0' NOT NULL,
505
level tinyint(4) DEFAULT '0' NOT NULL,
507
KEY parent_id (parent_id),
510
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);
511
INSERT INTO t1 values (179,5,2);
512
update t1 set parent_id=parent_id+100;
513
select * from t1 where parent_id=102;
518
update t1 set id=id+1000;
519
update t1 set id=1024 where id=1009;
561
update ignore t1 set id=id+1;
603
update ignore t1 set id=1023 where id=1010;
604
select * from t1 where parent_id=102;
609
explain select level from t1 where level=1;
610
id select_type table type possible_keys key key_len ref rows Extra
611
1 SIMPLE t1 ref level level 1 const # Using index
612
select level,id from t1 where level=1;
620
select level,id,parent_id from t1 where level=1;
628
select level,id from t1 where level=1 order by id;
636
delete from t1 where level=1;
674
sca_code char(6) NOT NULL,
675
cat_code char(6) NOT NULL,
676
sca_desc varchar(50),
677
lan_code char(2) NOT NULL,
678
sca_pic varchar(100),
679
sca_sdesc varchar(50),
680
sca_sch_desc varchar(16),
681
PRIMARY KEY (sca_code, cat_code, lan_code),
682
INDEX sca_pic (sca_pic)
684
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');
685
select count(*) from t1 where sca_code = 'PD';
688
select count(*) from t1 where sca_code <= 'PD';
691
select count(*) from t1 where sca_pic is null;
694
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
695
select count(*) from t1 where sca_code='PD' and sca_pic is null;
698
select count(*) from t1 where cat_code='E';
701
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
702
select count(*) from t1 where sca_code='PD' and sca_pic is null;
705
select count(*) from t1 where sca_pic >= 'n';
708
select sca_pic from t1 where sca_pic is null;
712
update t1 set sca_pic="test" where sca_pic is null;
713
delete from t1 where sca_code='pd';
716
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
717
insert into t1 (a) values(1),(2),(3);
718
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
723
select a from t1 natural join t1 as t2 where b >= @a order by a;
728
update t1 set a=5 where a=1;
735
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
736
insert into t1 values("hello",1),("world",2);
737
select * from t1 order by b desc;
742
Table Op Msg_type Msg_text
743
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
744
test.t1 optimize status OK
746
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
747
t1 0 PRIMARY 1 a A # NULL NULL BTREE
749
create table t1 (i int, j int ) ENGINE=innodb;
750
insert into t1 values (1,2);
751
select * from t1 where i=1 and j=2;
754
create index ax1 on t1 (i,j);
755
select * from t1 where i=1 and j=2;
760
a int3 unsigned NOT NULL,
761
b int1 unsigned NOT NULL,
764
INSERT INTO t1 VALUES (1, 1);
765
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
769
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
770
INSERT INTO t1 VALUES (1);
775
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;
776
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);
777
explain select * from t1 where a > 0 and a < 50;
778
id select_type table type possible_keys key key_len ref rows Extra
779
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where
781
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;
782
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
783
LOCK TABLES t1 WRITE;
784
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
785
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
798
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;
799
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
800
LOCK TABLES t1 WRITE;
802
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
803
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
809
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
811
select id,id3 from t1;
819
create table t1 (a char(20), unique (a(5))) engine=innodb;
821
create table t1 (a char(20), index (a(5))) engine=innodb;
822
show create table t1;
824
t1 CREATE TABLE `t1` (
825
`a` char(20) DEFAULT NULL,
827
) ENGINE=InnoDB DEFAULT CHARSET=latin1
829
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
830
insert into t1 values (NULL),(NULL),(NULL);
831
delete from t1 where a=3;
832
insert into t1 values (NULL);
838
alter table t1 add b int;
847
id int auto_increment primary key,
848
name varchar(32) not null,
853
insert into t1 values (1,'one','one value',101),
854
(2,'two','two value',102),(3,'three','three value',103);
856
replace into t1 (value,name,uid) values ('other value','two',102);
857
delete from t1 where uid=102;
859
replace into t1 (value,name,uid) values ('other value','two',102);
861
replace into t1 (value,name,uid) values ('other value','two',102);
865
3 three three value 103
866
6 two other value 102
868
create database mysqltest;
869
create table mysqltest.t1 (a int not null) engine= innodb;
870
insert into mysqltest.t1 values(1);
871
create table mysqltest.t2 (a int not null) engine= myisam;
872
insert into mysqltest.t2 values(1);
873
create table mysqltest.t3 (a int not null) engine= heap;
874
insert into mysqltest.t3 values(1);
876
drop database mysqltest;
877
show tables from mysqltest;
878
ERROR 42000: Unknown database 'mysqltest'
880
create table t1 (a int not null) engine= innodb;
881
insert into t1 values(1),(2);
888
insert into t1 values(1),(2);
895
create table t1 (a int not null) engine= innodb;
896
insert into t1 values(1),(2);
898
insert into t1 values(1),(2);
904
insert into t1 values(1),(2);
909
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
910
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
911
explain select * from t1 order by a;
912
id select_type table type possible_keys key key_len ref rows Extra
913
1 SIMPLE t1 index NULL PRIMARY 4 NULL #
914
explain select * from t1 order by b;
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 * from t1 order by c;
918
id select_type table type possible_keys key key_len ref rows Extra
919
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
920
explain select a from t1 order by a;
921
id select_type table type possible_keys key key_len ref rows Extra
922
1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index
923
explain select 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 order by b;
927
id select_type table type possible_keys key key_len ref rows Extra
928
1 SIMPLE t1 index NULL b 4 NULL # Using index
929
explain select a,b from t1;
930
id select_type table type possible_keys key key_len ref rows Extra
931
1 SIMPLE t1 index NULL b 4 NULL # Using index
932
explain select a,b,c from t1;
933
id select_type table type possible_keys key key_len ref rows Extra
934
1 SIMPLE t1 ALL NULL NULL NULL NULL #
936
create table t1 (t int not null default 1, key (t)) engine=innodb;
938
Field Type Null Key Default Extra
942
number bigint(20) NOT NULL default '0',
943
cname char(15) NOT NULL default '',
944
carrier_id smallint(6) NOT NULL default '0',
945
privacy tinyint(4) NOT NULL default '0',
946
last_mod_date timestamp NOT NULL,
947
last_mod_id smallint(6) NOT NULL default '0',
948
last_app_date timestamp NOT NULL,
949
last_app_id smallint(6) default '-1',
950
version smallint(6) NOT NULL default '0',
951
assigned_scps int(11) default '0',
952
status tinyint(4) default '0'
954
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
955
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
956
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
957
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
958
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
959
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
961
number bigint(20) NOT NULL default '0',
962
cname char(15) NOT NULL default '',
963
carrier_id smallint(6) NOT NULL default '0',
964
privacy tinyint(4) NOT NULL default '0',
965
last_mod_date timestamp NOT NULL,
966
last_mod_id smallint(6) NOT NULL default '0',
967
last_app_date timestamp NOT NULL,
968
last_app_id smallint(6) default '-1',
969
version smallint(6) NOT NULL default '0',
970
assigned_scps int(11) default '0',
971
status tinyint(4) default '0'
973
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
974
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
975
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
976
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
978
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
979
4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1
980
9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0
981
650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1
982
302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0
983
6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
984
333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
986
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
987
4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1
988
9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0
989
650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1
990
333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
991
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);
993
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
994
6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
995
333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 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
number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1001
333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1003
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;
1005
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1006
SELECT @@tx_isolation,@@global.tx_isolation;
1007
@@tx_isolation @@global.tx_isolation
1008
SERIALIZABLE REPEATABLE-READ
1009
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
1010
select id, code, name from t1 order by id;
1017
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1018
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
1019
select id, code, name from t1 order by id;
1027
SET binlog_format='MIXED';
1029
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1030
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
1031
select id, code, name from t1 order by id;
1042
create table t1 (n int(10), d int(10)) engine=innodb;
1043
create table t2 (n int(10), d int(10)) engine=innodb;
1044
insert into t1 values(1,1),(1,2);
1045
insert into t2 values(1,10),(2,20);
1046
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
1056
drop table if exists t1, t2;
1057
CREATE TABLE t1 (a int, PRIMARY KEY (a));
1058
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1059
create trigger trg_del_t2 after delete on t2 for each row
1060
insert into t1 values (1);
1061
insert into t1 values (1);
1062
insert into t2 values (1),(2);
1064
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1065
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1069
drop table if exists t1, t2;
1070
CREATE TABLE t1 (a int, PRIMARY KEY (a));
1071
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1072
create trigger trg_del_t2 after delete on t2 for each row
1073
insert into t1 values (1);
1074
insert into t1 values (1);
1075
insert into t2 values (1),(2);
1077
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1078
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1082
create table t1 (a int, b int) engine=innodb;
1083
insert into t1 values(20,null);
1084
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1086
b ifnull(t2.b,"this is null")
1088
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1089
t2.b=t3.a order by 1;
1090
b ifnull(t2.b,"this is null")
1092
insert into t1 values(10,null);
1093
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1094
t2.b=t3.a order by 1;
1095
b ifnull(t2.b,"this is null")
1099
create table t1 (a varchar(10) not null) engine=myisam;
1100
create table t2 (b varchar(10) not null unique) engine=innodb;
1101
select t1.a from t1,t2 where t1.a=t2.b;
1104
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
1105
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
1106
insert into t1 values (10, 20);
1107
insert into t2 values (10, 20);
1108
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
1110
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1111
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;
1112
insert into t1 set id=1;
1113
insert into t2 set id=1, t1_id=1;
1114
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
1120
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1121
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
1122
INSERT INTO t1 VALUES(1);
1123
INSERT INTO t2 VALUES(1, 1);
1127
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
1131
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
1137
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1138
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1139
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
1140
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
1142
INSERT INTO t1 VALUES("this-key", "will disappear");
1143
INSERT INTO t2 VALUES("this-key", "will also disappear");
1144
DELETE FROM t3 WHERE id1="my-test-1";
1147
this-key will disappear
1150
this-key will also disappear
1161
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
1166
DROP TABLE t1,t2,t3;
1167
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
1168
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1169
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1182
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
1183
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
1184
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);
1185
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1186
update t1,t2 set t1.a=t1.a+100;
1201
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
1216
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
1231
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;
1258
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
1259
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1261
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
1262
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
1265
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1269
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
1270
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
1271
select distinct parent,child from t1 order by parent;
1278
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1279
create table t2 (a int not null auto_increment primary key, b int);
1280
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1281
insert into t2 (a) select b from t1;
1282
insert into t1 (b) select b from t2;
1283
insert into t2 (a) select b from t1;
1284
insert into t1 (a) select b from t2;
1285
insert into t2 (a) select b from t1;
1286
insert into t1 (a) select b from t2;
1287
insert into t2 (a) select b from t1;
1288
insert into t1 (a) select b from t2;
1289
insert into t2 (a) select b from t1;
1290
insert into t1 (a) select b from t2;
1291
select count(*) from t1;
1294
explain select * from t1 where c between 1 and 2500;
1295
id select_type table type possible_keys key key_len ref rows Extra
1296
1 SIMPLE t1 range c c 5 NULL # Using where
1298
explain select * from t1 where c between 1 and 2500;
1299
id select_type table type possible_keys key key_len ref rows Extra
1300
1 SIMPLE t1 ALL c NULL NULL NULL # Using where
1302
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1303
insert into t1 (id) values (null),(null),(null),(null),(null);
1304
update t1 set fk=69 where fk is null order by id limit 1;
1313
create table t1 (a int not null, b int not null, key (a));
1314
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);
1316
update t1 set b=(@tmp:=@tmp+1) order by a;
1317
update t1 set b=99 where a=1 order by b asc limit 1;
1318
update t1 set b=100 where a=1 order by b desc limit 2;
1319
update t1 set a=a+10+b where a=1 order by b;
1320
select * from t1 order by a,b;
1335
create table t1 ( c char(8) not null ) engine=innodb;
1336
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
1337
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
1338
alter table t1 add b char(8) not null;
1339
alter table t1 add a char(8) not null;
1340
alter table t1 add primary key (a,b,c);
1341
update t1 set a=c, b=c;
1342
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;
1343
insert into t2 select * from t1;
1344
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1347
create table t1 (a integer auto_increment primary key) engine=innodb;
1348
insert into t1 (a) values (NULL),(NULL);
1350
insert into t1 (a) values (NULL),(NULL);
1356
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1357
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;
1359
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1360
insert into `t1`values ( 1 ) ;
1361
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;
1362
insert into `t2`values ( 1 ) ;
1363
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;
1364
insert into `t3`values ( 1 ) ;
1365
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1366
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`))
1367
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;
1368
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`))
1369
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1370
ERROR 42S22: Unknown column 't1.id' in 'where clause'
1371
drop table t3,t2,t1;
1376
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1377
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1378
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1379
delete from t1 where id=0;
1380
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)
1381
delete from t1 where id=15;
1382
delete from t1 where id=0;
1384
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1385
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1386
(stamp))ENGINE=InnoDB;
1387
insert into t1 values (1),(2),(3);
1388
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1390
Warning 1265 Data truncated for column 'stamp' at row 3
1391
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1392
'20020204120000' GROUP BY col1;
1400
`id` int(10) unsigned NOT NULL auto_increment,
1401
`id_object` int(10) unsigned default '0',
1402
`id_version` int(10) unsigned NOT NULL default '1',
1403
`label` varchar(100) NOT NULL default '',
1406
KEY `id_object` (`id_object`),
1407
KEY `id_version` (`id_version`)
1409
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);
1411
`id` int(10) unsigned NOT NULL auto_increment,
1412
`id_version` int(10) unsigned NOT NULL default '1',
1414
KEY `id_version` (`id_version`)
1416
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1417
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1418
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1419
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1426
3525 Fournisseur Test
1428
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1429
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1430
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1431
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1432
insert t2 select * from t1;
1433
insert t3 select * from t1;
1434
checksum table t1, t2, t3, t4 quick;
1441
Error 1146 Table 'test.t4' doesn't exist
1442
checksum table t1, t2, t3, t4;
1449
Error 1146 Table 'test.t4' doesn't exist
1450
checksum table t1, t2, t3, t4 extended;
1457
Error 1146 Table 'test.t4' doesn't exist
1458
drop table t1,t2,t3;
1459
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1460
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1461
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1473
create table t1 (a int) engine=innodb;
1474
create table t2 like t1;
1476
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1477
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1478
show create table t1;
1480
t1 CREATE TABLE `t1` (
1481
`id` int(11) NOT NULL,
1482
`id2` int(11) NOT NULL,
1483
UNIQUE KEY `id` (`id`,`id2`)
1484
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1485
show create table t2;
1487
t2 CREATE TABLE `t2` (
1488
`id` int(11) NOT NULL,
1489
KEY `t1_id_fk` (`id`),
1490
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1491
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1492
create index id on t2 (id);
1493
show create table t2;
1495
t2 CREATE TABLE `t2` (
1496
`id` int(11) NOT NULL,
1498
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1499
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1500
create index id2 on t2 (id);
1501
show create table t2;
1503
t2 CREATE TABLE `t2` (
1504
`id` int(11) NOT NULL,
1507
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1508
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1509
drop index id2 on t2;
1510
drop index id on t2;
1511
ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint
1512
show create table t2;
1514
t2 CREATE TABLE `t2` (
1515
`id` int(11) NOT NULL,
1517
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1518
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1520
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;
1521
show create table t2;
1523
t2 CREATE TABLE `t2` (
1524
`id` int(11) NOT NULL,
1525
`id2` int(11) NOT NULL,
1526
KEY `t1_id_fk` (`id`,`id2`),
1527
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1528
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1529
create unique index id on t2 (id,id2);
1530
show create table t2;
1532
t2 CREATE TABLE `t2` (
1533
`id` int(11) NOT NULL,
1534
`id2` int(11) NOT NULL,
1535
UNIQUE KEY `id` (`id`,`id2`),
1536
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1537
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1539
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;
1540
show create table t2;
1542
t2 CREATE TABLE `t2` (
1543
`id` int(11) NOT NULL,
1544
`id2` int(11) NOT NULL,
1545
UNIQUE KEY `id` (`id`,`id2`),
1546
KEY `t1_id_fk` (`id2`,`id`),
1547
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1548
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1550
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;
1551
show create table t2;
1553
t2 CREATE TABLE `t2` (
1554
`id` int(11) NOT NULL,
1555
`id2` int(11) NOT NULL,
1556
UNIQUE KEY `id` (`id`,`id2`),
1557
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1558
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1560
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;
1561
show create table t2;
1563
t2 CREATE TABLE `t2` (
1564
`id` int(11) NOT NULL,
1565
`id2` int(11) NOT NULL,
1566
UNIQUE KEY `id` (`id`,`id2`),
1567
KEY `t1_id_fk` (`id2`,`id`),
1568
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1569
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1571
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;
1572
show create table t2;
1574
t2 CREATE TABLE `t2` (
1575
`id` int(11) NOT NULL AUTO_INCREMENT,
1576
`id2` int(11) NOT NULL,
1578
KEY `id` (`id`,`id2`),
1579
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1580
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1582
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;
1583
show create table t2;
1585
t2 CREATE TABLE `t2` (
1586
`id` int(11) NOT NULL AUTO_INCREMENT,
1587
`id2` int(11) NOT NULL,
1588
KEY `t1_id_fk` (`id`),
1589
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1590
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1591
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1592
show create table t2;
1594
t2 CREATE TABLE `t2` (
1595
`id` int(11) NOT NULL AUTO_INCREMENT,
1596
`id2` int(11) NOT NULL,
1597
KEY `id_test` (`id`),
1598
KEY `id_test2` (`id`,`id2`),
1599
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1600
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1602
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;
1603
ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1604
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1605
show create table t2;
1607
t2 CREATE TABLE `t2` (
1608
`a` int(11) NOT NULL AUTO_INCREMENT,
1609
`b` int(11) DEFAULT NULL,
1611
UNIQUE KEY `b_2` (`b`),
1613
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1614
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1616
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;
1617
show create table t2;
1619
t2 CREATE TABLE `t2` (
1620
`a` int(11) NOT NULL AUTO_INCREMENT,
1621
`b` int(11) DEFAULT NULL,
1623
UNIQUE KEY `b` (`b`),
1624
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1625
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1626
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1628
create table t1 (c char(10), index (c,c)) engine=innodb;
1629
ERROR 42S21: Duplicate column name 'c'
1630
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1631
ERROR 42S21: Duplicate column name 'c1'
1632
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1633
ERROR 42S21: Duplicate column name 'c1'
1634
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1635
ERROR 42S21: Duplicate column name 'c1'
1636
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1637
alter table t1 add key (c1,c1);
1638
ERROR 42S21: Duplicate column name 'c1'
1639
alter table t1 add key (c2,c1,c1);
1640
ERROR 42S21: Duplicate column name 'c1'
1641
alter table t1 add key (c1,c2,c1);
1642
ERROR 42S21: Duplicate column name 'c1'
1643
alter table t1 add key (c1,c1,c2);
1644
ERROR 42S21: Duplicate column name 'c1'
1646
create table t1(a int(1) , b int(1)) engine=innodb;
1647
insert into t1 values ('1111', '3333');
1648
select distinct concat(a, b) from t1;
1652
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1653
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1654
ERROR HY000: The used table type doesn't support FULLTEXT indexes
1656
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1657
INSERT INTO t1 VALUES (1),(2),(3);
1658
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),
1659
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1660
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1661
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;
1668
create temporary table t1 (a int) engine=innodb;
1669
insert into t1 values (4711);
1671
insert into t1 values (42);
1676
create table t1 (a int) engine=innodb;
1677
insert into t1 values (4711);
1679
insert into t1 values (42);
1684
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;
1685
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1686
select * from t1 order by a,b,c,d;
1691
explain select * from t1 order by a,b,c,d;
1692
id select_type table type possible_keys key key_len ref rows Extra
1693
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1695
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1696
insert into t1 values ('8', '6'), ('4', '7');
1697
select min(a) from t1;
1700
select min(b) from t1 where a='8';
1704
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1705
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1708
18446744073709551600
1709
18446744073709551601
1710
select count(*) from t1 where x>0;
1713
select count(*) from t1 where x=0;
1716
select count(*) from t1 where x<0;
1719
select count(*) from t1 where x < -16;
1722
select count(*) from t1 where x = -16;
1725
explain select count(*) from t1 where x > -16;
1726
id select_type table type possible_keys key key_len ref rows Extra
1727
1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
1728
select count(*) from t1 where x > -16;
1731
select * from t1 where x > -16;
1733
18446744073709551600
1734
18446744073709551601
1735
select count(*) from t1 where x = 18446744073709551601;
1739
show status like "Innodb_buffer_pool_pages_total";
1741
Innodb_buffer_pool_pages_total 511
1742
show status like "Innodb_page_size";
1744
Innodb_page_size 16384
1745
show status like "Innodb_rows_deleted";
1747
Innodb_rows_deleted 71
1748
show status like "Innodb_rows_inserted";
1750
Innodb_rows_inserted 1084
1751
show status like "Innodb_rows_updated";
1753
Innodb_rows_updated 885
1754
show status like "Innodb_row_lock_waits";
1756
Innodb_row_lock_waits 0
1757
show status like "Innodb_row_lock_current_waits";
1759
Innodb_row_lock_current_waits 0
1760
show status like "Innodb_row_lock_time";
1762
Innodb_row_lock_time 0
1763
show status like "Innodb_row_lock_time_max";
1765
Innodb_row_lock_time_max 0
1766
show status like "Innodb_row_lock_time_avg";
1768
Innodb_row_lock_time_avg 0
1769
show variables like "innodb_sync_spin_loops";
1771
innodb_sync_spin_loops 20
1772
set global innodb_sync_spin_loops=1000;
1773
show variables like "innodb_sync_spin_loops";
1775
innodb_sync_spin_loops 1000
1776
set global innodb_sync_spin_loops=0;
1777
show variables like "innodb_sync_spin_loops";
1779
innodb_sync_spin_loops 0
1780
set global innodb_sync_spin_loops=20;
1781
show variables like "innodb_sync_spin_loops";
1783
innodb_sync_spin_loops 20
1784
show variables like "innodb_thread_concurrency";
1786
innodb_thread_concurrency 8
1787
set global innodb_thread_concurrency=1001;
1789
Warning 1292 Truncated incorrect thread_concurrency value: '1001'
1790
show variables like "innodb_thread_concurrency";
1792
innodb_thread_concurrency 1000
1793
set global innodb_thread_concurrency=0;
1794
show variables like "innodb_thread_concurrency";
1796
innodb_thread_concurrency 0
1797
set global innodb_thread_concurrency=16;
1798
show variables like "innodb_thread_concurrency";
1800
innodb_thread_concurrency 16
1801
show variables like "innodb_concurrency_tickets";
1803
innodb_concurrency_tickets 500
1804
set global innodb_concurrency_tickets=1000;
1805
show variables like "innodb_concurrency_tickets";
1807
innodb_concurrency_tickets 1000
1808
set global innodb_concurrency_tickets=0;
1810
Warning 1292 Truncated incorrect concurrency_tickets value: '0'
1811
show variables like "innodb_concurrency_tickets";
1813
innodb_concurrency_tickets 1
1814
set global innodb_concurrency_tickets=500;
1815
show variables like "innodb_concurrency_tickets";
1817
innodb_concurrency_tickets 500
1818
show variables like "innodb_thread_sleep_delay";
1820
innodb_thread_sleep_delay 10000
1821
set global innodb_thread_sleep_delay=100000;
1822
show variables like "innodb_thread_sleep_delay";
1824
innodb_thread_sleep_delay 100000
1825
set global innodb_thread_sleep_delay=0;
1826
show variables like "innodb_thread_sleep_delay";
1828
innodb_thread_sleep_delay 0
1829
set global innodb_thread_sleep_delay=10000;
1830
show variables like "innodb_thread_sleep_delay";
1832
innodb_thread_sleep_delay 10000
1833
set storage_engine=INNODB;
1834
drop table if exists t1,t2,t3;
1835
--- Testing varchar ---
1836
--- Testing varchar ---
1837
create table t1 (v varchar(10), c char(10), t text);
1838
insert into t1 values('+ ', '+ ', '+ ');
1839
set @a=repeat(' ',20);
1840
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1842
Note 1265 Data truncated for column 'v' at row 1
1843
select concat('*',v,'*',c,'*',t,'*') from t1;
1844
concat('*',v,'*',c,'*',t,'*')
1847
show create table t1;
1849
t1 CREATE TABLE `t1` (
1850
`v` varchar(10) DEFAULT NULL,
1851
`c` char(10) DEFAULT NULL,
1853
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1854
create table t2 like t1;
1855
show create table t2;
1857
t2 CREATE TABLE `t2` (
1858
`v` varchar(10) DEFAULT NULL,
1859
`c` char(10) DEFAULT NULL,
1861
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1862
create table t3 select * from t1;
1863
show create table t3;
1865
t3 CREATE TABLE `t3` (
1866
`v` varchar(10) DEFAULT NULL,
1867
`c` char(10) DEFAULT NULL,
1869
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1870
alter table t1 modify c varchar(10);
1871
show create table t1;
1873
t1 CREATE TABLE `t1` (
1874
`v` varchar(10) DEFAULT NULL,
1875
`c` varchar(10) DEFAULT NULL,
1877
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1878
alter table t1 modify v char(10);
1879
show create table t1;
1881
t1 CREATE TABLE `t1` (
1882
`v` char(10) DEFAULT NULL,
1883
`c` varchar(10) DEFAULT NULL,
1885
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1886
alter table t1 modify t varchar(10);
1888
Note 1265 Data truncated for column 't' at row 2
1889
show create table t1;
1891
t1 CREATE TABLE `t1` (
1892
`v` char(10) DEFAULT NULL,
1893
`c` varchar(10) DEFAULT NULL,
1894
`t` varchar(10) DEFAULT NULL
1895
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1896
select concat('*',v,'*',c,'*',t,'*') from t1;
1897
concat('*',v,'*',c,'*',t,'*')
1900
drop table t1,t2,t3;
1901
create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1902
show create table t1;
1904
t1 CREATE TABLE `t1` (
1905
`v` varchar(10) DEFAULT NULL,
1906
`c` char(10) DEFAULT NULL,
1911
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1912
select count(*) from t1;
1915
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1916
select count(*) from t1 where v='a';
1919
select count(*) from t1 where c='a';
1922
select count(*) from t1 where t='a';
1925
select count(*) from t1 where v='a ';
1928
select count(*) from t1 where c='a ';
1931
select count(*) from t1 where t='a ';
1934
select count(*) from t1 where v between 'a' and 'a ';
1937
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1940
select count(*) from t1 where v like 'a%';
1943
select count(*) from t1 where c like 'a%';
1946
select count(*) from t1 where t like 'a%';
1949
select count(*) from t1 where v like 'a %';
1952
explain select count(*) from t1 where v='a ';
1953
id select_type table type possible_keys key key_len ref rows Extra
1954
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1955
explain select count(*) from t1 where c='a ';
1956
id select_type table type possible_keys key key_len ref rows Extra
1957
1 SIMPLE t1 ref c c 11 const # Using where; Using index
1958
explain select count(*) from t1 where t='a ';
1959
id select_type table type possible_keys key key_len ref rows Extra
1960
1 SIMPLE t1 ref t t 13 const # Using where
1961
explain select count(*) from t1 where v like 'a%';
1962
id select_type table type possible_keys key key_len ref rows Extra
1963
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
1964
explain select count(*) from t1 where v between 'a' and 'a ';
1965
id select_type table type possible_keys key key_len ref rows Extra
1966
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1967
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1968
id select_type table type possible_keys key key_len ref rows Extra
1969
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1970
alter table t1 add unique(v);
1971
ERROR 23000: Duplicate entry 'v' for key 'v_2'
1972
alter table t1 add key(v);
1973
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1985
explain select * from t1 where v='a';
1986
id select_type table type possible_keys key key_len ref rows Extra
1987
1 SIMPLE t1 ref v,v_2 # 13 const # Using where
1988
select v,count(*) from t1 group by v limit 10;
2000
select v,count(t) from t1 group by v limit 10;
2012
select v,count(c) from t1 group by v limit 10;
2024
select sql_big_result v,count(t) from t1 group by v limit 10;
2036
select sql_big_result v,count(c) from t1 group by v limit 10;
2048
select c,count(*) from t1 group by c limit 10;
2060
select c,count(t) from t1 group by c limit 10;
2072
select sql_big_result c,count(t) from t1 group by c limit 10;
2084
select t,count(*) from t1 group by t limit 10;
2096
select t,count(t) from t1 group by t limit 10;
2108
select sql_big_result t,count(t) from t1 group by t limit 10;
2120
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
2121
show create table t1;
2123
t1 CREATE TABLE `t1` (
2124
`v` varchar(300) DEFAULT NULL,
2125
`c` char(10) DEFAULT NULL,
2130
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2131
select count(*) from t1 where v='a';
2134
select count(*) from t1 where v='a ';
2137
select count(*) from t1 where v between 'a' and 'a ';
2140
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2143
select count(*) from t1 where v like 'a%';
2146
select count(*) from t1 where v like 'a %';
2149
explain select count(*) from t1 where v='a ';
2150
id select_type table type possible_keys key key_len ref rows Extra
2151
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2152
explain select count(*) from t1 where v like 'a%';
2153
id select_type table type possible_keys key key_len ref rows Extra
2154
1 SIMPLE t1 range v v 303 NULL # Using where; Using index
2155
explain select count(*) from t1 where v between 'a' and 'a ';
2156
id select_type table type possible_keys key key_len ref rows Extra
2157
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2158
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2159
id select_type table type possible_keys key key_len ref rows Extra
2160
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2161
explain select * from t1 where v='a';
2162
id select_type table type possible_keys key key_len ref rows Extra
2163
1 SIMPLE t1 ref v v 303 const # Using where
2164
select v,count(*) from t1 group by v limit 10;
2176
select v,count(t) from t1 group by v limit 10;
2188
select sql_big_result v,count(t) from t1 group by v limit 10;
2200
alter table t1 drop key v, add key v (v(30));
2201
show create table t1;
2203
t1 CREATE TABLE `t1` (
2204
`v` varchar(300) DEFAULT NULL,
2205
`c` char(10) DEFAULT NULL,
2210
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2211
select count(*) from t1 where v='a';
2214
select count(*) from t1 where v='a ';
2217
select count(*) from t1 where v between 'a' and 'a ';
2220
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2223
select count(*) from t1 where v like 'a%';
2226
select count(*) from t1 where v like 'a %';
2229
explain select count(*) from t1 where v='a ';
2230
id select_type table type possible_keys key key_len ref rows Extra
2231
1 SIMPLE t1 ref v v 33 const # Using where
2232
explain select count(*) from t1 where v like 'a%';
2233
id select_type table type possible_keys key key_len ref rows Extra
2234
1 SIMPLE t1 range v v 33 NULL # Using where
2235
explain select count(*) from t1 where v between 'a' and 'a ';
2236
id select_type table type possible_keys key key_len ref rows Extra
2237
1 SIMPLE t1 ref v v 33 const # Using where
2238
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2239
id select_type table type possible_keys key key_len ref rows Extra
2240
1 SIMPLE t1 ref v v 33 const # Using where
2241
explain select * from t1 where v='a';
2242
id select_type table type possible_keys key key_len ref rows Extra
2243
1 SIMPLE t1 ref v v 33 const # Using where
2244
select v,count(*) from t1 group by v limit 10;
2256
select v,count(t) from t1 group by v limit 10;
2268
select sql_big_result v,count(t) from t1 group by v limit 10;
2280
alter table t1 modify v varchar(600), drop key v, add key v (v);
2281
show create table t1;
2283
t1 CREATE TABLE `t1` (
2284
`v` varchar(600) DEFAULT NULL,
2285
`c` char(10) DEFAULT NULL,
2290
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2291
select v,count(*) from t1 group by v limit 10;
2303
select v,count(t) from t1 group by v limit 10;
2315
select sql_big_result v,count(t) from t1 group by v limit 10;
2328
create table t1 (a char(10), unique (a));
2329
insert into t1 values ('a ');
2330
insert into t1 values ('a ');
2331
ERROR 23000: Duplicate entry 'a' for key 'a'
2332
alter table t1 modify a varchar(10);
2333
insert into t1 values ('a '),('a '),('a '),('a ');
2334
ERROR 23000: Duplicate entry 'a ' for key 'a'
2335
insert into t1 values ('a ');
2336
ERROR 23000: Duplicate entry 'a ' for key 'a'
2337
insert into t1 values ('a ');
2338
ERROR 23000: Duplicate entry 'a ' for key 'a'
2339
insert into t1 values ('a ');
2340
ERROR 23000: Duplicate entry 'a ' for key 'a'
2341
update t1 set a='a ' where a like 'a%';
2342
select concat(a,'.') from t1;
2345
update t1 set a='abc ' where a like 'a ';
2346
select concat(a,'.') from t1;
2349
update t1 set a='a ' where a like 'a %';
2350
select concat(a,'.') from t1;
2353
update t1 set a='a ' where a like 'a ';
2354
select concat(a,'.') from t1;
2358
create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2359
show create table t1;
2361
t1 CREATE TABLE `t1` (
2362
`v` varchar(10) DEFAULT NULL,
2363
`c` char(10) DEFAULT NULL,
2368
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2370
create table t1 (v char(10) character set utf8);
2371
show create table t1;
2373
t1 CREATE TABLE `t1` (
2374
`v` char(10) CHARACTER SET utf8 DEFAULT NULL
2375
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2377
create table t1 (v varchar(10), c char(10)) row_format=fixed;
2379
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
2380
show create table t1;
2382
t1 CREATE TABLE `t1` (
2383
`v` varchar(10) DEFAULT NULL,
2384
`c` char(10) DEFAULT NULL
2385
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
2386
insert into t1 values('a','a'),('a ','a ');
2387
select concat('*',v,'*',c,'*') from t1;
2388
concat('*',v,'*',c,'*')
2392
create table t1 (v varchar(65530), key(v(10)));
2393
insert into t1 values(repeat('a',65530));
2394
select length(v) from t1 where v=repeat('a',65530);
2398
create table t1(a int, b varchar(12), key ba(b, a));
2399
insert into t1 values (1, 'A'), (20, NULL);
2400
explain select * from t1 where a=20 and b is null;
2401
id select_type table type possible_keys key key_len ref rows Extra
2402
1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index
2403
select * from t1 where a=20 and b is null;
2407
create table t1 (v varchar(65530), key(v));
2409
Warning 1071 Specified key was too long; max key length is 767 bytes
2411
create table t1 (v varchar(65536));
2413
Note 1246 Converting column 'v' from VARCHAR to TEXT
2414
show create table t1;
2416
t1 CREATE TABLE `t1` (
2418
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2420
create table t1 (v varchar(65530) character set utf8);
2422
Note 1246 Converting column 'v' from VARCHAR to TEXT
2423
show create table t1;
2425
t1 CREATE TABLE `t1` (
2426
`v` mediumtext CHARACTER SET utf8
2427
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2429
set storage_engine=MyISAM;
2430
create table t1 (v varchar(16384)) engine=innodb;
2432
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2433
insert into t1 values ('8', '6'), ('4', '7');
2434
select min(a) from t1;
2437
select min(b) from t1 where a='8';
2441
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2442
insert into t1 (b) values (1);
2443
replace into t1 (b) values (2), (1), (3);
2450
insert into t1 (b) values (1);
2451
replace into t1 (b) values (2);
2452
replace into t1 (b) values (1);
2453
replace into t1 (b) values (3);
2460
create table t1 (rowid int not null auto_increment, val int not null,primary
2461
key (rowid), unique(val)) engine=innodb;
2462
replace into t1 (val) values ('1'),('2');
2463
replace into t1 (val) values ('1'),('2');
2464
insert into t1 (val) values ('1'),('2');
2465
ERROR 23000: Duplicate entry '1' for key 'val'
2471
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2472
insert into t1 (val) values (1);
2473
update t1 set a=2 where a=1;
2474
insert into t1 (val) values (1);
2475
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
2480
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2481
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2482
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2485
SELECT GRADE FROM t1 WHERE GRADE= 151;
2489
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
2490
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
2491
insert into t2 values ('aa','cc');
2492
insert into t1 values ('aa','bb'),('aa','cc');
2493
delete t1 from t1,t2 where f1=f3 and f4='cc';
2498
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2501
id INTEGER NOT NULL,
2502
FOREIGN KEY (id) REFERENCES t1 (id)
2504
INSERT INTO t1 (id) VALUES (NULL);
2509
INSERT INTO t1 (id) VALUES (NULL);
2515
INSERT INTO t1 (id) VALUES (NULL);
2524
CREATE TEMPORARY TABLE t2
2526
id INT NOT NULL PRIMARY KEY,
2528
FOREIGN KEY (b) REFERENCES test.t1(id)
2530
Got one of the listed errors
2532
create table t1 (col1 varchar(2000), index (col1(767)))
2533
character set = latin1 engine = innodb;
2534
create table t2 (col1 char(255), index (col1))
2535
character set = latin1 engine = innodb;
2536
create table t3 (col1 binary(255), index (col1))
2537
character set = latin1 engine = innodb;
2538
create table t4 (col1 varchar(767), index (col1))
2539
character set = latin1 engine = innodb;
2540
create table t5 (col1 varchar(767) primary key)
2541
character set = latin1 engine = innodb;
2542
create table t6 (col1 varbinary(767) primary key)
2543
character set = latin1 engine = innodb;
2544
create table t7 (col1 text, index(col1(767)))
2545
character set = latin1 engine = innodb;
2546
create table t8 (col1 blob, index(col1(767)))
2547
character set = latin1 engine = innodb;
2548
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
2549
character set = latin1 engine = innodb;
2550
show create table t9;
2552
t9 CREATE TABLE `t9` (
2553
`col1` varchar(512) DEFAULT NULL,
2554
`col2` varchar(512) DEFAULT NULL,
2555
KEY `col1` (`col1`,`col2`)
2556
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2557
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
2558
create table t1 (col1 varchar(768), index(col1))
2559
character set = latin1 engine = innodb;
2561
Warning 1071 Specified key was too long; max key length is 767 bytes
2562
create table t2 (col1 varbinary(768), index(col1))
2563
character set = latin1 engine = innodb;
2565
Warning 1071 Specified key was too long; max key length is 767 bytes
2566
create table t3 (col1 text, index(col1(768)))
2567
character set = latin1 engine = innodb;
2569
Warning 1071 Specified key was too long; max key length is 767 bytes
2570
create table t4 (col1 blob, index(col1(768)))
2571
character set = latin1 engine = innodb;
2573
Warning 1071 Specified key was too long; max key length is 767 bytes
2574
show create table t1;
2576
t1 CREATE TABLE `t1` (
2577
`col1` varchar(768) DEFAULT NULL,
2578
KEY `col1` (`col1`(767))
2579
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2580
drop table t1, t2, t3, t4;
2581
create table t1 (col1 varchar(768) primary key)
2582
character set = latin1 engine = innodb;
2583
ERROR 42000: Specified key was too long; max key length is 767 bytes
2584
create table t2 (col1 varbinary(768) primary key)
2585
character set = latin1 engine = innodb;
2586
ERROR 42000: Specified key was too long; max key length is 767 bytes
2587
create table t3 (col1 text, primary key(col1(768)))
2588
character set = latin1 engine = innodb;
2589
ERROR 42000: Specified key was too long; max key length is 767 bytes
2590
create table t4 (col1 blob, primary key(col1(768)))
2591
character set = latin1 engine = innodb;
2592
ERROR 42000: Specified key was too long; max key length is 767 bytes
2600
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2602
INSERT INTO t2 VALUES(2);
2603
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2604
INSERT INTO t1 VALUES(1);
2605
INSERT INTO t2 VALUES(1);
2606
DELETE FROM t1 WHERE id = 1;
2607
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2609
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
2610
SET FOREIGN_KEY_CHECKS=0;
2612
SET FOREIGN_KEY_CHECKS=1;
2613
INSERT INTO t2 VALUES(3);
2614
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2616
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2617
insert into t1 values (1),(2);
2622
insert into t1 values(3);
2632
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2633
insert into t1 values (1),(2);
2639
insert into t1 values(3);
2644
set foreign_key_checks=0;
2645
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2646
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2647
ERROR HY000: Can't create table 'test.t1' (errno: 150)
2648
set foreign_key_checks=1;
2650
set foreign_key_checks=0;
2651
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2652
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
2653
ERROR HY000: Can't create table 'test.t2' (errno: 150)
2654
set foreign_key_checks=1;
2656
set foreign_key_checks=0;
2657
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2658
create table t1(a varchar(10) primary key) engine = innodb;
2659
alter table t1 modify column a int;
2660
Got one of the listed errors
2661
set foreign_key_checks=1;
2663
set foreign_key_checks=0;
2664
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2665
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2666
alter table t1 convert to character set utf8;
2667
set foreign_key_checks=1;
2669
set foreign_key_checks=0;
2670
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2671
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
2672
rename table t3 to t1;
2673
ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150)
2674
set foreign_key_checks=1;
2676
create table t1(a int primary key) row_format=redundant engine=innodb;
2677
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2678
create table t3(a int primary key) row_format=compact engine=innodb;
2679
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2680
insert into t1 values(1);
2681
insert into t3 values(1);
2682
insert into t2 values(2);
2683
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`))
2684
insert into t4 values(2);
2685
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`))
2686
insert into t2 values(1);
2687
insert into t4 values(1);
2689
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`))
2691
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`))
2693
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`))
2695
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`))
2697
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`))
2699
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`))
2704
drop table t4,t3,t2,t1;
2705
create table t1 (a varchar(255) character set utf8,
2706
b varchar(255) character set utf8,
2707
c varchar(255) character set utf8,
2708
d varchar(255) character set utf8,
2709
key (a,b,c,d)) engine=innodb;
2711
create table t1 (a varchar(255) character set utf8,
2712
b varchar(255) character set utf8,
2713
c varchar(255) character set utf8,
2714
d varchar(255) character set utf8,
2715
e varchar(255) character set utf8,
2716
key (a,b,c,d,e)) engine=innodb;
2717
ERROR 42000: Specified key was too long; max key length is 3072 bytes
2718
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2719
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
2720
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
2721
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
2722
insert into t1 values (0x41),(0x4120),(0x4100);
2723
insert into t2 values (0x41),(0x4120),(0x4100);
2724
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2725
insert into t2 values (0x41),(0x4120);
2726
insert into t3 values (0x41),(0x4120),(0x4100);
2727
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2728
insert into t3 values (0x41),(0x4100);
2729
insert into t4 values (0x41),(0x4120),(0x4100);
2730
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2731
insert into t4 values (0x41),(0x4100);
2732
select hex(s1) from t1;
2737
select hex(s1) from t2;
2741
select hex(s1) from t3;
2745
select hex(s1) from t4;
2749
drop table t1,t2,t3,t4;
2750
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2751
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2752
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2753
insert into t2 values(0x42);
2754
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)
2755
insert into t2 values(0x41);
2756
select hex(s1) from t2;
2759
update t1 set s1=0x123456 where a=2;
2760
select hex(s1) from t2;
2763
update t1 set s1=0x12 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
update t1 set s1=0x12345678 where a=1;
2766
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)
2767
update t1 set s1=0x123457 where a=1;
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
update t1 set s1=0x1220 where a=1;
2770
select hex(s1) from t2;
2773
update t1 set s1=0x1200 where a=1;
2774
select hex(s1) from t2;
2777
update t1 set s1=0x4200 where a=1;
2778
select hex(s1) from t2;
2781
delete from t1 where a=1;
2782
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)
2783
delete from t1 where a=2;
2784
update t2 set s1=0x4120;
2786
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)
2787
delete from t1 where a!=3;
2788
select a,hex(s1) from t1;
2791
select hex(s1) from t2;
2795
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
2796
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2797
insert into t1 values(1,0x4100),(2,0x41);
2798
insert into t2 values(0x41);
2799
select hex(s1) from t2;
2802
update t1 set s1=0x1234 where a=1;
2803
select hex(s1) from t2;
2806
update t1 set s1=0x12 where a=2;
2807
select hex(s1) from t2;
2810
delete from t1 where a=1;
2811
delete from t1 where a=2;
2812
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)
2813
select a,hex(s1) from t1;
2816
select hex(s1) from t2;
2820
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2821
CREATE TABLE t2(a INT) ENGINE=InnoDB;
2822
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2823
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2824
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2825
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2826
SHOW CREATE TABLE t2;
2828
t2 CREATE TABLE `t2` (
2829
`a` int(11) DEFAULT NULL,
2830
KEY `t2_ibfk_0` (`a`)
2831
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2833
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2834
insert into t1(a) values (1),(2),(3);
2837
update t1 set b = 5 where a = 2;
2838
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
2840
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
2841
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
2842
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
2843
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
2844
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
2849
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2850
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2851
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2852
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2853
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2854
insert into t1(a) values (1),(2),(3);
2855
insert into t2(a) values (1),(2),(3);
2856
insert into t3(a) values (1),(2),(3);
2857
insert into t4(a) values (1),(2),(3);
2858
insert into t3(a) values (5),(7),(8);
2859
insert into t4(a) values (5),(7),(8);
2860
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
2861
create trigger t1t before insert on t1 for each row begin
2862
INSERT INTO t2 SET a = NEW.a;
2864
create trigger t2t before insert on t2 for each row begin
2865
DELETE FROM t3 WHERE a = NEW.a;
2867
create trigger t3t before delete on t3 for each row begin
2868
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
2870
create trigger t4t before update on t4 for each row begin
2871
UPDATE t5 SET b = b + 1 where a = NEW.a;
2875
update t1 set b = b + 5 where a = 1;
2876
update t2 set b = b + 5 where a = 1;
2877
update t3 set b = b + 5 where a = 1;
2878
update t4 set b = b + 5 where a = 1;
2879
insert into t5(a) values(20);
2881
insert into t1(a) values(7);
2882
insert into t2(a) values(8);
2883
delete from t2 where a = 3;
2884
update t4 set b = b + 1 where a = 3;
2890
drop table t1, t2, t3, t4, t5;
2892
field1 varchar(8) NOT NULL DEFAULT '',
2893
field2 varchar(8) NOT NULL DEFAULT '',
2894
PRIMARY KEY (field1, field2)
2897
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2898
FOREIGN KEY (field1) REFERENCES t1 (field1)
2899
ON DELETE CASCADE ON UPDATE CASCADE
2901
INSERT INTO t1 VALUES ('old', 'somevalu');
2902
INSERT INTO t1 VALUES ('other', 'anyvalue');
2903
INSERT INTO t2 VALUES ('old');
2904
INSERT INTO t2 VALUES ('other');
2905
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2906
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
2919
alter table t1 add constraint c2_fk foreign key (c2)
2920
references t2(c1) on delete cascade;
2921
show create table t1;
2923
t1 CREATE TABLE `t1` (
2924
`c1` bigint(20) NOT NULL,
2925
`c2` bigint(20) NOT NULL,
2927
UNIQUE KEY `c2` (`c2`),
2928
CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2929
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2930
alter table t1 drop foreign key c2_fk;
2931
show create table t1;
2933
t1 CREATE TABLE `t1` (
2934
`c1` bigint(20) NOT NULL,
2935
`c2` bigint(20) NOT NULL,
2937
UNIQUE KEY `c2` (`c2`)
2938
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2940
create table t1(a date) engine=innodb;
2941
create table t2(a date, key(a)) engine=innodb;
2942
insert into t1 values('2005-10-01');
2943
insert into t2 values('2005-10-01');
2944
select * from t1, t2
2945
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2947
2005-10-01 2005-10-01
2949
create table t1 (id int not null, f_id int not null, f int not null,
2950
primary key(f_id, id)) engine=innodb;
2951
create table t2 (id int not null,s_id int not null,s varchar(200),
2952
primary key(id)) engine=innodb;
2953
INSERT INTO t1 VALUES (8, 1, 3);
2954
INSERT INTO t1 VALUES (1, 2, 1);
2955
INSERT INTO t2 VALUES (1, 0, '');
2956
INSERT INTO t2 VALUES (8, 1, '');
2958
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2959
WHERE mm.id IS NULL;
2960
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2961
where mm.id is null lock in share mode;
2964
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2965
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2967
SET binlog_format='MIXED';
2969
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2970
update t1 set b = 5 where b = 1;
2971
SET binlog_format='MIXED';
2973
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2974
select * from t1 where a = 7 and b = 3 for update;
2980
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2981
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2984
select * from t1 lock in share mode;
2992
update t1 set b = 5 where b = 1;
2994
select * from t1 where a = 2 and b = 2 for update;
2995
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
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(d int not null, e int, primary key(d)) engine=innodb;
3002
insert into t2 values (8,6),(12,1),(3,1);
3005
select * from t2 for update;
3010
SET binlog_format='MIXED';
3012
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3013
insert into t1 select * from t2;
3014
update t1 set b = (select e from t2 where a = d);
3015
create table t3(d int not null, e int, primary key(d)) engine=innodb
3019
drop table t1, t2, t3;
3020
create table t1(a int not null, b int, primary key(a)) engine=innodb;
3021
insert into t1 values (1,2),(5,3),(4,2);
3022
create table t2(a int not null, b int, primary key(a)) engine=innodb;
3023
insert into t2 values (8,6),(12,1),(3,1);
3024
create table t3(d int not null, b int, primary key(d)) engine=innodb;
3025
insert into t3 values (8,6),(12,1),(3,1);
3026
create table t5(a int not null, b int, primary key(a)) engine=innodb;
3027
insert into t5 values (1,2),(5,3),(4,2);
3028
create table t6(d int not null, e int, primary key(d)) engine=innodb;
3029
insert into t6 values (8,6),(12,1),(3,1);
3030
create table t8(a int not null, b int, primary key(a)) engine=innodb;
3031
insert into t8 values (1,2),(5,3),(4,2);
3032
create table t9(d int not null, e int, primary key(d)) engine=innodb;
3033
insert into t9 values (8,6),(12,1),(3,1);
3036
select * from t2 for update;
3041
SET binlog_format='MIXED';
3043
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3044
insert into t1 select * from t2;
3045
SET binlog_format='MIXED';
3047
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3048
update t3 set b = (select b from t2 where a = d);
3049
SET binlog_format='MIXED';
3051
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3052
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
3053
SET binlog_format='MIXED';
3055
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3056
insert into t5 (select * from t2 lock in share mode);
3057
SET binlog_format='MIXED';
3059
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3060
update t6 set e = (select b from t2 where a = d lock in share mode);
3061
SET binlog_format='MIXED';
3063
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3064
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
3065
SET binlog_format='MIXED';
3067
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3068
insert into t8 (select * from t2 for update);
3069
SET binlog_format='MIXED';
3071
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3072
update t9 set e = (select b from t2 where a = d for update);
3073
SET binlog_format='MIXED';
3075
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3076
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
3077
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3078
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3079
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3080
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3081
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3082
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3083
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3084
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3085
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3087
drop table t1, t2, t3, t5, t6, t8, t9;
3088
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
3089
ERROR HY000: Can't create table 'test.t1' (errno: -1)
3091
a BIGINT(20) NOT NULL,
3093
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3095
a BIGINT(20) NOT NULL,
3096
b VARCHAR(128) NOT NULL,
3099
KEY idx_t2_b_c (b,c(200)),
3100
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
3102
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3103
INSERT INTO t1 VALUES (1);
3104
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
3105
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
3106
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
3107
INSERT INTO t2 VALUES (1, 'customer_over', '1');
3108
SELECT * FROM t2 WHERE b = 'customer_over';
3111
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
3114
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
3117
/* Bang: Empty result set, above was expected: */
3118
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3121
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3125
CREATE TABLE t1 ( a int ) ENGINE=innodb;
3127
INSERT INTO t1 VALUES (1);
3129
Table Op Msg_type Msg_text
3130
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3131
test.t1 optimize status OK
3133
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
3134
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
3135
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
3136
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
3137
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
3138
DELETE CASCADE ON UPDATE CASCADE;
3139
SHOW CREATE TABLE t2;
3141
t2 CREATE TABLE `t2` (
3142
`id` int(11) NOT NULL,
3143
`f` int(11) NOT NULL,
3146
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
3147
CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
3148
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3150
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
3151
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
3152
INSERT INTO t1 VALUES (1);
3153
INSERT INTO t2 VALUES (1);
3154
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
3155
ALTER TABLE t2 MODIFY a INT NOT NULL;
3156
ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
3159
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
3161
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
3163
INSERT INTO t1 VALUES ('DDD');
3168
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
3170
INSERT INTO t1 VALUES (0),(347),(0);
3176
SHOW CREATE TABLE t1;
3178
t1 CREATE TABLE `t1` (
3179
`id` int(11) NOT NULL AUTO_INCREMENT,
3181
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3182
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
3183
INSERT INTO t2 VALUES(42),(347),(348);
3184
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
3185
SHOW CREATE TABLE t1;
3187
t1 CREATE TABLE `t1` (
3188
`id` int(11) NOT NULL AUTO_INCREMENT,
3190
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
3191
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3193
set innodb_strict_mode=on;
3195
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
3196
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
3197
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
3198
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
3199
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
3200
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
3201
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
3202
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
3204
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
3205
DROP TABLE IF EXISTS t1;
3207
Note 1051 Unknown table 't1'
3209
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
3211
INSERT INTO t1 VALUES(-10);
3215
INSERT INTO t1 VALUES(NULL);
3221
SET binlog_format='MIXED';
3222
SET TX_ISOLATION='read-committed';
3224
DROP TABLE IF EXISTS t1, t2;
3226
Note 1051 Unknown table 't1'
3227
Note 1051 Unknown table 't2'
3228
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
3229
CREATE TABLE t2 LIKE t1;
3232
SET binlog_format='MIXED';
3233
SET TX_ISOLATION='read-committed';
3235
INSERT INTO t1 VALUES (1);
3237
SELECT * FROM t1 WHERE a=1;
3240
SET binlog_format='MIXED';
3241
SET TX_ISOLATION='read-committed';
3245
SET binlog_format='MIXED';
3246
SET TX_ISOLATION='read-committed';
3248
INSERT INTO t1 VALUES (2);
3250
SELECT * FROM t1 WHERE a=2;
3253
SELECT * FROM t1 WHERE a=2;
3258
create table t1 (i int, j int) engine=innodb;
3259
insert into t1 (i, j) values (1, 1), (2, 2);
3260
update t1 set j = 2;
3262
info: Rows matched: 2 Changed: 1 Warnings: 0
3264
create table t1 (id int) comment='this is a comment' engine=innodb;
3265
select table_comment, data_free > 0 as data_free_is_set
3266
from information_schema.tables
3267
where table_schema='test' and table_name = 't1';
3268
table_comment data_free_is_set
3272
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3273
c2 VARCHAR(128) NOT NULL,
3275
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
3277
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3278
c2 INT(10) UNSIGNED DEFAULT NULL,
3280
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
3281
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3284
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
3285
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3290
CREATE TABLE t1 (c1 int default NULL,
3292
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
3295
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
3297
info: Records: 5 Duplicates: 0 Warnings: 0
added
removed
storage/innobase/mysql-test/innodb.result
