1################################################################################ 2# # 3# include/mix2.inc # 4# # 5# This is a derivate of t/innodb.test and has to be maintained by MySQL # 6# guys only. # 7# # 8# Please, DO NOT create a toplevel testcase mix2_innodb.test, because # 9# innodb.test does already these tests. # 10# # 11# Variables which have to be set before calling this script: # 12# $engine_type -- Storage engine to be tested # 13# $other_engine_type -- storage engine <> $engine_type # 14# $other_engine_type1 -- storage engine <> $engine_type # 15# storage engine <> $other_engine_type, if possible # 16# $other_non_trans_engine_type -- storage engine <> $engine_type # 17# $other_non_trans_engine_type must be a non # 18# transactional storage engine # 19# $other_non_live_chks_engine_type # 20# -- storage engine <> $engine_type, if possible # 21# storage engine must not support live checksum # 22# $other_live_chks_engine_type # 23# -- storage engine <> $engine_type, if possible # 24# storage engine must support live checksum # 25# General Note: The $other_*_engine_type variables must point to all # 26# time available storage engines # 27# 2006-08 MySQL 5.1 MyISAM and MEMORY only # 28# $test_transactions -- 0, skip transactional tests # 29# -- 1, do not skip transactional tests # 30# $test_foreign_keys -- 0, skip foreign key tests # 31# -- 1, do not skip foreign key tests # 32# $fulltext_query_unsupported -- 0, execute fulltext_query tests # 33# -- 1, skip fulltext query tests # 34# $no_autoinc_update -- 0, skip tests where it is expected that an update # 35# does not update the internal auto-increment value# 36# -- 1, do not skip these tests # 37# $no_spatial_key -- 0, skip tests where it is expected that keys on # 38# spatial data type are not allowed # 39# -- 1, do not skip these tests # 40# # 41# The comments/expectations refer to InnoDB. # 42# They might be not valid for other storage engines. # 43# # 44# # 45# Last update: # 46# 2006-08-15 ML - introduce several $variables # 47# - correct some storage engine assignments # 48# - minor improvements like correct wrong table after analyze # 49# - let checksum testcase meet all table variants with/without # 50# live checksum feature exiting and/or enabled # 51# 2006-07-26 ML create script by using t/innodb.test and introduce $variables # 52# # 53################################################################################ 54 55# Set the SESSION DEFAULT STORAGE ENGINE to a value <> storage engine 56# to be tested. This must not affect any CREATE TABLE statement, where 57# the storage engine is assigned explicitly, 58eval SET SESSION STORAGE_ENGINE = $other_engine_type; 59 60# 61# Small basic test with ignore 62# 63 64--disable_warnings 65drop table if exists t1,t2,t3,t4; 66drop database if exists mysqltest; 67--enable_warnings 68 69eval create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type; 70 71insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); 72select id, code, name from t1 order by id; 73 74update ignore t1 set id = 8, name = 'Sinisa' where id < 3; 75select id, code, name from t1 order by id; 76update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; 77select id, code, name from t1 order by id; 78 79drop table t1; 80 81# 82# A bit bigger test 83# The 'replace_column' statements are needed because the cardinality calculated 84# by innodb is not always the same between runs 85# 86 87eval CREATE TABLE t1 ( 88 id int(11) NOT NULL auto_increment, 89 parent_id int(11) DEFAULT '0' NOT NULL, 90 level tinyint(4) DEFAULT '0' NOT NULL, 91 PRIMARY KEY (id), 92 KEY parent_id (parent_id), 93 KEY level (level) 94) engine=$engine_type; 95INSERT 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); 96update t1 set parent_id=parent_id+100; 97select * from t1 where parent_id=102; 98update t1 set id=id+1000; 99-- error ER_DUP_ENTRY,1022 100update t1 set id=1024 where id=1009; 101select * from t1; 102update ignore t1 set id=id+1; # This will change all rows 103select * from t1; 104update ignore t1 set id=1023 where id=1010; 105select * from t1 where parent_id=102; 106--replace_column 9 # 107explain select level from t1 where level=1; 108--replace_column 9 # 109explain select level,id from t1 where level=1; 110--replace_column 9 # 111explain select level,id,parent_id from t1 where level=1; 112select level,id from t1 where level=1; 113select level,id,parent_id from t1 where level=1; 114optimize table t1; 115--replace_column 7 # 116show keys from t1; 117drop table t1; 118 119# 120# Test replace 121# 122 123eval CREATE TABLE t1 ( 124 gesuchnr int(11) DEFAULT '0' NOT NULL, 125 benutzer_id int(11) DEFAULT '0' NOT NULL, 126 PRIMARY KEY (gesuchnr,benutzer_id) 127) engine=$engine_type; 128 129replace into t1 (gesuchnr,benutzer_id) values (2,1); 130replace into t1 (gesuchnr,benutzer_id) values (1,1); 131replace into t1 (gesuchnr,benutzer_id) values (1,1); 132select * from t1; 133drop table t1; 134 135# 136# test delete using hidden_primary_key 137# 138 139eval create table t1 (a int) engine=$engine_type; 140insert into t1 values (1), (2); 141optimize table t1; 142delete from t1 where a = 1; 143select * from t1; 144check table t1; 145drop table t1; 146 147eval create table t1 (a int,b varchar(20)) engine=$engine_type; 148insert into t1 values (1,""), (2,"testing"); 149delete from t1 where a = 1; 150select * from t1; 151create index skr on t1 (a); 152insert into t1 values (3,""), (4,"testing"); 153analyze table t1; 154--replace_column 7 # 155show keys from t1; 156drop table t1; 157 158 159# Test of reading on secondary key with may be null 160 161eval create table t1 (a int,b varchar(20),key(a)) engine=$engine_type; 162insert into t1 values (1,""), (2,"testing"); 163select * from t1 where a = 1; 164drop table t1; 165 166if ($test_transactions) 167{ 168# 169# Test rollback 170# 171 172eval create table t1 (n int not null primary key) engine=$engine_type; 173set autocommit=0; 174insert into t1 values (4); 175rollback; 176select n, "after rollback" from t1; 177insert into t1 values (4); 178commit; 179select n, "after commit" from t1; 180commit; 181insert into t1 values (5); 182-- error ER_DUP_ENTRY 183insert into t1 values (4); 184commit; 185select n, "after commit" from t1; 186set autocommit=1; 187insert into t1 values (6); 188-- error ER_DUP_ENTRY 189insert into t1 values (4); 190select n from t1; 191set autocommit=0; 192# 193# savepoints 194# 195begin; 196savepoint `my_savepoint`; 197insert into t1 values (7); 198savepoint `savept2`; 199insert into t1 values (3); 200select n from t1; 201savepoint savept3; 202rollback to savepoint savept2; 203--error 1305 204rollback to savepoint savept3; 205rollback to savepoint savept2; 206release savepoint `my_savepoint`; 207select n from t1; 208-- error 1305 209rollback to savepoint `my_savepoint`; 210--error 1305 211rollback to savepoint savept2; 212insert into t1 values (8); 213savepoint sv; 214commit; 215savepoint sv; 216set autocommit=1; 217# nop 218rollback; 219drop table t1; 220 221# 222# Test for commit and FLUSH TABLES WITH READ LOCK 223# 224 225eval create table t1 (n int not null primary key) engine=$engine_type; 226start transaction; 227insert into t1 values (4); 228flush tables with read lock; 229# 230# Current code can't handle a read lock in middle of transaction 231#--error 1223; 232commit; 233unlock tables; 234commit; 235select * from t1; 236drop table t1; 237 238# 239# Testing transactions 240# 241 242eval create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=$engine_type; 243begin; 244insert into t1 values(1,'hamdouni'); 245select id as afterbegin_id,nom as afterbegin_nom from t1; 246rollback; 247select id as afterrollback_id,nom as afterrollback_nom from t1; 248set autocommit=0; 249insert into t1 values(2,'mysql'); 250select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; 251rollback; 252select id as afterrollback_id,nom as afterrollback_nom from t1; 253set autocommit=1; 254drop table t1; 255 256# 257# Simple not autocommit test 258# 259 260eval CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=$engine_type; 261insert into t1 values ('pippo', 12); 262-- error ER_DUP_ENTRY 263insert into t1 values ('pippo', 12); # Gives error 264delete from t1; 265delete from t1 where id = 'pippo'; 266select * from t1; 267 268insert into t1 values ('pippo', 12); 269set autocommit=0; 270delete from t1; 271rollback; 272select * from t1; 273delete from t1; 274commit; 275select * from t1; 276drop table t1; 277 278# 279# Test of active transactions 280# 281 282eval create table t1 (a integer) engine=$engine_type; 283start transaction; 284rename table t1 to t2; 285eval create table t1 (b integer) engine=$engine_type; 286insert into t1 values (1); 287rollback; 288drop table t1; 289rename table t2 to t1; 290drop table t1; 291set autocommit=1; 292 293# 294# The following simple tests failed at some point 295# 296 297eval CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=$engine_type; 298INSERT INTO t1 VALUES (1, 'Jochen'); 299select * from t1; 300drop table t1; 301 302eval CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type; 303set autocommit=0; 304INSERT INTO t1 SET _userid='marc@anyware.co.uk'; 305COMMIT; 306SELECT * FROM t1; 307SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; 308drop table t1; 309set autocommit=1; 310 311} 312# End of transactional tests 313 314# 315# Test when reading on part of unique key 316# 317eval CREATE TABLE t1 ( 318 user_id int(10) DEFAULT '0' NOT NULL, 319 name varchar(100), 320 phone varchar(100), 321 ref_email varchar(100) DEFAULT '' NOT NULL, 322 detail varchar(200), 323 PRIMARY KEY (user_id,ref_email) 324)engine=$engine_type; 325 326INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); 327select * from t1 where user_id=10292; 328INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); 329select * from t1 where user_id=10292; 330select * from t1 where user_id>=10292; 331select * from t1 where user_id>10292; 332select * from t1 where user_id<10292; 333drop table t1; 334 335# 336# Test that keys are created in right order 337# 338 339eval CREATE TABLE t1 (a int not null, b int not null,c int not null, 340key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = $engine_type; 341--replace_column 7 # 342show index from t1; 343drop table t1; 344 345# 346# Test of ALTER TABLE and innodb tables 347# 348 349eval create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type; 350eval alter table t1 engine=$engine_type; 351insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); 352select * from t1; 353update t1 set col2='7' where col1='4'; 354select * from t1; 355alter table t1 add co3 int not null; 356select * from t1; 357update t1 set col2='9' where col1='2'; 358select * from t1; 359drop table t1; 360 361# 362# INSERT INTO innodb tables 363# 364 365eval create table t1 (a int not null , b int, primary key (a)) engine = $engine_type; 366eval create table t2 (a int not null , b int, primary key (a)) engine = $other_engine_type; 367insert into t1 VALUES (1,3) , (2,3), (3,3); 368select * from t1; 369insert into t2 select * from t1; 370select * from t2; 371delete from t1 where b = 3; 372select * from t1; 373insert into t1 select * from t2; 374select * from t1; 375select * from t2; 376drop table t1,t2; 377 378# 379# Search on unique key 380# 381 382eval CREATE TABLE t1 ( 383 id int(11) NOT NULL auto_increment, 384 ggid varchar(32) binary DEFAULT '' NOT NULL, 385 email varchar(64) DEFAULT '' NOT NULL, 386 passwd varchar(32) binary DEFAULT '' NOT NULL, 387 PRIMARY KEY (id), 388 UNIQUE ggid (ggid) 389) ENGINE=$engine_type; 390 391insert into t1 (ggid,passwd) values ('test1','xxx'); 392insert into t1 (ggid,passwd) values ('test2','yyy'); 393-- error ER_DUP_ENTRY 394insert into t1 (ggid,passwd) values ('test2','this will fail'); 395-- error ER_DUP_ENTRY 396insert into t1 (ggid,id) values ('this will fail',1); 397 398select * from t1 where ggid='test1'; 399select * from t1 where passwd='xxx'; 400select * from t1 where id=2; 401 402replace into t1 (ggid,id) values ('this will work',1); 403replace into t1 (ggid,passwd) values ('test2','this will work'); 404-- error ER_DUP_ENTRY 405update t1 set id=100,ggid='test2' where id=1; 406select * from t1; 407select * from t1 where id=1; 408select * from t1 where id=999; 409drop table t1; 410 411# 412# ORDER BY on not primary key 413# 414 415eval CREATE TABLE t1 ( 416 user_name varchar(12), 417 password text, 418 subscribed char(1), 419 user_id int(11) DEFAULT '0' NOT NULL, 420 quota bigint(20), 421 weight double, 422 access_date date, 423 access_time time, 424 approved datetime, 425 dummy_primary_key int(11) NOT NULL auto_increment, 426 PRIMARY KEY (dummy_primary_key) 427) ENGINE=$engine_type; 428INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); 429INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 430INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); 431INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); 432INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); 433select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; 434drop table t1; 435 436# 437# Testing of tables without primary keys 438# 439 440eval CREATE TABLE t1 ( 441 id int(11) NOT NULL auto_increment, 442 parent_id int(11) DEFAULT '0' NOT NULL, 443 level tinyint(4) DEFAULT '0' NOT NULL, 444 KEY (id), 445 KEY parent_id (parent_id), 446 KEY level (level) 447) engine=$engine_type; 448INSERT 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); 449INSERT INTO t1 values (179,5,2); 450update t1 set parent_id=parent_id+100; 451select * from t1 where parent_id=102; 452update t1 set id=id+1000; 453update t1 set id=1024 where id=1009; 454select * from t1; 455update ignore t1 set id=id+1; # This will change all rows 456select * from t1; 457update ignore t1 set id=1023 where id=1010; 458select * from t1 where parent_id=102; 459--replace_column 9 # 460explain select level from t1 where level=1; 461select level,id from t1 where level=1; 462select level,id,parent_id from t1 where level=1; 463select level,id from t1 where level=1 order by id; 464delete from t1 where level=1; 465select * from t1; 466drop table t1; 467 468# 469# Test of index only reads 470# 471eval CREATE TABLE t1 ( 472 sca_code char(6) NOT NULL, 473 cat_code char(6) NOT NULL, 474 sca_desc varchar(50), 475 lan_code char(2) NOT NULL, 476 sca_pic varchar(100), 477 sca_sdesc varchar(50), 478 sca_sch_desc varchar(16), 479 PRIMARY KEY (sca_code, cat_code, lan_code), 480 INDEX sca_pic (sca_pic) 481) engine = $engine_type ; 482 483INSERT 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'); 484select count(*) from t1 where sca_code = 'PD'; 485select count(*) from t1 where sca_code <= 'PD'; 486select count(*) from t1 where sca_pic is null; 487alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); 488select count(*) from t1 where sca_code='PD' and sca_pic is null; 489select count(*) from t1 where cat_code='E'; 490 491alter table t1 drop index sca_pic, add index (sca_pic, cat_code); 492select count(*) from t1 where sca_code='PD' and sca_pic is null; 493select count(*) from t1 where sca_pic >= 'n'; 494select sca_pic from t1 where sca_pic is null; 495update t1 set sca_pic="test" where sca_pic is null; 496delete from t1 where sca_code='pd'; 497drop table t1; 498 499# 500# Test of opening table twice and timestamps 501# 502set @a:=now(); 503eval CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=$engine_type; 504insert into t1 (a) values(1),(2),(3); 505select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; 506select a from t1 natural join t1 as t2 where b >= @a order by a; 507update t1 set a=5 where a=1; 508select a from t1; 509drop table t1; 510 511# 512# Test with variable length primary key 513# 514eval create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=$engine_type; 515insert into t1 values("hello",1),("world",2); 516select * from t1 order by b desc; 517optimize table t1; 518--replace_column 7 # 519show keys from t1; 520drop table t1; 521 522# 523# Test of create index with NULL columns 524# 525eval create table t1 (i int, j int ) ENGINE=$engine_type; 526insert into t1 values (1,2); 527select * from t1 where i=1 and j=2; 528create index ax1 on t1 (i,j); 529select * from t1 where i=1 and j=2; 530drop table t1; 531 532# 533# Test min-max optimization 534# 535 536eval CREATE TABLE t1 ( 537 a int3 unsigned NOT NULL, 538 b int1 unsigned NOT NULL, 539 UNIQUE (a, b) 540) ENGINE = $engine_type; 541 542INSERT INTO t1 VALUES (1, 1); 543SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; 544drop table t1; 545 546# 547# Test INSERT DELAYED 548# 549 550eval CREATE TABLE t1 (a int unsigned NOT NULL) engine=$engine_type; 551# Can't test this in 3.23 552# INSERT DELAYED INTO t1 VALUES (1); 553INSERT INTO t1 VALUES (1); 554SELECT * FROM t1; 555DROP TABLE t1; 556 557 558# 559# Crash when using many tables (Test case by Jeremy D Zawodny) 560# 561 562eval create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = $engine_type; 563insert 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); 564--replace_column 9 # 565explain select * from t1 where a > 0 and a < 50; 566drop table t1; 567 568# 569# Test lock tables 570# 571 572eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type; 573insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 574LOCK TABLES t1 WRITE; 575--error ER_DUP_ENTRY 576insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 577select id from t1; 578select id from t1; 579UNLOCK TABLES; 580DROP TABLE t1; 581 582eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type; 583insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); 584LOCK TABLES t1 WRITE; 585begin; 586--error ER_DUP_ENTRY 587insert into t1 values (99,1,2,'D'),(1,1,2,'D'); 588select id from t1; 589insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); 590commit; 591select id,id3 from t1; 592UNLOCK TABLES; 593DROP TABLE t1; 594 595# 596# Test prefix key 597# 598eval create table t1 (a char(20), unique (a(5))) engine=$engine_type; 599drop table t1; 600eval create table t1 (a char(20), index (a(5))) engine=$engine_type; 601show create table t1; 602drop table t1; 603 604# 605# Test using temporary table and auto_increment 606# 607 608eval create temporary table t1 (a int not null auto_increment, primary key(a)) engine=$engine_type; 609insert into t1 values (NULL),(NULL),(NULL); 610delete from t1 where a=3; 611insert into t1 values (NULL); 612select * from t1; 613alter table t1 add b int; 614select * from t1; 615drop table t1; 616 617#Slashdot bug 618eval create table t1 619 ( 620 id int auto_increment primary key, 621 name varchar(32) not null, 622 value text not null, 623 uid int not null, 624 unique key(name,uid) 625 ) engine=$engine_type; 626insert into t1 values (1,'one','one value',101), 627 (2,'two','two value',102),(3,'three','three value',103); 628set insert_id=5; 629replace into t1 (value,name,uid) values ('other value','two',102); 630delete from t1 where uid=102; 631set insert_id=5; 632replace into t1 (value,name,uid) values ('other value','two',102); 633set insert_id=6; 634replace into t1 (value,name,uid) values ('other value','two',102); 635select * from t1; 636drop table t1; 637 638# 639# Test DROP DATABASE 640# 641# ML: Test logics 642# Check that the creation of a table with engine = $engine_type does 643# in a certain database (already containing some tables using other 644# storage engines) not prevent the dropping of this database. 645 646create database mysqltest; 647eval create table mysqltest.t1 (a int not null) engine= $engine_type; 648insert into mysqltest.t1 values(1); 649eval create table mysqltest.t2 (a int not null) engine= $other_engine_type; 650insert into mysqltest.t2 values(1); 651eval create table mysqltest.t3 (a int not null) engine= $other_engine_type1; 652insert into mysqltest.t3 values(1); 653commit; 654drop database mysqltest; 655# Don't check error message 656--error 1049 657show tables from mysqltest; 658 659# 660# Test truncate table with and without auto_commit 661# 662 663set autocommit=0; 664eval create table t1 (a int not null) engine= $engine_type; 665insert into t1 values(1),(2); 666truncate table t1; 667commit; 668truncate table t1; 669truncate table t1; 670select * from t1; 671insert into t1 values(1),(2); 672delete from t1; 673select * from t1; 674commit; 675drop table t1; 676set autocommit=1; 677 678eval create table t1 (a int not null) engine= $engine_type; 679insert into t1 values(1),(2); 680truncate table t1; 681insert into t1 values(1),(2); 682select * from t1; 683truncate table t1; 684insert into t1 values(1),(2); 685delete from t1; 686select * from t1; 687drop table t1; 688 689# 690# Test of how ORDER BY works when doing it on the whole table 691# 692 693eval create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=$engine_type; 694insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 695--replace_column 9 # 696explain select * from t1 order by a; 697--replace_column 9 # 698explain select * from t1 order by b; 699--replace_column 9 # 700explain select * from t1 order by c; 701--replace_column 9 # 702explain select a from t1 order by a; 703--replace_column 9 # 704explain select b from t1 order by b; 705--replace_column 9 # 706explain select a,b from t1 order by b; 707--replace_column 9 # 708explain select a,b from t1; 709--replace_column 9 # 710explain select a,b,c from t1; 711drop table t1; 712 713# 714# Check describe 715# 716 717eval create table t1 (t int not null default 1, key (t)) engine=$engine_type; 718desc t1; 719drop table t1; 720 721# 722# Test of multi-table-delete 723# 724 725eval CREATE TABLE t1 ( 726 number bigint(20) NOT NULL default '0', 727 cname char(15) NOT NULL default '', 728 carrier_id smallint(6) NOT NULL default '0', 729 privacy tinyint(4) NOT NULL default '0', 730 last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 731 last_mod_id smallint(6) NOT NULL default '0', 732 last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 733 last_app_id smallint(6) default '-1', 734 version smallint(6) NOT NULL default '0', 735 assigned_scps int(11) default '0', 736 status tinyint(4) default '0' 737) ENGINE=$engine_type; 738INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); 739INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); 740INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); 741INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); 742INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); 743INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); 744eval CREATE TABLE t2 ( 745 number bigint(20) NOT NULL default '0', 746 cname char(15) NOT NULL default '', 747 carrier_id smallint(6) NOT NULL default '0', 748 privacy tinyint(4) NOT NULL default '0', 749 last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 750 last_mod_id smallint(6) NOT NULL default '0', 751 last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 752 last_app_id smallint(6) default '-1', 753 version smallint(6) NOT NULL default '0', 754 assigned_scps int(11) default '0', 755 status tinyint(4) default '0' 756) ENGINE=$engine_type; 757INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); 758INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); 759INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); 760INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); 761select * from t1; 762select * from t2; 763delete 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); 764select * from t1; 765select * from t2; 766select * from t2; 767drop table t1,t2; 768 769# 770# A simple test with some isolation levels 771# TODO: Make this into a test using replication to really test how 772# this works. 773# 774 775eval create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type; 776 777BEGIN; 778SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 779SELECT @@tx_isolation,@@global.tx_isolation; 780insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); 781select id, code, name from t1 order by id; 782COMMIT; 783 784BEGIN; 785SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 786insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); 787select id, code, name from t1 order by id; 788COMMIT; 789 790BEGIN; 791SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 792insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); 793select id, code, name from t1 order by id; 794COMMIT; 795DROP TABLE t1; 796 797# 798# Test of multi-table-update 799# 800eval create table t1 (n int(10), d int(10)) engine=$engine_type; 801eval create table t2 (n int(10), d int(10)) engine=$engine_type; 802insert into t1 values(1,1),(1,2); 803insert into t2 values(1,10),(2,20); 804UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 805select * from t1; 806select * from t2; 807drop table t1,t2; 808 809# 810# Testing of IFNULL 811# 812eval create table t1 (a int, b int) engine=$engine_type; 813insert into t1 values(20,null); 814select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 815t2.b=t3.a; 816select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 817t2.b=t3.a order by 1; 818insert into t1 values(10,null); 819select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 820t2.b=t3.a order by 1; 821drop table t1; 822 823# 824# Test of read_through not existing const_table 825# 826 827eval create table t1 (a varchar(10) not null) engine = $other_engine_type; 828eval create table t2 (b varchar(10) not null unique) engine=$engine_type; 829select t1.a from t1,t2 where t1.a=t2.b; 830drop table t1,t2; 831eval create table t1 (a int not null, b int, primary key (a)) engine = $engine_type; 832eval create table t2 (a int not null, b int, primary key (a)) engine = $engine_type; 833insert into t1 values (10, 20); 834insert into t2 values (10, 20); 835update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; 836drop table t1,t2; 837 838if ($test_foreign_keys) 839{ 840# 841# Test of multi-table-delete with foreign key constraints 842# 843 844eval CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type; 845eval CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=$engine_type; 846insert into t1 set id=1; 847insert into t2 set id=1, t1_id=1; 848delete t1,t2 from t1,t2 where t1.id=t2.t1_id; 849select * from t1; 850select * from t2; 851drop table t2,t1; 852eval CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type; 853eval CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type; 854INSERT INTO t1 VALUES(1); 855INSERT INTO t2 VALUES(1, 1); 856SELECT * from t1; 857UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1; 858SELECT * from t1; 859UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id; 860SELECT * from t1; 861DROP TABLE t1,t2; 862} 863 864if ($test_transactions) 865{ 866# 867# Test of range_optimizer 868# 869 870set autocommit=0; 871 872eval CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type; 873 874eval CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type; 875 876eval CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=$engine_type; 877 878INSERT INTO t3 VALUES("my-test-1", "my-test-2"); 879COMMIT; 880 881INSERT INTO t1 VALUES("this-key", "will disappear"); 882INSERT INTO t2 VALUES("this-key", "will also disappear"); 883DELETE FROM t3 WHERE id1="my-test-1"; 884 885SELECT * FROM t1; 886SELECT * FROM t2; 887SELECT * FROM t3; 888ROLLBACK; 889 890SELECT * FROM t1; 891SELECT * FROM t2; 892SELECT * FROM t3; 893SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE; 894COMMIT; 895set autocommit=1; 896DROP TABLE t1,t2,t3; 897} 898 899# 900# Check update with conflicting key 901# 902 903eval CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=$engine_type; 904INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 905# We need the a < 1000 test here to quard against the halloween problems 906UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; 907SELECT * from t1; 908drop table t1; 909 910# 911# Test multi update with different join methods 912# 913 914eval CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=$engine_type; 915eval CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=$engine_type; 916INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12); 917INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 918 919# Full join, without key 920update t1,t2 set t1.a=t1.a+100; 921select * from t1; 922 923# unique key 924update t1,t2 set t1.a=t1.a+100 where t1.a=101; 925select * from t1; 926 927# ref key 928update t1,t2 set t1.b=t1.b+10 where t1.b=2; 929select * from t1; 930 931# Range key (in t1) 932update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100; 933select * from t1; 934select * from t2; 935 936drop table t1,t2; 937eval CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type; 938eval CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type; 939SET AUTOCOMMIT=0; 940INSERT INTO t1 ( B_ID ) VALUES ( 1 ); 941INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); 942ROLLBACK; 943SELECT * FROM t1; 944drop table t1,t2; 945eval create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type; 946insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); 947select distinct parent,child from t1 order by parent; 948drop table t1; 949 950# 951# Test that MySQL priorities clustered indexes 952# 953eval create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=$engine_type; 954eval create table t2 (a int not null auto_increment primary key, b int) ENGINE = $other_engine_type; 955insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); 956insert into t2 (a) select b from t1; 957insert into t1 (b) select b from t2; 958insert into t2 (a) select b from t1; 959insert into t1 (a) select b from t2; 960insert into t2 (a) select b from t1; 961insert into t1 (a) select b from t2; 962insert into t2 (a) select b from t1; 963insert into t1 (a) select b from t2; 964insert into t2 (a) select b from t1; 965insert into t1 (a) select b from t2; 966insert into t2 (a) select b from t1; 967insert into t1 (a) select b from t2; 968insert into t2 (a) select b from t1; 969insert into t1 (a) select b from t2; 970insert into t2 (a) select b from t1; 971insert into t1 (a) select b from t2; 972insert into t2 (a) select b from t1; 973insert into t1 (a) select b from t2; 974select count(*) from t1; 975--replace_column 9 # 976explain select * from t1 where c between 1 and 2500; 977update t1 set c=a; 978--replace_column 9 # 979explain select * from t1 where c between 1 and 2500; 980drop table t1,t2; 981 982# 983# Test of UPDATE ... ORDER BY 984# 985 986eval create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=$engine_type; 987 988insert into t1 (id) values (null),(null),(null),(null),(null); 989update t1 set fk=69 where fk is null order by id limit 1; 990SELECT * from t1; 991drop table t1; 992 993eval create table t1 (a int not null, b int not null, key (a)) engine=$engine_type; 994insert 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); 995SET @tmp=0; 996update t1 set b=(@tmp:=@tmp+1) order by a; 997update t1 set b=99 where a=1 order by b asc limit 1; 998update t1 set b=100 where a=1 order by b desc limit 2; 999update t1 set a=a+10+b where a=1 order by b; 1000select * from t1 order by a,b; 1001drop table t1; 1002 1003# 1004# Test of multi-table-updates (bug #1980). 1005# 1006 1007eval create table t1 ( c char(8) not null ) engine=$engine_type; 1008insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 1009insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 1010 1011alter table t1 add b char(8) not null; 1012alter table t1 add a char(8) not null; 1013alter table t1 add primary key (a,b,c); 1014update t1 set a=c, b=c; 1015 1016eval create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type; 1017insert into t2 select * from t1; 1018 1019delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 1020drop table t1,t2; 1021 1022# 1023# test autoincrement with TRUNCATE 1024# 1025 1026SET AUTOCOMMIT=1; 1027eval create table t1 (a integer auto_increment primary key) engine=$engine_type; 1028insert into t1 (a) values (NULL),(NULL); 1029truncate table t1; 1030insert into t1 (a) values (NULL),(NULL); 1031SELECT * from t1; 1032drop table t1; 1033 1034 1035if ($test_foreign_keys) 1036{ 1037# 1038# Test dictionary handling with spaceand quoting 1039# 1040 1041eval CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=$engine_type; 1042eval CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=$engine_type; 1043#show create table t2; 1044drop table t2,t1; 1045 1046# 1047# Test of multi updated and foreign keys 1048# 1049 1050eval create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = $engine_type; 1051insert into `t1`values ( 1 ) ; 1052eval create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type; 1053insert into `t2`values ( 1 ) ; 1054eval create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type; 1055insert into `t3`values ( 1 ) ; 1056--error 1451 1057delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; 1058--error 1451 1059update 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; 1060--error 1054 1061update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; 1062drop table t3,t2,t1; 1063 1064# 1065# test for recursion depth limit 1066# 1067eval create table t1( 1068 id int primary key, 1069 pid int, 1070 index(pid), 1071 foreign key(pid) references t1(id) on delete cascade) engine=$engine_type; 1072insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6), 1073 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14); 1074-- error 1451 1075delete from t1 where id=0; 1076delete from t1 where id=15; 1077delete from t1 where id=0; 1078 1079drop table t1; 1080} 1081# End of FOREIGN KEY tests 1082 1083# 1084# Test timestamps 1085# 1086 1087eval CREATE TABLE t1 (col1 int(1))ENGINE=$engine_type; 1088eval CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx 1089(stamp))ENGINE=$engine_type; 1090insert into t1 values (1),(2),(3); 1091# Note that timestamp 3 is wrong 1092insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); 1093SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < 1094'20020204120000' GROUP BY col1; 1095drop table t1,t2; 1096 1097# 1098# Test by Francois MASUREL 1099# 1100 1101eval CREATE TABLE t1 ( 1102 `id` int(10) unsigned NOT NULL auto_increment, 1103 `id_object` int(10) unsigned default '0', 1104 `id_version` int(10) unsigned NOT NULL default '1', 1105 `label` varchar(100) NOT NULL default '', 1106 `description` text, 1107 PRIMARY KEY (`id`), 1108 KEY `id_object` (`id_object`), 1109 KEY `id_version` (`id_version`) 1110) ENGINE=$engine_type; 1111 1112INSERT 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); 1113 1114eval CREATE TABLE t2 ( 1115 `id` int(10) unsigned NOT NULL auto_increment, 1116 `id_version` int(10) unsigned NOT NULL default '1', 1117 PRIMARY KEY (`id`), 1118 KEY `id_version` (`id_version`) 1119) ENGINE=$engine_type; 1120 1121INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); 1122 1123SELECT t2.id, t1.`label` FROM t2 INNER JOIN 1124(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 1125ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); 1126drop table t1,t2; 1127 1128# Live checksum feature available + enabled 1129eval create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=$other_live_chks_engine_type; 1130# Live checksum feature available + disabled 1131eval create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=$other_live_chks_engine_type; 1132# 1133# Live checksum feature not available + enabled 1134eval create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=$other_non_live_chks_engine_type; 1135# Live checksum feature not available + disabled 1136eval create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=$other_non_live_chks_engine_type; 1137# 1138# Live checksum feature probably available + enabled 1139eval create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=$engine_type; 1140# Live checksum feature probably available + disabled 1141eval create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=$engine_type; 1142# 1143insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 1144insert t2 select * from t1; 1145insert t3 select * from t1; 1146insert t4 select * from t1; 1147insert t5 select * from t1; 1148insert t6 select * from t1; 1149checksum table t1, t2, t3, t4, t5, t6, t7 quick; 1150checksum table t1, t2, t3, t4, t5, t6, t7; 1151checksum table t1, t2, t3, t4, t5, t6, t7 extended; 1152# #show table status; 1153drop table t1,t2,t3, t4, t5, t6; 1154 1155# 1156# Test problem with referring to different fields in same table in UNION 1157# (Bug#2552: UNION returns NULL instead of expected value (innoDB only tables)) 1158# 1159eval create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=$engine_type; 1160insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); 1161select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; 1162drop table t1; 1163 1164# 1165# Bug#2160: Extra error message for CREATE TABLE LIKE with InnoDB 1166# 1167eval create table t1 (a int) engine=$engine_type; 1168create table t2 like t1; 1169show create table t2; 1170drop table t1,t2; 1171 1172if ($test_foreign_keys) 1173{ 1174# 1175# Test of automaticly created foreign keys 1176# 1177 1178eval create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=$engine_type; 1179eval create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type; 1180show create table t1; 1181show create table t2; 1182create index id on t2 (id); 1183show create table t2; 1184create index id2 on t2 (id); 1185show create table t2; 1186drop index id2 on t2; 1187--error 1025,1025 1188drop index id on t2; 1189show create table t2; 1190drop table t2; 1191 1192eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type; 1193show create table t2; 1194create unique index id on t2 (id,id2); 1195show create table t2; 1196drop table t2; 1197 1198# Check foreign key columns created in different order than key columns 1199eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type; 1200show create table t2; 1201drop table t2; 1202 1203eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type; 1204show create table t2; 1205drop table t2; 1206 1207eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type; 1208show create table t2; 1209drop table t2; 1210 1211eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type; 1212show create table t2; 1213drop table t2; 1214 1215eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type; 1216show create table t2; 1217alter table t2 add index id_test (id), add index id_test2 (id,id2); 1218show create table t2; 1219drop table t2; 1220 1221# Test error handling 1222 1223# Clean up filename -- embedded server reports whole path without .frm, 1224# regular server reports relative path with .frm (argh!) 1225--replace_result \\ / $MYSQL_TEST_DIR . /var/mysqld.1/data/ / t2.frm t2 1226--error 1005 1227eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type; 1228 1229# bug#3749 1230 1231eval create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=$engine_type; 1232show create table t2; 1233drop table t2; 1234eval create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=$engine_type; 1235show create table t2; 1236drop table t2, t1; 1237} 1238# End of FOREIGN KEY tests 1239 1240 1241# 1242# Let us test binlog_cache_use and binlog_cache_disk_use status vars. 1243# Actually this test has nothing to do with innodb per se, it just requires 1244# transactional table. 1245# 1246flush status; 1247show status like "binlog_cache_use"; 1248show status like "binlog_cache_disk_use"; 1249 1250eval create table t1 (a int) engine=$engine_type; 1251 1252# Now we are going to create transaction which is long enough so its 1253# transaction binlog will be flushed to disk... 1254let $1=2000; 1255disable_query_log; 1256begin; 1257while ($1) 1258{ 1259 eval insert into t1 values( $1 ); 1260 dec $1; 1261} 1262commit; 1263enable_query_log; 1264show status like "binlog_cache_use"; 1265show status like "binlog_cache_disk_use"; 1266 1267# Transaction which should not be flushed to disk and so should not 1268# increase binlog_cache_disk_use. 1269begin; 1270delete from t1; 1271commit; 1272show status like "binlog_cache_use"; 1273show status like "binlog_cache_disk_use"; 1274drop table t1; 1275 1276# 1277# Bug #6126: Duplicate columns in keys gives misleading error message 1278# 1279--error 1060 1280eval create table t1 (c char(10), index (c,c)) engine=$engine_type; 1281--error 1060 1282eval create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type; 1283--error 1060 1284eval create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type; 1285--error 1060 1286eval create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type; 1287eval create table t1 (c1 char(10), c2 char(10)) engine=$engine_type; 1288--error 1060 1289alter table t1 add key (c1,c1); 1290--error 1060 1291alter table t1 add key (c2,c1,c1); 1292--error 1060 1293alter table t1 add key (c1,c2,c1); 1294--error 1060 1295alter table t1 add key (c1,c1,c2); 1296drop table t1; 1297 1298# 1299# Bug #4082: integer truncation 1300# 1301 1302eval create table t1(a int(1) , b int(1)) engine=$engine_type; 1303insert into t1 values ('1111', '3333'); 1304select distinct concat(a, b) from t1; 1305drop table t1; 1306 1307if ($fulltext_query_unsupported) 1308{ 1309# 1310# BUG#7709 test case - Boolean fulltext query against unsupported 1311# engines does not fail 1312# 1313 1314eval CREATE TABLE t1 ( a char(10) ) ENGINE=$engine_type; 1315--error 1214 1316SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE); 1317DROP TABLE t1; 1318} 1319 1320if ($test_foreign_keys) 1321{ 1322# 1323# check null values #1 1324# 1325 1326--disable_warnings 1327eval CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1; 1328INSERT INTO t1 VALUES (1),(2),(3); 1329eval CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), 1330 CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=$engine_type DEFAULT CHARSET=latin1; 1331--enable_warnings 1332INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); 1333SELECT * 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; 1334DROP TABLE t2; 1335DROP TABLE t1; 1336} 1337 1338# 1339# Bug#11816 - Truncate table doesn't work with temporary innodb tables 1340# This is not an innodb bug, but we test it using innodb. 1341# 1342eval create temporary table t1 (a int) engine=$engine_type; 1343insert into t1 values (4711); 1344truncate t1; 1345insert into t1 values (42); 1346select * from t1; 1347drop table t1; 1348# Show that it works with permanent tables too. 1349eval create table t1 (a int) engine=$engine_type; 1350insert into t1 values (4711); 1351truncate t1; 1352insert into t1 values (42); 1353select * from t1; 1354drop table t1; 1355 1356# 1357# Bug #13025 Server crash during filesort 1358# 1359 1360eval create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=$engine_type; 1361insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); 1362select * from t1 order by a,b,c,d; 1363explain select * from t1 order by a,b,c,d; 1364drop table t1; 1365 1366# 1367# BUG#11039,#13218 Wrong key length in min() 1368# 1369 1370eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type; 1371insert into t1 values ('8', '6'), ('4', '7'); 1372select min(a) from t1; 1373select min(b) from t1 where a='8'; 1374drop table t1; 1375 1376# End of 4.1 tests 1377 1378# 1379# range optimizer problem 1380# 1381 1382eval create table t1 (x bigint unsigned not null primary key) engine=$engine_type; 1383insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); 1384select * from t1; 1385select count(*) from t1 where x>0; 1386select count(*) from t1 where x=0; 1387select count(*) from t1 where x<0; 1388select count(*) from t1 where x < -16; 1389select count(*) from t1 where x = -16; 1390explain select count(*) from t1 where x > -16; 1391select count(*) from t1 where x > -16; 1392select * from t1 where x > -16; 1393select count(*) from t1 where x = 18446744073709551601; 1394drop table t1; 1395 1396# Please do not remove the following skipped InnoDB specific tests. 1397# They make the synchronization with innodb.test easier and give 1398# an idea what to test on other storage engines. 1399if (0) 1400{ 1401 1402# Test for testable InnoDB status variables. This test 1403# uses previous ones(pages_created, rows_deleted, ...). 1404show status like "Innodb_buffer_pool_pages_total"; 1405show status like "Innodb_page_size"; 1406show status like "Innodb_rows_deleted"; 1407show status like "Innodb_rows_inserted"; 1408show status like "Innodb_rows_updated"; 1409 1410# Test for row locks InnoDB status variables. 1411show status like "Innodb_row_lock_waits"; 1412show status like "Innodb_row_lock_current_waits"; 1413show status like "Innodb_row_lock_time"; 1414show status like "Innodb_row_lock_time_max"; 1415show status like "Innodb_row_lock_time_avg"; 1416 1417# Test for innodb_sync_spin_loops variable 1418show variables like "innodb_sync_spin_loops"; 1419set global innodb_sync_spin_loops=1000; 1420show variables like "innodb_sync_spin_loops"; 1421set global innodb_sync_spin_loops=0; 1422show variables like "innodb_sync_spin_loops"; 1423set global innodb_sync_spin_loops=20; 1424show variables like "innodb_sync_spin_loops"; 1425 1426# Test for innodb_thread_concurrency variable 1427show variables like "innodb_thread_concurrency"; 1428set global innodb_thread_concurrency=1001; 1429show variables like "innodb_thread_concurrency"; 1430set global innodb_thread_concurrency=0; 1431show variables like "innodb_thread_concurrency"; 1432set global innodb_thread_concurrency=16; 1433show variables like "innodb_thread_concurrency"; 1434 1435# Test for innodb_concurrency_tickets variable 1436show variables like "innodb_concurrency_tickets"; 1437set global innodb_concurrency_tickets=1000; 1438show variables like "innodb_concurrency_tickets"; 1439set global innodb_concurrency_tickets=0; 1440show variables like "innodb_concurrency_tickets"; 1441set global innodb_concurrency_tickets=500; 1442show variables like "innodb_concurrency_tickets"; 1443 1444# Test for innodb_thread_sleep_delay variable 1445show variables like "innodb_thread_sleep_delay"; 1446set global innodb_thread_sleep_delay=100000; 1447show variables like "innodb_thread_sleep_delay"; 1448set global innodb_thread_sleep_delay=0; 1449show variables like "innodb_thread_sleep_delay"; 1450set global innodb_thread_sleep_delay=10000; 1451show variables like "innodb_thread_sleep_delay"; 1452 1453} 1454 1455 1456# 1457# Test varchar 1458# 1459 1460let $default=`select @@storage_engine`; 1461eval set storage_engine=$engine_type; 1462source include/varchar.inc; 1463 1464# 1465# Some errors/warnings on create 1466# 1467 1468# Clean up filename -- embedded server reports whole path without .frm, 1469# regular server reports relative path with .frm (argh!) 1470--replace_result \\ / $MYSQL_TEST_DIR . /var/mysqld.1/data/ / t1.frm t1 1471create table t1 (v varchar(65530), key(v)); 1472drop table t1; 1473SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1474create table t1 (v varchar(65536)); 1475show create table t1; 1476drop table t1; 1477SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1478create table t1 (v varchar(65530) character set utf8); 1479show create table t1; 1480drop table t1; 1481 1482eval set storage_engine=$default; 1483 1484# InnoDB specific varchar tests 1485eval create table t1 (v varchar(16384)) engine=$engine_type; 1486drop table t1; 1487 1488# 1489# BUG#11039 Wrong key length in min() 1490# 1491 1492eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type; 1493insert into t1 values ('8', '6'), ('4', '7'); 1494select min(a) from t1; 1495select min(b) from t1 where a='8'; 1496drop table t1; 1497 1498# 1499# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error 1500# 1501 1502eval CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type; 1503insert into t1 (b) values (1); 1504replace into t1 (b) values (2), (1), (3); 1505select * from t1; 1506truncate table t1; 1507insert into t1 (b) values (1); 1508replace into t1 (b) values (2); 1509replace into t1 (b) values (1); 1510replace into t1 (b) values (3); 1511select * from t1; 1512drop table t1; 1513 1514eval create table t1 (rowid int not null auto_increment, val int not null,primary 1515key (rowid), unique(val)) engine=$engine_type; 1516replace into t1 (val) values ('1'),('2'); 1517replace into t1 (val) values ('1'),('2'); 1518--error ER_DUP_ENTRY 1519insert into t1 (val) values ('1'),('2'); 1520select * from t1; 1521drop table t1; 1522 1523if ($no_autoinc_update) 1524{ 1525# 1526# Test that update does not change internal auto-increment value 1527# 1528 1529eval create table t1 (a int not null auto_increment primary key, val int) engine=$engine_type; 1530insert into t1 (val) values (1); 1531update t1 set a=2 where a=1; 1532# We should get the following error because InnoDB does not update the counter 1533--error ER_DUP_ENTRY 1534insert into t1 (val) values (1); 1535select * from t1; 1536drop table t1; 1537} 1538 1539 1540# 1541# Bug#10465: DECIMAL, crash on DELETE (InnoDB only) 1542# 1543 1544--disable_warnings 1545eval CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=$engine_type; 1546--enable_warnings 1547INSERT INTO t1 (GRADE) VALUES (151),(252),(343); 1548SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; 1549SELECT GRADE FROM t1 WHERE GRADE= 151; 1550DROP TABLE t1; 1551 1552# 1553# Bug #12340 multitable delete deletes only one record 1554# 1555eval create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type; 1556eval create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type; 1557insert into t2 values ('aa','cc'); 1558insert into t1 values ('aa','bb'),('aa','cc'); 1559delete t1 from t1,t2 where f1=f3 and f4='cc'; 1560select * from t1; 1561drop table t1,t2; 1562 1563if ($test_foreign_keys) 1564{ 1565# 1566# Test that the slow TRUNCATE implementation resets autoincrement columns 1567# (bug #11946) 1568# 1569 1570eval CREATE TABLE t1 ( 1571id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) 1572) ENGINE=$engine_type; 1573 1574eval CREATE TABLE t2 ( 1575id INTEGER NOT NULL, 1576FOREIGN KEY (id) REFERENCES t1 (id) 1577) ENGINE=$engine_type; 1578 1579INSERT INTO t1 (id) VALUES (NULL); 1580SELECT * FROM t1; 1581TRUNCATE t1; 1582INSERT INTO t1 (id) VALUES (NULL); 1583SELECT * FROM t1; 1584 1585# continued from above; test that doing a slow TRUNCATE on a table with 0 1586# rows resets autoincrement columns 1587DELETE FROM t1; 1588TRUNCATE t1; 1589INSERT INTO t1 (id) VALUES (NULL); 1590SELECT * FROM t1; 1591DROP TABLE t2, t1; 1592 1593# Test that foreign keys in temporary tables are not accepted (bug #12084) 1594eval CREATE TABLE t1 1595( 1596 id INT PRIMARY KEY 1597) ENGINE=$engine_type; 1598 1599--error 1005,1005 1600eval CREATE TEMPORARY TABLE t2 1601( 1602 id INT NOT NULL PRIMARY KEY, 1603 b INT, 1604 FOREIGN KEY (b) REFERENCES test.t1(id) 1605) ENGINE=$engine_type; 1606DROP TABLE t1; 1607} 1608# End of FOREIGN KEY test 1609 1610# Please do not remove the following skipped InnoDB specific tests. 1611# They make the synchronization with innodb.test easier and give 1612# an idea what to test on other storage engines. 1613if (0) 1614{ 1615 1616# 1617# Test that index column max sizes are honored (bug #13315) 1618# 1619 1620# prefix index 1621eval create table t1 (col1 varchar(2000), index (col1(767))) 1622 character set = latin1 engine = $engine_type; 1623 1624# normal indexes 1625eval create table t2 (col1 char(255), index (col1)) 1626 character set = latin1 engine = $engine_type; 1627eval create table t3 (col1 binary(255), index (col1)) 1628 character set = latin1 engine = $engine_type; 1629eval create table t4 (col1 varchar(767), index (col1)) 1630 character set = latin1 engine = $engine_type; 1631eval create table t5 (col1 varchar(767) primary key) 1632 character set = latin1 engine = $engine_type; 1633eval create table t6 (col1 varbinary(767) primary key) 1634 character set = latin1 engine = $engine_type; 1635eval create table t7 (col1 text, index(col1(767))) 1636 character set = latin1 engine = $engine_type; 1637eval create table t8 (col1 blob, index(col1(767))) 1638 character set = latin1 engine = $engine_type; 1639 1640 1641# multi-column indexes are allowed to be longer 1642eval create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2)) 1643 character set = latin1 engine = $engine_type; 1644 1645show create table t9; 1646 1647drop table t1, t2, t3, t4, t5, t6, t7, t8, t9; 1648 1649# these should have their index length trimmed 1650eval create table t1 (col1 varchar(768), index(col1)) 1651 character set = latin1 engine = $engine_type; 1652eval create table t2 (col1 varbinary(768), index(col1)) 1653 character set = latin1 engine = $engine_type; 1654eval create table t3 (col1 text, index(col1(768))) 1655 character set = latin1 engine = $engine_type; 1656eval create table t4 (col1 blob, index(col1(768))) 1657 character set = latin1 engine = $engine_type; 1658 1659show create table t1; 1660 1661drop table t1, t2, t3, t4; 1662 1663} 1664# End of skipped test 1665 1666# Please do not remove the following skipped InnoDB specific tests. 1667# They make the synchronization with innodb.test easier and give 1668# an idea what to test on other storage engines. 1669if (0) 1670{ 1671 1672# these should be refused 1673--error 1071 1674eval create table t1 (col1 varchar(768) primary key) 1675 character set = latin1 engine = $engine_type; 1676--error 1071 1677eval create table t2 (col1 varbinary(768) primary key) 1678 character set = latin1 engine = $engine_type; 1679--error 1071 1680eval create table t3 (col1 text, primary key(col1(768))) 1681 character set = latin1 engine = $engine_type; 1682--error 1071 1683eval create table t4 (col1 blob, primary key(col1(768))) 1684 character set = latin1 engine = $engine_type; 1685 1686} 1687 1688if ($test_foreign_keys) 1689{ 1690# 1691# Test improved foreign key error messages (bug #3443) 1692# 1693 1694eval CREATE TABLE t1 1695( 1696 id INT PRIMARY KEY 1697) ENGINE=$engine_type; 1698 1699eval CREATE TABLE t2 1700( 1701 v INT, 1702 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id) 1703) ENGINE=$engine_type; 1704 1705--error 1452 1706INSERT INTO t2 VALUES(2); 1707 1708INSERT INTO t1 VALUES(1); 1709INSERT INTO t2 VALUES(1); 1710 1711--error 1451 1712DELETE FROM t1 WHERE id = 1; 1713 1714--error 1217 1715DROP TABLE t1; 1716 1717SET FOREIGN_KEY_CHECKS=0; 1718DROP TABLE t1; 1719SET FOREIGN_KEY_CHECKS=1; 1720 1721--error 1452 1722INSERT INTO t2 VALUES(3); 1723 1724DROP TABLE t2; 1725} 1726# End of FOREIGN tests 1727 1728if ($test_transactions) 1729{ 1730# 1731# Test that checksum table uses a consistent read Bug #12669 1732# 1733connect (a,localhost,root,,); 1734connect (b,localhost,root,,); 1735connection a; 1736eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1; 1737insert into t1 values (1),(2); 1738set autocommit=0; 1739checksum table t1; 1740connection b; 1741insert into t1 values(3); 1742connection a; 1743# 1744# Here checksum should not see insert 1745# 1746checksum table t1; 1747connection a; 1748commit; 1749checksum table t1; 1750commit; 1751drop table t1; 1752# 1753# autocommit = 1 1754# 1755connection a; 1756eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1; 1757insert into t1 values (1),(2); 1758set autocommit=1; 1759checksum table t1; 1760connection b; 1761set autocommit=1; 1762insert into t1 values(3); 1763connection a; 1764# 1765# Here checksum sees insert 1766# 1767checksum table t1; 1768drop table t1; 1769 1770connection default; 1771disconnect a; 1772disconnect b; 1773} 1774 1775# tests for bugs #9802 and #13778 1776 1777if ($test_foreign_keys) 1778{ 1779# test that FKs between invalid types are not accepted 1780 1781set foreign_key_checks=0; 1782eval create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type; 1783--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' 1784-- error 1005 1785eval create table t1(a char(10) primary key, b varchar(20)) engine = $engine_type; 1786set foreign_key_checks=1; 1787drop table t2; 1788 1789# test that FKs between different charsets are not accepted in CREATE even 1790# when f_k_c is 0 1791 1792set foreign_key_checks=0; 1793eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1; 1794--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' 1795-- error 1005 1796eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8; 1797set foreign_key_checks=1; 1798drop table t1; 1799 1800# test that invalid datatype conversions with ALTER are not allowed 1801 1802set foreign_key_checks=0; 1803eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type; 1804eval create table t1(a varchar(10) primary key) engine = $engine_type; 1805-- error 1025,1025 1806alter table t1 modify column a int; 1807set foreign_key_checks=1; 1808drop table t2,t1; 1809 1810# test that charset conversions with ALTER are allowed when f_k_c is 0 1811 1812set foreign_key_checks=0; 1813eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1; 1814eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1; 1815alter table t1 convert to character set utf8; 1816set foreign_key_checks=1; 1817drop table t2,t1; 1818 1819# test that RENAME does not allow invalid charsets when f_k_c is 0 1820 1821set foreign_key_checks=0; 1822eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1; 1823eval create table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8; 1824--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' 1825-- error 1025 1826rename table t3 to t1; 1827set foreign_key_checks=1; 1828drop table t2,t3; 1829 1830# test that foreign key errors are reported correctly (Bug #15550) 1831 1832eval create table t1(a int primary key) row_format=redundant engine=$engine_type; 1833eval create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=$engine_type; 1834eval create table t3(a int primary key) row_format=compact engine=$engine_type; 1835eval create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=$engine_type; 1836 1837insert into t1 values(1); 1838insert into t3 values(1); 1839-- error 1452 1840insert into t2 values(2); 1841-- error 1452 1842insert into t4 values(2); 1843insert into t2 values(1); 1844insert into t4 values(1); 1845-- error 1451 1846update t1 set a=2; 1847-- error 1452 1848update t2 set a=2; 1849-- error 1451 1850update t3 set a=2; 1851-- error 1452 1852update t4 set a=2; 1853-- error 1451 1854truncate t1; 1855-- error 1451 1856truncate t3; 1857truncate t2; 1858truncate t4; 1859truncate t1; 1860truncate t3; 1861 1862drop table t4,t3,t2,t1; 1863} 1864# End of FOREIGN KEY tests 1865 1866 1867# Please do not remove the following skipped InnoDB specific tests. 1868# They make the synchronization with innodb.test easier and give 1869# an idea what to test on other storage engines. 1870if (0) 1871{ 1872 1873# 1874# Test that we can create a large (>1K) key 1875# 1876eval create table t1 (a varchar(255) character set utf8, 1877 b varchar(255) character set utf8, 1878 c varchar(255) character set utf8, 1879 d varchar(255) character set utf8, 1880 key (a,b,c,d)) engine=$engine_type; 1881drop table t1; 1882--error ER_TOO_LONG_KEY 1883eval create table t1 (a varchar(255) character set utf8, 1884 b varchar(255) character set utf8, 1885 c varchar(255) character set utf8, 1886 d varchar(255) character set utf8, 1887 e varchar(255) character set utf8, 1888 key (a,b,c,d,e)) engine=$engine_type; 1889 1890 1891# test the padding of BINARY types and collations (Bug #14189) 1892 1893eval create table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type; 1894eval create table t2 (s1 binary(2),primary key (s1)) engine=$engine_type; 1895eval create table t3 (s1 varchar(2) binary,primary key (s1)) engine=$engine_type; 1896eval create table t4 (s1 char(2) binary,primary key (s1)) engine=$engine_type; 1897 1898insert into t1 values (0x41),(0x4120),(0x4100); 1899-- error ER_DUP_ENTRY 1900insert into t2 values (0x41),(0x4120),(0x4100); 1901insert into t2 values (0x41),(0x4120); 1902-- error ER_DUP_ENTRY 1903insert into t3 values (0x41),(0x4120),(0x4100); 1904insert into t3 values (0x41),(0x4100); 1905-- error ER_DUP_ENTRY 1906insert into t4 values (0x41),(0x4120),(0x4100); 1907insert into t4 values (0x41),(0x4100); 1908select hex(s1) from t1; 1909select hex(s1) from t2; 1910select hex(s1) from t3; 1911select hex(s1) from t4; 1912drop table t1,t2,t3,t4; 1913} 1914 1915if ($test_foreign_keys) 1916{ 1917eval create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=$engine_type; 1918eval create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type; 1919 1920insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42); 1921-- error 1452 1922insert into t2 values(0x42); 1923insert into t2 values(0x41); 1924select hex(s1) from t2; 1925update t1 set s1=0x123456 where a=2; 1926select hex(s1) from t2; 1927-- error 1451 1928update t1 set s1=0x12 where a=1; 1929-- error 1451 1930update t1 set s1=0x12345678 where a=1; 1931-- error 1451 1932update t1 set s1=0x123457 where a=1; 1933update t1 set s1=0x1220 where a=1; 1934select hex(s1) from t2; 1935update t1 set s1=0x1200 where a=1; 1936select hex(s1) from t2; 1937update t1 set s1=0x4200 where a=1; 1938select hex(s1) from t2; 1939-- error 1451 1940delete from t1 where a=1; 1941delete from t1 where a=2; 1942update t2 set s1=0x4120; 1943-- error 1451 1944delete from t1; 1945delete from t1 where a!=3; 1946select a,hex(s1) from t1; 1947select hex(s1) from t2; 1948 1949drop table t2,t1; 1950 1951eval create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=$engine_type; 1952eval create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type; 1953 1954insert into t1 values(1,0x4100),(2,0x41); 1955insert into t2 values(0x41); 1956select hex(s1) from t2; 1957update t1 set s1=0x1234 where a=1; 1958select hex(s1) from t2; 1959update t1 set s1=0x12 where a=2; 1960select hex(s1) from t2; 1961delete from t1 where a=1; 1962-- error 1451 1963delete from t1 where a=2; 1964select a,hex(s1) from t1; 1965select hex(s1) from t2; 1966 1967drop table t2,t1; 1968} 1969# End FOREIGN KEY tests 1970 1971if ($test_foreign_keys) 1972{ 1973# Ensure that <tablename>_ibfk_0 is not mistreated as a 1974# generated foreign key identifier. (Bug #16387) 1975 1976eval CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type; 1977eval CREATE TABLE t2(a INT) ENGINE=$engine_type; 1978ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); 1979ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; 1980ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a); 1981ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0; 1982SHOW CREATE TABLE t2; 1983DROP TABLE t2,t1; 1984} 1985 1986# 1987# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing 1988# 1989 1990connect (a,localhost,root,,); 1991connect (b,localhost,root,,); 1992connection a; 1993eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 1994insert into t1(a) values (1),(2),(3); 1995commit; 1996connection b; 1997set autocommit = 0; 1998update t1 set b = 5 where a = 2; 1999commit; 2000connection a; 2001delimiter |; 2002create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end | 2003delimiter ;| 2004set autocommit = 0; 2005connection a; 2006insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100), 2007(11),(21),(31),(41),(51),(61),(71),(81),(91),(101), 2008(12),(22),(32),(42),(52),(62),(72),(82),(92),(102), 2009(13),(23),(33),(43),(53),(63),(73),(83),(93),(103), 2010(14),(24),(34),(44),(54),(64),(74),(84),(94),(104); 2011connection b; 2012commit; 2013connection a; 2014commit; 2015drop trigger t1t; 2016drop table t1; 2017disconnect a; 2018disconnect b; 2019# 2020# Another trigger test 2021# 2022connect (a,localhost,root,,); 2023connect (b,localhost,root,,); 2024connection a; 2025eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 2026eval create table t2(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 2027eval create table t3(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 2028eval create table t4(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 2029eval create table t5(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type; 2030insert into t1(a) values (1),(2),(3); 2031insert into t2(a) values (1),(2),(3); 2032insert into t3(a) values (1),(2),(3); 2033insert into t4(a) values (1),(2),(3); 2034insert into t3(a) values (5),(7),(8); 2035insert into t4(a) values (5),(7),(8); 2036insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12); 2037 2038delimiter |; 2039create trigger t1t before insert on t1 for each row begin 2040 INSERT INTO t2 SET a = NEW.a; 2041end | 2042 2043create trigger t2t before insert on t2 for each row begin 2044 DELETE FROM t3 WHERE a = NEW.a; 2045end | 2046 2047create trigger t3t before delete on t3 for each row begin 2048 UPDATE t4 SET b = b + 1 WHERE a = OLD.a; 2049end | 2050 2051create trigger t4t before update on t4 for each row begin 2052 UPDATE t5 SET b = b + 1 where a = NEW.a; 2053end | 2054delimiter ;| 2055commit; 2056set autocommit = 0; 2057update t1 set b = b + 5 where a = 1; 2058update t2 set b = b + 5 where a = 1; 2059update t3 set b = b + 5 where a = 1; 2060update t4 set b = b + 5 where a = 1; 2061insert into t5(a) values(20); 2062commit; 2063connection b; 2064set autocommit = 0; 2065insert into t1(a) values(7); 2066insert into t2(a) values(8); 2067delete from t2 where a = 3; 2068update t4 set b = b + 1 where a = 3; 2069commit; 2070drop trigger t1t; 2071drop trigger t2t; 2072drop trigger t3t; 2073drop trigger t4t; 2074drop table t1, t2, t3, t4, t5; 2075connection default; 2076disconnect a; 2077disconnect b; 2078 2079if ($test_foreign_keys) 2080{ 2081# 2082# Test that cascading updates leading to duplicate keys give the correct 2083# error message (bug #9680) 2084# 2085 2086eval CREATE TABLE t1 ( 2087 field1 varchar(8) NOT NULL DEFAULT '', 2088 field2 varchar(8) NOT NULL DEFAULT '', 2089 PRIMARY KEY (field1, field2) 2090) ENGINE=$engine_type; 2091 2092eval CREATE TABLE t2 ( 2093 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY, 2094 FOREIGN KEY (field1) REFERENCES t1 (field1) 2095 ON DELETE CASCADE ON UPDATE CASCADE 2096) ENGINE=$engine_type; 2097 2098INSERT INTO t1 VALUES ('old', 'somevalu'); 2099INSERT INTO t1 VALUES ('other', 'anyvalue'); 2100 2101INSERT INTO t2 VALUES ('old'); 2102INSERT INTO t2 VALUES ('other'); 2103 2104--error ER_FOREIGN_DUPLICATE_KEY 2105UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu'; 2106 2107DROP TABLE t2; 2108DROP TABLE t1; 2109 2110# 2111# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE 2112# 2113eval create table t1 ( 2114 c1 bigint not null, 2115 c2 bigint not null, 2116 primary key (c1), 2117 unique key (c2) 2118) engine=$engine_type; 2119# 2120eval create table t2 ( 2121 c1 bigint not null, 2122 primary key (c1) 2123) engine=$engine_type; 2124# 2125alter table t1 add constraint c2_fk foreign key (c2) 2126 references t2(c1) on delete cascade; 2127show create table t1; 2128# 2129alter table t1 drop foreign key c2_fk; 2130show create table t1; 2131# 2132drop table t1, t2; 2133} 2134# End FOREIGN KEY test 2135 2136# 2137# Bug #14360: problem with intervals 2138# 2139 2140eval create table t1(a date) engine=$engine_type; 2141eval create table t2(a date, key(a)) engine=$engine_type; 2142insert into t1 values('2005-10-01'); 2143insert into t2 values('2005-10-01'); 2144select * from t1, t2 2145 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; 2146drop table t1, t2; 2147 2148eval create table t1 (id int not null, f_id int not null, f int not null, 2149primary key(f_id, id)) engine=$engine_type; 2150eval create table t2 (id int not null,s_id int not null,s varchar(200), 2151primary key(id)) engine=$engine_type; 2152INSERT INTO t1 VALUES (8, 1, 3); 2153INSERT INTO t1 VALUES (1, 2, 1); 2154INSERT INTO t2 VALUES (1, 0, ''); 2155INSERT INTO t2 VALUES (8, 1, ''); 2156commit; 2157DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) 2158WHERE mm.id IS NULL; 2159select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) 2160where mm.id is null lock in share mode; 2161drop table t1,t2; 2162 2163# 2164# Test case where X-locks on unused rows should be released in a 2165# update (because READ COMMITTED isolation level) 2166# 2167 2168connect (a,localhost,root,,); 2169connect (b,localhost,root,,); 2170connection a; 2171eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type; 2172insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); 2173commit; 2174set autocommit = 0; 2175SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2176update t1 set b = 5 where b = 1; 2177connection b; 2178set autocommit = 0; 2179SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2180# 2181# X-lock to record (7,3) should be released in a update 2182# 2183select * from t1 where a = 7 and b = 3 for update; 2184connection a; 2185commit; 2186connection b; 2187commit; 2188drop table t1; 2189connection default; 2190disconnect a; 2191disconnect b; 2192 2193if ($test_transactions) 2194{ 2195# 2196# Test case where no locks should be released (because we are not 2197# using READ COMMITTED isolation level) 2198# 2199 2200connect (a,localhost,root,,); 2201connect (b,localhost,root,,); 2202connection a; 2203eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type; 2204insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2); 2205commit; 2206set autocommit = 0; 2207select * from t1 lock in share mode; 2208update t1 set b = 5 where b = 1; 2209connection b; 2210set autocommit = 0; 2211# 2212# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update 2213# 2214--error 1205 2215select * from t1 where a = 2 and b = 2 for update; 2216# 2217# X-lock to record (1,1),(3,1),(5,1) should not be released in a update 2218# 2219--error 1205 2220connection a; 2221commit; 2222connection b; 2223commit; 2224connection default; 2225disconnect a; 2226disconnect b; 2227drop table t1; 2228 2229# 2230# Consistent read should be used in following selects 2231# 2232# 1) INSERT INTO ... SELECT 2233# 2) UPDATE ... = ( SELECT ...) 2234# 3) CREATE ... SELECT 2235 2236connect (a,localhost,root,,); 2237connect (b,localhost,root,,); 2238connection a; 2239eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type; 2240insert into t1 values (1,2),(5,3),(4,2); 2241eval create table t2(d int not null, e int, primary key(d)) engine=$engine_type; 2242insert into t2 values (8,6),(12,1),(3,1); 2243commit; 2244set autocommit = 0; 2245select * from t2 for update; 2246connection b; 2247set autocommit = 0; 2248SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2249insert into t1 select * from t2; 2250update t1 set b = (select e from t2 where a = d); 2251eval create table t3(d int not null, e int, primary key(d)) engine=$engine_type 2252select * from t2; 2253commit; 2254connection a; 2255commit; 2256connection default; 2257disconnect a; 2258disconnect b; 2259drop table t1, t2, t3; 2260 2261# 2262# Consistent read should not be used if 2263# 2264# (a) isolation level is serializable OR 2265# (b) select ... lock in share mode OR 2266# (c) select ... for update 2267# 2268# in following queries: 2269# 2270# 1) INSERT INTO ... SELECT 2271# 2) UPDATE ... = ( SELECT ...) 2272# 3) CREATE ... SELECT 2273 2274connect (a,localhost,root,,); 2275eval SET SESSION STORAGE_ENGINE = $engine_type; 2276connect (b,localhost,root,,); 2277eval SET SESSION STORAGE_ENGINE = $engine_type; 2278connect (c,localhost,root,,); 2279eval SET SESSION STORAGE_ENGINE = $engine_type; 2280connect (d,localhost,root,,); 2281eval SET SESSION STORAGE_ENGINE = $engine_type; 2282connect (e,localhost,root,,); 2283eval SET SESSION STORAGE_ENGINE = $engine_type; 2284connect (f,localhost,root,,); 2285eval SET SESSION STORAGE_ENGINE = $engine_type; 2286connect (g,localhost,root,,); 2287eval SET SESSION STORAGE_ENGINE = $engine_type; 2288connect (h,localhost,root,,); 2289eval SET SESSION STORAGE_ENGINE = $engine_type; 2290connect (i,localhost,root,,); 2291eval SET SESSION STORAGE_ENGINE = $engine_type; 2292connect (j,localhost,root,,); 2293eval SET SESSION STORAGE_ENGINE = $engine_type; 2294connection a; 2295create table t1(a int not null, b int, primary key(a)); 2296insert into t1 values (1,2),(5,3),(4,2); 2297create table t2(a int not null, b int, primary key(a)); 2298insert into t2 values (8,6),(12,1),(3,1); 2299create table t3(d int not null, b int, primary key(d)); 2300insert into t3 values (8,6),(12,1),(3,1); 2301create table t5(a int not null, b int, primary key(a)); 2302insert into t5 values (1,2),(5,3),(4,2); 2303create table t6(d int not null, e int, primary key(d)); 2304insert into t6 values (8,6),(12,1),(3,1); 2305create table t8(a int not null, b int, primary key(a)); 2306insert into t8 values (1,2),(5,3),(4,2); 2307create table t9(d int not null, e int, primary key(d)); 2308insert into t9 values (8,6),(12,1),(3,1); 2309commit; 2310set autocommit = 0; 2311select * from t2 for update; 2312connection b; 2313set autocommit = 0; 2314SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2315--send 2316insert into t1 select * from t2; 2317connection c; 2318set autocommit = 0; 2319SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2320--send 2321update t3 set b = (select b from t2 where a = d); 2322connection d; 2323set autocommit = 0; 2324SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 2325--send 2326create table t4(a int not null, b int, primary key(a)) select * from t2; 2327connection e; 2328set autocommit = 0; 2329SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2330--send 2331insert into t5 (select * from t2 lock in share mode); 2332connection f; 2333set autocommit = 0; 2334SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2335--send 2336update t6 set e = (select b from t2 where a = d lock in share mode); 2337connection g; 2338set autocommit = 0; 2339SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2340--send 2341create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode; 2342connection h; 2343set autocommit = 0; 2344SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2345--send 2346insert into t8 (select * from t2 for update); 2347connection i; 2348set autocommit = 0; 2349SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2350--send 2351update t9 set e = (select b from t2 where a = d for update); 2352connection j; 2353set autocommit = 0; 2354SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2355--send 2356create table t10(a int not null, b int, primary key(a)) select * from t2 for update; 2357 2358connection b; 2359--error 1205 2360reap; 2361 2362connection c; 2363--error 1205 2364reap; 2365 2366connection d; 2367--error 1205 2368reap; 2369 2370connection e; 2371--error 1205 2372reap; 2373 2374connection f; 2375--error 1205 2376reap; 2377 2378connection g; 2379--error 1205 2380reap; 2381 2382connection h; 2383--error 1205 2384reap; 2385 2386connection i; 2387--error 1205 2388reap; 2389 2390connection j; 2391--error 1205 2392reap; 2393 2394connection a; 2395commit; 2396 2397connection default; 2398disconnect a; 2399disconnect b; 2400disconnect c; 2401disconnect d; 2402disconnect e; 2403disconnect f; 2404disconnect g; 2405disconnect h; 2406disconnect i; 2407disconnect j; 2408drop table t1, t2, t3, t5, t6, t8, t9; 2409} 2410# End transactional tests 2411 2412if ($test_foreign_keys) 2413{ 2414# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID" 2415--error 1005 2416eval CREATE TABLE t1 (DB_ROW_ID int) engine=$engine_type; 2417 2418# 2419# Bug #17152: Wrong result with BINARY comparison on aliased column 2420# 2421 2422eval CREATE TABLE t1 ( 2423 a BIGINT(20) NOT NULL, 2424 PRIMARY KEY (a) 2425 ) ENGINE=$engine_type DEFAULT CHARSET=UTF8; 2426 2427eval CREATE TABLE t2 ( 2428 a BIGINT(20) NOT NULL, 2429 b VARCHAR(128) NOT NULL, 2430 c TEXT NOT NULL, 2431 PRIMARY KEY (a,b), 2432 KEY idx_t2_b_c (b,c(200)), 2433 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a) 2434 ON DELETE CASCADE 2435 ) ENGINE=$engine_type DEFAULT CHARSET=UTF8; 2436 2437INSERT INTO t1 VALUES (1); 2438INSERT INTO t2 VALUES (1, 'bar', 'vbar'); 2439INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR'); 2440INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi'); 2441INSERT INTO t2 VALUES (1, 'customer_over', '1'); 2442 2443SELECT * FROM t2 WHERE b = 'customer_over'; 2444SELECT * FROM t2 WHERE BINARY b = 'customer_over'; 2445SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over'; 2446/* Bang: Empty result set, above was expected: */ 2447SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; 2448SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; 2449 2450drop table t2, t1; 2451} 2452 2453if ($no_spatial_key) 2454{ 2455# 2456# Bug #15680 (SPATIAL key in innodb) 2457# 2458--error ER_TABLE_CANT_HANDLE_SPKEYS 2459eval create table t1 (g geometry not null, spatial gk(g)) engine=$engine_type; 2460} 2461 2462# 2463# Test optimize on table with open transaction 2464# 2465 2466eval CREATE TABLE t1 ( a int ) ENGINE=$engine_type; 2467BEGIN; 2468INSERT INTO t1 VALUES (1); 2469OPTIMIZE TABLE t1; 2470DROP TABLE t1; 2471 2472####################################################################### 2473# # 2474# This is derivate of t/innodb.test and has to be maintained by # 2475# MySQL guys only. # 2476# # 2477# Please synchronize this file from time to time with t/innodb.test. # 2478# Please, DO NOT create a toplevel testcase innodb-mix2.test, because # 2479# innodb.test does already these tests. # 2480# # 2481####################################################################### 2482