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
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
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
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
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
drop table if exists t1, t2;
1066
CREATE TABLE t1 (a int, PRIMARY KEY (a));
1067
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1068
create trigger trg_del_t2 after delete on t2 for each row
1069
insert into t1 values (1);
1070
insert into t1 values (1);
1071
insert into t2 values (1),(2);
1073
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1074
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1078
create table t1 (a int, b int) engine=innodb;
1079
insert into t1 values(20,null);
1080
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1082
b ifnull(t2.b,"this is null")
1084
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1085
t2.b=t3.a order by 1;
1086
b ifnull(t2.b,"this is null")
1088
insert into t1 values(10,null);
1089
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1090
t2.b=t3.a order by 1;
1091
b ifnull(t2.b,"this is null")
1095
create table t1 (a varchar(10) not null) engine=myisam;
1096
create table t2 (b varchar(10) not null unique) engine=innodb;
1097
select t1.a from t1,t2 where t1.a=t2.b;
1100
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
1101
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
1102
insert into t1 values (10, 20);
1103
insert into t2 values (10, 20);
1104
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
1106
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1107
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;
1108
insert into t1 set id=1;
1109
insert into t2 set id=1, t1_id=1;
1110
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
1116
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1117
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
1118
INSERT INTO t1 VALUES(1);
1119
INSERT INTO t2 VALUES(1, 1);
1123
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
1127
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
1133
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1134
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1135
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
1136
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
1138
INSERT INTO t1 VALUES("this-key", "will disappear");
1139
INSERT INTO t2 VALUES("this-key", "will also disappear");
1140
DELETE FROM t3 WHERE id1="my-test-1";
1143
this-key will disappear
1146
this-key will also disappear
1157
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
1162
DROP TABLE t1,t2,t3;
1163
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
1164
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1165
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1178
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
1179
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
1180
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);
1181
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1182
update t1,t2 set t1.a=t1.a+100;
1197
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
1212
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
1227
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;
1254
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
1255
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1257
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
1258
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
1261
Warning 1196 Some non-transactional changed tables couldn't be rolled back
1265
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
1266
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
1267
select distinct parent,child from t1 order by parent;
1274
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1275
create table t2 (a int not null auto_increment primary key, b int);
1276
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1277
insert into t2 (a) select b from t1;
1278
insert into t1 (b) select b from t2;
1279
insert into t2 (a) select b from t1;
1280
insert into t1 (a) select b from t2;
1281
insert into t2 (a) select b from t1;
1282
insert into t1 (a) 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
select count(*) from t1;
1290
explain select * from t1 where c between 1 and 2500;
1291
id select_type table type possible_keys key key_len ref rows Extra
1292
1 SIMPLE t1 range c c 5 NULL # Using where
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 ALL c NULL NULL NULL # Using where
1298
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1299
insert into t1 (id) values (null),(null),(null),(null),(null);
1300
update t1 set fk=69 where fk is null order by id limit 1;
1309
create table t1 (a int not null, b int not null, key (a));
1310
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);
1312
update t1 set b=(@tmp:=@tmp+1) order by a;
1313
update t1 set b=99 where a=1 order by b asc limit 1;
1314
update t1 set b=100 where a=1 order by b desc limit 2;
1315
update t1 set a=a+10+b where a=1 order by b;
1316
select * from t1 order by a,b;
1331
create table t1 ( c char(8) not null ) engine=innodb;
1332
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
1333
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
1334
alter table t1 add b char(8) not null;
1335
alter table t1 add a char(8) not null;
1336
alter table t1 add primary key (a,b,c);
1337
update t1 set a=c, b=c;
1338
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;
1339
insert into t2 select * from t1;
1340
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1343
create table t1 (a integer auto_increment primary key) engine=innodb;
1344
insert into t1 (a) values (NULL),(NULL);
1346
insert into t1 (a) values (NULL),(NULL);
1352
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1353
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;
1355
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1356
insert into `t1`values ( 1 ) ;
1357
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;
1358
insert into `t2`values ( 1 ) ;
1359
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;
1360
insert into `t3`values ( 1 ) ;
1361
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1362
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`))
1363
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;
1364
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`))
1365
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1366
ERROR 42S22: Unknown column 't1.id' in 'where clause'
1367
drop table t3,t2,t1;
1372
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1373
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1374
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1375
delete from t1 where id=0;
1376
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)
1377
delete from t1 where id=15;
1378
delete from t1 where id=0;
1380
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1381
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1382
(stamp))ENGINE=InnoDB;
1383
insert into t1 values (1),(2),(3);
1384
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1386
Warning 1265 Data truncated for column 'stamp' at row 3
1387
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1388
'20020204120000' GROUP BY col1;
1396
`id` int(10) unsigned NOT NULL auto_increment,
1397
`id_object` int(10) unsigned default '0',
1398
`id_version` int(10) unsigned NOT NULL default '1',
1399
`label` varchar(100) NOT NULL default '',
1402
KEY `id_object` (`id_object`),
1403
KEY `id_version` (`id_version`)
1405
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);
1407
`id` int(10) unsigned NOT NULL auto_increment,
1408
`id_version` int(10) unsigned NOT NULL default '1',
1410
KEY `id_version` (`id_version`)
1412
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1413
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1414
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1415
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1422
3525 Fournisseur Test
1424
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1425
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1426
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1427
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1428
insert t2 select * from t1;
1429
insert t3 select * from t1;
1430
checksum table t1, t2, t3, t4 quick;
1437
Error 1146 Table 'test.t4' doesn't exist
1438
checksum table t1, t2, t3, t4;
1445
Error 1146 Table 'test.t4' doesn't exist
1446
checksum table t1, t2, t3, t4 extended;
1453
Error 1146 Table 'test.t4' doesn't exist
1454
drop table t1,t2,t3;
1455
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1456
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1457
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1469
create table t1 (a int) engine=innodb;
1470
create table t2 like t1;
1472
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1473
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1474
show create table t1;
1476
t1 CREATE TABLE `t1` (
1477
`id` int(11) NOT NULL,
1478
`id2` int(11) NOT NULL,
1479
UNIQUE KEY `id` (`id`,`id2`)
1480
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1481
show create table t2;
1483
t2 CREATE TABLE `t2` (
1484
`id` int(11) NOT NULL,
1485
KEY `t1_id_fk` (`id`),
1486
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1487
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1488
create index id on t2 (id);
1489
show create table t2;
1491
t2 CREATE TABLE `t2` (
1492
`id` int(11) NOT NULL,
1494
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1495
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1496
create index id2 on t2 (id);
1497
show create table t2;
1499
t2 CREATE TABLE `t2` (
1500
`id` int(11) NOT NULL,
1503
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1504
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1505
drop index id2 on t2;
1506
drop index id on t2;
1507
ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint
1508
show create table t2;
1510
t2 CREATE TABLE `t2` (
1511
`id` int(11) NOT NULL,
1513
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1514
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1516
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;
1517
show create table t2;
1519
t2 CREATE TABLE `t2` (
1520
`id` int(11) NOT NULL,
1521
`id2` int(11) NOT NULL,
1522
KEY `t1_id_fk` (`id`,`id2`),
1523
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1524
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1525
create unique index id on t2 (id,id2);
1526
show create table t2;
1528
t2 CREATE TABLE `t2` (
1529
`id` int(11) NOT NULL,
1530
`id2` int(11) NOT NULL,
1531
UNIQUE KEY `id` (`id`,`id2`),
1532
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1533
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1535
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;
1536
show create table t2;
1538
t2 CREATE TABLE `t2` (
1539
`id` int(11) NOT NULL,
1540
`id2` int(11) NOT NULL,
1541
UNIQUE KEY `id` (`id`,`id2`),
1542
KEY `t1_id_fk` (`id2`,`id`),
1543
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1544
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1546
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;
1547
show create table t2;
1549
t2 CREATE TABLE `t2` (
1550
`id` int(11) NOT NULL,
1551
`id2` int(11) NOT NULL,
1552
UNIQUE KEY `id` (`id`,`id2`),
1553
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1554
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1556
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;
1557
show create table t2;
1559
t2 CREATE TABLE `t2` (
1560
`id` int(11) NOT NULL,
1561
`id2` int(11) NOT NULL,
1562
UNIQUE KEY `id` (`id`,`id2`),
1563
KEY `t1_id_fk` (`id2`,`id`),
1564
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1565
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1567
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;
1568
show create table t2;
1570
t2 CREATE TABLE `t2` (
1571
`id` int(11) NOT NULL AUTO_INCREMENT,
1572
`id2` int(11) NOT NULL,
1574
KEY `id` (`id`,`id2`),
1575
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1576
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1578
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;
1579
show create table t2;
1581
t2 CREATE TABLE `t2` (
1582
`id` int(11) NOT NULL AUTO_INCREMENT,
1583
`id2` int(11) NOT NULL,
1584
KEY `t1_id_fk` (`id`),
1585
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1586
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1587
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1588
show create table t2;
1590
t2 CREATE TABLE `t2` (
1591
`id` int(11) NOT NULL AUTO_INCREMENT,
1592
`id2` int(11) NOT NULL,
1593
KEY `id_test` (`id`),
1594
KEY `id_test2` (`id`,`id2`),
1595
CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1596
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1598
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;
1599
ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1600
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1601
show create table t2;
1603
t2 CREATE TABLE `t2` (
1604
`a` int(11) NOT NULL AUTO_INCREMENT,
1605
`b` int(11) DEFAULT NULL,
1607
UNIQUE KEY `b_2` (`b`),
1609
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1610
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1612
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;
1613
show create table t2;
1615
t2 CREATE TABLE `t2` (
1616
`a` int(11) NOT NULL AUTO_INCREMENT,
1617
`b` int(11) DEFAULT NULL,
1619
UNIQUE KEY `b` (`b`),
1620
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1621
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1622
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1624
create table t1 (c char(10), index (c,c)) engine=innodb;
1625
ERROR 42S21: Duplicate column name 'c'
1626
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1627
ERROR 42S21: Duplicate column name 'c1'
1628
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1629
ERROR 42S21: Duplicate column name 'c1'
1630
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1631
ERROR 42S21: Duplicate column name 'c1'
1632
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1633
alter table t1 add key (c1,c1);
1634
ERROR 42S21: Duplicate column name 'c1'
1635
alter table t1 add key (c2,c1,c1);
1636
ERROR 42S21: Duplicate column name 'c1'
1637
alter table t1 add key (c1,c2,c1);
1638
ERROR 42S21: Duplicate column name 'c1'
1639
alter table t1 add key (c1,c1,c2);
1640
ERROR 42S21: Duplicate column name 'c1'
1642
create table t1(a int(1) , b int(1)) engine=innodb;
1643
insert into t1 values ('1111', '3333');
1644
select distinct concat(a, b) from t1;
1648
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1649
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1650
ERROR HY000: The used table type doesn't support FULLTEXT indexes
1652
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1653
INSERT INTO t1 VALUES (1),(2),(3);
1654
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),
1655
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1656
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1657
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;
1664
create temporary table t1 (a int) engine=innodb;
1665
insert into t1 values (4711);
1667
insert into t1 values (42);
1672
create table t1 (a int) engine=innodb;
1673
insert into t1 values (4711);
1675
insert into t1 values (42);
1680
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;
1681
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1682
select * from t1 order by a,b,c,d;
1687
explain select * from t1 order by a,b,c,d;
1688
id select_type table type possible_keys key key_len ref rows Extra
1689
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1691
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1692
insert into t1 values ('8', '6'), ('4', '7');
1693
select min(a) from t1;
1696
select min(b) from t1 where a='8';
1700
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1701
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1704
18446744073709551600
1705
18446744073709551601
1706
select count(*) from t1 where x>0;
1709
select count(*) from t1 where x=0;
1712
select count(*) from t1 where x<0;
1715
select count(*) from t1 where x < -16;
1718
select count(*) from t1 where x = -16;
1721
explain select count(*) from t1 where x > -16;
1722
id select_type table type possible_keys key key_len ref rows Extra
1723
1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
1724
select count(*) from t1 where x > -16;
1727
select * from t1 where x > -16;
1729
18446744073709551600
1730
18446744073709551601
1731
select count(*) from t1 where x = 18446744073709551601;
1735
show status like "Innodb_buffer_pool_pages_total";
1737
Innodb_buffer_pool_pages_total 511
1738
show status like "Innodb_page_size";
1740
Innodb_page_size 16384
1741
show status like "Innodb_rows_deleted";
1743
Innodb_rows_deleted 71
1744
show status like "Innodb_rows_inserted";
1746
Innodb_rows_inserted 1084
1747
show status like "Innodb_rows_updated";
1749
Innodb_rows_updated 885
1750
show status like "Innodb_row_lock_waits";
1752
Innodb_row_lock_waits 0
1753
show status like "Innodb_row_lock_current_waits";
1755
Innodb_row_lock_current_waits 0
1756
show status like "Innodb_row_lock_time";
1758
Innodb_row_lock_time 0
1759
show status like "Innodb_row_lock_time_max";
1761
Innodb_row_lock_time_max 0
1762
show status like "Innodb_row_lock_time_avg";
1764
Innodb_row_lock_time_avg 0
1765
show variables like "innodb_sync_spin_loops";
1767
innodb_sync_spin_loops 20
1768
set global innodb_sync_spin_loops=1000;
1769
show variables like "innodb_sync_spin_loops";
1771
innodb_sync_spin_loops 1000
1772
set global innodb_sync_spin_loops=0;
1773
show variables like "innodb_sync_spin_loops";
1775
innodb_sync_spin_loops 0
1776
set global innodb_sync_spin_loops=20;
1777
show variables like "innodb_sync_spin_loops";
1779
innodb_sync_spin_loops 20
1780
show variables like "innodb_thread_concurrency";
1782
innodb_thread_concurrency 8
1783
set global innodb_thread_concurrency=1001;
1785
Warning 1292 Truncated incorrect thread_concurrency value: '1001'
1786
show variables like "innodb_thread_concurrency";
1788
innodb_thread_concurrency 1000
1789
set global innodb_thread_concurrency=0;
1790
show variables like "innodb_thread_concurrency";
1792
innodb_thread_concurrency 0
1793
set global innodb_thread_concurrency=16;
1794
show variables like "innodb_thread_concurrency";
1796
innodb_thread_concurrency 16
1797
show variables like "innodb_concurrency_tickets";
1799
innodb_concurrency_tickets 500
1800
set global innodb_concurrency_tickets=1000;
1801
show variables like "innodb_concurrency_tickets";
1803
innodb_concurrency_tickets 1000
1804
set global innodb_concurrency_tickets=0;
1806
Warning 1292 Truncated incorrect concurrency_tickets value: '0'
1807
show variables like "innodb_concurrency_tickets";
1809
innodb_concurrency_tickets 1
1810
set global innodb_concurrency_tickets=500;
1811
show variables like "innodb_concurrency_tickets";
1813
innodb_concurrency_tickets 500
1814
show variables like "innodb_thread_sleep_delay";
1816
innodb_thread_sleep_delay 10000
1817
set global innodb_thread_sleep_delay=100000;
1818
show variables like "innodb_thread_sleep_delay";
1820
innodb_thread_sleep_delay 100000
1821
set global innodb_thread_sleep_delay=0;
1822
show variables like "innodb_thread_sleep_delay";
1824
innodb_thread_sleep_delay 0
1825
set global innodb_thread_sleep_delay=10000;
1826
show variables like "innodb_thread_sleep_delay";
1828
innodb_thread_sleep_delay 10000
1829
set storage_engine=INNODB;
1830
drop table if exists t1,t2,t3;
1831
--- Testing varchar ---
1832
--- Testing varchar ---
1833
create table t1 (v varchar(10), c char(10), t text);
1834
insert into t1 values('+ ', '+ ', '+ ');
1835
set @a=repeat(' ',20);
1836
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1838
Note 1265 Data truncated for column 'v' at row 1
1839
Note 1265 Data truncated for column 'c' at row 1
1840
select concat('*',v,'*',c,'*',t,'*') from t1;
1841
concat('*',v,'*',c,'*',t,'*')
1844
show create table t1;
1846
t1 CREATE TABLE `t1` (
1847
`v` varchar(10) DEFAULT NULL,
1848
`c` char(10) DEFAULT NULL,
1850
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1851
create table t2 like t1;
1852
show create table t2;
1854
t2 CREATE TABLE `t2` (
1855
`v` varchar(10) DEFAULT NULL,
1856
`c` char(10) DEFAULT NULL,
1858
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1859
create table t3 select * from t1;
1860
show create table t3;
1862
t3 CREATE TABLE `t3` (
1863
`v` varchar(10) DEFAULT NULL,
1864
`c` char(10) DEFAULT NULL,
1866
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1867
alter table t1 modify c varchar(10);
1868
show create table t1;
1870
t1 CREATE TABLE `t1` (
1871
`v` varchar(10) DEFAULT NULL,
1872
`c` varchar(10) DEFAULT NULL,
1874
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1875
alter table t1 modify v char(10);
1876
show create table t1;
1878
t1 CREATE TABLE `t1` (
1879
`v` char(10) DEFAULT NULL,
1880
`c` varchar(10) DEFAULT NULL,
1882
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1883
alter table t1 modify t varchar(10);
1885
Note 1265 Data truncated for column 't' at row 2
1886
show create table t1;
1888
t1 CREATE TABLE `t1` (
1889
`v` char(10) DEFAULT NULL,
1890
`c` varchar(10) DEFAULT NULL,
1891
`t` varchar(10) DEFAULT NULL
1892
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1893
select concat('*',v,'*',c,'*',t,'*') from t1;
1894
concat('*',v,'*',c,'*',t,'*')
1897
drop table t1,t2,t3;
1898
create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1899
show create table t1;
1901
t1 CREATE TABLE `t1` (
1902
`v` varchar(10) DEFAULT NULL,
1903
`c` char(10) DEFAULT NULL,
1908
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1909
select count(*) from t1;
1912
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1913
select count(*) from t1 where v='a';
1916
select count(*) from t1 where c='a';
1919
select count(*) from t1 where t='a';
1922
select count(*) from t1 where v='a ';
1925
select count(*) from t1 where c='a ';
1928
select count(*) from t1 where t='a ';
1931
select count(*) from t1 where v between 'a' and 'a ';
1934
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1937
select count(*) from t1 where v like 'a%';
1940
select count(*) from t1 where c like 'a%';
1943
select count(*) from t1 where t like 'a%';
1946
select count(*) from t1 where v like 'a %';
1949
explain select count(*) from t1 where v='a ';
1950
id select_type table type possible_keys key key_len ref rows Extra
1951
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1952
explain select count(*) from t1 where c='a ';
1953
id select_type table type possible_keys key key_len ref rows Extra
1954
1 SIMPLE t1 ref c c 11 const # Using where; Using index
1955
explain select count(*) from t1 where t='a ';
1956
id select_type table type possible_keys key key_len ref rows Extra
1957
1 SIMPLE t1 ref t t 13 const # Using where
1958
explain select count(*) from t1 where v like 'a%';
1959
id select_type table type possible_keys key key_len ref rows Extra
1960
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
1961
explain select count(*) from t1 where v between 'a' and 'a ';
1962
id select_type table type possible_keys key key_len ref rows Extra
1963
1 SIMPLE t1 ref v v 13 const # Using where; Using index
1964
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
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
alter table t1 add unique(v);
1968
ERROR 23000: Duplicate entry 'v' for key 'v_2'
1969
alter table t1 add key(v);
1970
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1982
explain select * from t1 where v='a';
1983
id select_type table type possible_keys key key_len ref rows Extra
1984
1 SIMPLE t1 ref v,v_2 # 13 const # Using where
1985
select v,count(*) from t1 group by v limit 10;
1997
select v,count(t) from t1 group by v limit 10;
2009
select v,count(c) from t1 group by v limit 10;
2021
select sql_big_result v,count(t) from t1 group by v limit 10;
2033
select sql_big_result v,count(c) from t1 group by v limit 10;
2045
select c,count(*) from t1 group by c limit 10;
2057
select c,count(t) from t1 group by c limit 10;
2069
select sql_big_result c,count(t) from t1 group by c limit 10;
2081
select t,count(*) from t1 group by t limit 10;
2093
select t,count(t) from t1 group by t limit 10;
2105
select sql_big_result t,count(t) from t1 group by t limit 10;
2117
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
2118
show create table t1;
2120
t1 CREATE TABLE `t1` (
2121
`v` varchar(300) DEFAULT NULL,
2122
`c` char(10) DEFAULT NULL,
2127
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2128
select count(*) from t1 where v='a';
2131
select count(*) from t1 where v='a ';
2134
select count(*) from t1 where v between 'a' and 'a ';
2137
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2140
select count(*) from t1 where v like 'a%';
2143
select count(*) from t1 where v like 'a %';
2146
explain select count(*) from t1 where v='a ';
2147
id select_type table type possible_keys key key_len ref rows Extra
2148
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2149
explain select count(*) from t1 where v like 'a%';
2150
id select_type table type possible_keys key key_len ref rows Extra
2151
1 SIMPLE t1 range v v 303 NULL # Using where; Using index
2152
explain select count(*) from t1 where v between 'a' and 'a ';
2153
id select_type table type possible_keys key key_len ref rows Extra
2154
1 SIMPLE t1 ref v v 303 const # Using where; Using index
2155
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
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 * from t1 where v='a';
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
2161
select v,count(*) from t1 group by v limit 10;
2173
select v,count(t) from t1 group by v limit 10;
2185
select sql_big_result v,count(t) from t1 group by v limit 10;
2197
alter table t1 drop key v, add key v (v(30));
2198
show create table t1;
2200
t1 CREATE TABLE `t1` (
2201
`v` varchar(300) DEFAULT NULL,
2202
`c` char(10) DEFAULT NULL,
2207
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2208
select count(*) from t1 where v='a';
2211
select count(*) from t1 where v='a ';
2214
select count(*) from t1 where v between 'a' and 'a ';
2217
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2220
select count(*) from t1 where v like 'a%';
2223
select count(*) from t1 where v like 'a %';
2226
explain select count(*) from t1 where v='a ';
2227
id select_type table type possible_keys key key_len ref rows Extra
2228
1 SIMPLE t1 ref v v 33 const # Using where
2229
explain select count(*) from t1 where v like 'a%';
2230
id select_type table type possible_keys key key_len ref rows Extra
2231
1 SIMPLE t1 range v v 33 NULL # Using where
2232
explain select count(*) from t1 where v between 'a' and 'a ';
2233
id select_type table type possible_keys key key_len ref rows Extra
2234
1 SIMPLE t1 ref v v 33 const # Using where
2235
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
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 * from t1 where v='a';
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
select v,count(*) from t1 group by v limit 10;
2253
select v,count(t) from t1 group by v limit 10;
2265
select sql_big_result v,count(t) from t1 group by v limit 10;
2277
alter table t1 modify v varchar(600), drop key v, add key v (v);
2278
show create table t1;
2280
t1 CREATE TABLE `t1` (
2281
`v` varchar(600) DEFAULT NULL,
2282
`c` char(10) DEFAULT NULL,
2287
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2288
select v,count(*) from t1 group by v limit 10;
2300
select v,count(t) from t1 group by v limit 10;
2312
select sql_big_result v,count(t) from t1 group by v limit 10;
2325
create table t1 (a char(10), unique (a));
2326
insert into t1 values ('a ');
2327
insert into t1 values ('a ');
2328
ERROR 23000: Duplicate entry 'a' for key 'a'
2329
alter table t1 modify a varchar(10);
2330
insert into t1 values ('a '),('a '),('a '),('a ');
2331
ERROR 23000: Duplicate entry 'a ' for key 'a'
2332
insert into t1 values ('a ');
2333
ERROR 23000: Duplicate entry 'a ' for key 'a'
2334
insert into t1 values ('a ');
2335
ERROR 23000: Duplicate entry 'a ' for key 'a'
2336
insert into t1 values ('a ');
2337
ERROR 23000: Duplicate entry 'a ' for key 'a'
2338
update t1 set a='a ' where a like 'a%';
2339
select concat(a,'.') from t1;
2342
update t1 set a='abc ' where a like 'a ';
2343
select concat(a,'.') from t1;
2346
update t1 set a='a ' where a like 'a %';
2347
select concat(a,'.') from t1;
2350
update t1 set a='a ' where a like 'a ';
2351
select concat(a,'.') from t1;
2355
create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2356
show create table t1;
2358
t1 CREATE TABLE `t1` (
2359
`v` varchar(10) DEFAULT NULL,
2360
`c` char(10) DEFAULT NULL,
2365
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2367
create table t1 (v char(10) character set utf8);
2368
show create table t1;
2370
t1 CREATE TABLE `t1` (
2371
`v` char(10) CHARACTER SET utf8 DEFAULT NULL
2372
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2374
create table t1 (v varchar(10), c char(10)) row_format=fixed;
2376
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
2377
show create table t1;
2379
t1 CREATE TABLE `t1` (
2380
`v` varchar(10) DEFAULT NULL,
2381
`c` char(10) DEFAULT NULL
2382
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
2383
insert into t1 values('a','a'),('a ','a ');
2384
select concat('*',v,'*',c,'*') from t1;
2385
concat('*',v,'*',c,'*')
2389
create table t1 (v varchar(65530), key(v(10)));
2390
insert into t1 values(repeat('a',65530));
2391
select length(v) from t1 where v=repeat('a',65530);
2395
create table t1(a int, b varchar(12), key ba(b, a));
2396
insert into t1 values (1, 'A'), (20, NULL);
2397
explain select * from t1 where a=20 and b is null;
2398
id select_type table type possible_keys key key_len ref rows Extra
2399
1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index
2400
select * from t1 where a=20 and b is null;
2404
create table t1 (v varchar(65530), key(v));
2406
Warning 1071 Specified key was too long; max key length is 767 bytes
2408
create table t1 (v varchar(65536));
2410
Note 1246 Converting column 'v' from VARCHAR to TEXT
2411
show create table t1;
2413
t1 CREATE TABLE `t1` (
2415
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2417
create table t1 (v varchar(65530) character set utf8);
2419
Note 1246 Converting column 'v' from VARCHAR to TEXT
2420
show create table t1;
2422
t1 CREATE TABLE `t1` (
2423
`v` mediumtext CHARACTER SET utf8
2424
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2426
set storage_engine=MyISAM;
2427
create table t1 (v varchar(16384)) engine=innodb;
2429
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2430
insert into t1 values ('8', '6'), ('4', '7');
2431
select min(a) from t1;
2434
select min(b) from t1 where a='8';
2438
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2439
insert into t1 (b) values (1);
2440
replace into t1 (b) values (2), (1), (3);
2447
insert into t1 (b) values (1);
2448
replace into t1 (b) values (2);
2449
replace into t1 (b) values (1);
2450
replace into t1 (b) values (3);
2457
create table t1 (rowid int not null auto_increment, val int not null,primary
2458
key (rowid), unique(val)) engine=innodb;
2459
replace into t1 (val) values ('1'),('2');
2460
replace into t1 (val) values ('1'),('2');
2461
insert into t1 (val) values ('1'),('2');
2462
ERROR 23000: Duplicate entry '1' for key 'val'
2468
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2469
insert into t1 (val) values (1);
2470
update t1 set a=2 where a=1;
2471
insert into t1 (val) values (1);
2472
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
2477
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2478
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2479
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2482
SELECT GRADE FROM t1 WHERE GRADE= 151;
2486
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
2487
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
2488
insert into t2 values ('aa','cc');
2489
insert into t1 values ('aa','bb'),('aa','cc');
2490
delete t1 from t1,t2 where f1=f3 and f4='cc';
2495
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2498
id INTEGER NOT NULL,
2499
FOREIGN KEY (id) REFERENCES t1 (id)
2501
INSERT INTO t1 (id) VALUES (NULL);
2506
INSERT INTO t1 (id) VALUES (NULL);
2512
INSERT INTO t1 (id) VALUES (NULL);
2521
CREATE TEMPORARY TABLE t2
2523
id INT NOT NULL PRIMARY KEY,
2525
FOREIGN KEY (b) REFERENCES test.t1(id)
2527
Got one of the listed errors
2529
create table t1 (col1 varchar(2000), index (col1(767)))
2530
character set = latin1 engine = innodb;
2531
create table t2 (col1 char(255), index (col1))
2532
character set = latin1 engine = innodb;
2533
create table t3 (col1 binary(255), index (col1))
2534
character set = latin1 engine = innodb;
2535
create table t4 (col1 varchar(767), index (col1))
2536
character set = latin1 engine = innodb;
2537
create table t5 (col1 varchar(767) primary key)
2538
character set = latin1 engine = innodb;
2539
create table t6 (col1 varbinary(767) primary key)
2540
character set = latin1 engine = innodb;
2541
create table t7 (col1 text, index(col1(767)))
2542
character set = latin1 engine = innodb;
2543
create table t8 (col1 blob, index(col1(767)))
2544
character set = latin1 engine = innodb;
2545
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
2546
character set = latin1 engine = innodb;
2547
show create table t9;
2549
t9 CREATE TABLE `t9` (
2550
`col1` varchar(512) DEFAULT NULL,
2551
`col2` varchar(512) DEFAULT NULL,
2552
KEY `col1` (`col1`,`col2`)
2553
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2554
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
2555
create table t1 (col1 varchar(768), index(col1))
2556
character set = latin1 engine = innodb;
2558
Warning 1071 Specified key was too long; max key length is 767 bytes
2559
create table t2 (col1 varbinary(768), index(col1))
2560
character set = latin1 engine = innodb;
2562
Warning 1071 Specified key was too long; max key length is 767 bytes
2563
create table t3 (col1 text, index(col1(768)))
2564
character set = latin1 engine = innodb;
2566
Warning 1071 Specified key was too long; max key length is 767 bytes
2567
create table t4 (col1 blob, index(col1(768)))
2568
character set = latin1 engine = innodb;
2570
Warning 1071 Specified key was too long; max key length is 767 bytes
2571
show create table t1;
2573
t1 CREATE TABLE `t1` (
2574
`col1` varchar(768) DEFAULT NULL,
2575
KEY `col1` (`col1`(767))
2576
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2577
drop table t1, t2, t3, t4;
2578
create table t1 (col1 varchar(768) primary key)
2579
character set = latin1 engine = innodb;
2580
ERROR 42000: Specified key was too long; max key length is 767 bytes
2581
create table t2 (col1 varbinary(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 t3 (col1 text, primary key(col1(768)))
2585
character set = latin1 engine = innodb;
2586
ERROR 42000: Specified key was too long; max key length is 767 bytes
2587
create table t4 (col1 blob, primary key(col1(768)))
2588
character set = latin1 engine = innodb;
2589
ERROR 42000: Specified key was too long; max key length is 767 bytes
2597
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2599
INSERT INTO t2 VALUES(2);
2600
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2601
INSERT INTO t1 VALUES(1);
2602
INSERT INTO t2 VALUES(1);
2603
DELETE FROM t1 WHERE id = 1;
2604
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2606
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
2607
SET FOREIGN_KEY_CHECKS=0;
2609
SET FOREIGN_KEY_CHECKS=1;
2610
INSERT INTO t2 VALUES(3);
2611
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2613
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2614
insert into t1 values (1),(2);
2619
insert into t1 values(3);
2629
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2630
insert into t1 values (1),(2);
2636
insert into t1 values(3);
2641
set foreign_key_checks=0;
2642
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2643
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2644
ERROR HY000: Can't create table 'test.t1' (errno: 150)
2645
set foreign_key_checks=1;
2647
set foreign_key_checks=0;
2648
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2649
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
2650
ERROR HY000: Can't create table 'test.t2' (errno: 150)
2651
set foreign_key_checks=1;
2653
set foreign_key_checks=0;
2654
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2655
create table t1(a varchar(10) primary key) engine = innodb;
2656
alter table t1 modify column a int;
2657
Got one of the listed errors
2658
set foreign_key_checks=1;
2660
set foreign_key_checks=0;
2661
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2662
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2663
alter table t1 convert to character set utf8;
2664
set foreign_key_checks=1;
2666
set foreign_key_checks=0;
2667
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2668
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
2669
rename table t3 to t1;
2670
ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150)
2671
set foreign_key_checks=1;
2673
create table t1(a int primary key) row_format=redundant engine=innodb;
2674
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2675
create table t3(a int primary key) row_format=compact engine=innodb;
2676
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2677
insert into t1 values(1);
2678
insert into t3 values(1);
2679
insert into t2 values(2);
2680
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`))
2681
insert into t4 values(2);
2682
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`))
2683
insert into t2 values(1);
2684
insert into t4 values(1);
2686
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`))
2688
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`))
2690
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`))
2692
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`))
2694
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`))
2696
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`))
2701
drop table t4,t3,t2,t1;
2702
create table t1 (a varchar(255) character set utf8,
2703
b varchar(255) character set utf8,
2704
c varchar(255) character set utf8,
2705
d varchar(255) character set utf8,
2706
key (a,b,c,d)) engine=innodb;
2708
create table t1 (a varchar(255) character set utf8,
2709
b varchar(255) character set utf8,
2710
c varchar(255) character set utf8,
2711
d varchar(255) character set utf8,
2712
e varchar(255) character set utf8,
2713
key (a,b,c,d,e)) engine=innodb;
2714
ERROR 42000: Specified key was too long; max key length is 3072 bytes
2715
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2716
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
2717
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
2718
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
2719
insert into t1 values (0x41),(0x4120),(0x4100);
2720
insert into t2 values (0x41),(0x4120),(0x4100);
2721
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2722
insert into t2 values (0x41),(0x4120);
2723
insert into t3 values (0x41),(0x4120),(0x4100);
2724
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2725
insert into t3 values (0x41),(0x4100);
2726
insert into t4 values (0x41),(0x4120),(0x4100);
2727
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2728
insert into t4 values (0x41),(0x4100);
2729
select hex(s1) from t1;
2734
select hex(s1) from t2;
2738
select hex(s1) from t3;
2742
select hex(s1) from t4;
2746
drop table t1,t2,t3,t4;
2747
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2748
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2749
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2750
insert into t2 values(0x42);
2751
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)
2752
insert into t2 values(0x41);
2753
select hex(s1) from t2;
2756
update t1 set s1=0x123456 where a=2;
2757
select hex(s1) from t2;
2760
update t1 set s1=0x12 where a=1;
2761
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)
2762
update t1 set s1=0x12345678 where a=1;
2763
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)
2764
update t1 set s1=0x123457 where a=1;
2765
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)
2766
update t1 set s1=0x1220 where a=1;
2767
select hex(s1) from t2;
2770
update t1 set s1=0x1200 where a=1;
2771
select hex(s1) from t2;
2774
update t1 set s1=0x4200 where a=1;
2775
select hex(s1) from t2;
2778
delete from t1 where a=1;
2779
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)
2780
delete from t1 where a=2;
2781
update t2 set s1=0x4120;
2783
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)
2784
delete from t1 where a!=3;
2785
select a,hex(s1) from t1;
2788
select hex(s1) from t2;
2792
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
2793
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2794
insert into t1 values(1,0x4100),(2,0x41);
2795
insert into t2 values(0x41);
2796
select hex(s1) from t2;
2799
update t1 set s1=0x1234 where a=1;
2800
select hex(s1) from t2;
2803
update t1 set s1=0x12 where a=2;
2804
select hex(s1) from t2;
2807
delete from t1 where a=1;
2808
delete from t1 where a=2;
2809
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)
2810
select a,hex(s1) from t1;
2813
select hex(s1) from t2;
2817
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2818
CREATE TABLE t2(a INT) ENGINE=InnoDB;
2819
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2820
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2821
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2822
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2823
SHOW CREATE TABLE t2;
2825
t2 CREATE TABLE `t2` (
2826
`a` int(11) DEFAULT NULL,
2827
KEY `t2_ibfk_0` (`a`)
2828
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2830
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2831
insert into t1(a) values (1),(2),(3);
2834
update t1 set b = 5 where a = 2;
2835
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
2837
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
2838
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
2839
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
2840
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
2841
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
2846
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2847
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2848
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2849
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2850
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2851
insert into t1(a) values (1),(2),(3);
2852
insert into t2(a) values (1),(2),(3);
2853
insert into t3(a) values (1),(2),(3);
2854
insert into t4(a) values (1),(2),(3);
2855
insert into t3(a) values (5),(7),(8);
2856
insert into t4(a) values (5),(7),(8);
2857
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
2858
create trigger t1t before insert on t1 for each row begin
2859
INSERT INTO t2 SET a = NEW.a;
2861
create trigger t2t before insert on t2 for each row begin
2862
DELETE FROM t3 WHERE a = NEW.a;
2864
create trigger t3t before delete on t3 for each row begin
2865
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
2867
create trigger t4t before update on t4 for each row begin
2868
UPDATE t5 SET b = b + 1 where a = NEW.a;
2872
update t1 set b = b + 5 where a = 1;
2873
update t2 set b = b + 5 where a = 1;
2874
update t3 set b = b + 5 where a = 1;
2875
update t4 set b = b + 5 where a = 1;
2876
insert into t5(a) values(20);
2878
insert into t1(a) values(7);
2879
insert into t2(a) values(8);
2880
delete from t2 where a = 3;
2881
update t4 set b = b + 1 where a = 3;
2887
drop table t1, t2, t3, t4, t5;
2889
field1 varchar(8) NOT NULL DEFAULT '',
2890
field2 varchar(8) NOT NULL DEFAULT '',
2891
PRIMARY KEY (field1, field2)
2894
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2895
FOREIGN KEY (field1) REFERENCES t1 (field1)
2896
ON DELETE CASCADE ON UPDATE CASCADE
2898
INSERT INTO t1 VALUES ('old', 'somevalu');
2899
INSERT INTO t1 VALUES ('other', 'anyvalue');
2900
INSERT INTO t2 VALUES ('old');
2901
INSERT INTO t2 VALUES ('other');
2902
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2903
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
2916
alter table t1 add constraint c2_fk foreign key (c2)
2917
references t2(c1) on delete cascade;
2918
show create table t1;
2920
t1 CREATE TABLE `t1` (
2921
`c1` bigint(20) NOT NULL,
2922
`c2` bigint(20) NOT NULL,
2924
UNIQUE KEY `c2` (`c2`),
2925
CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2926
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2927
alter table t1 drop foreign key c2_fk;
2928
show create table t1;
2930
t1 CREATE TABLE `t1` (
2931
`c1` bigint(20) NOT NULL,
2932
`c2` bigint(20) NOT NULL,
2934
UNIQUE KEY `c2` (`c2`)
2935
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2937
create table t1(a date) engine=innodb;
2938
create table t2(a date, key(a)) engine=innodb;
2939
insert into t1 values('2005-10-01');
2940
insert into t2 values('2005-10-01');
2941
select * from t1, t2
2942
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2944
2005-10-01 2005-10-01
2946
create table t1 (id int not null, f_id int not null, f int not null,
2947
primary key(f_id, id)) engine=innodb;
2948
create table t2 (id int not null,s_id int not null,s varchar(200),
2949
primary key(id)) engine=innodb;
2950
INSERT INTO t1 VALUES (8, 1, 3);
2951
INSERT INTO t1 VALUES (1, 2, 1);
2952
INSERT INTO t2 VALUES (1, 0, '');
2953
INSERT INTO t2 VALUES (8, 1, '');
2955
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2956
WHERE mm.id IS NULL;
2957
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2958
where mm.id is null lock in share mode;
2961
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2962
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2965
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2966
update t1 set b = 5 where b = 1;
2968
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2969
select * from t1 where a = 7 and b = 3 for update;
2975
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2976
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2979
select * from t1 lock in share mode;
2987
update t1 set b = 5 where b = 1;
2989
select * from t1 where a = 2 and b = 2 for update;
2990
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2994
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2995
insert into t1 values (1,2),(5,3),(4,2);
2996
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2997
insert into t2 values (8,6),(12,1),(3,1);
3000
select * from t2 for update;
3006
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3007
insert into t1 select * from t2;
3008
update t1 set b = (select e from t2 where a = d);
3009
create table t3(d int not null, e int, primary key(d)) engine=innodb
3013
drop table t1, t2, t3;
3014
create table t1(a int not null, b int, primary key(a)) engine=innodb;
3015
insert into t1 values (1,2),(5,3),(4,2);
3016
create table t2(a int not null, b int, primary key(a)) engine=innodb;
3017
insert into t2 values (8,6),(12,1),(3,1);
3018
create table t3(d int not null, b int, primary key(d)) engine=innodb;
3019
insert into t3 values (8,6),(12,1),(3,1);
3020
create table t5(a int not null, b int, primary key(a)) engine=innodb;
3021
insert into t5 values (1,2),(5,3),(4,2);
3022
create table t6(d int not null, e int, primary key(d)) engine=innodb;
3023
insert into t6 values (8,6),(12,1),(3,1);
3024
create table t8(a int not null, b int, primary key(a)) engine=innodb;
3025
insert into t8 values (1,2),(5,3),(4,2);
3026
create table t9(d int not null, e int, primary key(d)) engine=innodb;
3027
insert into t9 values (8,6),(12,1),(3,1);
3030
select * from t2 for update;
3036
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3037
insert into t1 select * from t2;
3039
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3040
update t3 set b = (select b from t2 where a = d);
3042
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3043
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
3045
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3046
insert into t5 (select * from t2 lock in share mode);
3048
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3049
update t6 set e = (select b from t2 where a = d lock in share mode);
3051
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3052
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
3054
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3055
insert into t8 (select * from t2 for update);
3057
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3058
update t9 set e = (select b from t2 where a = d for update);
3060
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3061
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
3062
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3063
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3064
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3065
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3066
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3067
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3068
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3069
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3070
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3072
drop table t1, t2, t3, t5, t6, t8, t9;
3073
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
3074
ERROR HY000: Can't create table 'test.t1' (errno: -1)
3076
a BIGINT(20) NOT NULL,
3078
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3080
a BIGINT(20) NOT NULL,
3081
b VARCHAR(128) NOT NULL,
3084
KEY idx_t2_b_c (b,c(200)),
3085
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
3087
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3088
INSERT INTO t1 VALUES (1);
3089
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
3090
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
3091
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
3092
INSERT INTO t2 VALUES (1, 'customer_over', '1');
3093
SELECT * FROM t2 WHERE b = 'customer_over';
3096
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
3099
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
3102
/* Bang: Empty result set, above was expected: */
3103
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3106
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3110
CREATE TABLE t1 ( a int ) ENGINE=innodb;
3112
INSERT INTO t1 VALUES (1);
3114
Table Op Msg_type Msg_text
3115
test.t1 optimize status OK
3117
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
3118
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
3119
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
3120
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
3121
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
3122
DELETE CASCADE ON UPDATE CASCADE;
3123
SHOW CREATE TABLE t2;
3125
t2 CREATE TABLE `t2` (
3126
`id` int(11) NOT NULL,
3127
`f` int(11) NOT NULL,
3130
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
3131
CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
3132
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3134
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
3135
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
3136
INSERT INTO t1 VALUES (1);
3137
INSERT INTO t2 VALUES (1);
3138
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
3139
ALTER TABLE t2 MODIFY a INT NOT NULL;
3140
ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
3143
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
3145
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
3147
INSERT INTO t1 VALUES ('DDD');
3152
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
3154
INSERT INTO t1 VALUES (0),(347),(0);
3160
SHOW CREATE TABLE t1;
3162
t1 CREATE TABLE `t1` (
3163
`id` int(11) NOT NULL AUTO_INCREMENT,
3165
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3166
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
3167
INSERT INTO t2 VALUES(42),(347),(348);
3168
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
3169
SHOW CREATE TABLE t1;
3171
t1 CREATE TABLE `t1` (
3172
`id` int(11) NOT NULL AUTO_INCREMENT,
3174
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
3175
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3178
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
3179
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
3180
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
3181
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
3182
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
3183
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
3184
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
3185
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
3187
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
3188
DROP TABLE IF EXISTS t1;
3190
Note 1051 Unknown table 't1'
3192
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
3194
INSERT INTO t1 VALUES(-10);
3198
INSERT INTO t1 VALUES(NULL);
3204
SET TX_ISOLATION='read-committed';
3206
DROP TABLE IF EXISTS t1, t2;
3208
Note 1051 Unknown table 't1'
3209
Note 1051 Unknown table 't2'
3210
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
3211
CREATE TABLE t2 LIKE t1;
3214
SET TX_ISOLATION='read-committed';
3216
INSERT INTO t1 VALUES (1);
3218
SELECT * FROM t1 WHERE a=1;
3221
SET TX_ISOLATION='read-committed';
3225
SET TX_ISOLATION='read-committed';
3227
INSERT INTO t1 VALUES (2);
3229
SELECT * FROM t1 WHERE a=2;
3232
SELECT * FROM t1 WHERE a=2;
3237
create table t1 (i int, j int) engine=innodb;
3238
insert into t1 (i, j) values (1, 1), (2, 2);
3239
update t1 set j = 2;
3241
info: Rows matched: 2 Changed: 1 Warnings: 0
3243
create table t1 (id int) comment='this is a comment' engine=innodb;
3244
select table_comment, data_free > 0 as data_free_is_set
3245
from information_schema.tables
3246
where table_schema='test' and table_name = 't1';
3247
table_comment data_free_is_set
3251
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3252
c2 VARCHAR(128) NOT NULL,
3254
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
3256
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3257
c2 INT(10) UNSIGNED DEFAULT NULL,
3259
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
3260
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3263
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
3264
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3269
CREATE TABLE t1 (c1 int default NULL,
3271
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
3274
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
3276
info: Records: 5 Duplicates: 0 Warnings: 0
added
removed
storage/innobase/mysql-test/innodb.result
