innodb.test 16.3 KB
Newer Older
1
-- source include/have_innodb.inc
2 3 4 5 6

#
# Small basic test with ignore
#

7
drop table if exists t1,t2;
8
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)) type=innodb;
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
select id, code, name from t1 order by id;
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
select id, code, name from t1 order by id;

drop table t1;

#
# A bit bigger test
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
31
) type=innodb;
32 33 34 35
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
36
-- error 1062,1022
37
update t1 set id=1024 where id=1009; 
38 39 40 41 42 43 44 45 46 47
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
explain select level from t1 where level=1;
explain select level,id from t1 where level=1;
explain select level,id,parent_id from t1 where level=1;
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
48 49
optimize table t1;
show keys from t1;
50 51 52 53 54 55 56 57 58 59
drop table t1;

#
# Test replace
#

CREATE TABLE t1 (
  gesuchnr int(11) DEFAULT '0' NOT NULL,
  benutzer_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (gesuchnr,benutzer_id)
60
) type=innodb;
61 62 63 64 65 66 67 68 69 70 71

replace into t1 (gesuchnr,benutzer_id) values (2,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
select * from t1;
drop table t1;

#
# test delete using hidden_primary_key
#

72
create table t1 (a int) type=innodb;
73
insert into t1 values (1), (2);
74
optimize table t1;
75 76
delete from t1 where a = 1;
select * from t1;
77 78 79
check table t1;
drop table t1;

80
create table t1 (a int,b varchar(20)) type=innodb;
81 82 83 84 85 86 87 88 89 90 91 92
insert into t1 values (1,""), (2,"testing");
delete from t1 where a = 1;
select * from t1;
create index skr on t1 (a);
insert into t1 values (3,""), (4,"testing");
analyze table t1;
show keys from t1;
drop table t1;


# Test of reading on secondary key with may be null

93
create table t1 (a int,b varchar(20),key(a)) type=innodb;
94 95
insert into t1 values (1,""), (2,"testing");
select * from t1 where a = 1;
96 97 98 99 100 101
drop table t1;

#
# Test rollback
#

102
create table t1 (n int not null primary key) type=innodb;
103 104 105 106 107 108 109 110 111
set autocommit=0;
insert into t1 values (4);
rollback;
select n, "after rollback" from t1;
insert into t1 values (4);
commit;
select n, "after commit" from t1;
commit;
insert into t1 values (5);
112 113
-- error 1062
insert into t1 values (4);
114 115 116 117
commit;
select n, "after commit" from t1;
set autocommit=1;
insert into t1 values (6);
118 119
-- error 1062
insert into t1 values (4);
120 121 122 123 124 125 126 127 128
select n from t1;
# nop
rollback;
drop table t1;

#
# Testing transactions
#

129
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=innodb;
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
begin;
insert into t1 values(1,'hamdouni');
select id as afterbegin_id,nom as afterbegin_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=0;
insert into t1 values(2,'mysql');
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=1;
drop table t1;

#
# Simple not autocommit test
# 

147
CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=innodb;
148
insert into t1 values ('pippo', 12);
149 150
-- error 1062
insert into t1 values ('pippo', 12); # Gives error
151 152 153 154 155 156 157 158 159 160 161 162 163
delete from t1;
delete from t1 where id = 'pippo';
select * from t1;

insert into t1 values ('pippo', 12);
set autocommit=0;
delete from t1;
rollback;
select * from t1;
delete from t1;
commit;
select * from t1;
drop table t1;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
164 165 166 167 168 169 170 171 172 173 174 175 176 177

#
# Test of active transactions
#

create table t1 (a integer) type=innodb;
begin;
rename table t1 to t2;
create table t1 (b integer) type=innodb;
insert into t1 values (1);
rollback;
drop table t1;
rename table t2 to t1;
drop table t1;
178 179 180 181 182 183
set autocommit=1;

#
# The following simple tests failed at some point
#

184
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=innodb;
185 186 187 188
INSERT INTO t1 VALUES (1, 'Jochen');
select * from t1;
drop table t1;

189
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=innodb;
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207
set autocommit=0;
INSERT INTO t1  SET _userid='marc@anyware.co.uk';
COMMIT;
SELECT * FROM t1;
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
drop table t1;
set autocommit=1;

#
# Test when reading on part of unique key
#
CREATE TABLE t1 (
  user_id int(10) DEFAULT '0' NOT NULL,
  name varchar(100),
  phone varchar(100),
  ref_email varchar(100) DEFAULT '' NOT NULL,
  detail varchar(200),
  PRIMARY KEY (user_id,ref_email)
208
)type=innodb;
209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
select * from t1 where user_id=10292;
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
select * from t1 where user_id=10292;
select * from t1 where user_id>=10292;
select * from t1 where user_id>10292;
select * from t1 where user_id<10292;
drop table t1;

#
# Test that keys are created in right order
#

CREATE TABLE t1 (a int not null, b int not null,c int not null,
key(a),primary key(a,b), unique(c),key(a),unique(b));
show index from t1;
drop table t1;

#
229
# Test of ALTER TABLE and innodb tables
230 231 232
#

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
233
alter table t1 type=innodb;
234 235 236 237 238 239 240 241 242 243 244
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
select * from t1;
update t1 set col2='7' where col1='4';
select * from t1;
alter table t1 add co3 int not null;
select * from t1;
update t1 set col2='9' where col1='2';
select * from t1;
drop table t1;

#
245
# INSERT INTO innodb tables
246 247
#

248
create table t1 (a int not null , b int, primary key (a)) type = innodb;
249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271
create table t2 (a int not null , b int, primary key (a)) type = myisam;
insert into t1 VALUES (1,3) , (2,3), (3,3);
select * from t1;
insert into t2 select * from t1;
select * from t2;
delete from t1 where b = 3;
select * from t1;
insert into t1 select * from t2;
select * from t1;
select * from t2;
drop table t1,t2;

#
# Search on unique key
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  ggid varchar(32) binary DEFAULT '' NOT NULL,
  email varchar(64) DEFAULT '' NOT NULL,
  passwd varchar(32) binary DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  UNIQUE ggid (ggid)
272
) TYPE=innodb;
273 274 275

insert into t1 (ggid,passwd) values ('test1','xxx');
insert into t1 (ggid,passwd) values ('test2','yyy');
276 277 278 279
-- error 1062
insert into t1 (ggid,passwd) values ('test2','this will fail');
-- error 1062
insert into t1 (ggid,id) values ('this will fail',1);
280 281 282 283

select * from t1 where ggid='test1';
select * from t1 where passwd='xxx';
select * from t1 where id=2;
284 285 286 287 288 289 290 291

replace into t1 (ggid,id) values ('this will work',1);
replace into t1 (ggid,passwd) values ('test2','this will work');
-- error 1062
update t1 set id=100,ggid='test2' where id=1;
select * from t1;
select * from t1 where id=1;
select * from t1 where id=999;
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309
drop table t1;

#
# ORDER BY on not primary key
#

CREATE TABLE t1 (
  user_name varchar(12),
  password text,
  subscribed char(1),
  user_id int(11) DEFAULT '0' NOT NULL,
  quota bigint(20),
  weight double,
  access_date date,
  access_time time,
  approved datetime,
  dummy_primary_key int(11) NOT NULL auto_increment,
  PRIMARY KEY (dummy_primary_key)
310
) TYPE=innodb;
311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
drop table t1;

#
# Testing of tables without primary keys
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
330
) type=innodb;
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
INSERT INTO t1 values (179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
update t1 set id=1024 where id=1009; 
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
explain select level from t1 where level=1;
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
select level,id from t1 where level=1 order by id;
delete from t1 where level=1;
select * from t1;
drop table t1;

#
# Test of index only reads
#
CREATE TABLE t1 (
   sca_code char(6) NOT NULL,
   cat_code char(6) NOT NULL,
   sca_desc varchar(50),
   lan_code char(2) NOT NULL,
   sca_pic varchar(100),
   sca_sdesc varchar(50),
   sca_sch_desc varchar(16),
361 362
   PRIMARY KEY (sca_code, cat_code, lan_code),
   INDEX sca_pic (sca_pic)
363
) type = innodb ;
364

365
INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
366
select count(*) from t1 where sca_code = 'PD';
367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384
select count(*) from t1 where sca_code <= 'PD';
select count(*) from t1 where sca_pic is null;
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where cat_code='E';

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where sca_pic >= 'n';
select sca_pic from t1 where sca_pic is null;
update t1 set sca_pic="test" where sca_pic is null;
delete from t1 where sca_code='pd';
drop table t1;

#
# Test of opening table twice and timestamps
#
set @a:=now();
385
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=innodb;
386 387 388 389 390 391 392 393 394
insert into t1 (a) values(1),(2),(3);
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
update t1 set a=5 where a=1;
select a from t1;
drop table t1;

#
# Test with variable length primary key
#
395
create table t1 (a varchar(100) not null, primary key(a), b int not null) type=innodb;
396 397 398 399
insert into t1 values("hello",1),("world",2);
select * from t1 order by b desc;
optimize table t1;
show keys from t1;
400 401 402
drop table t1;

#
403
# Test of create index with NULL columns
404
#
405
create table t1 (i int, j int ) TYPE=innodb;
406 407 408 409
insert into t1 values (1,2);
select * from t1 where i=1 and j=2;
create index ax1 on t1 (i,j);
select * from t1 where i=1 and j=2;
410
drop table t1;
411 412 413 414 415 416 417 418 419

#
# Test min-max optimization
#

CREATE TABLE t1 (
  a int3 unsigned NOT NULL,
  b int1 unsigned NOT NULL,
  UNIQUE (a, b)
420
) TYPE = innodb;
421 422 423 424
 
INSERT INTO t1 VALUES (1, 1);
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
drop table t1;
425

426 427 428 429 430
#
# Test INSERT DELAYED
#

CREATE TABLE t1 (a int unsigned NOT NULL) type=innodb;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
431 432
# Can't test this in 3.23
# INSERT DELAYED INTO t1 VALUES (1);
433 434 435 436 437
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
DROP TABLE t1;


438 439 440 441
#
# Crash when using many tables (Test case by Jeremy D Zawodny)
#

442
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) type = innodb;
443 444 445
insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
explain select * from t1 where a > 0 and a < 50;
drop table t1;
446 447 448 449 450 451 452 453

#
# Test lock tables
#

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)) type=innodb;
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
454
--error 1062
455 456 457 458 459 460 461 462 463 464
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
select id from t1;
UNLOCK TABLES;
DROP TABLE t1;

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)) type=innodb;
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
begin;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
465
--error 1062
466 467 468 469 470 471 472
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
commit;
select id,id3 from t1;
UNLOCK TABLES;
DROP TABLE t1;
473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491

#
# Test prefix key
#
--error 1089
create table t1 (a char(20), unique (a(5))) type=innodb;
create table t1 (a char(20), index (a(5))) type=innodb;
show create table t1;
drop table t1;

#
# Test using temporary table and auto_increment
#

create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb;
insert into t1 values (NULL),(NULL),(NULL);
delete from t1 where a=3;
insert into t1 values (NULL);
select * from t1;
492 493
alter table t1 add b int;
select * from t1;
494
drop table t1;
495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516

#Slashdot bug
create table t1
 (
  id int auto_increment primary key,
  name varchar(32) not null,
  value text not null,
  uid int not null,
  unique key(name,uid)
 ) type=innodb;
insert into t1 values (1,'one','one value',101),
 (2,'two','two value',102),(3,'three','three value',103);
set insert_id=5;
replace into t1 (value,name,uid) values ('other value','two',102);
delete from t1 where uid=102;
set insert_id=5;
replace into t1 (value,name,uid) values ('other value','two',102);
set insert_id=6;
replace into t1 (value,name,uid) values ('other value','two',102);
select * from t1;
drop table t1;

517 518 519 520
#
# Test DROP DATABASE
#

521 522 523 524 525 526 527
create database mysqltest;
create table mysqltest.t1 (a int not null) type= innodb;
insert into mysqltest.t1 values(1);
create table mysqltest.t2 (a int not null) type= myisam;
insert into mysqltest.t2 values(1);
create table mysqltest.t3 (a int not null) type= heap;
insert into mysqltest.t3 values(1);
528
commit;
529
drop database mysqltest;
530 531
# Don't check error message
--error 12,12
532
show tables from mysqltest;
533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549

#
# Test truncate table
#

create table t1 (a int not null) type= innodb;
insert into t1 values(1),(2);
--error 1192
truncate table t1;
commit;
truncate table t1;
select * from t1;
insert into t1 values(1),(2);
delete from t1;
select * from t1;
commit;
drop table t1;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
550

551 552 553 554 555 556 557 558 559 560 561 562 563 564 565
#
# Test of how ORDER BY works when doing it on the whole table
#

create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb;
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
explain select * from t1 order by a;
explain select * from t1 order by b;
explain select * from t1 order by c;
explain select a from t1 order by a;
explain select b from t1 order by b;
explain select a,b from t1 order by b;
explain select a,b from t1;
explain select a,b,c from t1;
drop table t1;
566 567

#
568
# Check describe & handler
569 570
#

571
create table t1 (t int not null default 1, key (t)) type=innodb;
572
desc t1;
573 574 575 576
--error 1031
handler t1 open t1;
--error 1109
handler t1 read t first;
577
--error 1109
578
handler t1 close;
579
drop table t1;