1 drop table if exists t1,t2,t3,t4;
2 drop database if exists mysqltest;
3 CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
4 INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000));
5 UPDATE bug58912 SET a=REPEAT('a',7999);
6 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;
7 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
8 select id, code, name from t1 order by id;
17 update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
18 select id, code, name from t1 order by id;
27 update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
28 select id, code, name from t1 order by id;
39 id int(11) NOT NULL auto_increment,
40 parent_id int(11) DEFAULT '0' NOT NULL,
41 level tinyint(4) DEFAULT '0' NOT NULL,
43 KEY parent_id (parent_id),
46 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);
47 update t1 set parent_id=parent_id+100;
48 select * from t1 where parent_id=102;
53 update t1 set id=id+1000;
54 update t1 set id=1024 where id=1009;
55 Got one of the listed errors
97 update ignore t1 set id=id+1;
139 update ignore t1 set id=1023 where id=1010;
140 select * from t1 where parent_id=102;
145 explain select level 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 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 # Using index
151 explain select level,id,parent_id from t1 where level=1;
152 id select_type table type possible_keys key key_len ref rows Extra
153 1 SIMPLE t1 ref level level 1 const #
154 select level,id from t1 where level=1;
162 select level,id,parent_id from t1 where level=1;
171 Table Op Msg_type Msg_text
172 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
173 test.t1 optimize status OK
175 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
176 t1 0 PRIMARY 1 id A # NULL NULL BTREE
177 t1 1 parent_id 1 parent_id A # NULL NULL BTREE
178 t1 1 level 1 level A # NULL NULL BTREE
181 gesuchnr int(11) DEFAULT '0' NOT NULL,
182 benutzer_id int(11) DEFAULT '0' NOT NULL,
183 PRIMARY KEY (gesuchnr,benutzer_id)
185 replace into t1 (gesuchnr,benutzer_id) values (2,1);
186 replace into t1 (gesuchnr,benutzer_id) values (1,1);
187 replace into t1 (gesuchnr,benutzer_id) values (1,1);
193 create table t1 (a int) engine=innodb;
194 insert into t1 values (1), (2);
196 Table Op Msg_type Msg_text
197 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
198 test.t1 optimize status OK
199 delete from t1 where a = 1;
204 Table Op Msg_type Msg_text
205 test.t1 check status OK
207 create table t1 (a int,b varchar(20)) engine=innodb;
208 insert into t1 values (1,""), (2,"testing");
209 delete from t1 where a = 1;
213 create index skr on t1 (a);
214 insert into t1 values (3,""), (4,"testing");
216 Table Op Msg_type Msg_text
217 test.t1 analyze status OK
219 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
220 t1 1 skr 1 a A # NULL NULL YES BTREE
222 create table t1 (a int,b varchar(20),key(a)) engine=innodb;
223 insert into t1 values (1,""), (2,"testing");
224 select * from t1 where a = 1;
228 create table t1 (n int not null primary key) engine=innodb;
230 insert into t1 values (4);
232 select n, "after rollback" from t1;
234 insert into t1 values (4);
236 select n, "after commit" from t1;
240 insert into t1 values (5);
241 insert into t1 values (4);
242 ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
244 select n, "after commit" from t1;
249 insert into t1 values (6);
250 insert into t1 values (4);
251 ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
259 savepoint `my_savepoint`;
260 insert into t1 values (7);
262 insert into t1 values (3);
271 rollback to savepoint savept2;
272 rollback to savepoint savept3;
273 ERROR 42000: SAVEPOINT savept3 does not exist
274 rollback to savepoint savept2;
275 release savepoint `my_savepoint`;
282 rollback to savepoint `my_savepoint`;
283 ERROR 42000: SAVEPOINT my_savepoint does not exist
284 rollback to savepoint savept2;
285 ERROR 42000: SAVEPOINT savept2 does not exist
286 insert into t1 values (8);
293 create table t1 (n int not null primary key) engine=innodb;
295 insert into t1 values (4);
296 flush tables with read lock;
304 create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
306 insert into t1 values(1,'hamdouni');
307 select id as afterbegin_id,nom as afterbegin_nom from t1;
308 afterbegin_id afterbegin_nom
311 select id as afterrollback_id,nom as afterrollback_nom from t1;
312 afterrollback_id afterrollback_nom
314 insert into t1 values(2,'mysql');
315 select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
316 afterautocommit0_id afterautocommit0_nom
319 select id as afterrollback_id,nom as afterrollback_nom from t1;
320 afterrollback_id afterrollback_nom
323 CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
324 insert into t1 values ('pippo', 12);
325 insert into t1 values ('pippo', 12);
326 ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
328 delete from t1 where id = 'pippo';
331 insert into t1 values ('pippo', 12);
343 create table t1 (a integer) engine=innodb;
345 rename table t1 to t2;
346 create table t1 (b integer) engine=innodb;
347 insert into t1 values (1);
350 rename table t2 to t1;
353 CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
354 INSERT INTO t1 VALUES (1, 'Jochen');
359 CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
361 INSERT INTO t1 SET _userid='marc@anyware.co.uk';
366 SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
372 user_id int(10) DEFAULT '0' NOT NULL,
375 ref_email varchar(100) DEFAULT '' NOT NULL,
377 PRIMARY KEY (user_id,ref_email)
379 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');
380 select * from t1 where user_id=10292;
381 user_id name phone ref_email detail
382 10292 sanjeev 29153373 sansh777@hotmail.com xxx
383 10292 shirish 2333604 shirish@yahoo.com ddsds
384 10292 sonali 323232 sonali@bolly.com filmstar
385 INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
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 select * from t1 where user_id>=10292;
392 user_id name phone ref_email detail
393 10292 sanjeev 29153373 sansh777@hotmail.com xxx
394 10292 shirish 2333604 shirish@yahoo.com ddsds
395 10292 sonali 323232 sonali@bolly.com filmstar
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 10293 shirish 2333604 shirish@yahoo.com ddsds
400 select * from t1 where user_id<10292;
401 user_id name phone ref_email detail
402 10291 sanjeev 29153373 sansh777@hotmail.com xxx
404 CREATE TABLE t1 (a int not null, b int not null,c int not null,
405 key(a),primary key(a,b), unique(c),key(a),unique(b));
407 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
408 t1 0 PRIMARY 1 a A # NULL NULL BTREE
409 t1 0 PRIMARY 2 b A # NULL NULL BTREE
410 t1 0 c 1 c A # NULL NULL BTREE
411 t1 0 b 1 b A # NULL NULL BTREE
412 t1 1 a 1 a A # NULL NULL BTREE
413 t1 1 a_2 1 a A # NULL NULL BTREE
415 create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
416 alter table t1 engine=innodb;
417 insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
425 update t1 set col2='7' where col1='4';
433 alter table t1 add co3 int not null;
441 update t1 set col2='9' where col1='2';
450 create table t1 (a int not null , b int, primary key (a)) engine = innodb;
451 create table t2 (a int not null , b int, primary key (a)) engine = myisam;
452 insert into t1 VALUES (1,3) , (2,3), (3,3);
458 insert into t2 select * from t1;
464 delete from t1 where b = 3;
467 insert into t1 select * from t2;
480 user_name varchar(12),
483 user_id int(11) DEFAULT '0' NOT NULL,
489 dummy_primary_key int(11) NOT NULL auto_increment,
490 PRIMARY KEY (dummy_primary_key)
492 INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
493 INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
494 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);
495 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);
496 INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
497 select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
498 user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key
499 user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1
500 user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2
501 user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3
502 user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4
503 user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5
506 id int(11) NOT NULL auto_increment,
507 parent_id int(11) DEFAULT '0' NOT NULL,
508 level tinyint(4) DEFAULT '0' NOT NULL,
510 KEY parent_id (parent_id),
513 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);
514 INSERT INTO t1 values (179,5,2);
515 update t1 set parent_id=parent_id+100;
516 select * from t1 where parent_id=102;
521 update t1 set id=id+1000;
522 update t1 set id=1024 where id=1009;
564 update ignore t1 set id=id+1;
606 update ignore t1 set id=1023 where id=1010;
607 select * from t1 where parent_id=102;
612 explain select level from t1 where level=1;
613 id select_type table type possible_keys key key_len ref rows Extra
614 1 SIMPLE t1 ref level level 1 const # Using index
615 select level,id from t1 where level=1;
623 select level,id,parent_id from t1 where level=1;
631 select level,id from t1 where level=1 order by id;
639 delete from t1 where level=1;
677 sca_code char(6) NOT NULL,
678 cat_code char(6) NOT NULL,
679 sca_desc varchar(50),
680 lan_code char(2) NOT NULL,
681 sca_pic varchar(100),
682 sca_sdesc varchar(50),
683 sca_sch_desc varchar(16),
684 PRIMARY KEY (sca_code, cat_code, lan_code),
685 INDEX sca_pic (sca_pic)
687 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');
688 select count(*) from t1 where sca_code = 'PD';
691 select count(*) from t1 where sca_code <= 'PD';
694 select count(*) from t1 where sca_pic is null;
697 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
698 alter table t1 drop index sca_pic;
699 alter table t1 add index sca_pic (cat_code, sca_pic);
700 select count(*) from t1 where sca_code='PD' and sca_pic is null;
703 select count(*) from t1 where cat_code='E';
706 alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
707 ERROR 42000: Incorrect index name 'sca_pic'
708 alter table t1 drop index sca_pic;
709 alter table t1 add index (sca_pic, cat_code);
710 select count(*) from t1 where sca_code='PD' and sca_pic is null;
713 select count(*) from t1 where sca_pic >= 'n';
716 select sca_pic from t1 where sca_pic is null;
720 update t1 set sca_pic="test" where sca_pic is null;
721 delete from t1 where sca_code='pd';
724 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
725 insert into t1 (a) values(1),(2),(3);
726 select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
731 select a from t1 natural join t1 as t2 where b >= @a order by a;
736 update t1 set a=5 where a=1;
743 create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
744 insert into t1 values("hello",1),("world",2);
745 select * from t1 order by b desc;
750 Table Op Msg_type Msg_text
751 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
752 test.t1 optimize status OK
754 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
755 t1 0 PRIMARY 1 a A # NULL NULL BTREE
757 create table t1 (i int, j int ) ENGINE=innodb;
758 insert into t1 values (1,2);
759 select * from t1 where i=1 and j=2;
762 create index ax1 on t1 (i,j);
763 select * from t1 where i=1 and j=2;
768 a int3 unsigned NOT NULL,
769 b int1 unsigned NOT NULL,
772 INSERT INTO t1 VALUES (1, 1);
773 SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
777 CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
778 INSERT INTO t1 VALUES (1);
783 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;
784 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);
785 explain select * from t1 where a > 0 and a < 50;
786 id select_type table type possible_keys key key_len ref rows Extra
787 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where
789 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;
790 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
791 LOCK TABLES t1 WRITE;
792 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
793 ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
806 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;
807 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
808 LOCK TABLES t1 WRITE;
810 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
811 ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
817 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
819 select id,id3 from t1;
827 create table t1 (a char(20), unique (a(5))) engine=innodb;
829 create table t1 (a char(20), index (a(5))) engine=innodb;
830 show create table t1;
832 t1 CREATE TABLE `t1` (
833 `a` char(20) DEFAULT NULL,
835 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
837 create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
838 insert into t1 values (NULL),(NULL),(NULL);
839 delete from t1 where a=3;
840 insert into t1 values (NULL);
846 alter table t1 add b int;
855 id int auto_increment primary key,
856 name varchar(32) not null,
861 insert into t1 values (1,'one','one value',101),
862 (2,'two','two value',102),(3,'three','three value',103);
864 replace into t1 (value,name,uid) values ('other value','two',102);
865 delete from t1 where uid=102;
867 replace into t1 (value,name,uid) values ('other value','two',102);
869 replace into t1 (value,name,uid) values ('other value','two',102);
873 3 three three value 103
874 6 two other value 102
876 create database mysqltest;
877 create table mysqltest.t1 (a int not null) engine= innodb;
878 insert into mysqltest.t1 values(1);
879 create table mysqltest.t2 (a int not null) engine= myisam;
880 insert into mysqltest.t2 values(1);
881 create table mysqltest.t3 (a int not null) engine= heap;
882 insert into mysqltest.t3 values(1);
884 drop database mysqltest;
885 show tables from mysqltest;
886 ERROR 42000: Unknown database 'mysqltest'
888 create table t1 (a int not null) engine= innodb;
889 insert into t1 values(1),(2);
896 insert into t1 values(1),(2);
903 create table t1 (a int not null) engine= innodb;
904 insert into t1 values(1),(2);
906 insert into t1 values(1),(2);
912 insert into t1 values(1),(2);
917 create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
918 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
919 explain select * from t1 order by a;
920 id select_type table type possible_keys key key_len ref rows Extra
921 1 SIMPLE t1 index NULL PRIMARY 4 NULL #
922 explain select * from t1 order by b;
923 id select_type table type possible_keys key key_len ref rows Extra
924 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
925 explain select * from t1 order by c;
926 id select_type table type possible_keys key key_len ref rows Extra
927 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
928 explain select a from t1 order by a;
929 id select_type table type possible_keys key key_len ref rows Extra
930 1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index
931 explain select b from t1 order by b;
932 id select_type table type possible_keys key key_len ref rows Extra
933 1 SIMPLE t1 index NULL b 4 NULL # Using index
934 explain select a,b from t1 order by b;
935 id select_type table type possible_keys key key_len ref rows Extra
936 1 SIMPLE t1 index NULL b 4 NULL # Using index
937 explain select a,b from t1;
938 id select_type table type possible_keys key key_len ref rows Extra
939 1 SIMPLE t1 index NULL b 4 NULL # Using index
940 explain select a,b,c from t1;
941 id select_type table type possible_keys key key_len ref rows Extra
942 1 SIMPLE t1 ALL NULL NULL NULL NULL #
944 create table t1 (t int not null default 1, key (t)) engine=innodb;
946 Field Type Null Key Default Extra
950 number bigint(20) NOT NULL default '0',
951 cname char(15) NOT NULL default '',
952 carrier_id smallint(6) NOT NULL default '0',
953 privacy tinyint(4) NOT NULL default '0',
954 last_mod_date timestamp NOT NULL,
955 last_mod_id smallint(6) NOT NULL default '0',
956 last_app_date timestamp NOT NULL,
957 last_app_id smallint(6) default '-1',
958 version smallint(6) NOT NULL default '0',
959 assigned_scps int(11) default '0',
960 status tinyint(4) default '0'
962 INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
963 INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
964 INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
965 INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
966 INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
967 INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
969 number bigint(20) NOT NULL default '0',
970 cname char(15) NOT NULL default '',
971 carrier_id smallint(6) NOT NULL default '0',
972 privacy tinyint(4) NOT NULL default '0',
973 last_mod_date timestamp NOT NULL,
974 last_mod_id smallint(6) NOT NULL default '0',
975 last_app_date timestamp NOT NULL,
976 last_app_id smallint(6) default '-1',
977 version smallint(6) NOT NULL default '0',
978 assigned_scps int(11) default '0',
979 status tinyint(4) default '0'
981 INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
982 INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
983 INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
984 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,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 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1
988 9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0
989 650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1
990 302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0
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 4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1
996 9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0
997 650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1
998 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
999 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);
1001 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1002 6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0
1003 333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0
1005 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1006 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1008 number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status
1009 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0
1011 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;
1013 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1014 SELECT @@tx_isolation,@@global.tx_isolation;
1015 @@tx_isolation @@global.tx_isolation
1016 SERIALIZABLE REPEATABLE-READ
1017 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
1018 select id, code, name from t1 order by id;
1025 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1026 insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
1027 select id, code, name from t1 order by id;
1035 SET binlog_format='MIXED';
1037 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1038 insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
1039 select id, code, name from t1 order by id;
1050 create table t1 (n int(10), d int(10)) engine=innodb;
1051 create table t2 (n int(10), d int(10)) engine=innodb;
1052 insert into t1 values(1,1),(1,2);
1053 insert into t2 values(1,10),(2,20);
1054 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
1064 drop table if exists t1, t2;
1065 CREATE TABLE t1 (a int, PRIMARY KEY (a));
1066 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1067 create trigger trg_del_t2 after delete on t2 for each row
1068 insert into t1 values (1);
1069 insert into t1 values (1);
1070 insert into t2 values (1),(2);
1072 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1073 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1077 drop table if exists t1, t2;
1078 CREATE TABLE t1 (a int, PRIMARY KEY (a));
1079 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
1080 create trigger trg_del_t2 after delete on t2 for each row
1081 insert into t1 values (1);
1082 insert into t1 values (1);
1083 insert into t2 values (1),(2);
1085 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1086 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
1090 create table t1 (a int, b int) engine=innodb;
1091 insert into t1 values(20,null);
1092 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1094 b ifnull(t2.b,"this is null")
1096 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1097 t2.b=t3.a order by 1;
1098 b ifnull(t2.b,"this is null")
1100 insert into t1 values(10,null);
1101 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
1102 t2.b=t3.a order by 1;
1103 b ifnull(t2.b,"this is null")
1107 create table t1 (a varchar(10) not null) engine=myisam;
1108 create table t2 (b varchar(10) not null unique) engine=innodb;
1109 select t1.a from t1,t2 where t1.a=t2.b;
1112 create table t1 (a int not null, b int, primary key (a)) engine = innodb;
1113 create table t2 (a int not null, b int, primary key (a)) engine = innodb;
1114 insert into t1 values (10, 20);
1115 insert into t2 values (10, 20);
1116 update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
1118 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1119 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;
1120 insert into t1 set id=1;
1121 insert into t2 set id=1, t1_id=1;
1122 delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
1128 CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1129 CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
1130 INSERT INTO t1 VALUES(1);
1131 INSERT INTO t2 VALUES(1, 1);
1135 UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
1139 UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
1145 CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1146 CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
1147 CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
1148 INSERT INTO t3 VALUES("my-test-1", "my-test-2");
1150 INSERT INTO t1 VALUES("this-key", "will disappear");
1151 INSERT INTO t2 VALUES("this-key", "will also disappear");
1152 DELETE FROM t3 WHERE id1="my-test-1";
1155 this-key will disappear
1158 this-key will also disappear
1169 SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
1174 DROP TABLE t1,t2,t3;
1175 CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
1176 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1177 UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
1190 CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
1191 CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1193 INSERT INTO t1 ( B_ID ) VALUES ( 1 );
1194 INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
1197 Warning 1196 Some non-transactional changed tables couldn't be rolled back
1201 create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
1202 insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
1203 select distinct parent,child from t1 order by parent;
1210 create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
1211 create table t2 (a int not null auto_increment primary key, b int);
1212 insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
1213 insert into t2 (a) select b from t1;
1214 insert into t1 (b) select b from t2;
1215 insert into t2 (a) select b from t1;
1216 insert into t1 (a) select b from t2;
1217 insert into t2 (a) select b from t1;
1218 insert into t1 (a) select b from t2;
1219 insert into t2 (a) select b from t1;
1220 insert into t1 (a) select b from t2;
1221 insert into t2 (a) select b from t1;
1222 insert into t1 (a) select b from t2;
1223 select count(*) from t1;
1226 explain select * from t1 where c between 1 and 2500;
1227 id select_type table type possible_keys key key_len ref rows Extra
1228 1 SIMPLE t1 range c c 5 NULL # Using where
1230 explain select * from t1 where c between 1 and 2500;
1231 id select_type table type possible_keys key key_len ref rows Extra
1232 1 SIMPLE t1 ALL c NULL NULL NULL # Using where
1234 create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
1235 insert into t1 (id) values (null),(null),(null),(null),(null);
1236 update t1 set fk=69 where fk is null order by id limit 1;
1245 create table t1 (a int not null, b int not null, key (a));
1246 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);
1248 update t1 set b=(@tmp:=@tmp+1) order by a;
1249 update t1 set b=99 where a=1 order by b asc limit 1;
1250 update t1 set b=100 where a=1 order by b desc limit 2;
1251 update t1 set a=a+10+b where a=1 order by b;
1252 select * from t1 order by a,b;
1267 create table t1 ( c char(8) not null ) engine=innodb;
1268 insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
1269 insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
1270 alter table t1 add b char(8) not null;
1271 alter table t1 add a char(8) not null;
1272 alter table t1 add primary key (a,b,c);
1273 update t1 set a=c, b=c;
1274 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;
1275 insert into t2 select * from t1;
1276 delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1279 create table t1 (a integer auto_increment primary key) engine=innodb;
1280 insert into t1 (a) values (NULL),(NULL);
1282 insert into t1 (a) values (NULL),(NULL);
1288 CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1289 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;
1291 create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1292 insert into `t1`values ( 1 ) ;
1293 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;
1294 insert into `t2`values ( 1 ) ;
1295 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;
1296 insert into `t3`values ( 1 ) ;
1297 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1298 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`))
1299 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;
1300 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`))
1301 update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1302 ERROR 42S22: Unknown column 't1.id' in 'where clause'
1303 drop table t3,t2,t1;
1305 c1 VARCHAR(8), c2 VARCHAR(8),
1306 PRIMARY KEY (c1, c2)
1310 c1 VARCHAR(8) UNIQUE,
1311 FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE
1313 INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue');
1314 INSERT INTO t2 VALUES (10, 'old'), (20, 'other');
1315 UPDATE t1 SET c1 = 'other' WHERE c1 = 'old';
1316 ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 2 would lead to a duplicate entry
1322 foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1323 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1324 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1325 delete from t1 where id=0;
1326 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)
1327 delete from t1 where id=15;
1328 delete from t1 where id=0;
1330 CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1331 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1332 (stamp))ENGINE=InnoDB;
1333 insert into t1 values (1),(2),(3);
1334 insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1336 Warning 1265 Data truncated for column 'stamp' at row 3
1337 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1338 '20020204120000' GROUP BY col1;
1346 `id` int(10) unsigned NOT NULL auto_increment,
1347 `id_object` int(10) unsigned default '0',
1348 `id_version` int(10) unsigned NOT NULL default '1',
1349 `label` varchar(100) NOT NULL default '',
1352 KEY `id_object` (`id_object`),
1353 KEY `id_version` (`id_version`)
1355 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);
1357 `id` int(10) unsigned NOT NULL auto_increment,
1358 `id_version` int(10) unsigned NOT NULL default '1',
1360 KEY `id_version` (`id_version`)
1362 INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1363 SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1364 (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1365 ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1372 3525 Fournisseur Test
1374 create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1375 create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1376 create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1377 insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1378 insert t2 select * from t1;
1379 insert t3 select * from t1;
1380 checksum table t1, t2, t3, t4 quick;
1387 Error 1146 Table 'test.t4' doesn't exist
1388 checksum table t1, t2, t3, t4;
1395 Error 1146 Table 'test.t4' doesn't exist
1396 checksum table t1, t2, t3, t4 extended;
1403 Error 1146 Table 'test.t4' doesn't exist
1404 drop table t1,t2,t3;
1405 create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1406 insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1407 select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1419 create table t1 (a int) engine=innodb;
1420 create table t2 like t1;
1422 create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1423 create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1424 show create table t1;
1426 t1 CREATE TABLE `t1` (
1427 `id` int(11) NOT NULL,
1428 `id2` int(11) NOT NULL,
1429 UNIQUE KEY `id` (`id`,`id2`)
1430 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1431 show create table t2;
1433 t2 CREATE TABLE `t2` (
1434 `id` int(11) NOT NULL,
1435 KEY `t1_id_fk` (`id`),
1436 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1437 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1438 create index id on t2 (id);
1439 show create table t2;
1441 t2 CREATE TABLE `t2` (
1442 `id` int(11) NOT NULL,
1444 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1445 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1446 create index id2 on t2 (id);
1447 show create table t2;
1449 t2 CREATE TABLE `t2` (
1450 `id` int(11) NOT NULL,
1453 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1454 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1455 drop index id2 on t2;
1456 drop index id on t2;
1457 ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint
1458 show create table t2;
1460 t2 CREATE TABLE `t2` (
1461 `id` int(11) NOT NULL,
1463 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1464 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1466 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;
1467 show create table t2;
1469 t2 CREATE TABLE `t2` (
1470 `id` int(11) NOT NULL,
1471 `id2` int(11) NOT NULL,
1472 KEY `t1_id_fk` (`id`,`id2`),
1473 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1474 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1475 create unique index id on t2 (id,id2);
1476 show create table t2;
1478 t2 CREATE TABLE `t2` (
1479 `id` int(11) NOT NULL,
1480 `id2` int(11) NOT NULL,
1481 UNIQUE KEY `id` (`id`,`id2`),
1482 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)
1483 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1485 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;
1486 show create table t2;
1488 t2 CREATE TABLE `t2` (
1489 `id` int(11) NOT NULL,
1490 `id2` int(11) NOT NULL,
1491 UNIQUE KEY `id` (`id`,`id2`),
1492 KEY `t1_id_fk` (`id2`,`id`),
1493 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1494 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1496 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;
1497 show create table t2;
1499 t2 CREATE TABLE `t2` (
1500 `id` int(11) NOT NULL,
1501 `id2` int(11) NOT NULL,
1502 UNIQUE KEY `id` (`id`,`id2`),
1503 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1504 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1506 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;
1507 show create table t2;
1509 t2 CREATE TABLE `t2` (
1510 `id` int(11) NOT NULL,
1511 `id2` int(11) NOT NULL,
1512 UNIQUE KEY `id` (`id`,`id2`),
1513 KEY `t1_id_fk` (`id2`,`id`),
1514 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)
1515 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1517 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;
1518 show create table t2;
1520 t2 CREATE TABLE `t2` (
1521 `id` int(11) NOT NULL AUTO_INCREMENT,
1522 `id2` int(11) NOT NULL,
1524 KEY `id` (`id`,`id2`),
1525 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1526 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1528 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;
1529 show create table t2;
1531 t2 CREATE TABLE `t2` (
1532 `id` int(11) NOT NULL AUTO_INCREMENT,
1533 `id2` int(11) NOT NULL,
1534 KEY `t1_id_fk` (`id`),
1535 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1536 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1537 alter table t2 add index id_test (id), add index id_test2 (id,id2);
1538 show create table t2;
1540 t2 CREATE TABLE `t2` (
1541 `id` int(11) NOT NULL AUTO_INCREMENT,
1542 `id2` int(11) NOT NULL,
1543 KEY `id_test` (`id`),
1544 KEY `id_test2` (`id`,`id2`),
1545 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
1546 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1548 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;
1549 ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
1550 create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1551 show create table t2;
1553 t2 CREATE TABLE `t2` (
1554 `a` int(11) NOT NULL AUTO_INCREMENT,
1555 `b` int(11) DEFAULT NULL,
1557 UNIQUE KEY `b_2` (`b`),
1559 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1560 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1562 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;
1563 show create table t2;
1565 t2 CREATE TABLE `t2` (
1566 `a` int(11) NOT NULL AUTO_INCREMENT,
1567 `b` int(11) DEFAULT NULL,
1569 UNIQUE KEY `b` (`b`),
1570 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),
1571 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`)
1572 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1574 create table t1 (c char(10), index (c,c)) engine=innodb;
1575 ERROR 42S21: Duplicate column name 'c'
1576 create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1577 ERROR 42S21: Duplicate column name 'c1'
1578 create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1579 ERROR 42S21: Duplicate column name 'c1'
1580 create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1581 ERROR 42S21: Duplicate column name 'c1'
1582 create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1583 alter table t1 add key (c1,c1);
1584 ERROR 42S21: Duplicate column name 'c1'
1585 alter table t1 add key (c2,c1,c1);
1586 ERROR 42S21: Duplicate column name 'c1'
1587 alter table t1 add key (c1,c2,c1);
1588 ERROR 42S21: Duplicate column name 'c1'
1589 alter table t1 add key (c1,c1,c2);
1590 ERROR 42S21: Duplicate column name 'c1'
1592 create table t1(a int(1) , b int(1)) engine=innodb;
1593 insert into t1 values ('1111', '3333');
1594 select distinct concat(a, b) from t1;
1598 CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1599 SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1600 ERROR HY000: The used table type doesn't support FULLTEXT indexes
1602 CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1603 INSERT INTO t1 VALUES (1),(2),(3);
1604 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),
1605 CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1606 INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1607 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;
1614 create temporary table t1 (a int) engine=innodb;
1615 insert into t1 values (4711);
1617 insert into t1 values (42);
1622 create table t1 (a int) engine=innodb;
1623 insert into t1 values (4711);
1625 insert into t1 values (42);
1630 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;
1631 insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1632 select * from t1 order by a,b,c,d;
1637 explain select * from t1 order by a,b,c,d;
1638 id select_type table type possible_keys key key_len ref rows Extra
1639 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1641 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1642 insert into t1 values ('8', '6'), ('4', '7');
1643 select min(a) from t1;
1646 select min(b) from t1 where a='8';
1650 create table t1 (x bigint unsigned not null primary key) engine=innodb;
1651 insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1654 18446744073709551600
1655 18446744073709551601
1656 select count(*) from t1 where x>0;
1659 select count(*) from t1 where x=0;
1662 select count(*) from t1 where x<0;
1665 select count(*) from t1 where x < -16;
1668 select count(*) from t1 where x = -16;
1671 explain select count(*) from t1 where x > -16;
1672 id select_type table type possible_keys key key_len ref rows Extra
1673 1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index
1674 select count(*) from t1 where x > -16;
1677 select * from t1 where x > -16;
1679 18446744073709551600
1680 18446744073709551601
1681 select count(*) from t1 where x = 18446744073709551601;
1685 SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
1688 SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
1691 SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted';
1692 variable_value - @innodb_rows_deleted_orig
1694 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
1695 variable_value - @innodb_rows_inserted_orig
1697 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
1698 variable_value - @innodb_rows_updated_orig
1700 SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
1701 variable_value - @innodb_row_lock_waits_orig
1703 SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
1704 variable_value - @innodb_row_lock_current_waits_orig
1706 SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
1707 variable_value - @innodb_row_lock_time_orig
1709 SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
1710 variable_value - @innodb_row_lock_time_max_orig
1712 SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
1713 variable_value - @innodb_row_lock_time_avg_orig
1715 SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops;
1716 show variables like "innodb_sync_spin_loops";
1718 innodb_sync_spin_loops 30
1719 set global innodb_sync_spin_loops=1000;
1720 show variables like "innodb_sync_spin_loops";
1722 innodb_sync_spin_loops 1000
1723 set global innodb_sync_spin_loops=0;
1724 show variables like "innodb_sync_spin_loops";
1726 innodb_sync_spin_loops 0
1727 set global innodb_sync_spin_loops=20;
1728 show variables like "innodb_sync_spin_loops";
1730 innodb_sync_spin_loops 20
1731 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
1732 show variables like "innodb_thread_concurrency";
1734 innodb_thread_concurrency 0
1735 set global innodb_thread_concurrency=1001;
1737 Warning 1292 Truncated incorrect thread_concurrency value: '1001'
1738 show variables like "innodb_thread_concurrency";
1740 innodb_thread_concurrency 1000
1741 set global innodb_thread_concurrency=0;
1742 show variables like "innodb_thread_concurrency";
1744 innodb_thread_concurrency 0
1745 set global innodb_thread_concurrency=16;
1746 show variables like "innodb_thread_concurrency";
1748 innodb_thread_concurrency 16
1749 show variables like "innodb_concurrency_tickets";
1751 innodb_concurrency_tickets 500
1752 set global innodb_concurrency_tickets=1000;
1753 show variables like "innodb_concurrency_tickets";
1755 innodb_concurrency_tickets 1000
1756 set global innodb_concurrency_tickets=0;
1758 Warning 1292 Truncated incorrect concurrency_tickets value: '0'
1759 show variables like "innodb_concurrency_tickets";
1761 innodb_concurrency_tickets 1
1762 set global innodb_concurrency_tickets=500;
1763 show variables like "innodb_concurrency_tickets";
1765 innodb_concurrency_tickets 500
1766 show variables like "innodb_thread_sleep_delay";
1768 innodb_thread_sleep_delay 10000
1769 set global innodb_thread_sleep_delay=100000;
1770 show variables like "innodb_thread_sleep_delay";
1772 innodb_thread_sleep_delay 100000
1773 set global innodb_thread_sleep_delay=0;
1774 show variables like "innodb_thread_sleep_delay";
1776 innodb_thread_sleep_delay 0
1777 set global innodb_thread_sleep_delay=10000;
1778 show variables like "innodb_thread_sleep_delay";
1780 innodb_thread_sleep_delay 10000
1781 set storage_engine=INNODB;
1782 set session old_alter_table=1;
1783 drop table if exists t1,t2,t3;
1784 --- Testing varchar ---
1785 --- Testing varchar ---
1786 create table t1 (v varchar(10), c char(10), t text);
1787 insert into t1 values('+ ', '+ ', '+ ');
1788 set @a=repeat(' ',20);
1789 insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1791 Note 1265 Data truncated for column 'v' at row 1
1792 select concat('*',v,'*',c,'*',t,'*') from t1;
1793 concat('*',v,'*',c,'*',t,'*')
1796 show create table t1;
1798 t1 CREATE TABLE `t1` (
1799 `v` varchar(10) DEFAULT NULL,
1800 `c` char(10) DEFAULT NULL,
1802 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1803 create table t2 like t1;
1804 show create table t2;
1806 t2 CREATE TABLE `t2` (
1807 `v` varchar(10) DEFAULT NULL,
1808 `c` char(10) DEFAULT NULL,
1810 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1811 create table t3 select * from t1;
1812 show create table t3;
1814 t3 CREATE TABLE `t3` (
1815 `v` varchar(10) DEFAULT NULL,
1816 `c` char(10) DEFAULT NULL,
1818 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1819 alter table t1 modify c varchar(10);
1820 show create table t1;
1822 t1 CREATE TABLE `t1` (
1823 `v` varchar(10) DEFAULT NULL,
1824 `c` varchar(10) DEFAULT NULL,
1826 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1827 alter table t1 modify v char(10);
1828 show create table t1;
1830 t1 CREATE TABLE `t1` (
1831 `v` char(10) DEFAULT NULL,
1832 `c` varchar(10) DEFAULT NULL,
1834 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1835 alter table t1 modify t varchar(10);
1837 Note 1265 Data truncated for column 't' at row 2
1838 show create table t1;
1840 t1 CREATE TABLE `t1` (
1841 `v` char(10) DEFAULT NULL,
1842 `c` varchar(10) DEFAULT NULL,
1843 `t` varchar(10) DEFAULT NULL
1844 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1845 select concat('*',v,'*',c,'*',t,'*') from t1;
1846 concat('*',v,'*',c,'*',t,'*')
1849 drop table t1,t2,t3;
1850 create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1851 show create table t1;
1853 t1 CREATE TABLE `t1` (
1854 `v` varchar(10) DEFAULT NULL,
1855 `c` char(10) DEFAULT NULL,
1860 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1861 select count(*) from t1;
1864 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1865 select count(*) from t1 where v='a';
1868 select count(*) from t1 where c='a';
1871 select count(*) from t1 where t='a';
1874 select count(*) from t1 where v='a ';
1877 select count(*) from t1 where c='a ';
1880 select count(*) from t1 where t='a ';
1883 select count(*) from t1 where v between 'a' and 'a ';
1886 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1889 select count(*) from t1 where v like 'a%';
1892 select count(*) from t1 where c like 'a%';
1895 select count(*) from t1 where t like 'a%';
1898 select count(*) from t1 where v like 'a %';
1901 explain select count(*) from t1 where v='a ';
1902 id select_type table type possible_keys key key_len ref rows Extra
1903 1 SIMPLE t1 ref v v 13 const # Using where; Using index
1904 explain select count(*) from t1 where c='a ';
1905 id select_type table type possible_keys key key_len ref rows Extra
1906 1 SIMPLE t1 ref c c 11 const # Using where; Using index
1907 explain select count(*) from t1 where t='a ';
1908 id select_type table type possible_keys key key_len ref rows Extra
1909 1 SIMPLE t1 ref t t 13 const # Using where
1910 explain select count(*) from t1 where v like 'a%';
1911 id select_type table type possible_keys key key_len ref rows Extra
1912 1 SIMPLE t1 range v v 13 NULL # Using where; Using index
1913 explain select count(*) from t1 where v between 'a' and 'a ';
1914 id select_type table type possible_keys key key_len ref rows Extra
1915 1 SIMPLE t1 ref v v 13 const # Using where; Using index
1916 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1917 id select_type table type possible_keys key key_len ref rows Extra
1918 1 SIMPLE t1 ref v v 13 const # Using where; Using index
1919 alter table t1 add unique(v);
1920 ERROR 23000: Duplicate entry '{ ' for key 'v_2'
1921 alter table t1 add key(v);
1922 select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1934 explain select * from t1 where v='a';
1935 id select_type table type possible_keys key key_len ref rows Extra
1936 1 SIMPLE t1 ref v,v_2 # 13 const # Using where
1937 select v,count(*) from t1 group by v limit 10;
1949 select v,count(t) from t1 group by v limit 10;
1961 select v,count(c) from t1 group by v limit 10;
1973 select sql_big_result v,count(t) from t1 group by v limit 10;
1985 select sql_big_result v,count(c) from t1 group by v limit 10;
1997 select c,count(*) from t1 group by c limit 10;
2009 select c,count(t) from t1 group by c limit 10;
2021 select sql_big_result c,count(t) from t1 group by c limit 10;
2033 select t,count(*) from t1 group by t limit 10;
2045 select t,count(t) from t1 group by t limit 10;
2057 select sql_big_result t,count(t) from t1 group by t limit 10;
2069 alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
2070 show create table t1;
2072 t1 CREATE TABLE `t1` (
2073 `v` varchar(300) DEFAULT NULL,
2074 `c` char(10) DEFAULT NULL,
2079 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2080 select count(*) from t1 where v='a';
2083 select count(*) from t1 where v='a ';
2086 select count(*) from t1 where v between 'a' and 'a ';
2089 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2092 select count(*) from t1 where v like 'a%';
2095 select count(*) from t1 where v like 'a %';
2098 explain select count(*) from t1 where v='a ';
2099 id select_type table type possible_keys key key_len ref rows Extra
2100 1 SIMPLE t1 ref v v 303 const # Using where; Using index
2101 explain select count(*) from t1 where v like 'a%';
2102 id select_type table type possible_keys key key_len ref rows Extra
2103 1 SIMPLE t1 range v v 303 NULL # Using where; Using index
2104 explain select count(*) from t1 where v between 'a' and 'a ';
2105 id select_type table type possible_keys key key_len ref rows Extra
2106 1 SIMPLE t1 ref v v 303 const # Using where; Using index
2107 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2108 id select_type table type possible_keys key key_len ref rows Extra
2109 1 SIMPLE t1 ref v v 303 const # Using where; Using index
2110 explain select * from t1 where v='a';
2111 id select_type table type possible_keys key key_len ref rows Extra
2112 1 SIMPLE t1 ref v v 303 const # Using where
2113 select v,count(*) from t1 group by v limit 10;
2125 select v,count(t) from t1 group by v limit 10;
2137 select sql_big_result v,count(t) from t1 group by v limit 10;
2149 alter table t1 drop key v, add key v (v(30));
2150 show create table t1;
2152 t1 CREATE TABLE `t1` (
2153 `v` varchar(300) DEFAULT NULL,
2154 `c` char(10) DEFAULT NULL,
2159 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2160 select count(*) from t1 where v='a';
2163 select count(*) from t1 where v='a ';
2166 select count(*) from t1 where v between 'a' and 'a ';
2169 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2172 select count(*) from t1 where v like 'a%';
2175 select count(*) from t1 where v like 'a %';
2178 explain select count(*) from t1 where v='a ';
2179 id select_type table type possible_keys key key_len ref rows Extra
2180 1 SIMPLE t1 ref v v 33 const # Using where
2181 explain select count(*) from t1 where v like 'a%';
2182 id select_type table type possible_keys key key_len ref rows Extra
2183 1 SIMPLE t1 range v v 33 NULL # Using where
2184 explain select count(*) from t1 where v between 'a' and 'a ';
2185 id select_type table type possible_keys key key_len ref rows Extra
2186 1 SIMPLE t1 ref v v 33 const # Using where
2187 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
2188 id select_type table type possible_keys key key_len ref rows Extra
2189 1 SIMPLE t1 ref v v 33 const # Using where
2190 explain select * from t1 where v='a';
2191 id select_type table type possible_keys key key_len ref rows Extra
2192 1 SIMPLE t1 ref v v 33 const # Using where
2193 select v,count(*) from t1 group by v limit 10;
2205 select v,count(t) from t1 group by v limit 10;
2217 select sql_big_result v,count(t) from t1 group by v limit 10;
2229 alter table t1 modify v varchar(600), drop key v, add key v (v);
2230 show create table t1;
2232 t1 CREATE TABLE `t1` (
2233 `v` varchar(600) DEFAULT NULL,
2234 `c` char(10) DEFAULT NULL,
2239 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2240 select v,count(*) from t1 group by v limit 10;
2252 select v,count(t) from t1 group by v limit 10;
2264 select sql_big_result v,count(t) from t1 group by v limit 10;
2277 create table t1 (a char(10), unique (a));
2278 insert into t1 values ('a ');
2279 insert into t1 values ('a ');
2280 ERROR 23000: Duplicate entry 'a' for key 'a'
2281 alter table t1 modify a varchar(10);
2282 insert into t1 values ('a '),('a '),('a '),('a ');
2283 ERROR 23000: Duplicate entry 'a ' for key 'a'
2284 insert into t1 values ('a ');
2285 ERROR 23000: Duplicate entry 'a ' for key 'a'
2286 insert into t1 values ('a ');
2287 ERROR 23000: Duplicate entry 'a ' for key 'a'
2288 insert into t1 values ('a ');
2289 ERROR 23000: Duplicate entry 'a ' for key 'a'
2290 update t1 set a='a ' where a like 'a%';
2291 select concat(a,'.') from t1;
2294 update t1 set a='abc ' where a like 'a ';
2295 select concat(a,'.') from t1;
2298 update t1 set a='a ' where a like 'a %';
2299 select concat(a,'.') from t1;
2302 update t1 set a='a ' where a like 'a ';
2303 select concat(a,'.') from t1;
2307 create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
2308 show create table t1;
2310 t1 CREATE TABLE `t1` (
2311 `v` varchar(10) DEFAULT NULL,
2312 `c` char(10) DEFAULT NULL,
2317 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2319 create table t1 (v char(10) character set utf8);
2320 show create table t1;
2322 t1 CREATE TABLE `t1` (
2323 `v` char(10) CHARACTER SET utf8 DEFAULT NULL
2324 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2326 create table t1 (v varchar(10), c char(10)) row_format=fixed;
2328 Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
2329 show create table t1;
2331 t1 CREATE TABLE `t1` (
2332 `v` varchar(10) DEFAULT NULL,
2333 `c` char(10) DEFAULT NULL
2334 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
2335 insert into t1 values('a','a'),('a ','a ');
2336 select concat('*',v,'*',c,'*') from t1;
2337 concat('*',v,'*',c,'*')
2341 create table t1 (v varchar(65530), key(v(10)));
2342 insert into t1 values(repeat('a',65530));
2343 select length(v) from t1 where v=repeat('a',65530);
2347 create table t1(a int, b varchar(12), key ba(b, a));
2348 insert into t1 values (1, 'A'), (20, NULL);
2349 explain select * from t1 where a=20 and b is null;
2350 id select_type table type possible_keys key key_len ref rows Extra
2351 1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index
2352 select * from t1 where a=20 and b is null;
2356 set session old_alter_table=0;
2357 create table t1 (v varchar(65530), key(v));
2359 Warning 1071 Specified key was too long; max key length is 767 bytes
2361 create table t1 (v varchar(65536));
2363 Note 1246 Converting column 'v' from VARCHAR to TEXT
2364 show create table t1;
2366 t1 CREATE TABLE `t1` (
2368 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2370 create table t1 (v varchar(65530) character set utf8);
2372 Note 1246 Converting column 'v' from VARCHAR to TEXT
2373 show create table t1;
2375 t1 CREATE TABLE `t1` (
2376 `v` mediumtext CHARACTER SET utf8
2377 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2379 set storage_engine=MyISAM;
2380 create table t1 (v varchar(16384)) engine=innodb;
2382 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
2383 insert into t1 values ('8', '6'), ('4', '7');
2384 select min(a) from t1;
2387 select min(b) from t1 where a='8';
2391 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
2392 insert into t1 (b) values (1);
2393 replace into t1 (b) values (2), (1), (3);
2400 insert into t1 (b) values (1);
2401 replace into t1 (b) values (2);
2402 replace into t1 (b) values (1);
2403 replace into t1 (b) values (3);
2410 create table t1 (rowid int not null auto_increment, val int not null,primary
2411 key (rowid), unique(val)) engine=innodb;
2412 replace into t1 (val) values ('1'),('2');
2413 replace into t1 (val) values ('1'),('2');
2414 insert into t1 (val) values ('1'),('2');
2415 ERROR 23000: Duplicate entry '1' for key 'val'
2421 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
2422 insert into t1 (val) values (1);
2423 update t1 set a=2 where a=1;
2424 insert into t1 (val) values (1);
2425 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
2430 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
2431 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
2432 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
2435 SELECT GRADE FROM t1 WHERE GRADE= 151;
2439 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
2440 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
2441 insert into t2 values ('aa','cc');
2442 insert into t1 values ('aa','bb'),('aa','cc');
2443 delete t1 from t1,t2 where f1=f3 and f4='cc';
2448 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
2451 id INTEGER NOT NULL,
2452 FOREIGN KEY (id) REFERENCES t1 (id)
2454 INSERT INTO t1 (id) VALUES (NULL);
2459 INSERT INTO t1 (id) VALUES (NULL);
2465 INSERT INTO t1 (id) VALUES (NULL);
2474 CREATE TEMPORARY TABLE t2
2476 id INT NOT NULL PRIMARY KEY,
2478 FOREIGN KEY (b) REFERENCES test.t1(id)
2480 Got one of the listed errors
2482 create table t1 (col1 varchar(2000), index (col1(767)))
2483 character set = latin1 engine = innodb;
2484 create table t2 (col1 char(255), index (col1))
2485 character set = latin1 engine = innodb;
2486 create table t3 (col1 binary(255), index (col1))
2487 character set = latin1 engine = innodb;
2488 create table t4 (col1 varchar(767), index (col1))
2489 character set = latin1 engine = innodb;
2490 create table t5 (col1 varchar(767) primary key)
2491 character set = latin1 engine = innodb;
2492 create table t6 (col1 varbinary(767) primary key)
2493 character set = latin1 engine = innodb;
2494 create table t7 (col1 text, index(col1(767)))
2495 character set = latin1 engine = innodb;
2496 create table t8 (col1 blob, index(col1(767)))
2497 character set = latin1 engine = innodb;
2498 create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
2499 character set = latin1 engine = innodb;
2500 show create table t9;
2502 t9 CREATE TABLE `t9` (
2503 `col1` varchar(512) DEFAULT NULL,
2504 `col2` varchar(512) DEFAULT NULL,
2505 KEY `col1` (`col1`,`col2`)
2506 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2507 drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
2508 create table t1 (col1 varchar(768), index(col1))
2509 character set = latin1 engine = innodb;
2511 Warning 1071 Specified key was too long; max key length is 767 bytes
2512 create table t2 (col1 varbinary(768), index(col1))
2513 character set = latin1 engine = innodb;
2515 Warning 1071 Specified key was too long; max key length is 767 bytes
2516 create table t3 (col1 text, index(col1(768)))
2517 character set = latin1 engine = innodb;
2519 Warning 1071 Specified key was too long; max key length is 767 bytes
2520 create table t4 (col1 blob, index(col1(768)))
2521 character set = latin1 engine = innodb;
2523 Warning 1071 Specified key was too long; max key length is 767 bytes
2524 show create table t1;
2526 t1 CREATE TABLE `t1` (
2527 `col1` varchar(768) DEFAULT NULL,
2528 KEY `col1` (`col1`(767))
2529 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2530 drop table t1, t2, t3, t4;
2531 create table t1 (col1 varchar(768) primary key)
2532 character set = latin1 engine = innodb;
2533 ERROR 42000: Specified key was too long; max key length is 767 bytes
2534 create table t2 (col1 varbinary(768) primary key)
2535 character set = latin1 engine = innodb;
2536 ERROR 42000: Specified key was too long; max key length is 767 bytes
2537 create table t3 (col1 text, primary key(col1(768)))
2538 character set = latin1 engine = innodb;
2539 ERROR 42000: Specified key was too long; max key length is 767 bytes
2540 create table t4 (col1 blob, primary key(col1(768)))
2541 character set = latin1 engine = innodb;
2542 ERROR 42000: Specified key was too long; max key length is 767 bytes
2550 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
2552 INSERT INTO t2 VALUES(2);
2553 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2554 INSERT INTO t1 VALUES(1);
2555 INSERT INTO t2 VALUES(1);
2556 DELETE FROM t1 WHERE id = 1;
2557 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2559 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
2560 SET FOREIGN_KEY_CHECKS=0;
2562 SET FOREIGN_KEY_CHECKS=1;
2563 INSERT INTO t2 VALUES(3);
2564 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
2566 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2567 insert into t1 values (1),(2);
2572 insert into t1 values(3);
2582 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
2583 insert into t1 values (1),(2);
2589 insert into t1 values(3);
2594 set foreign_key_checks=0;
2595 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
2596 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
2597 ERROR HY000: Can't create table 'test.t1' (errno: 150)
2598 set foreign_key_checks=1;
2600 set foreign_key_checks=0;
2601 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2602 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
2603 ERROR HY000: Can't create table 'test.t2' (errno: 150)
2604 set foreign_key_checks=1;
2606 set foreign_key_checks=0;
2607 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
2608 create table t1(a varchar(10) primary key) engine = innodb;
2609 alter table t1 modify column a int;
2610 Got one of the listed errors
2611 set foreign_key_checks=1;
2613 set foreign_key_checks=0;
2614 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2615 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
2616 alter table t1 convert to character set utf8;
2617 set foreign_key_checks=1;
2619 set foreign_key_checks=0;
2620 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
2621 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
2622 rename table t3 to t1;
2623 ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150)
2624 set foreign_key_checks=1;
2626 create table t1(a int primary key) row_format=redundant engine=innodb;
2627 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
2628 create table t3(a int primary key) row_format=compact engine=innodb;
2629 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
2630 insert into t1 values(1);
2631 insert into t3 values(1);
2632 insert into t2 values(2);
2633 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`))
2634 insert into t4 values(2);
2635 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`))
2636 insert into t2 values(1);
2637 insert into t4 values(1);
2639 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`))
2641 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`))
2643 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`))
2645 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`))
2647 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`))
2649 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`))
2654 drop table t4,t3,t2,t1;
2655 create table t1 (a varchar(255) character set utf8,
2656 b varchar(255) character set utf8,
2657 c varchar(255) character set utf8,
2658 d varchar(255) character set utf8,
2659 key (a,b,c,d)) engine=innodb;
2661 create table t1 (a varchar(255) character set utf8,
2662 b varchar(255) character set utf8,
2663 c varchar(255) character set utf8,
2664 d varchar(255) character set utf8,
2665 e varchar(255) character set utf8,
2666 key (a,b,c,d,e)) engine=innodb;
2667 ERROR 42000: Specified key was too long; max key length is 3072 bytes
2668 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
2669 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
2670 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
2671 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
2672 insert into t1 values (0x41),(0x4120),(0x4100);
2673 insert into t2 values (0x41),(0x4120),(0x4100);
2674 ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2675 insert into t2 values (0x41),(0x4120);
2676 insert into t3 values (0x41),(0x4120),(0x4100);
2677 ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
2678 insert into t3 values (0x41),(0x4100);
2679 insert into t4 values (0x41),(0x4120),(0x4100);
2680 ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
2681 insert into t4 values (0x41),(0x4100);
2682 select hex(s1) from t1;
2687 select hex(s1) from t2;
2691 select hex(s1) from t3;
2695 select hex(s1) from t4;
2699 drop table t1,t2,t3,t4;
2700 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
2701 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2702 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
2703 insert into t2 values(0x42);
2704 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)
2705 insert into t2 values(0x41);
2706 select hex(s1) from t2;
2709 update t1 set s1=0x123456 where a=2;
2710 select hex(s1) from t2;
2713 update t1 set s1=0x12 where a=1;
2714 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)
2715 update t1 set s1=0x12345678 where a=1;
2716 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)
2717 update t1 set s1=0x123457 where a=1;
2718 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)
2719 update t1 set s1=0x1220 where a=1;
2720 select hex(s1) from t2;
2723 update t1 set s1=0x1200 where a=1;
2724 select hex(s1) from t2;
2727 update t1 set s1=0x4200 where a=1;
2728 select hex(s1) from t2;
2731 delete from t1 where a=1;
2732 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)
2733 delete from t1 where a=2;
2734 update t2 set s1=0x4120;
2736 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)
2737 delete from t1 where a!=3;
2738 select a,hex(s1) from t1;
2741 select hex(s1) from t2;
2745 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
2746 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
2747 insert into t1 values(1,0x4100),(2,0x41);
2748 insert into t2 values(0x41);
2749 select hex(s1) from t2;
2752 update t1 set s1=0x1234 where a=1;
2753 select hex(s1) from t2;
2756 update t1 set s1=0x12 where a=2;
2757 select hex(s1) from t2;
2760 delete from t1 where a=1;
2761 delete from t1 where a=2;
2762 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)
2763 select a,hex(s1) from t1;
2766 select hex(s1) from t2;
2770 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
2771 CREATE TABLE t2(a INT) ENGINE=InnoDB;
2772 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
2773 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
2774 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
2775 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
2776 SHOW CREATE TABLE t2;
2778 t2 CREATE TABLE `t2` (
2779 `a` int(11) DEFAULT NULL,
2780 KEY `t2_ibfk_0` (`a`)
2781 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2783 create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2784 insert into t1(a) values (1),(2),(3);
2787 update t1 set b = 5 where a = 2;
2788 create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
2790 insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
2791 (11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
2792 (12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
2793 (13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
2794 (14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
2799 create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2800 create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2801 create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2802 create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2803 create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
2804 insert into t1(a) values (1),(2),(3);
2805 insert into t2(a) values (1),(2),(3);
2806 insert into t3(a) values (1),(2),(3);
2807 insert into t4(a) values (1),(2),(3);
2808 insert into t3(a) values (5),(7),(8);
2809 insert into t4(a) values (5),(7),(8);
2810 insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
2811 create trigger t1t before insert on t1 for each row begin
2812 INSERT INTO t2 SET a = NEW.a;
2814 create trigger t2t before insert on t2 for each row begin
2815 DELETE FROM t3 WHERE a = NEW.a;
2817 create trigger t3t before delete on t3 for each row begin
2818 UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
2820 create trigger t4t before update on t4 for each row begin
2821 UPDATE t5 SET b = b + 1 where a = NEW.a;
2825 update t1 set b = b + 5 where a = 1;
2826 update t2 set b = b + 5 where a = 1;
2827 update t3 set b = b + 5 where a = 1;
2828 update t4 set b = b + 5 where a = 1;
2829 insert into t5(a) values(20);
2831 insert into t1(a) values(7);
2832 insert into t2(a) values(8);
2833 delete from t2 where a = 3;
2834 update t4 set b = b + 1 where a = 3;
2840 drop table t1, t2, t3, t4, t5;
2842 field1 varchar(8) NOT NULL DEFAULT '',
2843 field2 varchar(8) NOT NULL DEFAULT '',
2844 PRIMARY KEY (field1, field2)
2847 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
2848 FOREIGN KEY (field1) REFERENCES t1 (field1)
2849 ON DELETE CASCADE ON UPDATE CASCADE
2851 INSERT INTO t1 VALUES ('old', 'somevalu');
2852 INSERT INTO t1 VALUES ('other', 'anyvalue');
2853 INSERT INTO t2 VALUES ('old');
2854 INSERT INTO t2 VALUES ('other');
2855 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
2856 ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
2869 alter table t1 add constraint c2_fk foreign key (c2)
2870 references t2(c1) on delete cascade;
2871 show create table t1;
2873 t1 CREATE TABLE `t1` (
2874 `c1` bigint(20) NOT NULL,
2875 `c2` bigint(20) NOT NULL,
2877 UNIQUE KEY `c2` (`c2`),
2878 CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
2879 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2880 alter table t1 drop foreign key c2_fk;
2881 show create table t1;
2883 t1 CREATE TABLE `t1` (
2884 `c1` bigint(20) NOT NULL,
2885 `c2` bigint(20) NOT NULL,
2887 UNIQUE KEY `c2` (`c2`)
2888 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2890 create table t1(a date) engine=innodb;
2891 create table t2(a date, key(a)) engine=innodb;
2892 insert into t1 values('2005-10-01');
2893 insert into t2 values('2005-10-01');
2894 select * from t1, t2
2895 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2897 2005-10-01 2005-10-01
2899 create table t1 (id int not null, f_id int not null, f int not null,
2900 primary key(f_id, id)) engine=innodb;
2901 create table t2 (id int not null,s_id int not null,s varchar(200),
2902 primary key(id)) engine=innodb;
2903 INSERT INTO t1 VALUES (8, 1, 3);
2904 INSERT INTO t1 VALUES (1, 2, 1);
2905 INSERT INTO t2 VALUES (1, 0, '');
2906 INSERT INTO t2 VALUES (8, 1, '');
2908 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2909 WHERE mm.id IS NULL;
2910 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2911 where mm.id is null lock in share mode;
2914 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2915 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2917 SET binlog_format='MIXED';
2919 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2920 update t1 set b = 5 where b = 1;
2921 SET binlog_format='MIXED';
2923 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2924 select * from t1 where a = 7 and b = 3 for update;
2930 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2931 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2934 select * from t1 lock in share mode;
2942 update t1 set b = 5 where b = 1;
2944 select * from t1 where a = 2 and b = 2 for update;
2945 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2949 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2950 insert into t1 values (1,2),(5,3),(4,2);
2951 create table t2(d int not null, e int, primary key(d)) engine=innodb;
2952 insert into t2 values (8,6),(12,1),(3,1);
2955 select * from t2 for update;
2960 SET binlog_format='MIXED';
2962 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2963 insert into t1 select * from t2;
2964 update t1 set b = (select e from t2 where a = d);
2965 create table t3(d int not null, e int, primary key(d)) engine=innodb
2969 drop table t1, t2, t3;
2970 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2971 insert into t1 values (1,2),(5,3),(4,2);
2972 create table t2(a int not null, b int, primary key(a)) engine=innodb;
2973 insert into t2 values (8,6),(12,1),(3,1);
2974 create table t3(d int not null, b int, primary key(d)) engine=innodb;
2975 insert into t3 values (8,6),(12,1),(3,1);
2976 create table t5(a int not null, b int, primary key(a)) engine=innodb;
2977 insert into t5 values (1,2),(5,3),(4,2);
2978 create table t6(d int not null, e int, primary key(d)) engine=innodb;
2979 insert into t6 values (8,6),(12,1),(3,1);
2980 create table t8(a int not null, b int, primary key(a)) engine=innodb;
2981 insert into t8 values (1,2),(5,3),(4,2);
2982 create table t9(d int not null, e int, primary key(d)) engine=innodb;
2983 insert into t9 values (8,6),(12,1),(3,1);
2986 select * from t2 for update;
2991 SET binlog_format='MIXED';
2993 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2994 insert into t1 select * from t2;
2995 SET binlog_format='MIXED';
2997 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2998 update t3 set b = (select b from t2 where a = d);
2999 SET binlog_format='MIXED';
3001 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3002 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
3003 SET binlog_format='MIXED';
3005 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3006 insert into t5 (select * from t2 lock in share mode);
3007 SET binlog_format='MIXED';
3009 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3010 update t6 set e = (select b from t2 where a = d lock in share mode);
3011 SET binlog_format='MIXED';
3013 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3014 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
3015 SET binlog_format='MIXED';
3017 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3018 insert into t8 (select * from t2 for update);
3019 SET binlog_format='MIXED';
3021 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3022 update t9 set e = (select b from t2 where a = d for update);
3023 SET binlog_format='MIXED';
3025 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3026 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
3027 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3028 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3029 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3030 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3031 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3032 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3033 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3034 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3035 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3037 drop table t1, t2, t3, t5, t6, t8, t9;
3038 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
3039 ERROR 42000: Incorrect column name 'DB_ROW_ID'
3041 a BIGINT(20) NOT NULL,
3043 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3045 a BIGINT(20) NOT NULL,
3046 b VARCHAR(128) NOT NULL,
3049 KEY idx_t2_b_c (b,c(200)),
3050 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
3052 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
3053 INSERT INTO t1 VALUES (1);
3054 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
3055 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
3056 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
3057 INSERT INTO t2 VALUES (1, 'customer_over', '1');
3058 SELECT * FROM t2 WHERE b = 'customer_over';
3061 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
3064 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
3067 /* Bang: Empty result set, above was expected: */
3068 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3071 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
3075 CREATE TABLE t1 ( a int ) ENGINE=innodb;
3077 INSERT INTO t1 VALUES (1);
3079 Table Op Msg_type Msg_text
3080 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3081 test.t1 optimize status OK
3083 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
3084 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
3085 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
3086 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
3087 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
3088 DELETE CASCADE ON UPDATE CASCADE;
3089 SHOW CREATE TABLE t2;
3091 t2 CREATE TABLE `t2` (
3092 `id` int(11) NOT NULL,
3093 `f` int(11) NOT NULL,
3096 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
3097 CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
3098 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
3100 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
3101 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
3102 INSERT INTO t1 VALUES (1);
3103 INSERT INTO t2 VALUES (1);
3104 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
3105 ALTER TABLE t2 MODIFY a INT NOT NULL;
3106 ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
3109 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
3111 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
3113 INSERT INTO t1 VALUES ('DDD');
3118 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
3120 INSERT INTO t1 VALUES (0),(347),(0);
3126 SHOW CREATE TABLE t1;
3128 t1 CREATE TABLE `t1` (
3129 `id` int(11) NOT NULL AUTO_INCREMENT,
3131 ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3132 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
3133 INSERT INTO t2 VALUES(42),(347),(348);
3134 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
3135 SHOW CREATE TABLE t1;
3137 t1 CREATE TABLE `t1` (
3138 `id` int(11) NOT NULL AUTO_INCREMENT,
3140 CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
3141 ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
3143 set innodb_strict_mode=on;
3145 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
3146 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
3147 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
3148 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
3149 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
3150 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
3151 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
3152 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
3154 ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
3155 DROP TABLE IF EXISTS t1;
3157 Note 1051 Unknown table 't1'
3159 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
3161 INSERT INTO t1 VALUES(-10);
3165 INSERT INTO t1 VALUES(NULL);
3171 SET binlog_format='MIXED';
3172 SET TX_ISOLATION='read-committed';
3174 DROP TABLE IF EXISTS t1, t2;
3176 Note 1051 Unknown table 't1'
3177 Note 1051 Unknown table 't2'
3178 CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
3179 CREATE TABLE t2 LIKE t1;
3182 SET binlog_format='MIXED';
3183 SET TX_ISOLATION='read-committed';
3185 INSERT INTO t1 VALUES (1);
3187 SELECT * FROM t1 WHERE a=1;
3190 SET binlog_format='MIXED';
3191 SET TX_ISOLATION='read-committed';
3195 SET binlog_format='MIXED';
3196 SET TX_ISOLATION='read-committed';
3198 INSERT INTO t1 VALUES (2);
3200 SELECT * FROM t1 WHERE a=2;
3203 SELECT * FROM t1 WHERE a=2;
3208 create table t1 (i int, j int) engine=innodb;
3209 insert into t1 (i, j) values (1, 1), (2, 2);
3210 update t1 set j = 2;
3212 info: Rows matched: 2 Changed: 1 Warnings: 0
3214 create table t1 (id int) comment='this is a comment' engine=innodb;
3215 select table_comment, data_free > 0 as data_free_is_set
3216 from information_schema.tables
3217 where table_schema='test' and table_name = 't1';
3218 table_comment data_free_is_set
3222 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3223 c2 VARCHAR(128) NOT NULL,
3225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
3227 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3228 c2 INT(10) UNSIGNED DEFAULT NULL,
3230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
3231 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3234 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
3235 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
3240 CREATE TABLE t1 (c1 int default NULL,
3242 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
3245 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
3247 info: Records: 5 Duplicates: 0 Warnings: 0
3259 DROP TABLE bug58912;
3260 create table t1 (f1 integer primary key) engine=innodb;
3262 show status like "handler_read_key";
3267 show status like "handler_read_key";