1create temporary table t (a char(1) character set filename) engine=innodb; 2drop temporary table t; 3set optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 4drop table if exists t1,t2,t3,t4; 5drop database if exists mysqltest; 6CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB; 7INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000)); 8UPDATE bug58912 SET a=REPEAT('a',7999); 9create 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; 10insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); 11select id, code, name from t1 order by id; 12id code name 131 1 Tim 142 1 Monty 153 2 David 164 2 Erik 175 3 Sasha 186 3 Jeremy 197 4 Matt 20update ignore t1 set id = 8, name = 'Sinisa' where id < 3; 21select id, code, name from t1 order by id; 22id code name 232 1 Monty 243 2 David 254 2 Erik 265 3 Sasha 276 3 Jeremy 287 4 Matt 298 1 Sinisa 30update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; 31select id, code, name from t1 order by id; 32id code name 333 2 David 344 2 Erik 355 3 Sasha 366 3 Jeremy 377 4 Matt 388 1 Sinisa 3912 1 Ralph 40drop table t1; 41CREATE TABLE t1 ( 42id int(11) NOT NULL auto_increment, 43parent_id int(11) DEFAULT '0' NOT NULL, 44level tinyint(4) DEFAULT '0' NOT NULL, 45PRIMARY KEY (id), 46KEY parent_id (parent_id), 47KEY level (level) 48) engine=innodb; 49INSERT 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); 50update t1 set parent_id=parent_id+100; 51select * from t1 where parent_id=102; 52id parent_id level 538 102 2 549 102 2 5515 102 2 56update t1 set id=id+1000; 57update t1 set id=1024 where id=1009; 58Got one of the listed errors 59select * from t1; 60id parent_id level 611001 100 0 621002 101 1 631003 101 1 641004 101 1 651005 101 1 661006 101 1 671007 101 1 681008 102 2 691009 102 2 701015 102 2 711016 103 2 721017 103 2 731018 103 2 741019 103 2 751020 103 2 761021 104 2 771022 104 2 781024 104 2 791025 105 2 801026 105 2 811027 105 2 821028 105 2 831029 105 2 841030 105 2 851031 106 2 861032 106 2 871033 106 2 881034 106 2 891035 106 2 901036 107 2 911037 107 2 921038 107 2 931040 107 2 941157 100 0 951179 105 2 961183 104 2 971193 105 2 981202 107 2 991203 107 2 100update ignore t1 set id=id+1; 101select * from t1; 102id parent_id level 1031001 100 0 1041002 101 1 1051003 101 1 1061004 101 1 1071005 101 1 1081006 101 1 1091007 101 1 1101008 102 2 1111010 102 2 1121015 102 2 1131016 103 2 1141017 103 2 1151018 103 2 1161019 103 2 1171020 103 2 1181021 104 2 1191023 104 2 1201024 104 2 1211025 105 2 1221026 105 2 1231027 105 2 1241028 105 2 1251029 105 2 1261030 105 2 1271031 106 2 1281032 106 2 1291033 106 2 1301034 106 2 1311035 106 2 1321036 107 2 1331037 107 2 1341039 107 2 1351041 107 2 1361158 100 0 1371180 105 2 1381184 104 2 1391194 105 2 1401202 107 2 1411204 107 2 142update ignore t1 set id=1023 where id=1010; 143select * from t1 where parent_id=102; 144id parent_id level 1451008 102 2 1461010 102 2 1471015 102 2 148explain select level from t1 where level=1; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t1 ref level level 1 const # Using index 151explain select level,id from t1 where level=1; 152id select_type table type possible_keys key key_len ref rows Extra 1531 SIMPLE t1 ref level level 1 const # Using index 154explain select level,id,parent_id from t1 where level=1; 155id select_type table type possible_keys key key_len ref rows Extra 1561 SIMPLE t1 ref level level 1 const # 157select level,id from t1 where level=1; 158level id 1591 1002 1601 1003 1611 1004 1621 1005 1631 1006 1641 1007 165select level,id,parent_id from t1 where level=1; 166level id parent_id 1671 1002 101 1681 1003 101 1691 1004 101 1701 1005 101 1711 1006 101 1721 1007 101 173optimize table t1; 174Table Op Msg_type Msg_text 175test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 176test.t1 optimize status OK 177show keys from t1; 178Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 179t1 0 PRIMARY 1 id A # NULL NULL BTREE 180t1 1 parent_id 1 parent_id A # NULL NULL BTREE 181t1 1 level 1 level A # NULL NULL BTREE 182drop table t1; 183CREATE TABLE t1 ( 184gesuchnr int(11) DEFAULT '0' NOT NULL, 185benutzer_id int(11) DEFAULT '0' NOT NULL, 186PRIMARY KEY (gesuchnr,benutzer_id) 187) engine=innodb; 188replace into t1 (gesuchnr,benutzer_id) values (2,1); 189replace into t1 (gesuchnr,benutzer_id) values (1,1); 190replace into t1 (gesuchnr,benutzer_id) values (1,1); 191select * from t1; 192gesuchnr benutzer_id 1931 1 1942 1 195drop table t1; 196create table t1 (a int) engine=innodb; 197insert into t1 values (1), (2); 198optimize table t1; 199Table Op Msg_type Msg_text 200test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 201test.t1 optimize status OK 202delete from t1 where a = 1; 203select * from t1; 204a 2052 206check table t1; 207Table Op Msg_type Msg_text 208test.t1 check status OK 209drop table t1; 210create table t1 (a int,b varchar(20)) engine=innodb; 211insert into t1 values (1,""), (2,"testing"); 212delete from t1 where a = 1; 213select * from t1; 214a b 2152 testing 216create index skr on t1 (a); 217insert into t1 values (3,""), (4,"testing"); 218analyze table t1; 219Table Op Msg_type Msg_text 220test.t1 analyze status OK 221show keys from t1; 222Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 223t1 1 skr 1 a A # NULL NULL YES BTREE 224drop table t1; 225create table t1 (a int,b varchar(20),key(a)) engine=innodb; 226insert into t1 values (1,""), (2,"testing"); 227select * from t1 where a = 1; 228a b 2291 230drop table t1; 231create table t1 (n int not null primary key) engine=innodb; 232set autocommit=0; 233insert into t1 values (4); 234rollback; 235select n, "after rollback" from t1; 236n after rollback 237insert into t1 values (4); 238commit; 239select n, "after commit" from t1; 240n after commit 2414 after commit 242commit; 243insert into t1 values (5); 244insert into t1 values (4); 245ERROR 23000: Duplicate entry '4' for key 'PRIMARY' 246commit; 247select n, "after commit" from t1; 248n after commit 2494 after commit 2505 after commit 251set autocommit=1; 252insert into t1 values (6); 253insert into t1 values (4); 254ERROR 23000: Duplicate entry '4' for key 'PRIMARY' 255select n from t1; 256n 2574 2585 2596 260set autocommit=0; 261begin; 262savepoint `my_savepoint`; 263insert into t1 values (7); 264savepoint `savept2`; 265insert into t1 values (3); 266select n from t1; 267n 2683 2694 2705 2716 2727 273savepoint savept3; 274rollback to savepoint savept2; 275rollback to savepoint savept3; 276ERROR 42000: SAVEPOINT savept3 does not exist 277rollback to savepoint savept2; 278release savepoint `my_savepoint`; 279select n from t1; 280n 2814 2825 2836 2847 285rollback to savepoint `my_savepoint`; 286ERROR 42000: SAVEPOINT my_savepoint does not exist 287rollback to savepoint savept2; 288ERROR 42000: SAVEPOINT savept2 does not exist 289insert into t1 values (8); 290savepoint sv; 291commit; 292savepoint sv; 293set autocommit=1; 294rollback; 295drop table t1; 296create table t1 (n int not null primary key) engine=innodb; 297start transaction; 298insert into t1 values (4); 299flush tables with read lock; 300commit; 301unlock tables; 302commit; 303select * from t1; 304n 3054 306drop table t1; 307create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb; 308begin; 309insert into t1 values(1,'hamdouni'); 310select id as afterbegin_id,nom as afterbegin_nom from t1; 311afterbegin_id afterbegin_nom 3121 hamdouni 313rollback; 314select id as afterrollback_id,nom as afterrollback_nom from t1; 315afterrollback_id afterrollback_nom 316set autocommit=0; 317insert into t1 values(2,'mysql'); 318select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; 319afterautocommit0_id afterautocommit0_nom 3202 mysql 321rollback; 322select id as afterrollback_id,nom as afterrollback_nom from t1; 323afterrollback_id afterrollback_nom 324set autocommit=1; 325drop table t1; 326CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb; 327insert into t1 values ('pippo', 12); 328insert into t1 values ('pippo', 12); 329ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY' 330delete from t1; 331delete from t1 where id = 'pippo'; 332select * from t1; 333id val 334insert into t1 values ('pippo', 12); 335set autocommit=0; 336delete from t1; 337rollback; 338select * from t1; 339id val 340pippo 12 341delete from t1; 342commit; 343select * from t1; 344id val 345drop table t1; 346create table t1 (a integer) engine=innodb; 347start transaction; 348rename table t1 to t2; 349create table t1 (b integer) engine=innodb; 350insert into t1 values (1); 351rollback; 352drop table t1; 353rename table t2 to t1; 354drop table t1; 355set autocommit=1; 356CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb; 357INSERT INTO t1 VALUES (1, 'Jochen'); 358select * from t1; 359ID NAME 3601 Jochen 361drop table t1; 362CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb; 363set autocommit=0; 364INSERT INTO t1 SET _userid='marc@anyware.co.uk'; 365COMMIT; 366SELECT * FROM t1; 367_userid 368marc@anyware.co.uk 369SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; 370_userid 371marc@anyware.co.uk 372drop table t1; 373set autocommit=1; 374CREATE TABLE t1 ( 375user_id int(10) DEFAULT '0' NOT NULL, 376name varchar(100), 377phone varchar(100), 378ref_email varchar(100) DEFAULT '' NOT NULL, 379detail varchar(200), 380PRIMARY KEY (user_id,ref_email) 381)engine=innodb; 382INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); 383select * from t1 where user_id=10292; 384user_id name phone ref_email detail 38510292 sanjeev 29153373 sansh777@hotmail.com xxx 38610292 shirish 2333604 shirish@yahoo.com ddsds 38710292 sonali 323232 sonali@bolly.com filmstar 388INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); 389select * from t1 where user_id=10292; 390user_id name phone ref_email detail 39110292 sanjeev 29153373 sansh777@hotmail.com xxx 39210292 shirish 2333604 shirish@yahoo.com ddsds 39310292 sonali 323232 sonali@bolly.com filmstar 394select * from t1 where user_id>=10292; 395user_id name phone ref_email detail 39610292 sanjeev 29153373 sansh777@hotmail.com xxx 39710292 shirish 2333604 shirish@yahoo.com ddsds 39810292 sonali 323232 sonali@bolly.com filmstar 39910293 shirish 2333604 shirish@yahoo.com ddsds 400select * from t1 where user_id>10292; 401user_id name phone ref_email detail 40210293 shirish 2333604 shirish@yahoo.com ddsds 403select * from t1 where user_id<10292; 404user_id name phone ref_email detail 40510291 sanjeev 29153373 sansh777@hotmail.com xxx 406drop table t1; 407CREATE TABLE t1 (a int not null, b int not null,c int not null, 408key(a),primary key(a,b), unique(c),key(a),unique(b)); 409Warnings: 410Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release 411show index from t1; 412Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 413t1 0 PRIMARY 1 a A # NULL NULL BTREE 414t1 0 PRIMARY 2 b A # NULL NULL BTREE 415t1 0 c 1 c A # NULL NULL BTREE 416t1 0 b 1 b A # NULL NULL BTREE 417t1 1 a 1 a A # NULL NULL BTREE 418t1 1 a_2 1 a A # NULL NULL BTREE 419drop table t1; 420create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); 421alter table t1 engine=innodb; 422insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); 423select * from t1; 424col1 col2 4251 1 4262 3 4273 4 4284 4 4295 2 430update t1 set col2='7' where col1='4'; 431select * from t1; 432col1 col2 4331 1 4342 3 4353 4 4364 7 4375 2 438alter table t1 add co3 int not null; 439select * from t1; 440col1 col2 co3 4411 1 0 4422 3 0 4433 4 0 4444 7 0 4455 2 0 446update t1 set col2='9' where col1='2'; 447select * from t1; 448col1 col2 co3 4491 1 0 4502 9 0 4513 4 0 4524 7 0 4535 2 0 454drop table t1; 455create table t1 (a int not null , b int, primary key (a)) engine = innodb; 456create table t2 (a int not null , b int, primary key (a)) engine = myisam; 457insert into t1 VALUES (1,3) , (2,3), (3,3); 458select * from t1; 459a b 4601 3 4612 3 4623 3 463insert into t2 select * from t1; 464select * from t2; 465a b 4661 3 4672 3 4683 3 469delete from t1 where b = 3; 470select * from t1; 471a b 472insert into t1 select * from t2; 473select * from t1; 474a b 4751 3 4762 3 4773 3 478select * from t2; 479a b 4801 3 4812 3 4823 3 483drop table t1,t2; 484CREATE TABLE t1 ( 485user_name varchar(12), 486password text, 487subscribed char(1), 488user_id int(11) DEFAULT '0' NOT NULL, 489quota bigint(20), 490weight double, 491access_date date, 492access_time time, 493approved datetime, 494dummy_primary_key int(11) NOT NULL auto_increment, 495PRIMARY KEY (dummy_primary_key) 496) ENGINE=innodb; 497INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); 498INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 499INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); 500INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); 501INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); 502select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; 503user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key 504user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 505user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 506user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 507user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 508user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 509drop table t1; 510CREATE TABLE t1 ( 511id int(11) NOT NULL auto_increment, 512parent_id int(11) DEFAULT '0' NOT NULL, 513level tinyint(4) DEFAULT '0' NOT NULL, 514KEY (id), 515KEY parent_id (parent_id), 516KEY level (level) 517) engine=innodb; 518INSERT 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); 519INSERT INTO t1 values (179,5,2); 520update t1 set parent_id=parent_id+100; 521select * from t1 where parent_id=102; 522id parent_id level 5238 102 2 5249 102 2 52515 102 2 526update t1 set id=id+1000; 527update t1 set id=1024 where id=1009; 528select * from t1; 529id parent_id level 5301001 100 0 5311003 101 1 5321004 101 1 5331008 102 2 5341024 102 2 5351017 103 2 5361022 104 2 5371024 104 2 5381028 105 2 5391029 105 2 5401030 105 2 5411031 106 2 5421032 106 2 5431033 106 2 5441203 107 2 5451202 107 2 5461020 103 2 5471157 100 0 5481193 105 2 5491040 107 2 5501002 101 1 5511015 102 2 5521006 101 1 5531034 106 2 5541035 106 2 5551016 103 2 5561007 101 1 5571036 107 2 5581018 103 2 5591026 105 2 5601027 105 2 5611183 104 2 5621038 107 2 5631025 105 2 5641037 107 2 5651021 104 2 5661019 103 2 5671005 101 1 5681179 105 2 569update ignore t1 set id=id+1; 570select * from t1; 571id parent_id level 5721002 100 0 5731004 101 1 5741005 101 1 5751009 102 2 5761025 102 2 5771018 103 2 5781023 104 2 5791025 104 2 5801029 105 2 5811030 105 2 5821031 105 2 5831032 106 2 5841033 106 2 5851034 106 2 5861204 107 2 5871203 107 2 5881021 103 2 5891158 100 0 5901194 105 2 5911041 107 2 5921003 101 1 5931016 102 2 5941007 101 1 5951035 106 2 5961036 106 2 5971017 103 2 5981008 101 1 5991037 107 2 6001019 103 2 6011027 105 2 6021028 105 2 6031184 104 2 6041039 107 2 6051026 105 2 6061038 107 2 6071022 104 2 6081020 103 2 6091006 101 1 6101180 105 2 611update ignore t1 set id=1023 where id=1010; 612select * from t1 where parent_id=102; 613id parent_id level 6141009 102 2 6151025 102 2 6161016 102 2 617explain select level from t1 where level=1; 618id select_type table type possible_keys key key_len ref rows Extra 6191 SIMPLE t1 ref level level 1 const # Using index 620select level,id from t1 where level=1; 621level id 6221 1004 6231 1005 6241 1003 6251 1007 6261 1008 6271 1006 628select level,id,parent_id from t1 where level=1; 629level id parent_id 6301 1004 101 6311 1005 101 6321 1003 101 6331 1007 101 6341 1008 101 6351 1006 101 636select level,id from t1 where level=1 order by id; 637level id 6381 1003 6391 1004 6401 1005 6411 1006 6421 1007 6431 1008 644delete from t1 where level=1; 645select * from t1; 646id parent_id level 6471002 100 0 6481009 102 2 6491025 102 2 6501018 103 2 6511023 104 2 6521025 104 2 6531029 105 2 6541030 105 2 6551031 105 2 6561032 106 2 6571033 106 2 6581034 106 2 6591204 107 2 6601203 107 2 6611021 103 2 6621158 100 0 6631194 105 2 6641041 107 2 6651016 102 2 6661035 106 2 6671036 106 2 6681017 103 2 6691037 107 2 6701019 103 2 6711027 105 2 6721028 105 2 6731184 104 2 6741039 107 2 6751026 105 2 6761038 107 2 6771022 104 2 6781020 103 2 6791180 105 2 680drop table t1; 681CREATE TABLE t1 ( 682sca_code char(6) NOT NULL, 683cat_code char(6) NOT NULL, 684sca_desc varchar(50), 685lan_code char(2) NOT NULL, 686sca_pic varchar(100), 687sca_sdesc varchar(50), 688sca_sch_desc varchar(16), 689PRIMARY KEY (sca_code, cat_code, lan_code), 690INDEX sca_pic (sca_pic) 691) engine = innodb ; 692INSERT 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'); 693select count(*) from t1 where sca_code = 'PD'; 694count(*) 6951 696select count(*) from t1 where sca_code <= 'PD'; 697count(*) 6981 699select count(*) from t1 where sca_pic is null; 700count(*) 7012 702alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); 703select count(*) from t1 where sca_code='PD' and sca_pic is null; 704count(*) 7051 706select count(*) from t1 where cat_code='E'; 707count(*) 7080 709alter table t1 drop index sca_pic, add index (sca_pic, cat_code); 710select count(*) from t1 where sca_code='PD' and sca_pic is null; 711count(*) 7121 713select count(*) from t1 where sca_pic >= 'n'; 714count(*) 7151 716select sca_pic from t1 where sca_pic is null; 717sca_pic 718NULL 719NULL 720update t1 set sca_pic="test" where sca_pic is null; 721delete from t1 where sca_code='pd'; 722drop table t1; 723set @a:=now(); 724CREATE TABLE t1 (a int not null, b timestamp not null default 725current_timestamp on update current_timestamp, primary key (a)) engine=innodb; 726insert into t1 (a) values(1),(2),(3); 727select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; 728a 7291 7302 7313 732select a from t1 natural join t1 as t2 where b >= @a order by a; 733a 7341 7352 7363 737update t1 set a=5 where a=1; 738select a from t1; 739a 7402 7413 7425 743drop table t1; 744create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb; 745insert into t1 values("hello",1),("world",2); 746select * from t1 order by b desc; 747a b 748world 2 749hello 1 750optimize table t1; 751Table Op Msg_type Msg_text 752test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 753test.t1 optimize status OK 754show keys from t1; 755Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 756t1 0 PRIMARY 1 a A # NULL NULL BTREE 757drop table t1; 758create table t1 (i int, j int ) ENGINE=innodb; 759insert into t1 values (1,2); 760select * from t1 where i=1 and j=2; 761i j 7621 2 763create index ax1 on t1 (i,j); 764select * from t1 where i=1 and j=2; 765i j 7661 2 767drop table t1; 768CREATE TABLE t1 ( 769a int3 unsigned NOT NULL, 770b int1 unsigned NOT NULL, 771UNIQUE (a, b) 772) ENGINE = innodb; 773INSERT INTO t1 VALUES (1, 1); 774SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; 775MIN(B) MAX(b) 7761 1 777drop table t1; 778CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb; 779INSERT INTO t1 VALUES (1); 780SELECT * FROM t1; 781a 7821 783DROP TABLE t1; 784create 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; 785insert 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); 786explain select * from t1 where a > 0 and a < 50; 787id select_type table type possible_keys key key_len ref rows Extra 7881 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition 789drop table t1; 790create 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; 791insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 792LOCK TABLES t1 WRITE; 793insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 794ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 795select id from t1; 796id 7970 7981 7992 800select id from t1; 801id 8020 8031 8042 805UNLOCK TABLES; 806DROP TABLE t1; 807create 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; 808insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 809LOCK TABLES t1 WRITE; 810begin; 811insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 812ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 813select id from t1; 814id 8150 8161 8172 818insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); 819Warnings: 820Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' 821commit; 822select id,id3 from t1; 823id id3 8240 0 8251 1 8262 2 827100 2 828UNLOCK TABLES; 829DROP TABLE t1; 830create table t1 (a char(20), unique (a(5))) engine=innodb; 831drop table t1; 832create table t1 (a char(20), index (a(5))) engine=innodb; 833show create table t1; 834Table Create Table 835t1 CREATE TABLE `t1` ( 836 `a` char(20) DEFAULT NULL, 837 KEY `a` (`a`(5)) 838) ENGINE=InnoDB DEFAULT CHARSET=latin1 839drop table t1; 840create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb; 841insert into t1 values (NULL),(NULL),(NULL); 842delete from t1 where a=3; 843insert into t1 values (NULL); 844select * from t1; 845a 8461 8472 8484 849alter table t1 add b int; 850select * from t1; 851a b 8521 NULL 8532 NULL 8544 NULL 855drop table t1; 856create table t1 857( 858id int auto_increment primary key, 859name varchar(32) not null, 860value text not null, 861uid int not null, 862unique key(name,uid) 863) engine=innodb; 864insert into t1 values (1,'one','one value',101), 865(2,'two','two value',102),(3,'three','three value',103); 866set insert_id=5; 867replace into t1 (value,name,uid) values ('other value','two',102); 868delete from t1 where uid=102; 869set insert_id=5; 870replace into t1 (value,name,uid) values ('other value','two',102); 871set insert_id=6; 872replace into t1 (value,name,uid) values ('other value','two',102); 873select * from t1; 874id name value uid 8751 one one value 101 8763 three three value 103 8776 two other value 102 878drop table t1; 879create database mysqltest; 880create table mysqltest.t1 (a int not null) engine= innodb; 881insert into mysqltest.t1 values(1); 882create table mysqltest.t2 (a int not null) engine= myisam; 883insert into mysqltest.t2 values(1); 884create table mysqltest.t3 (a int not null) engine= heap; 885insert into mysqltest.t3 values(1); 886commit; 887drop database mysqltest; 888show tables from mysqltest; 889ERROR 42000: Unknown database 'mysqltest' 890set autocommit=0; 891create table t1 (a int not null) engine= innodb; 892insert into t1 values(1),(2); 893truncate table t1; 894commit; 895truncate table t1; 896truncate table t1; 897select * from t1; 898a 899insert into t1 values(1),(2); 900delete from t1; 901select * from t1; 902a 903commit; 904drop table t1; 905set autocommit=1; 906create table t1 (a int not null) engine= innodb; 907insert into t1 values(1),(2); 908truncate table t1; 909insert into t1 values(1),(2); 910select * from t1; 911a 9121 9132 914truncate table t1; 915insert into t1 values(1),(2); 916delete from t1; 917select * from t1; 918a 919drop table t1; 920create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb; 921insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 922explain select * from t1 order by a; 923id select_type table type possible_keys key key_len ref rows Extra 9241 SIMPLE t1 index NULL PRIMARY 4 NULL # 925explain select * from t1 order by b; 926id select_type table type possible_keys key key_len ref rows Extra 9271 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 928explain select * from t1 order by c; 929id select_type table type possible_keys key key_len ref rows Extra 9301 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort 931explain select a from t1 order by a; 932id select_type table type possible_keys key key_len ref rows Extra 9331 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index 934explain select b from t1 order by b; 935id select_type table type possible_keys key key_len ref rows Extra 9361 SIMPLE t1 index NULL b 4 NULL # Using index 937explain select a,b from t1 order by b; 938id select_type table type possible_keys key key_len ref rows Extra 9391 SIMPLE t1 index NULL b 4 NULL # Using index 940explain select a,b from t1; 941id select_type table type possible_keys key key_len ref rows Extra 9421 SIMPLE t1 index NULL b 4 NULL # Using index 943explain select a,b,c from t1; 944id select_type table type possible_keys key key_len ref rows Extra 9451 SIMPLE t1 ALL NULL NULL NULL NULL # 946drop table t1; 947create table t1 (t int not null default 1, key (t)) engine=innodb; 948desc t1; 949Field Type Null Key Default Extra 950t int(11) NO MUL 1 951drop table t1; 952SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 953CREATE TABLE t1 ( 954number bigint(20) NOT NULL default '0', 955cname char(15) NOT NULL default '', 956carrier_id smallint(6) NOT NULL default '0', 957privacy tinyint(4) NOT NULL default '0', 958last_mod_date timestamp NOT NULL, 959last_mod_id smallint(6) NOT NULL default '0', 960last_app_date timestamp NOT NULL, 961last_app_id smallint(6) default '-1', 962version smallint(6) NOT NULL default '0', 963assigned_scps int(11) default '0', 964status tinyint(4) default '0' 965) ENGINE=InnoDB; 966INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); 967INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); 968INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); 969INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); 970INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); 971INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); 972CREATE TABLE t2 ( 973number bigint(20) NOT NULL default '0', 974cname char(15) NOT NULL default '', 975carrier_id smallint(6) NOT NULL default '0', 976privacy tinyint(4) NOT NULL default '0', 977last_mod_date timestamp NOT NULL, 978last_mod_id smallint(6) NOT NULL default '0', 979last_app_date timestamp NOT NULL, 980last_app_id smallint(6) default '-1', 981version smallint(6) NOT NULL default '0', 982assigned_scps int(11) default '0', 983status tinyint(4) default '0' 984) ENGINE=InnoDB; 985INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); 986INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); 987INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); 988INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); 989select * from t1; 990number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 9914077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1 9929197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0 993650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1 994302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0 9956014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 996333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 997select * from t2; 998number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 9994077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1 10009197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0 1001650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1 1002333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 1003delete 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); 1004select * from t1; 1005number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 10066014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 1007333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 1008select * from t2; 1009number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 1010333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 1011select * from t2; 1012number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 1013333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 1014drop table t1,t2; 1015SET sql_mode = default; 1016create 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; 1017BEGIN; 1018SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 1019SELECT @@tx_isolation,@@global.tx_isolation; 1020@@tx_isolation @@global.tx_isolation 1021SERIALIZABLE REPEATABLE-READ 1022insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); 1023select id, code, name from t1 order by id; 1024id code name 10251 1 Tim 10262 1 Monty 10273 2 David 1028COMMIT; 1029BEGIN; 1030SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 1031insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); 1032select id, code, name from t1 order by id; 1033id code name 10341 1 Tim 10352 1 Monty 10363 2 David 10374 2 Erik 10385 3 Sasha 1039COMMIT; 1040SET binlog_format='MIXED'; 1041BEGIN; 1042SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 1043insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); 1044select id, code, name from t1 order by id; 1045id code name 10461 1 Tim 10472 1 Monty 10483 2 David 10494 2 Erik 10505 3 Sasha 10516 3 Jeremy 10527 4 Matt 1053COMMIT; 1054DROP TABLE t1; 1055create table t1 (n int(10), d int(10)) engine=innodb; 1056create table t2 (n int(10), d int(10)) engine=innodb; 1057insert into t1 values(1,1),(1,2); 1058insert into t2 values(1,10),(2,20); 1059UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 1060select * from t1; 1061n d 10621 10 10631 10 1064select * from t2; 1065n d 10661 30 10672 20 1068drop table t1,t2; 1069drop table if exists t1, t2; 1070CREATE TABLE t1 (a int, PRIMARY KEY (a)); 1071CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 1072create trigger trg_del_t2 after delete on t2 for each row 1073insert into t1 values (1); 1074insert into t1 values (1); 1075insert into t2 values (1),(2); 1076delete t2 from t2; 1077ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1078select count(*) from t2 /* must be 2 as restored after rollback caused by the error */; 1079count(*) 10802 1081drop table t1, t2; 1082drop table if exists t1, t2; 1083CREATE TABLE t1 (a int, PRIMARY KEY (a)); 1084CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 1085create trigger trg_del_t2 after delete on t2 for each row 1086insert into t1 values (1); 1087insert into t1 values (1); 1088insert into t2 values (1),(2); 1089delete t2 from t2; 1090ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1091select count(*) from t2 /* must be 2 as restored after rollback caused by the error */; 1092count(*) 10932 1094drop table t1, t2; 1095create table t1 (a int, b int) engine=innodb; 1096insert into t1 values(20,null); 1097select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1098t2.b=t3.a; 1099b ifnull(t2.b,"this is null") 1100NULL this is null 1101select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1102t2.b=t3.a order by 1; 1103b ifnull(t2.b,"this is null") 1104NULL this is null 1105insert into t1 values(10,null); 1106select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 1107t2.b=t3.a order by 1; 1108b ifnull(t2.b,"this is null") 1109NULL this is null 1110NULL this is null 1111drop table t1; 1112create table t1 (a varchar(10) not null) engine=myisam; 1113create table t2 (b varchar(10) not null unique) engine=innodb; 1114select t1.a from t1,t2 where t1.a=t2.b; 1115a 1116drop table t1,t2; 1117create table t1 (a int not null, b int, primary key (a)) engine = innodb; 1118create table t2 (a int not null, b int, primary key (a)) engine = innodb; 1119insert into t1 values (10, 20); 1120insert into t2 values (10, 20); 1121update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; 1122drop table t1,t2; 1123CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 1124CREATE 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; 1125insert into t1 set id=1; 1126insert into t2 set id=1, t1_id=1; 1127delete t1,t2 from t1,t2 where t1.id=t2.t1_id; 1128select * from t1; 1129id 1130select * from t2; 1131id t1_id 1132drop table t2,t1; 1133CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 1134CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB; 1135INSERT INTO t1 VALUES(1); 1136INSERT INTO t2 VALUES(1, 1); 1137SELECT * from t1; 1138id 11391 1140UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1; 1141SELECT * from t1; 1142id 11432 1144UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id; 1145SELECT * from t1; 1146id 11473 1148DROP TABLE t1,t2; 1149set autocommit=0; 1150CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; 1151CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; 1152CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB; 1153INSERT INTO t3 VALUES("my-test-1", "my-test-2"); 1154COMMIT; 1155INSERT INTO t1 VALUES("this-key", "will disappear"); 1156INSERT INTO t2 VALUES("this-key", "will also disappear"); 1157DELETE FROM t3 WHERE id1="my-test-1"; 1158SELECT * FROM t1; 1159id value 1160this-key will disappear 1161SELECT * FROM t2; 1162id value 1163this-key will also disappear 1164SELECT * FROM t3; 1165id1 id2 1166ROLLBACK; 1167SELECT * FROM t1; 1168id value 1169SELECT * FROM t2; 1170id value 1171SELECT * FROM t3; 1172id1 id2 1173my-test-1 my-test-2 1174SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE; 1175id1 id2 1176my-test-1 my-test-2 1177COMMIT; 1178set autocommit=1; 1179DROP TABLE t1,t2,t3; 1180CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb; 1181INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 1182UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; 1183SELECT * from t1; 1184a b 11851 1 1186102 2 1187103 3 11884 4 11895 5 11906 6 11917 7 11928 8 11939 9 1194drop table t1; 1195CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM; 1196CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB; 1197SET AUTOCOMMIT=0; 1198INSERT INTO t1 ( B_ID ) VALUES ( 1 ); 1199INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); 1200ROLLBACK; 1201Warnings: 1202Warning 1196 Some non-transactional changed tables couldn't be rolled back 1203SELECT * FROM t1; 1204B_ID 1205drop table t1,t2; 1206create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb; 1207insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); 1208select distinct parent,child from t1 order by parent; 1209parent child 12100 4 12111 2 12121 3 12132 1 1214drop table t1; 1215create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb; 1216create table t2 (a int not null auto_increment primary key, b int); 1217insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); 1218insert into t2 (a) select b from t1; 1219insert into t1 (b) select b from t2; 1220insert into t2 (a) select b from t1; 1221insert into t1 (a) select b from t2; 1222insert into t2 (a) select b from t1; 1223insert into t1 (a) select b from t2; 1224insert into t2 (a) select b from t1; 1225insert into t1 (a) select b from t2; 1226insert into t2 (a) select b from t1; 1227insert into t1 (a) select b from t2; 1228select count(*) from t1; 1229count(*) 1230623 1231explain select * from t1 where c between 1 and 2500; 1232id select_type table type possible_keys key key_len ref rows Extra 12331 SIMPLE t1 range c c 5 NULL # # 1234update t1 set c=a; 1235explain select * from t1 where c between 1 and 2500; 1236id select_type table type possible_keys key key_len ref rows Extra 12371 SIMPLE t1 ALL c NULL NULL NULL # # 1238drop table t1,t2; 1239create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb; 1240insert into t1 (id) values (null),(null),(null),(null),(null); 1241update t1 set fk=69 where fk is null order by id limit 1; 1242SELECT * from t1; 1243id fk 12442 NULL 12453 NULL 12464 NULL 12475 NULL 12481 69 1249drop table t1; 1250create table t1 (a int not null, b int not null, key (a)); 1251insert 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); 1252SET @tmp=0; 1253update t1 set b=(@tmp:=@tmp+1) order by a; 1254update t1 set b=99 where a=1 order by b asc limit 1; 1255update t1 set b=100 where a=1 order by b desc limit 2; 1256update t1 set a=a+10+b where a=1 order by b; 1257select * from t1 order by a,b; 1258a b 12592 4 12602 5 12612 6 12623 7 12633 8 12643 9 12653 10 12663 11 12673 12 126813 2 1269111 100 1270111 100 1271drop table t1; 1272create table t1 ( c char(8) not null ) engine=innodb; 1273insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 1274insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 1275alter table t1 add b char(8) not null; 1276alter table t1 add a char(8) not null; 1277alter table t1 add primary key (a,b,c); 1278update t1 set a=c, b=c; 1279create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb; 1280insert into t2 select * from t1; 1281delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 1282drop table t1,t2; 1283SET AUTOCOMMIT=1; 1284create table t1 (a integer auto_increment primary key) engine=innodb; 1285insert into t1 (a) values (NULL),(NULL); 1286truncate table t1; 1287insert into t1 (a) values (NULL),(NULL); 1288SELECT * from t1; 1289a 12901 12912 1292drop table t1; 1293CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB; 1294CREATE 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; 1295drop table t2,t1; 1296create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb; 1297insert into `t1`values ( 1 ) ; 1298create 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; 1299insert into `t2`values ( 1 ) ; 1300create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb; 1301insert into `t3`values ( 1 ) ; 1302delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; 1303ERROR 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`)) 1304update 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; 1305ERROR 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`)) 1306update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; 1307ERROR 42S22: Unknown column 't1.id' in 'where clause' 1308drop table t3,t2,t1; 1309CREATE TABLE t1 ( 1310c1 VARCHAR(8), c2 VARCHAR(8), 1311PRIMARY KEY (c1, c2) 1312) ENGINE=InnoDB; 1313CREATE TABLE t2 ( 1314c0 INT PRIMARY KEY, 1315c1 VARCHAR(8) UNIQUE, 1316FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE 1317) ENGINE=InnoDB; 1318INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue'); 1319INSERT INTO t2 VALUES (10, 'old'), (20, 'other'); 1320UPDATE t1 SET c1 = 'other' WHERE c1 = 'old'; 1321ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'c1' 1322DROP TABLE t2,t1; 1323call mtr.add_suppression("Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\. Please drop excessive foreign constraints and try again"); 1324create table t1( 1325id int primary key, 1326pid int, 1327index(pid), 1328foreign key(pid) references t1(id) on delete cascade) engine=innodb; 1329insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6), 1330(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14); 1331delete from t1 where id=0; 1332Got one of the listed errors 1333delete from t1 where id=15; 1334delete from t1 where id=0; 1335drop table t1; 1336CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; 1337CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx 1338(stamp))ENGINE=InnoDB; 1339insert into t1 values (1),(2),(3); 1340insert ignore into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); 1341Warnings: 1342Warning 1265 Data truncated for column 'stamp' at row 3 1343SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < 1344'20020204120000' GROUP BY col1; 1345col1 13461 13472 13483 13494 1350drop table t1,t2; 1351CREATE TABLE t1 ( 1352`id` int(10) unsigned NOT NULL auto_increment, 1353`id_object` int(10) unsigned default '0', 1354`id_version` int(10) unsigned NOT NULL default '1', 1355`label` varchar(100) NOT NULL default '', 1356`description` text, 1357PRIMARY KEY (`id`), 1358KEY `id_object` (`id_object`), 1359KEY `id_version` (`id_version`) 1360) ENGINE=InnoDB; 1361INSERT 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); 1362CREATE TABLE t2 ( 1363`id` int(10) unsigned NOT NULL auto_increment, 1364`id_version` int(10) unsigned NOT NULL default '1', 1365PRIMARY KEY (`id`), 1366KEY `id_version` (`id_version`) 1367) ENGINE=InnoDB; 1368INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); 1369SELECT t2.id, t1.`label` FROM t2 INNER JOIN 1370(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 1371ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); 1372id label 13733382 Test 1374102 Le Pekin (Test) 13751794 Test de resto 13761822 Test 3 13773524 Societe Test 13783525 Fournisseur Test 1379drop table t1,t2; 1380create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam; 1381create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb; 1382create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb; 1383insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 1384insert t2 select * from t1; 1385insert t3 select * from t1; 1386checksum table t1, t2, t3, t4 quick; 1387Table Checksum 1388test.t1 3442722830 1389test.t2 NULL 1390test.t3 NULL 1391test.t4 NULL 1392Warnings: 1393Error 1146 Table 'test.t4' doesn't exist 1394checksum table t1, t2, t3, t4; 1395Table Checksum 1396test.t1 3442722830 1397test.t2 3442722830 1398test.t3 3442722830 1399test.t4 NULL 1400Warnings: 1401Error 1146 Table 'test.t4' doesn't exist 1402checksum table t1, t2, t3, t4 extended; 1403Table Checksum 1404test.t1 3442722830 1405test.t2 3442722830 1406test.t3 3442722830 1407test.t4 NULL 1408Warnings: 1409Error 1146 Table 'test.t4' doesn't exist 1410drop table t1,t2,t3; 1411create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb; 1412insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); 1413select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; 1414trim(name2) 1415fff 1416sss 1417ttt 1418first 1419second 1420third 14211 14222 14233 1424drop table t1; 1425create table t1 (a int) engine=innodb; 1426create table t2 like t1; 1427drop table t1,t2; 1428create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb; 1429create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb; 1430show create table t1; 1431Table Create Table 1432t1 CREATE TABLE `t1` ( 1433 `id` int(11) NOT NULL, 1434 `id2` int(11) NOT NULL, 1435 UNIQUE KEY `id` (`id`,`id2`) 1436) ENGINE=InnoDB DEFAULT CHARSET=latin1 1437show create table t2; 1438Table Create Table 1439t2 CREATE TABLE `t2` ( 1440 `id` int(11) NOT NULL, 1441 KEY `t1_id_fk` (`id`), 1442 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1443) ENGINE=InnoDB DEFAULT CHARSET=latin1 1444create index id on t2 (id); 1445show create table t2; 1446Table Create Table 1447t2 CREATE TABLE `t2` ( 1448 `id` int(11) NOT NULL, 1449 KEY `id` (`id`), 1450 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1451) ENGINE=InnoDB DEFAULT CHARSET=latin1 1452create index id2 on t2 (id); 1453Warnings: 1454Note 1831 Duplicate index `id2`. This is deprecated and will be disallowed in a future release 1455show create table t2; 1456Table Create Table 1457t2 CREATE TABLE `t2` ( 1458 `id` int(11) NOT NULL, 1459 KEY `id` (`id`), 1460 KEY `id2` (`id`), 1461 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1462) ENGINE=InnoDB DEFAULT CHARSET=latin1 1463drop index id2 on t2; 1464drop index id on t2; 1465ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint 1466show create table t2; 1467Table Create Table 1468t2 CREATE TABLE `t2` ( 1469 `id` int(11) NOT NULL, 1470 KEY `id` (`id`), 1471 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1472) ENGINE=InnoDB DEFAULT CHARSET=latin1 1473drop table t2; 1474create 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; 1475show create table t2; 1476Table Create Table 1477t2 CREATE TABLE `t2` ( 1478 `id` int(11) NOT NULL, 1479 `id2` int(11) NOT NULL, 1480 KEY `t1_id_fk` (`id`,`id2`), 1481 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) 1482) ENGINE=InnoDB DEFAULT CHARSET=latin1 1483create unique index id on t2 (id,id2); 1484show create table t2; 1485Table Create Table 1486t2 CREATE TABLE `t2` ( 1487 `id` int(11) NOT NULL, 1488 `id2` int(11) NOT NULL, 1489 UNIQUE KEY `id` (`id`,`id2`), 1490 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) 1491) ENGINE=InnoDB DEFAULT CHARSET=latin1 1492drop table t2; 1493create 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; 1494show create table t2; 1495Table Create Table 1496t2 CREATE TABLE `t2` ( 1497 `id` int(11) NOT NULL, 1498 `id2` int(11) NOT NULL, 1499 UNIQUE KEY `id` (`id`,`id2`), 1500 KEY `t1_id_fk` (`id2`,`id`), 1501 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) 1502) ENGINE=InnoDB DEFAULT CHARSET=latin1 1503drop table t2; 1504create 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; 1505show create table t2; 1506Table Create Table 1507t2 CREATE TABLE `t2` ( 1508 `id` int(11) NOT NULL, 1509 `id2` int(11) NOT NULL, 1510 UNIQUE KEY `id` (`id`,`id2`), 1511 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1512) ENGINE=InnoDB DEFAULT CHARSET=latin1 1513drop table t2; 1514create 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; 1515show create table t2; 1516Table Create Table 1517t2 CREATE TABLE `t2` ( 1518 `id` int(11) NOT NULL, 1519 `id2` int(11) NOT NULL, 1520 UNIQUE KEY `id` (`id`,`id2`), 1521 KEY `t1_id_fk` (`id2`,`id`), 1522 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) 1523) ENGINE=InnoDB DEFAULT CHARSET=latin1 1524drop table t2; 1525create 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; 1526show create table t2; 1527Table Create Table 1528t2 CREATE TABLE `t2` ( 1529 `id` int(11) NOT NULL AUTO_INCREMENT, 1530 `id2` int(11) NOT NULL, 1531 PRIMARY KEY (`id`), 1532 KEY `id` (`id`,`id2`), 1533 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1534) ENGINE=InnoDB DEFAULT CHARSET=latin1 1535drop table t2; 1536create 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; 1537show create table t2; 1538Table Create Table 1539t2 CREATE TABLE `t2` ( 1540 `id` int(11) NOT NULL AUTO_INCREMENT, 1541 `id2` int(11) NOT NULL, 1542 KEY `t1_id_fk` (`id`), 1543 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1544) ENGINE=InnoDB DEFAULT CHARSET=latin1 1545alter table t2 add index id_test (id), add index id_test2 (id,id2); 1546show create table t2; 1547Table Create Table 1548t2 CREATE TABLE `t2` ( 1549 `id` int(11) NOT NULL AUTO_INCREMENT, 1550 `id2` int(11) NOT NULL, 1551 KEY `id_test` (`id`), 1552 KEY `id_test2` (`id`,`id2`), 1553 CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) 1554) ENGINE=InnoDB DEFAULT CHARSET=latin1 1555drop table t2; 1556create 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; 1557ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match 1558create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb; 1559show create table t2; 1560Table Create Table 1561t2 CREATE TABLE `t2` ( 1562 `a` int(11) NOT NULL AUTO_INCREMENT, 1563 `b` int(11) DEFAULT NULL, 1564 PRIMARY KEY (`a`), 1565 UNIQUE KEY `b_2` (`b`), 1566 KEY `b` (`b`), 1567 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) 1568) ENGINE=InnoDB DEFAULT CHARSET=latin1 1569drop table t2; 1570create 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; 1571show create table t2; 1572Table Create Table 1573t2 CREATE TABLE `t2` ( 1574 `a` int(11) NOT NULL AUTO_INCREMENT, 1575 `b` int(11) DEFAULT NULL, 1576 PRIMARY KEY (`a`), 1577 UNIQUE KEY `b` (`b`), 1578 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`), 1579 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) 1580) ENGINE=InnoDB DEFAULT CHARSET=latin1 1581drop table t2, t1; 1582create table t1 (c char(10), index (c,c)) engine=innodb; 1583ERROR 42S21: Duplicate column name 'c' 1584create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb; 1585ERROR 42S21: Duplicate column name 'c1' 1586create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb; 1587ERROR 42S21: Duplicate column name 'c1' 1588create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb; 1589ERROR 42S21: Duplicate column name 'c1' 1590create table t1 (c1 char(10), c2 char(10)) engine=innodb; 1591alter table t1 add key (c1,c1); 1592ERROR 42S21: Duplicate column name 'c1' 1593alter table t1 add key (c2,c1,c1); 1594ERROR 42S21: Duplicate column name 'c1' 1595alter table t1 add key (c1,c2,c1); 1596ERROR 42S21: Duplicate column name 'c1' 1597alter table t1 add key (c1,c1,c2); 1598ERROR 42S21: Duplicate column name 'c1' 1599drop table t1; 1600create table t1(a int(1) , b int(1)) engine=innodb; 1601insert into t1 values ('1111', '3333'); 1602select distinct concat(a, b) from t1; 1603concat(a, b) 160411113333 1605drop table t1; 1606CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1607INSERT INTO t1 VALUES (1),(2),(3); 1608CREATE 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), 1609CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1610INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); 1611SELECT * 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; 1612a_id b_list 16131 1,2,3 16142 4,5 16153 NULL 1616DROP TABLE t2; 1617DROP TABLE t1; 1618create temporary table t1 (a int) engine=innodb; 1619insert into t1 values (4711); 1620truncate t1; 1621insert into t1 values (42); 1622select * from t1; 1623a 162442 1625drop table t1; 1626create table t1 (a int) engine=innodb; 1627insert into t1 values (4711); 1628truncate t1; 1629insert into t1 values (42); 1630select * from t1; 1631a 163242 1633drop table t1; 1634create 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; 1635insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); 1636analyze table t1; 1637select * from t1 order by a,b,c,d; 1638a b c d e 16391 1 a 1 1 16402 2 b 2 2 16413 3 ab 3 3 1642explain select * from t1 order by a,b,c,d; 1643id select_type table type possible_keys key key_len ref rows Extra 16441 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1645drop table t1; 1646create table t1 (a char(1), b char(1), key(a, b)) engine=innodb; 1647insert into t1 values ('8', '6'), ('4', '7'); 1648select min(a) from t1; 1649min(a) 16504 1651select min(b) from t1 where a='8'; 1652min(b) 16536 1654drop table t1; 1655create table t1 (x bigint unsigned not null primary key) engine=innodb; 1656insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); 1657select * from t1; 1658x 165918446744073709551600 166018446744073709551601 1661select count(*) from t1 where x>0; 1662count(*) 16632 1664select count(*) from t1 where x=0; 1665count(*) 16660 1667select count(*) from t1 where x<0; 1668count(*) 16690 1670select count(*) from t1 where x < -16; 1671count(*) 16720 1673select count(*) from t1 where x = -16; 1674count(*) 16750 1676explain select count(*) from t1 where x > -16; 1677id select_type table type possible_keys key key_len ref rows Extra 16781 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index 1679select count(*) from t1 where x > -16; 1680count(*) 16812 1682select * from t1 where x > -16; 1683x 168418446744073709551600 168518446744073709551601 1686select count(*) from t1 where x = 18446744073709551601; 1687count(*) 16881 1689drop table t1; 1690SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; 1691variable_value 1692ok 1693SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; 1694variable_value 169516384 1696SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; 1697variable_value - @innodb_rows_deleted_orig 169871 1699SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; 1700variable_value - @innodb_rows_inserted_orig 1701964 1702SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; 1703variable_value - @innodb_rows_updated_orig 1704865 1705SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'; 1706variable_value - @innodb_row_lock_waits_orig 17070 1708SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits'; 1709variable_value - @innodb_row_lock_current_waits_orig 17100 1711SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time'; 1712variable_value - @innodb_row_lock_time_orig 17130 1714SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max'; 1715variable_value - @innodb_row_lock_time_max_orig 17160 1717SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg'; 1718variable_value - @innodb_row_lock_time_avg_orig 17190 1720set storage_engine=INNODB; 1721drop table if exists t1,t2,t3; 1722--- Testing varchar --- 1723--- Testing varchar --- 1724create table t1 (v varchar(10), c char(10), t text); 1725insert into t1 values('+ ', '+ ', '+ '); 1726set @a=repeat(' ',20); 1727insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1728Warnings: 1729Note 1265 Data truncated for column 'v' at row 1 1730select concat('*',v,'*',c,'*',t,'*') from t1; 1731concat('*',v,'*',c,'*',t,'*') 1732*+ *+*+ * 1733*+ *+*+ * 1734show create table t1; 1735Table Create Table 1736t1 CREATE TABLE `t1` ( 1737 `v` varchar(10) DEFAULT NULL, 1738 `c` char(10) DEFAULT NULL, 1739 `t` text DEFAULT NULL 1740) ENGINE=InnoDB DEFAULT CHARSET=latin1 1741create table t2 like t1; 1742show create table t2; 1743Table Create Table 1744t2 CREATE TABLE `t2` ( 1745 `v` varchar(10) DEFAULT NULL, 1746 `c` char(10) DEFAULT NULL, 1747 `t` text DEFAULT NULL 1748) ENGINE=InnoDB DEFAULT CHARSET=latin1 1749create table t3 select * from t1; 1750show create table t3; 1751Table Create Table 1752t3 CREATE TABLE `t3` ( 1753 `v` varchar(10) DEFAULT NULL, 1754 `c` char(10) DEFAULT NULL, 1755 `t` text DEFAULT NULL 1756) ENGINE=InnoDB DEFAULT CHARSET=latin1 1757alter table t1 modify c varchar(10); 1758show create table t1; 1759Table Create Table 1760t1 CREATE TABLE `t1` ( 1761 `v` varchar(10) DEFAULT NULL, 1762 `c` varchar(10) DEFAULT NULL, 1763 `t` text DEFAULT NULL 1764) ENGINE=InnoDB DEFAULT CHARSET=latin1 1765alter table t1 modify v char(10); 1766show create table t1; 1767Table Create Table 1768t1 CREATE TABLE `t1` ( 1769 `v` char(10) DEFAULT NULL, 1770 `c` varchar(10) DEFAULT NULL, 1771 `t` text DEFAULT NULL 1772) ENGINE=InnoDB DEFAULT CHARSET=latin1 1773alter table t1 modify t varchar(10); 1774Warnings: 1775Note 1265 Data truncated for column 't' at row 2 1776show create table t1; 1777Table Create Table 1778t1 CREATE TABLE `t1` ( 1779 `v` char(10) DEFAULT NULL, 1780 `c` varchar(10) DEFAULT NULL, 1781 `t` varchar(10) DEFAULT NULL 1782) ENGINE=InnoDB DEFAULT CHARSET=latin1 1783select concat('*',v,'*',c,'*',t,'*') from t1; 1784concat('*',v,'*',c,'*',t,'*') 1785*+*+*+ * 1786*+*+*+ * 1787drop table t1,t2,t3; 1788create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); 1789show create table t1; 1790Table Create Table 1791t1 CREATE TABLE `t1` ( 1792 `v` varchar(10) DEFAULT NULL, 1793 `c` char(10) DEFAULT NULL, 1794 `t` text DEFAULT NULL, 1795 KEY `v` (`v`), 1796 KEY `c` (`c`), 1797 KEY `t` (`t`(10)) 1798) ENGINE=InnoDB DEFAULT CHARSET=latin1 1799select count(*) from t1; 1800count(*) 1801270 1802insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1803select count(*) from t1 where v='a'; 1804count(*) 180510 1806select count(*) from t1 where c='a'; 1807count(*) 180810 1809select count(*) from t1 where t='a'; 1810count(*) 181110 1812select count(*) from t1 where v='a '; 1813count(*) 181410 1815select count(*) from t1 where c='a '; 1816count(*) 181710 1818select count(*) from t1 where t='a '; 1819count(*) 182010 1821select count(*) from t1 where v between 'a' and 'a '; 1822count(*) 182310 1824select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1825count(*) 182610 1827select count(*) from t1 where v like 'a%'; 1828count(*) 182911 1830select count(*) from t1 where c like 'a%'; 1831count(*) 183211 1833select count(*) from t1 where t like 'a%'; 1834count(*) 183511 1836select count(*) from t1 where v like 'a %'; 1837count(*) 18389 1839explain select count(*) from t1 where v='a '; 1840id select_type table type possible_keys key key_len ref rows Extra 18411 SIMPLE t1 ref v v 13 const # Using where; Using index 1842explain select count(*) from t1 where c='a '; 1843id select_type table type possible_keys key key_len ref rows Extra 18441 SIMPLE t1 ref c c 11 const # Using where; Using index 1845explain select count(*) from t1 where t='a '; 1846id select_type table type possible_keys key key_len ref rows Extra 18471 SIMPLE t1 ref t t 13 const # Using where 1848explain select count(*) from t1 where v like 'a%'; 1849id select_type table type possible_keys key key_len ref rows Extra 18501 SIMPLE t1 range v v 13 NULL # Using where; Using index 1851explain select count(*) from t1 where v between 'a' and 'a '; 1852id select_type table type possible_keys key key_len ref rows Extra 18531 SIMPLE t1 ref v v 13 const # Using where; Using index 1854explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1855id select_type table type possible_keys key key_len ref rows Extra 18561 SIMPLE t1 ref v v 13 const # Using where; Using index 1857alter table t1 add unique(v); 1858ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1859show warnings; 1860Level Code Message 1861Error 1062 Duplicate entry 'v' for key 'v_2' 1862alter table t1 add key(v); 1863Warnings: 1864Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release 1865select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1866qq 1867*a*a*a* 1868*a *a*a * 1869*a *a*a * 1870*a *a*a * 1871*a *a*a * 1872*a *a*a * 1873*a *a*a * 1874*a *a*a * 1875*a *a*a * 1876*a *a*a * 1877explain select * from t1 where v='a'; 1878id select_type table type possible_keys key key_len ref rows Extra 18791 SIMPLE t1 ref v,v_2 # 13 const # # 1880select v,count(*) from t1 group by v limit 10; 1881v count(*) 1882a 1 1883a 10 1884b 10 1885c 10 1886d 10 1887e 10 1888f 10 1889g 10 1890h 10 1891i 10 1892select v,count(t) from t1 group by v limit 10; 1893v count(t) 1894a 1 1895a 10 1896b 10 1897c 10 1898d 10 1899e 10 1900f 10 1901g 10 1902h 10 1903i 10 1904select v,count(c) from t1 group by v limit 10; 1905v count(c) 1906a 1 1907a 10 1908b 10 1909c 10 1910d 10 1911e 10 1912f 10 1913g 10 1914h 10 1915i 10 1916select sql_big_result v,count(t) from t1 group by v limit 10; 1917v count(t) 1918a 1 1919a 10 1920b 10 1921c 10 1922d 10 1923e 10 1924f 10 1925g 10 1926h 10 1927i 10 1928select sql_big_result v,count(c) from t1 group by v limit 10; 1929v count(c) 1930a 1 1931a 10 1932b 10 1933c 10 1934d 10 1935e 10 1936f 10 1937g 10 1938h 10 1939i 10 1940select c,count(*) from t1 group by c limit 10; 1941c count(*) 1942a 1 1943a 10 1944b 10 1945c 10 1946d 10 1947e 10 1948f 10 1949g 10 1950h 10 1951i 10 1952select c,count(t) from t1 group by c limit 10; 1953c count(t) 1954a 1 1955a 10 1956b 10 1957c 10 1958d 10 1959e 10 1960f 10 1961g 10 1962h 10 1963i 10 1964select sql_big_result c,count(t) from t1 group by c limit 10; 1965c count(t) 1966a 1 1967a 10 1968b 10 1969c 10 1970d 10 1971e 10 1972f 10 1973g 10 1974h 10 1975i 10 1976select t,count(*) from t1 group by t limit 10; 1977t count(*) 1978a 1 1979a 10 1980b 10 1981c 10 1982d 10 1983e 10 1984f 10 1985g 10 1986h 10 1987i 10 1988select t,count(t) from t1 group by t limit 10; 1989t count(t) 1990a 1 1991a 10 1992b 10 1993c 10 1994d 10 1995e 10 1996f 10 1997g 10 1998h 10 1999i 10 2000select sql_big_result t,count(t) from t1 group by t limit 10; 2001t count(t) 2002a 1 2003a 10 2004b 10 2005c 10 2006d 10 2007e 10 2008f 10 2009g 10 2010h 10 2011i 10 2012alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 2013show create table t1; 2014Table Create Table 2015t1 CREATE TABLE `t1` ( 2016 `v` varchar(300) DEFAULT NULL, 2017 `c` char(10) DEFAULT NULL, 2018 `t` text DEFAULT NULL, 2019 KEY `c` (`c`), 2020 KEY `t` (`t`(10)), 2021 KEY `v` (`v`) 2022) ENGINE=InnoDB DEFAULT CHARSET=latin1 2023select count(*) from t1 where v='a'; 2024count(*) 202510 2026select count(*) from t1 where v='a '; 2027count(*) 202810 2029select count(*) from t1 where v between 'a' and 'a '; 2030count(*) 203110 2032select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 2033count(*) 203410 2035select count(*) from t1 where v like 'a%'; 2036count(*) 203711 2038select count(*) from t1 where v like 'a %'; 2039count(*) 20409 2041explain select count(*) from t1 where v='a '; 2042id select_type table type possible_keys key key_len ref rows Extra 20431 SIMPLE t1 ref v v 303 const # Using where; Using index 2044explain select count(*) from t1 where v like 'a%'; 2045id select_type table type possible_keys key key_len ref rows Extra 20461 SIMPLE t1 range v v 303 NULL # Using where; Using index 2047explain select count(*) from t1 where v between 'a' and 'a '; 2048id select_type table type possible_keys key key_len ref rows Extra 20491 SIMPLE t1 ref v v 303 const # Using where; Using index 2050explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 2051id select_type table type possible_keys key key_len ref rows Extra 20521 SIMPLE t1 ref v v 303 const # Using where; Using index 2053explain select * from t1 where v='a'; 2054id select_type table type possible_keys key key_len ref rows Extra 20551 SIMPLE t1 ref v v 303 const # # 2056select v,count(*) from t1 group by v limit 10; 2057v count(*) 2058a 1 2059a 10 2060b 10 2061c 10 2062d 10 2063e 10 2064f 10 2065g 10 2066h 10 2067i 10 2068select v,count(t) from t1 group by v limit 10; 2069v count(t) 2070a 1 2071a 10 2072b 10 2073c 10 2074d 10 2075e 10 2076f 10 2077g 10 2078h 10 2079i 10 2080select sql_big_result v,count(t) from t1 group by v limit 10; 2081v count(t) 2082a 1 2083a 10 2084b 10 2085c 10 2086d 10 2087e 10 2088f 10 2089g 10 2090h 10 2091i 10 2092alter table t1 drop key v, add key v (v(30)); 2093show create table t1; 2094Table Create Table 2095t1 CREATE TABLE `t1` ( 2096 `v` varchar(300) DEFAULT NULL, 2097 `c` char(10) DEFAULT NULL, 2098 `t` text DEFAULT NULL, 2099 KEY `c` (`c`), 2100 KEY `t` (`t`(10)), 2101 KEY `v` (`v`(30)) 2102) ENGINE=InnoDB DEFAULT CHARSET=latin1 2103select count(*) from t1 where v='a'; 2104count(*) 210510 2106select count(*) from t1 where v='a '; 2107count(*) 210810 2109select count(*) from t1 where v between 'a' and 'a '; 2110count(*) 211110 2112select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 2113count(*) 211410 2115select count(*) from t1 where v like 'a%'; 2116count(*) 211711 2118select count(*) from t1 where v like 'a %'; 2119count(*) 21209 2121explain select count(*) from t1 where v='a '; 2122id select_type table type possible_keys key key_len ref rows Extra 21231 SIMPLE t1 ref v v 33 const # Using where 2124explain select count(*) from t1 where v like 'a%'; 2125id select_type table type possible_keys key key_len ref rows Extra 21261 SIMPLE t1 range v v 33 NULL # Using where 2127explain select count(*) from t1 where v between 'a' and 'a '; 2128id select_type table type possible_keys key key_len ref rows Extra 21291 SIMPLE t1 ref v v 33 const # Using where 2130explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 2131id select_type table type possible_keys key key_len ref rows Extra 21321 SIMPLE t1 ref v v 33 const # Using where 2133explain select * from t1 where v='a'; 2134id select_type table type possible_keys key key_len ref rows Extra 21351 SIMPLE t1 ref v v 33 const # # 2136select v,count(*) from t1 group by v limit 10; 2137v count(*) 2138a 1 2139a 10 2140b 10 2141c 10 2142d 10 2143e 10 2144f 10 2145g 10 2146h 10 2147i 10 2148select v,count(t) from t1 group by v limit 10; 2149v count(t) 2150a 1 2151a 10 2152b 10 2153c 10 2154d 10 2155e 10 2156f 10 2157g 10 2158h 10 2159i 10 2160select sql_big_result v,count(t) from t1 group by v limit 10; 2161v count(t) 2162a 1 2163a 10 2164b 10 2165c 10 2166d 10 2167e 10 2168f 10 2169g 10 2170h 10 2171i 10 2172alter table t1 modify v varchar(600), drop key v, add key v (v); 2173show create table t1; 2174Table Create Table 2175t1 CREATE TABLE `t1` ( 2176 `v` varchar(600) DEFAULT NULL, 2177 `c` char(10) DEFAULT NULL, 2178 `t` text DEFAULT NULL, 2179 KEY `c` (`c`), 2180 KEY `t` (`t`(10)), 2181 KEY `v` (`v`) 2182) ENGINE=InnoDB DEFAULT CHARSET=latin1 2183select v,count(*) from t1 group by v limit 10; 2184v count(*) 2185a 1 2186a 10 2187b 10 2188c 10 2189d 10 2190e 10 2191f 10 2192g 10 2193h 10 2194i 10 2195select v,count(t) from t1 group by v limit 10; 2196v count(t) 2197a 1 2198a 10 2199b 10 2200c 10 2201d 10 2202e 10 2203f 10 2204g 10 2205h 10 2206i 10 2207select sql_big_result v,count(t) from t1 group by v limit 10; 2208v count(t) 2209a 1 2210a 10 2211b 10 2212c 10 2213d 10 2214e 10 2215f 10 2216g 10 2217h 10 2218i 10 2219drop table t1; 2220create table t1 (a char(10), unique (a)); 2221insert into t1 values ('a '); 2222insert into t1 values ('a '); 2223ERROR 23000: Duplicate entry 'a' for key 'a' 2224alter table t1 modify a varchar(10); 2225insert into t1 values ('a '),('a '),('a '),('a '); 2226ERROR 23000: Duplicate entry 'a ' for key 'a' 2227insert into t1 values ('a '); 2228ERROR 23000: Duplicate entry 'a ' for key 'a' 2229insert into t1 values ('a '); 2230ERROR 23000: Duplicate entry 'a ' for key 'a' 2231insert into t1 values ('a '); 2232ERROR 23000: Duplicate entry 'a ' for key 'a' 2233update t1 set a='a ' where a like 'a%'; 2234select concat(a,'.') from t1; 2235concat(a,'.') 2236a . 2237update t1 set a='abc ' where a like 'a '; 2238select concat(a,'.') from t1; 2239concat(a,'.') 2240a . 2241update t1 set a='a ' where a like 'a %'; 2242select concat(a,'.') from t1; 2243concat(a,'.') 2244a . 2245update t1 set a='a ' where a like 'a '; 2246select concat(a,'.') from t1; 2247concat(a,'.') 2248a . 2249drop table t1; 2250create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 2251show create table t1; 2252Table Create Table 2253t1 CREATE TABLE `t1` ( 2254 `v` varchar(10) DEFAULT NULL, 2255 `c` char(10) DEFAULT NULL, 2256 `t` text DEFAULT NULL, 2257 KEY `v` (`v`(5)), 2258 KEY `c` (`c`(5)), 2259 KEY `t` (`t`(5)) 2260) ENGINE=InnoDB DEFAULT CHARSET=latin1 2261drop table t1; 2262create table t1 (v char(10) character set utf8); 2263show create table t1; 2264Table Create Table 2265t1 CREATE TABLE `t1` ( 2266 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 2267) ENGINE=InnoDB DEFAULT CHARSET=latin1 2268drop table t1; 2269create table t1 (v varchar(10), c char(10)) row_format=fixed; 2270Warnings: 2271Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. 2272show create table t1; 2273Table Create Table 2274t1 CREATE TABLE `t1` ( 2275 `v` varchar(10) DEFAULT NULL, 2276 `c` char(10) DEFAULT NULL 2277) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED 2278insert into t1 values('a','a'),('a ','a '); 2279select concat('*',v,'*',c,'*') from t1; 2280concat('*',v,'*',c,'*') 2281*a*a* 2282*a *a* 2283drop table t1; 2284create table t1 (v varchar(65530), key(v(10))); 2285insert into t1 values(repeat('a',65530)); 2286select length(v) from t1 where v=repeat('a',65530); 2287length(v) 228865530 2289drop table t1; 2290create table t1(a int, b varchar(12), key ba(b, a)); 2291insert into t1 values (1, 'A'), (20, NULL); 2292explain select * from t1 where a=20 and b is null; 2293id select_type table type possible_keys key key_len ref rows Extra 22941 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index 2295select * from t1 where a=20 and b is null; 2296a b 229720 NULL 2298drop table t1; 2299SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 2300create table t1 (v varchar(65530), key(v)); 2301Warnings: 2302Note 1071 Specified key was too long; max key length is 3072 bytes 2303drop table t1; 2304create table t1 (v varchar(65536)); 2305Warnings: 2306Note 1246 Converting column 'v' from VARCHAR to TEXT 2307show create table t1; 2308Table Create Table 2309t1 CREATE TABLE `t1` ( 2310 `v` mediumtext DEFAULT NULL 2311) ENGINE=InnoDB DEFAULT CHARSET=latin1 2312drop table t1; 2313create table t1 (v varchar(65530) character set utf8); 2314Warnings: 2315Note 1246 Converting column 'v' from VARCHAR to TEXT 2316show create table t1; 2317Table Create Table 2318t1 CREATE TABLE `t1` ( 2319 `v` mediumtext CHARACTER SET utf8 DEFAULT NULL 2320) ENGINE=InnoDB DEFAULT CHARSET=latin1 2321drop table t1; 2322SET sql_mode = default; 2323set default_storage_engine=MyISAM; 2324create table t1 (v varchar(16384)) engine=innodb; 2325drop table t1; 2326create table t1 (a char(1), b char(1), key(a, b)) engine=innodb; 2327insert into t1 values ('8', '6'), ('4', '7'); 2328select min(a) from t1; 2329min(a) 23304 2331select min(b) from t1 where a='8'; 2332min(b) 23336 2334drop table t1; 2335CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb; 2336insert into t1 (b) values (1); 2337replace into t1 (b) values (2), (1), (3); 2338select * from t1; 2339a b 23403 1 23412 2 23424 3 2343truncate table t1; 2344insert into t1 (b) values (1); 2345replace into t1 (b) values (2); 2346replace into t1 (b) values (1); 2347replace into t1 (b) values (3); 2348select * from t1; 2349a b 23503 1 23512 2 23524 3 2353drop table t1; 2354create table t1 (rowid int not null auto_increment, val int not null,primary 2355key (rowid), unique(val)) engine=innodb; 2356replace into t1 (val) values ('1'),('2'); 2357replace into t1 (val) values ('1'),('2'); 2358insert into t1 (val) values ('1'),('2'); 2359ERROR 23000: Duplicate entry '1' for key 'val' 2360select * from t1; 2361rowid val 23623 1 23634 2 2364drop table t1; 2365create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB; 2366insert into t1 (val) values (1); 2367update t1 set a=2 where a=1; 2368insert into t1 (val) values (3); 2369select * from t1; 2370a val 23712 1 23723 3 2373insert into t1 values (2, 2); 2374ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 2375select * from t1; 2376a val 23772 1 23783 3 2379drop table t1; 2380CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB; 2381INSERT INTO t1 (GRADE) VALUES (151),(252),(343); 2382SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; 2383GRADE 2384252 2385SELECT GRADE FROM t1 WHERE GRADE= 151; 2386GRADE 2387151 2388DROP TABLE t1; 2389create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb; 2390create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb; 2391insert into t2 values ('aa','cc'); 2392insert into t1 values ('aa','bb'),('aa','cc'); 2393delete t1 from t1,t2 where f1=f3 and f4='cc'; 2394select * from t1; 2395f1 f2 2396drop table t1,t2; 2397CREATE TABLE t1 ( 2398id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) 2399) ENGINE=InnoDB; 2400CREATE TABLE t2 ( 2401id INTEGER NOT NULL, 2402FOREIGN KEY (id) REFERENCES t1 (id) 2403) ENGINE=InnoDB; 2404INSERT INTO t1 (id) VALUES (NULL); 2405SELECT * FROM t1; 2406id 24071 2408TRUNCATE t1; 2409ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test`.`t1` (`id`)) 2410INSERT INTO t1 (id) VALUES (NULL); 2411SELECT * FROM t1; 2412id 24131 24142 2415DELETE FROM t1; 2416TRUNCATE t1; 2417ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test`.`t1` (`id`)) 2418INSERT INTO t1 (id) VALUES (NULL); 2419SELECT * FROM t1; 2420id 24213 2422DROP TABLE t2; 2423TRUNCATE t1; 2424INSERT INTO t1 (id) VALUES (NULL); 2425SELECT * FROM t1; 2426id 24271 2428DROP TABLE t1; 2429CREATE TABLE t1 2430( 2431id INT PRIMARY KEY 2432) ENGINE=InnoDB; 2433CREATE TEMPORARY TABLE t2 2434( 2435id INT NOT NULL PRIMARY KEY, 2436b INT, 2437FOREIGN KEY (b) REFERENCES test.t1(id) 2438) ENGINE=InnoDB; 2439ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 2440DROP TABLE t1; 2441create table t1 (col1 varchar(2000), index (col1(767))) 2442character set = latin1 engine = innodb; 2443create table t2 (col1 char(255), index (col1)) 2444character set = latin1 engine = innodb; 2445create table t3 (col1 binary(255), index (col1)) 2446character set = latin1 engine = innodb; 2447create table t4 (col1 varchar(767), index (col1)) 2448character set = latin1 engine = innodb; 2449create table t5 (col1 varchar(767) primary key) 2450character set = latin1 engine = innodb; 2451create table t6 (col1 varbinary(767) primary key) 2452character set = latin1 engine = innodb; 2453create table t7 (col1 text, index(col1(767))) 2454character set = latin1 engine = innodb; 2455create table t8 (col1 blob, index(col1(767))) 2456character set = latin1 engine = innodb; 2457create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2)) 2458character set = latin1 engine = innodb; 2459show create table t9; 2460Table Create Table 2461t9 CREATE TABLE `t9` ( 2462 `col1` varchar(512) DEFAULT NULL, 2463 `col2` varchar(512) DEFAULT NULL, 2464 KEY `col1` (`col1`,`col2`) 2465) ENGINE=InnoDB DEFAULT CHARSET=latin1 2466drop table t1, t2, t3, t4, t5, t6, t7, t8, t9; 2467CREATE TABLE t1 2468( 2469id INT PRIMARY KEY 2470) ENGINE=InnoDB; 2471CREATE TABLE t2 2472( 2473v INT, 2474CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id) 2475) ENGINE=InnoDB; 2476INSERT INTO t2 VALUES(2); 2477ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) 2478INSERT INTO t1 VALUES(1); 2479INSERT INTO t2 VALUES(1); 2480DELETE FROM t1 WHERE id = 1; 2481ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) 2482DROP TABLE t1; 2483ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) 2484SET FOREIGN_KEY_CHECKS=0; 2485DROP TABLE t1; 2486SET FOREIGN_KEY_CHECKS=1; 2487INSERT INTO t2 VALUES(3); 2488ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) 2489DROP TABLE t2; 2490connect a,localhost,root,,; 2491connect b,localhost,root,,; 2492connection a; 2493create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; 2494insert into t1 values (1),(2); 2495set autocommit=0; 2496checksum table t1; 2497Table Checksum 2498test.t1 1531596814 2499connection b; 2500insert into t1 values(3); 2501connection a; 2502checksum table t1; 2503Table Checksum 2504test.t1 1531596814 2505connection a; 2506commit; 2507checksum table t1; 2508Table Checksum 2509test.t1 2050879373 2510commit; 2511drop table t1; 2512connection a; 2513create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; 2514insert into t1 values (1),(2); 2515set autocommit=1; 2516checksum table t1; 2517Table Checksum 2518test.t1 1531596814 2519connection b; 2520set autocommit=1; 2521insert into t1 values(3); 2522connection a; 2523checksum table t1; 2524Table Checksum 2525test.t1 2050879373 2526drop table t1; 2527connection default; 2528disconnect a; 2529disconnect b; 2530set foreign_key_checks=0; 2531create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; 2532create table t1(a char(10) primary key, b varchar(20)) engine = innodb; 2533ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") 2534set foreign_key_checks=1; 2535drop table t2; 2536set foreign_key_checks=0; 2537create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; 2538create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; 2539ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") 2540set foreign_key_checks=1; 2541drop table t1; 2542set foreign_key_checks=0; 2543create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; 2544create table t1(a varchar(10) primary key) engine = innodb; 2545alter table t1 modify column a int; 2546set foreign_key_checks=1; 2547drop table t2,t1; 2548set foreign_key_checks=0; 2549create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; 2550create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; 2551alter table t1 convert to character set utf8; 2552set foreign_key_checks=1; 2553drop table t2,t1; 2554call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition."); 2555set foreign_key_checks=0; 2556create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; 2557create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; 2558rename table t3 to t1; 2559ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150 "Foreign key constraint is incorrectly formed") 2560set foreign_key_checks=1; 2561drop table t2,t3; 2562create table t1(a int primary key) row_format=redundant engine=innodb; 2563create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb; 2564create table t3(a int primary key) row_format=compact engine=innodb; 2565create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb; 2566insert into t1 values(1); 2567insert into t3 values(1); 2568insert into t2 values(2); 2569ERROR 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`)) 2570insert into t4 values(2); 2571ERROR 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`)) 2572insert into t2 values(1); 2573insert into t4 values(1); 2574update t1 set a=2; 2575ERROR 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`)) 2576update t2 set a=2; 2577ERROR 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`)) 2578update t3 set a=2; 2579ERROR 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`)) 2580update t4 set a=2; 2581ERROR 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`)) 2582truncate t1; 2583ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)) 2584truncate t3; 2585ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`)) 2586truncate t2; 2587truncate t4; 2588truncate t1; 2589ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)) 2590truncate t3; 2591ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`)) 2592drop table t4,t3,t2,t1; 2593create table t1 (a varchar(255) character set utf8, 2594b varchar(255) character set utf8, 2595c varchar(255) character set utf8, 2596d varchar(255) character set utf8, 2597key (a,b,c,d)) engine=innodb; 2598drop table t1; 2599create table t1 (a varchar(255) character set utf8, 2600b varchar(255) character set utf8, 2601c varchar(255) character set utf8, 2602d varchar(255) character set utf8, 2603e varchar(255) character set utf8, 2604key (a,b,c,d,e)) engine=innodb; 2605ERROR 42000: Specified key was too long; max key length is 3072 bytes 2606SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 2607create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb; 2608create table t2 (s1 binary(2),primary key (s1)) engine=innodb; 2609create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb; 2610create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb; 2611insert into t1 values (0x41),(0x4120),(0x4100); 2612insert into t2 values (0x41),(0x4120),(0x4100); 2613ERROR 23000: Duplicate entry 'A' for key 'PRIMARY' 2614insert into t2 values (0x41),(0x4120); 2615insert into t3 values (0x41),(0x4120),(0x4100); 2616ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY' 2617insert into t3 values (0x41),(0x4100); 2618insert into t4 values (0x41),(0x4120),(0x4100); 2619ERROR 23000: Duplicate entry 'A' for key 'PRIMARY' 2620insert into t4 values (0x41),(0x4100); 2621select hex(s1) from t1; 2622hex(s1) 262341 26244100 26254120 2626select hex(s1) from t2; 2627hex(s1) 26284100 26294120 2630select hex(s1) from t3; 2631hex(s1) 26324100 263341 2634select hex(s1) from t4; 2635hex(s1) 26364100 263741 2638drop table t1,t2,t3,t4; 2639create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb; 2640create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; 2641insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42); 2642insert into t2 values(0x42); 2643ERROR 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) 2644insert into t2 values(0x41); 2645select hex(s1) from t2; 2646hex(s1) 26474100 2648update t1 set s1=0x123456 where a=2; 2649select hex(s1) from t2; 2650hex(s1) 26514100 2652update t1 set s1=0x12 where a=1; 2653ERROR 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) 2654update t1 set s1=0x12345678 where a=1; 2655ERROR 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) 2656update t1 set s1=0x123457 where a=1; 2657ERROR 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) 2658update t1 set s1=0x1220 where a=1; 2659select hex(s1) from t2; 2660hex(s1) 26611220 2662update t1 set s1=0x1200 where a=1; 2663select hex(s1) from t2; 2664hex(s1) 26651200 2666update t1 set s1=0x4200 where a=1; 2667select hex(s1) from t2; 2668hex(s1) 26694200 2670delete from t1 where a=1; 2671ERROR 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) 2672delete from t1 where a=2; 2673update t2 set s1=0x4120; 2674delete from t1; 2675ERROR 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) 2676delete from t1 where a!=3; 2677select a,hex(s1) from t1; 2678a hex(s1) 26793 4120 2680select hex(s1) from t2; 2681hex(s1) 26824120 2683drop table t2,t1; 2684create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb; 2685create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; 2686insert into t1 values(1,0x4100),(2,0x41); 2687insert into t2 values(0x41); 2688select hex(s1) from t2; 2689hex(s1) 269041 2691update t1 set s1=0x1234 where a=1; 2692select hex(s1) from t2; 2693hex(s1) 269441 2695update t1 set s1=0x12 where a=2; 2696select hex(s1) from t2; 2697hex(s1) 269812 2699delete from t1 where a=1; 2700delete from t1 where a=2; 2701ERROR 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) 2702select a,hex(s1) from t1; 2703a hex(s1) 27042 12 2705select hex(s1) from t2; 2706hex(s1) 270712 2708drop table t2,t1; 2709CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB; 2710CREATE TABLE t2(a INT) ENGINE=InnoDB; 2711ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); 2712ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; 2713ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a); 2714ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0; 2715SHOW CREATE TABLE t2; 2716Table Create Table 2717t2 CREATE TABLE `t2` ( 2718 `a` int(11) DEFAULT NULL, 2719 KEY `t2_ibfk_0` (`a`) 2720) ENGINE=InnoDB DEFAULT CHARSET=latin1 2721DROP TABLE t2,t1; 2722SET sql_mode = default; 2723CREATE TABLE t1 ( 2724field1 varchar(8) NOT NULL DEFAULT '', 2725field2 varchar(8) NOT NULL DEFAULT '', 2726PRIMARY KEY (field1, field2) 2727) ENGINE=InnoDB; 2728CREATE TABLE t2 ( 2729field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY, 2730FOREIGN KEY (field1) REFERENCES t1 (field1) 2731ON DELETE CASCADE ON UPDATE CASCADE 2732) ENGINE=InnoDB; 2733INSERT INTO t1 VALUES ('old', 'somevalu'); 2734INSERT INTO t1 VALUES ('other', 'anyvalue'); 2735INSERT INTO t2 VALUES ('old'); 2736INSERT INTO t2 VALUES ('other'); 2737UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu'; 2738ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'PRIMARY' 2739DROP TABLE t2; 2740DROP TABLE t1; 2741create table t1 ( 2742c1 bigint not null, 2743c2 bigint not null, 2744primary key (c1), 2745unique key (c2) 2746) engine=innodb; 2747create table t2 ( 2748c1 bigint not null, 2749primary key (c1) 2750) engine=innodb; 2751alter table t1 add constraint c2_fk foreign key (c2) 2752references t2(c1) on delete cascade; 2753show create table t1; 2754Table Create Table 2755t1 CREATE TABLE `t1` ( 2756 `c1` bigint(20) NOT NULL, 2757 `c2` bigint(20) NOT NULL, 2758 PRIMARY KEY (`c1`), 2759 UNIQUE KEY `c2` (`c2`), 2760 CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE 2761) ENGINE=InnoDB DEFAULT CHARSET=latin1 2762alter table t1 drop foreign key c2_fk; 2763show create table t1; 2764Table Create Table 2765t1 CREATE TABLE `t1` ( 2766 `c1` bigint(20) NOT NULL, 2767 `c2` bigint(20) NOT NULL, 2768 PRIMARY KEY (`c1`), 2769 UNIQUE KEY `c2` (`c2`) 2770) ENGINE=InnoDB DEFAULT CHARSET=latin1 2771drop table t1, t2; 2772create table t1(a date) engine=innodb; 2773create table t2(a date, key(a)) engine=innodb; 2774insert into t1 values('2005-10-01'); 2775insert into t2 values('2005-10-01'); 2776select * from t1, t2 2777where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; 2778a a 27792005-10-01 2005-10-01 2780drop table t1, t2; 2781create table t1 (id int not null, f_id int not null, f int not null, 2782primary key(f_id, id)) engine=innodb; 2783create table t2 (id int not null,s_id int not null,s varchar(200), 2784primary key(id)) engine=innodb; 2785INSERT INTO t1 VALUES (8, 1, 3); 2786INSERT INTO t1 VALUES (1, 2, 1); 2787INSERT INTO t2 VALUES (1, 0, ''); 2788INSERT INTO t2 VALUES (8, 1, ''); 2789commit; 2790DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 2791WHERE mm.id IS NULL; 2792select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 2793where mm.id is null lock in share mode; 2794id f_id f 2795drop table t1,t2; 2796connect a,localhost,root,,; 2797connect b,localhost,root,,; 2798connection a; 2799create table t1(a int not null, b int, primary key(a)) engine=innodb; 2800insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 2801commit; 2802SET binlog_format='MIXED'; 2803set autocommit = 0; 2804SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2805update t1 set b = 5 where b = 1; 2806connection b; 2807SET binlog_format='MIXED'; 2808set autocommit = 0; 2809SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2810select * from t1 where a = 7 and b = 3 for update; 2811a b 28127 3 2813connection a; 2814commit; 2815connection b; 2816commit; 2817drop table t1; 2818connection default; 2819disconnect a; 2820disconnect b; 2821connect a,localhost,root,,; 2822connect b,localhost,root,,; 2823connection a; 2824create table t1(a int not null, b int, primary key(a)) engine=innodb; 2825insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2); 2826commit; 2827set autocommit = 0; 2828select * from t1 lock in share mode; 2829a b 28301 1 28312 2 28323 1 28334 2 28345 1 28356 2 2836update t1 set b = 5 where b = 1; 2837connection b; 2838set autocommit = 0; 2839select * from t1 where a = 2 and b = 2 for update; 2840ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2841connection a; 2842commit; 2843connection b; 2844commit; 2845connection default; 2846disconnect a; 2847disconnect b; 2848drop table t1; 2849connect a,localhost,root,,; 2850connect b,localhost,root,,; 2851connection a; 2852create table t1(a int not null, b int, primary key(a)) engine=innodb; 2853insert into t1 values (1,2),(5,3),(4,2); 2854create table t2(d int not null, e int, primary key(d)) engine=innodb; 2855insert into t2 values (8,6),(12,1),(3,1); 2856commit; 2857set autocommit = 0; 2858select * from t2 for update; 2859d e 28603 1 28618 6 286212 1 2863connection b; 2864SET binlog_format='MIXED'; 2865set autocommit = 0; 2866SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2867insert into t1 select * from t2; 2868update t1 set b = (select e from t2 where a = d); 2869create table t3(d int not null, e int, primary key(d)) engine=innodb 2870select * from t2; 2871commit; 2872connection a; 2873commit; 2874connection default; 2875disconnect a; 2876disconnect b; 2877drop table t1, t2, t3; 2878connect a,localhost,root,,; 2879connect b,localhost,root,,; 2880connect c,localhost,root,,; 2881connect d,localhost,root,,; 2882connect e,localhost,root,,; 2883connect f,localhost,root,,; 2884connect g,localhost,root,,; 2885connect h,localhost,root,,; 2886connect i,localhost,root,,; 2887connect j,localhost,root,,; 2888connection a; 2889create table t1(a int not null, b int, primary key(a)) engine=innodb; 2890insert into t1 values (1,2),(5,3),(4,2); 2891create table t2(a int not null, b int, primary key(a)) engine=innodb; 2892insert into t2 values (8,6),(12,1),(3,1); 2893create table t3(d int not null, b int, primary key(d)) engine=innodb; 2894insert into t3 values (8,6),(12,1),(3,1); 2895create table t5(a int not null, b int, primary key(a)) engine=innodb; 2896insert into t5 values (1,2),(5,3),(4,2); 2897create table t6(d int not null, e int, primary key(d)) engine=innodb; 2898insert into t6 values (8,6),(12,1),(3,1); 2899create table t8(a int not null, b int, primary key(a)) engine=innodb; 2900insert into t8 values (1,2),(5,3),(4,2); 2901create table t9(d int not null, e int, primary key(d)) engine=innodb; 2902insert into t9 values (8,6),(12,1),(3,1); 2903commit; 2904set autocommit = 0; 2905select * from t2 for update; 2906a b 29073 1 29088 6 290912 1 2910connection b; 2911SET binlog_format='MIXED'; 2912set autocommit = 0; 2913SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2914insert into t1 select * from t2; 2915connection c; 2916SET binlog_format='MIXED'; 2917set autocommit = 0; 2918SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2919update t3 set b = (select b from t2 where a = d); 2920connection d; 2921SET binlog_format='MIXED'; 2922set autocommit = 0; 2923SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2924create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; 2925connection e; 2926SET binlog_format='MIXED'; 2927set autocommit = 0; 2928SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2929insert into t5 (select * from t2 lock in share mode); 2930connection f; 2931SET binlog_format='MIXED'; 2932set autocommit = 0; 2933SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2934update t6 set e = (select b from t2 where a = d lock in share mode); 2935connection g; 2936SET binlog_format='MIXED'; 2937set autocommit = 0; 2938SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2939create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; 2940connection h; 2941SET binlog_format='MIXED'; 2942set autocommit = 0; 2943SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2944insert into t8 (select * from t2 for update); 2945connection i; 2946SET binlog_format='MIXED'; 2947set autocommit = 0; 2948SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2949update t9 set e = (select b from t2 where a = d for update); 2950connection j; 2951SET binlog_format='MIXED'; 2952set autocommit = 0; 2953SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2954create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; 2955connection b; 2956ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2957connection c; 2958ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2959connection d; 2960ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2961connection e; 2962ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2963connection f; 2964ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2965connection g; 2966ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2967connection h; 2968ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2969connection i; 2970ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2971connection j; 2972ERROR HY000: Lock wait timeout exceeded; try restarting transaction 2973connection a; 2974commit; 2975connection default; 2976disconnect a; 2977disconnect b; 2978disconnect c; 2979disconnect d; 2980disconnect e; 2981disconnect f; 2982disconnect g; 2983disconnect h; 2984disconnect i; 2985disconnect j; 2986drop table t1, t2, t3, t5, t6, t8, t9; 2987CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; 2988ERROR 42000: Incorrect column name 'DB_ROW_ID' 2989CREATE TABLE t1 ( 2990a BIGINT(20) NOT NULL, 2991PRIMARY KEY (a) 2992) ENGINE=INNODB DEFAULT CHARSET=UTF8; 2993CREATE TABLE t2 ( 2994a BIGINT(20) NOT NULL, 2995b VARCHAR(128) NOT NULL, 2996c TEXT NOT NULL, 2997PRIMARY KEY (a,b), 2998KEY idx_t2_b_c (b,c(100)), 2999CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a) 3000ON DELETE CASCADE 3001) ENGINE=INNODB DEFAULT CHARSET=UTF8; 3002INSERT INTO t1 VALUES (1); 3003INSERT INTO t2 VALUES (1, 'bar', 'vbar'); 3004INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR'); 3005INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi'); 3006INSERT INTO t2 VALUES (1, 'customer_over', '1'); 3007SELECT * FROM t2 WHERE b = 'customer_over'; 3008a b c 30091 customer_over 1 3010SELECT * FROM t2 WHERE BINARY b = 'customer_over'; 3011a b c 30121 customer_over 1 3013SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over'; 3014a 30151 3016/* Bang: Empty result set, above was expected: */ 3017SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; 3018a 30191 3020SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; 3021a 30221 3023drop table t2, t1; 3024CREATE TABLE t1 ( a int ) ENGINE=innodb; 3025BEGIN; 3026INSERT INTO t1 VALUES (1); 3027OPTIMIZE TABLE t1; 3028Table Op Msg_type Msg_text 3029test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 3030test.t1 optimize status OK 3031DROP TABLE t1; 3032CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB; 3033CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL, 3034CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id) 3035ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB; 3036ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON 3037DELETE CASCADE ON UPDATE CASCADE; 3038SHOW CREATE TABLE t2; 3039Table Create Table 3040t2 CREATE TABLE `t2` ( 3041 `id` int(11) NOT NULL, 3042 `f` int(11) NOT NULL, 3043 PRIMARY KEY (`id`), 3044 KEY `f` (`f`), 3045 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE, 3046 CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 3047) ENGINE=InnoDB DEFAULT CHARSET=latin1 3048DROP TABLE t2, t1; 3049CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB; 3050CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB; 3051INSERT INTO t1 VALUES (1); 3052INSERT INTO t2 VALUES (1); 3053ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; 3054set @old_sql_mode = @@sql_mode; 3055set @@sql_mode = 'STRICT_TRANS_TABLES'; 3056ALTER TABLE t2 MODIFY a INT NOT NULL; 3057ERROR HY000: Column 'a' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL 3058set @@sql_mode = @old_sql_mode; 3059DELETE FROM t1; 3060DROP TABLE t2,t1; 3061CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) 3062ENGINE=InnoDB; 3063INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); 3064DELETE FROM t1; 3065INSERT INTO t1 VALUES ('DDD'); 3066SELECT * FROM t1; 3067a 3068DDD 3069DROP TABLE t1; 3070CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB 3071AUTO_INCREMENT=42; 3072INSERT INTO t1 VALUES (0),(347),(0); 3073SELECT * FROM t1; 3074id 307542 3076347 3077348 3078SHOW CREATE TABLE t1; 3079Table Create Table 3080t1 CREATE TABLE `t1` ( 3081 `id` int(11) NOT NULL AUTO_INCREMENT, 3082 PRIMARY KEY (`id`) 3083) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1 3084CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB; 3085INSERT INTO t2 VALUES(42),(347),(348); 3086ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id); 3087SHOW CREATE TABLE t1; 3088Table Create Table 3089t1 CREATE TABLE `t1` ( 3090 `id` int(11) NOT NULL AUTO_INCREMENT, 3091 PRIMARY KEY (`id`), 3092 CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`) 3093) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1 3094DROP TABLE t1,t2; 3095SET innodb_strict_mode=ON; 3096CREATE TABLE t1 ( 3097c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255), 3098c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255), 3099c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255), 3100c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255), 3101c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255), 3102c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255), 3103c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255), 3104c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255) 3105) ENGINE = InnoDB; 3106ERROR 42000: Row size too large (> {checked_valid}). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. 3107SET innodb_strict_mode=OFF; 3108DROP TABLE IF EXISTS t1; 3109Warnings: 3110Note 1051 Unknown table 'test.t1' 3111CREATE TABLE t1( 3112id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY 3113) ENGINE=InnoDB; 3114INSERT INTO t1 VALUES(-10); 3115SELECT * FROM t1; 3116id 3117-10 3118INSERT INTO t1 VALUES(NULL); 3119SELECT * FROM t1; 3120id 3121-10 31221 3123DROP TABLE t1; 3124CONNECT c1,localhost,root,,; 3125CONNECT c2,localhost,root,,; 3126connection c1; 3127SET binlog_format='MIXED'; 3128SET TX_ISOLATION='read-committed'; 3129SET AUTOCOMMIT=0; 3130DROP TABLE IF EXISTS t1, t2; 3131Warnings: 3132Note 1051 Unknown table 'test.t1' 3133Note 1051 Unknown table 'test.t2' 3134CREATE TABLE t1 ( a int ) ENGINE=InnoDB; 3135CREATE TABLE t2 LIKE t1; 3136SELECT * FROM t2; 3137a 3138connection c2; 3139SET binlog_format='MIXED'; 3140SET TX_ISOLATION='read-committed'; 3141SET AUTOCOMMIT=0; 3142INSERT INTO t1 VALUES (1); 3143COMMIT; 3144connection c1; 3145SELECT * FROM t1 WHERE a=1; 3146a 31471 3148disconnect c1; 3149disconnect c2; 3150CONNECT c1,localhost,root,,; 3151CONNECT c2,localhost,root,,; 3152connection c1; 3153SET binlog_format='MIXED'; 3154SET TX_ISOLATION='read-committed'; 3155SET AUTOCOMMIT=0; 3156SELECT * FROM t2; 3157a 3158connection c2; 3159SET binlog_format='MIXED'; 3160SET TX_ISOLATION='read-committed'; 3161SET AUTOCOMMIT=0; 3162INSERT INTO t1 VALUES (2); 3163COMMIT; 3164connection c1; 3165SELECT * FROM t1 WHERE a=2; 3166a 31672 3168SELECT * FROM t1 WHERE a=2; 3169a 31702 3171DROP TABLE t1; 3172DROP TABLE t2; 3173disconnect c1; 3174disconnect c2; 3175connection default; 3176create table t1 (i int, j int) engine=innodb; 3177insert into t1 (i, j) values (1, 1), (2, 2); 3178update t1 set j = 2; 3179affected rows: 1 3180info: Rows matched: 2 Changed: 1 Warnings: 0 3181drop table t1; 3182create table t1 (id int) comment='this is a comment' engine=innodb; 3183select table_comment, data_free > 0 as data_free_is_set 3184from information_schema.tables 3185where table_schema='test' and table_name = 't1'; 3186table_comment data_free_is_set 3187this is a comment 1 3188drop table t1; 3189connection default; 3190CREATE TABLE t1 ( 3191c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 3192c2 VARCHAR(128) NOT NULL, 3193PRIMARY KEY(c1) 3194) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; 3195CREATE TABLE t2 ( 3196c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 3197c2 INT(10) UNSIGNED DEFAULT NULL, 3198PRIMARY KEY(c1) 3199) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200; 3200SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; 3201AUTO_INCREMENT 3202200 3203ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1); 3204SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; 3205AUTO_INCREMENT 3206200 3207DROP TABLE t2; 3208DROP TABLE t1; 3209connection default; 3210CREATE TABLE t1 (c1 int default NULL, 3211c2 int default NULL 3212) ENGINE=InnoDB DEFAULT CHARSET=latin1; 3213TRUNCATE TABLE t1; 3214affected rows: 0 3215INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 3216affected rows: 5 3217info: Records: 5 Duplicates: 0 Warnings: 0 3218TRUNCATE TABLE t1; 3219affected rows: 0 3220DROP TABLE t1; 3221Variable_name Value 3222Handler_update 0 3223Variable_name Value 3224Handler_delete 0 3225Variable_name Value 3226Handler_update 1 3227Variable_name Value 3228Handler_delete 1 3229DROP TABLE bug58912; 3230create table t1 (f1 integer primary key) engine=innodb; 3231flush status; 3232show status like "handler_read_key"; 3233Variable_name Value 3234Handler_read_key 0 3235select f1 from t1; 3236f1 3237show status like "handler_read_key"; 3238Variable_name Value 3239Handler_read_key 0 3240drop table t1; 3241CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; 3242CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB; 3243START TRANSACTION READ ONLY; 3244INSERT INTO t2 VALUES(0); 3245INSERT INTO t1 VALUES(0); 3246ERROR 25006: Cannot execute statement in a READ ONLY transaction 3247ROLLBACK; 3248SELECT * FROM t1; 3249c1 3250SELECT * FROM t2; 3251c1 3252START TRANSACTION READ ONLY; 3253INSERT INTO t1 VALUES(0); 3254ERROR 25006: Cannot execute statement in a READ ONLY transaction 3255INSERT INTO t2 VALUES(1); 3256COMMIT; 3257SET TRANSACTION READ ONLY; 3258START TRANSACTION; 3259INSERT INTO t2 VALUES(3); 3260INSERT INTO t1 VALUES(0); 3261ERROR 25006: Cannot execute statement in a READ ONLY transaction 3262COMMIT; 3263SELECT * FROM t1; 3264c1 3265SELECT * FROM t2; 3266c1 32671 32683 3269DROP TABLE t2; 3270CREATE TEMPORARY TABLE t2 ( 3271c1 INT AUTO_INCREMENT PRIMARY KEY, 3272c2 INT, INDEX idx(c2)) ENGINE=InnoDB; 3273SHOW CREATE TABLE t2; 3274Table Create Table 3275t2 CREATE TEMPORARY TABLE `t2` ( 3276 `c1` int(11) NOT NULL AUTO_INCREMENT, 3277 `c2` int(11) DEFAULT NULL, 3278 PRIMARY KEY (`c1`), 3279 KEY `idx` (`c2`) 3280) ENGINE=InnoDB DEFAULT CHARSET=latin1 3281START TRANSACTION READ ONLY; 3282INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); 3283INSERT INTO t1 VALUES(0); 3284ERROR 25006: Cannot execute statement in a READ ONLY transaction 3285ROLLBACK; 3286SELECT * FROM t1; 3287c1 3288SELECT * FROM t2; 3289c1 c2 3290START TRANSACTION READ ONLY; 3291INSERT INTO t1 VALUES(0); 3292ERROR 25006: Cannot execute statement in a READ ONLY transaction 3293INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); 3294COMMIT; 3295SET TRANSACTION READ ONLY; 3296START TRANSACTION; 3297INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); 3298INSERT INTO t1 VALUES(0); 3299ERROR 25006: Cannot execute statement in a READ ONLY transaction 3300COMMIT; 3301SHOW CREATE TABLE t2; 3302Table Create Table 3303t2 CREATE TEMPORARY TABLE `t2` ( 3304 `c1` int(11) NOT NULL AUTO_INCREMENT, 3305 `c2` int(11) DEFAULT NULL, 3306 PRIMARY KEY (`c1`), 3307 KEY `idx` (`c2`) 3308) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 3309SELECT * FROM t1; 3310c1 3311SELECT * FROM t2; 3312c1 c2 33134 1 33147 1 33155 2 33168 2 33176 3 33189 3 3319DROP TABLE t1; 3320DROP TABLE t2; 3321# 3322# MDEV-24748 Extern field check missing 3323# in btr_index_rec_validate() 3324# 3325CREATE TABLE t1 (pk INT, c1 char(255), 3326c2 char(255), c3 char(255), c4 char(255), 3327c5 char(255), c6 char(255), c7 char(255), 3328c8 char(255), primary key (pk) 3329) CHARACTER SET utf32 ENGINE=InnoDB; 3330INSERT INTO t1 VALUES 3331(1, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'), 3332(2, 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p'); 3333CHECK TABLE t1; 3334Table Op Msg_type Msg_text 3335test.t1 check status OK 3336ALTER TABLE t1 FORCE; 3337DROP TABLE t1; 3338