1
#######################################################################
3
# Please, DO NOT TOUCH this file as well as the innodb.result file. #
4
# These files are to be modified ONLY BY INNOBASE guys. #
6
# Use innodb_mysql.[test|result] files instead. #
8
# If nevertheless you need to make some changes here, please, forward #
9
# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com #
10
# (otherwise your changes may be erased). #
12
#######################################################################
16
# Small basic test with ignore
20
drop table if exists t1,t2,t3,t4;
21
drop database if exists mysqltest;
23
#set engine_condition_pushdown=0;
24
create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
26
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
27
select id, code, name from t1 order by id;
29
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
30
select id, code, name from t1 order by id;
31
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
32
select id, code, name from t1 order by id;
38
# The 'replace_column' statements are needed because the cardinality calculated
39
# by innodb is not always the same between runs
43
id int(11) NOT NULL auto_increment,
44
parent_id int(11) DEFAULT '0' NOT NULL,
45
level int(4) DEFAULT '0' NOT NULL,
47
KEY parent_id (parent_id),
50
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);
51
update t1 set parent_id=parent_id+100;
52
select * from t1 where parent_id=102;
53
update t1 set id=id+1000;
54
update t1 set id=1024 where id=1009;
56
update ignore t1 set id=id+1; # This will change all rows
58
update ignore t1 set id=1023 where id=1010;
59
select * from t1 where parent_id=102;
61
explain select level from t1 where level=1;
63
explain select level,id from t1 where level=1;
65
explain select level,id,parent_id from t1 where level=1;
66
select level,id from t1 where level=1;
67
select level,id,parent_id from t1 where level=1;
78
gesuchnr int(11) DEFAULT '0' NOT NULL,
79
benutzer_id int(11) DEFAULT '0' NOT NULL,
80
PRIMARY KEY (gesuchnr,benutzer_id)
83
replace into t1 (gesuchnr,benutzer_id) values (2,1);
84
replace into t1 (gesuchnr,benutzer_id) values (1,1);
85
replace into t1 (gesuchnr,benutzer_id) values (1,1);
90
# test delete using hidden_primary_key
93
create table t1 (a int) engine=innodb;
94
insert into t1 values (1), (2);
96
delete from t1 where a = 1;
101
create table t1 (a int,b varchar(20)) engine=innodb;
102
insert into t1 values (1,""), (2,"testing");
103
delete from t1 where a = 1;
105
create index skr on t1 (a);
106
insert into t1 values (3,""), (4,"testing");
113
# Test of reading on secondary key with may be null
115
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
116
insert into t1 values (1,""), (2,"testing");
117
select * from t1 where a = 1;
124
create table t1 (n int not null primary key) engine=innodb;
126
insert into t1 values (4);
128
select n, "after rollback" from t1;
129
insert into t1 values (4);
131
select n, "after commit" from t1;
133
insert into t1 values (5);
134
insert into t1 values (4);
136
select n, "after commit" from t1;
138
insert into t1 values (6);
139
insert into t1 values (4);
146
savepoint `my_savepoint`;
147
insert into t1 values (7);
149
insert into t1 values (3);
152
rollback to savepoint savept2;
154
rollback to savepoint savept3;
155
rollback to savepoint savept2;
156
release savepoint `my_savepoint`;
158
rollback to savepoint `my_savepoint`;
160
rollback to savepoint savept2;
161
insert into t1 values (8);
171
# Test for commit and FLUSH TABLES WITH READ LOCK
174
create table t1 (n int not null primary key) engine=innodb;
176
insert into t1 values (4);
177
flush tables with read lock;
179
# Current code can't handle a read lock in middle of transaction
188
# Testing transactions
191
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
193
insert into t1 values(1,'hamdouni');
194
select id as afterbegin_id,nom as afterbegin_nom from t1;
196
select id as afterrollback_id,nom as afterrollback_nom from t1;
198
insert into t1 values(2,'mysql');
199
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
201
select id as afterrollback_id,nom as afterrollback_nom from t1;
206
# Simple not autocommit test
209
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
210
insert into t1 values ('pippo', 12);
211
insert into t1 values ('pippo', 12); # Gives error
213
delete from t1 where id = 'pippo';
216
insert into t1 values ('pippo', 12);
227
# Test of active transactions
230
create table t1 (a integer) engine=innodb;
232
rename table t1 to t2;
233
create table t1 (b integer) engine=innodb;
234
insert into t1 values (1);
237
rename table t2 to t1;
242
# The following simple tests failed at some point
245
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
246
INSERT INTO t1 VALUES (1, 'Jochen');
250
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
252
INSERT INTO t1 SET _userid='marc@anyware.co.uk';
255
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
260
# Test when reading on part of unique key
263
user_id int(10) DEFAULT '0' NOT NULL,
266
ref_email varchar(100) DEFAULT '' NOT NULL,
268
PRIMARY KEY (user_id,ref_email)
271
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');
272
select * from t1 where user_id=10292;
273
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
274
select * from t1 where user_id=10292;
275
select * from t1 where user_id>=10292;
276
select * from t1 where user_id>10292;
277
select * from t1 where user_id<10292;
281
# Test that keys are created in right order
284
CREATE TABLE t1 (a int not null, b int not null,c int not null,
285
key(a),primary key(a,b), unique(c),key(a),unique(b));
291
# Test of ALTER TABLE and innodb tables
294
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
295
alter table t1 engine=innodb;
296
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
298
update t1 set col2='7' where col1='4';
300
alter table t1 add co3 int not null;
302
update t1 set col2='9' where col1='2';
307
# INSERT INTO innodb tables
310
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
311
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
312
insert into t1 VALUES (1,3) , (2,3), (3,3);
314
insert into t2 select * from t1;
316
delete from t1 where b = 3;
318
insert into t1 select * from t2;
324
# ORDER BY on not primary key
328
user_name varchar(12),
331
user_id int(11) DEFAULT '0' NOT NULL,
337
dummy_primary_key int(11) NOT NULL auto_increment,
338
PRIMARY KEY (dummy_primary_key)
340
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
341
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
342
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);
343
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);
344
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
345
select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
349
# Testing of tables without primary keys
353
id int(11) NOT NULL auto_increment,
354
parent_id int(11) DEFAULT '0' NOT NULL,
355
level int(4) DEFAULT '0' NOT NULL,
357
KEY parent_id (parent_id),
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);
361
INSERT INTO t1 values (179,5,2);
362
update t1 set parent_id=parent_id+100;
363
select * from t1 where parent_id=102;
364
update t1 set id=id+1000;
365
update t1 set id=1024 where id=1009;
367
update ignore t1 set id=id+1; # This will change all rows
369
update ignore t1 set id=1023 where id=1010;
370
select * from t1 where parent_id=102;
372
explain select level from t1 where level=1;
373
select level,id from t1 where level=1;
374
select level,id,parent_id from t1 where level=1;
375
select level,id from t1 where level=1 order by id;
376
delete from t1 where level=1;
381
# Test of index only reads
384
sca_code char(6) NOT NULL,
385
cat_code char(6) NOT NULL,
386
sca_desc varchar(50),
387
lan_code char(2) NOT NULL,
388
sca_pic varchar(100),
389
sca_sdesc varchar(50),
390
sca_sch_desc varchar(16),
391
PRIMARY KEY (sca_code, cat_code, lan_code),
392
INDEX sca_pic (sca_pic)
395
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');
396
select count(*) from t1 where sca_code = 'PD';
397
select count(*) from t1 where sca_code <= 'PD';
398
select count(*) from t1 where sca_pic is null;
399
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
400
select count(*) from t1 where sca_code='PD' and sca_pic is null;
401
select count(*) from t1 where cat_code='E';
402
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
403
select count(*) from t1 where sca_code='PD' and sca_pic is null;
404
select count(*) from t1 where sca_pic >= 'n';
405
select sca_pic from t1 where sca_pic is null;
406
update t1 set sca_pic="test" where sca_pic is null;
407
delete from t1 where sca_code='pd';
411
# Test of opening table twice and timestamps
414
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
415
insert into t1 (a) values(1),(2),(3);
416
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
417
select a from t1 natural join t1 as t2 where b >= @a order by a;
418
update t1 set a=5 where a=1;
423
# Test with variable length primary key
425
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
426
insert into t1 values("hello",1),("world",2);
427
select * from t1 order by b desc;
434
# Test of create index with NULL columns
436
create table t1 (i int, j int ) ENGINE=innodb;
437
insert into t1 values (1,2);
438
select * from t1 where i=1 and j=2;
439
create index ax1 on t1 (i,j);
440
select * from t1 where i=1 and j=2;
444
# Test min-max optimization
453
INSERT INTO t1 VALUES (1, 1);
454
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
458
# Test INSERT DELAYED
461
CREATE TABLE t1 (a int NOT NULL) engine=innodb;
462
# Can't test this in 3.23
463
# INSERT DELAYED INTO t1 VALUES (1);
464
INSERT INTO t1 VALUES (1);
470
# Crash when using many tables (Test case by Jeremy D Zawodny)
473
create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;
474
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);
476
explain select * from t1 where a > 0 and a < 50;
483
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
484
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
485
LOCK TABLES t1 WRITE;
487
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
493
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
494
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
495
LOCK TABLES t1 WRITE;
498
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
500
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
502
select id,id3 from t1;
509
create table t1 (a char(20), unique (a(5))) engine=innodb;
511
create table t1 (a char(20), index (a(5))) engine=innodb;
512
show create table t1;
516
# Test using temporary table and auto_increment
519
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
520
insert into t1 values (NULL),(NULL),(NULL);
521
delete from t1 where a=3;
522
insert into t1 values (NULL);
524
alter table t1 add b int;
531
id int auto_increment primary key,
532
name varchar(32) not null,
537
insert into t1 values (1,'one','one value',101),
538
(2,'two','two value',102),(3,'three','three value',103);
540
replace into t1 (value,name,uid) values ('other value','two',102);
541
delete from t1 where uid=102;
543
replace into t1 (value,name,uid) values ('other value','two',102);
545
replace into t1 (value,name,uid) values ('other value','two',102);
553
create database mysqltest;
554
create table mysqltest.t1 (a int not null) engine= innodb;
555
insert into mysqltest.t1 values(1);
556
create table mysqltest.t2 (a int not null) engine= myisam;
557
insert into mysqltest.t2 values(1);
558
create table mysqltest.t3 (a int not null) engine= heap;
559
insert into mysqltest.t3 values(1);
561
drop database mysqltest;
562
# Don't check error message
564
show tables from mysqltest;
567
# Test truncate table with and without auto_commit
571
create table t1 (a int not null) engine= innodb;
572
insert into t1 values(1),(2);
578
insert into t1 values(1),(2);
585
create table t1 (a int not null) engine= innodb;
586
insert into t1 values(1),(2);
588
insert into t1 values(1),(2);
591
insert into t1 values(1),(2);
597
# Test of how ORDER BY works when doing it on the whole table
600
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
601
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
603
explain select * from t1 order by a;
605
explain select * from t1 order by b;
607
explain select * from t1 order by c;
609
explain select a from t1 order by a;
611
explain select b from t1 order by b;
613
explain select a,b from t1 order by b;
615
explain select a,b from t1;
617
explain select a,b,c from t1;
624
create table t1 (t int not null default 1, key (t)) engine=innodb;
629
# Test of multi-table-delete
633
number bigint(20) NOT NULL default '0',
634
cname char(15) NOT NULL default '',
635
carrier_id int(6) NOT NULL default '0',
636
privacy int(4) NOT NULL default '0',
637
last_mod_date timestamp NOT NULL,
638
last_mod_id int(6) NOT NULL default '0',
639
last_app_date timestamp NOT NULL,
640
last_app_id int(6) default '-1',
641
version int(6) NOT NULL default '0',
642
assigned_scps int(11) default '0',
643
status int(4) default '0'
645
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
646
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
647
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
648
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
649
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
650
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
652
number bigint(20) NOT NULL default '0',
653
cname char(15) NOT NULL default '',
654
carrier_id int(6) NOT NULL default '0',
655
privacy int(4) NOT NULL default '0',
656
last_mod_date timestamp NOT NULL,
657
last_mod_id int(6) NOT NULL default '0',
658
last_app_date timestamp NOT NULL,
659
last_app_id int(6) default '-1',
660
version int(6) NOT NULL default '0',
661
assigned_scps int(11) default '0',
662
status int(4) default '0'
664
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
665
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
666
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
667
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
670
delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);
677
# A simple test with some isolation levels
678
# TODO: Make this into a test using replication to really test how
682
create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
685
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
686
SELECT @@tx_isolation,@@global.tx_isolation;
687
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
688
select id, code, name from t1 order by id;
692
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
693
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
694
select id, code, name from t1 order by id;
698
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
699
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
700
select id, code, name from t1 order by id;
705
# Test of multi-table-update
707
create table t1 (n int(10), d int(10)) engine=innodb;
708
create table t2 (n int(10), d int(10)) engine=innodb;
709
insert into t1 values(1,1),(1,2);
710
insert into t2 values(1,10),(2,20);
711
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
717
# Bug #29136 erred multi-delete on trans table does not rollback
722
drop table if exists t1, t2;
724
CREATE TABLE t1 (a int, PRIMARY KEY (a));
725
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
726
create trigger trg_del_t2 after delete on t2 for each row
727
insert into t1 values (1);
728
insert into t1 values (1);
729
insert into t2 values (1),(2);
732
# exec cases A, B - see multi_update.test
734
# A. send_error() w/o send_eof() branch
741
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
751
create table t1 (a int, b int) engine=innodb;
752
insert into t1 values(20,null);
753
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
755
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
756
t2.b=t3.a order by 1;
757
insert into t1 values(10,null);
758
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
759
t2.b=t3.a order by 1;
763
# Test of read_through not existing const_table
766
create table t1 (a varchar(10) not null) engine=myisam;
767
create table t2 (b varchar(10) not null unique) engine=innodb;
768
select t1.a from t1,t2 where t1.a=t2.b;
770
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
771
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
772
insert into t1 values (10, 20);
773
insert into t2 values (10, 20);
774
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
778
# Test of multi-table-delete with foreign key constraints
781
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
782
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;
783
insert into t1 set id=1;
784
insert into t2 set id=1, t1_id=1;
785
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
789
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
790
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
791
INSERT INTO t1 VALUES(1);
792
INSERT INTO t2 VALUES(1, 1);
794
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
796
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
801
# Test of range_optimizer
806
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
808
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
810
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
812
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
815
INSERT INTO t1 VALUES("this-key", "will disappear");
816
INSERT INTO t2 VALUES("this-key", "will also disappear");
817
DELETE FROM t3 WHERE id1="my-test-1";
827
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
833
# Check update with conflicting key
836
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
837
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
838
# We need the a < 1000 test here to quard against the halloween problems
839
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
844
# Test multi update with different join methods
847
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
848
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
849
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);
850
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
852
# Full join, without key
853
update t1,t2 set t1.a=t1.a+100;
857
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
861
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
865
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
870
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
871
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
873
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
874
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
878
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
879
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
880
select distinct parent,child from t1 order by parent;
884
# Test that MySQL priorities clustered indexes
886
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
887
create table t2 (a int not null auto_increment primary key, b int);
888
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
889
insert into t2 (a) select b from t1;
890
insert into t1 (b) select b from t2;
891
insert into t2 (a) select b from t1;
892
insert into t1 (a) select b from t2;
893
insert into t2 (a) select b from t1;
894
insert into t1 (a) select b from t2;
895
insert into t2 (a) select b from t1;
896
insert into t1 (a) select b from t2;
897
insert into t2 (a) select b from t1;
898
insert into t1 (a) select b from t2;
899
select count(*) from t1;
901
explain select * from t1 where c between 1 and 2500;
904
explain select * from t1 where c between 1 and 2500;
908
# Test of UPDATE ... ORDER BY
911
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
913
insert into t1 (id) values (null),(null),(null),(null),(null);
914
update t1 set fk=69 where fk is null order by id limit 1;
918
create table t1 (a int not null, b int not null, key (a));
919
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
921
update t1 set b=(@tmp:=@tmp+1) order by a;
922
update t1 set b=99 where a=1 order by b asc limit 1;
923
update t1 set b=100 where a=1 order by b desc limit 2;
924
update t1 set a=a+10+b where a=1 order by b;
925
select * from t1 order by a,b;
929
# Test of multi-table-updates (bug #1980).
932
create table t1 ( c char(8) not null ) engine=innodb;
933
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
934
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
936
alter table t1 add b char(8) not null;
937
alter table t1 add a char(8) not null;
938
alter table t1 add primary key (a,b,c);
939
update t1 set a=c, b=c;
941
create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;
942
insert into t2 select * from t1;
944
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
948
# test autoincrement with TRUNCATE
952
create table t1 (a integer auto_increment primary key) engine=innodb;
953
insert into t1 (a) values (NULL),(NULL);
955
insert into t1 (a) values (NULL),(NULL);
960
# Test dictionary handling with spaceand quoting
963
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
964
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB;
965
#show create table t2;
969
# Test of multi updated and foreign keys
972
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
973
insert into `t1`values ( 1 ) ;
974
create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
975
insert into `t2`values ( 1 ) ;
976
create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
977
insert into `t3`values ( 1 ) ;
979
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
981
update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
983
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
987
# test for recursion depth limit
993
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
994
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
995
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
996
delete from t1 where id=0;
997
delete from t1 where id=15;
998
delete from t1 where id=0;
1006
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1007
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1008
(stamp))ENGINE=InnoDB;
1009
insert into t1 values (1),(2),(3);
1010
# Note that timestamp 3 is wrong
1011
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1012
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1013
'20020204120000' GROUP BY col1;
1017
# Test by Francois MASUREL
1021
`id` int(10) NOT NULL auto_increment,
1022
`id_object` int(10) default '0',
1023
`id_version` int(10) NOT NULL default '1',
1024
`label` varchar(100) NOT NULL default '',
1027
KEY `id_object` (`id_object`),
1028
KEY `id_version` (`id_version`)
1031
INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);
1034
`id` int(10) NOT NULL auto_increment,
1035
`id_version` int(10) NOT NULL default '1',
1037
KEY `id_version` (`id_version`)
1040
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1042
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1043
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1044
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1047
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1048
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1049
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1050
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1051
insert t2 select * from t1;
1052
insert t3 select * from t1;
1053
checksum table t1, t2, t3, t4 quick;
1054
checksum table t1, t2, t3, t4;
1055
checksum table t1, t2, t3, t4 extended;
1057
drop table t1,t2,t3;
1060
# Test problem with refering to different fields in same table in UNION
1063
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1064
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1065
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1071
create table t1 (a int) engine=innodb;
1072
create table t2 like t1;
1076
# Test of automaticly created foreign keys
1079
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1080
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1081
show create table t1;
1082
show create table t2;
1083
create index id on t2 (id);
1084
show create table t2;
1085
create index id2 on t2 (id);
1086
show create table t2;
1087
drop index id2 on t2;
1089
drop index id on t2;
1090
show create table t2;
1093
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
1094
show create table t2;
1095
create unique index id on t2 (id,id2);
1096
show create table t2;
1099
# Check foreign key columns created in different order than key columns
1100
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1101
show create table t2;
1104
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
1105
show create table t2;
1108
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1109
show create table t2;
1112
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
1113
show create table t2;
1116
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
1117
show create table t2;
1118
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1119
show create table t2;
1122
# Test error handling
1124
# Embedded server doesn't chdir to data directory
1125
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1126
--error ER_WRONG_FK_DEF
1127
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
1131
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1132
show create table t2;
1134
create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;
1135
show create table t2;
1140
# Bug #6126: Duplicate columns in keys gives misleading error message
1143
create table t1 (c char(10), index (c,c)) engine=innodb;
1145
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1147
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1149
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1150
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1152
alter table t1 add key (c1,c1);
1154
alter table t1 add key (c2,c1,c1);
1156
alter table t1 add key (c1,c2,c1);
1158
alter table t1 add key (c1,c1,c2);
1162
# Bug #4082: integer truncation
1165
create table t1(a int(1) , b int(1)) engine=innodb;
1166
insert into t1 values ('1111', '3333');
1167
select distinct concat(a, b) from t1;
1171
# BUG#7709 test case - Boolean fulltext query against unsupported
1172
# engines does not fail
1175
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1177
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1181
# check null values #1
1185
CREATE TABLE t1 (a_id int(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1186
INSERT INTO t1 VALUES (1),(2),(3);
1187
CREATE TABLE t2 (b_id int(4) NOT NULL default '0',b_a int(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),
1188
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1190
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1191
SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;
1196
# Bug#11816 - Truncate table doesn't work with temporary innodb tables
1197
# This is not an innodb bug, but we test it using innodb.
1199
create temporary table t1 (a int) engine=innodb;
1200
insert into t1 values (4711);
1202
insert into t1 values (42);
1205
# Show that it works with permanent tables too.
1206
create table t1 (a int) engine=innodb;
1207
insert into t1 values (4711);
1209
insert into t1 values (42);
1214
# Bug #13025 Server crash during filesort
1217
create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;
1218
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1219
select * from t1 order by a,b,c,d;
1220
explain select * from t1 order by a,b,c,d;
1224
# BUG#11039,#13218 Wrong key length in min()
1227
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1228
insert into t1 values ('8', '6'), ('4', '7');
1229
select min(a) from t1;
1230
select min(b) from t1 where a='8';
1236
# range optimizer problem
1239
create table t1 (x bigint not null primary key) engine=innodb;
1240
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1242
select count(*) from t1 where x>0;
1243
select count(*) from t1 where x=0;
1244
select count(*) from t1 where x<0;
1245
select count(*) from t1 where x < -16;
1246
select count(*) from t1 where x = -16;
1247
explain select count(*) from t1 where x > -16;
1248
select count(*) from t1 where x > -16;
1249
select * from t1 where x > -16;
1250
select count(*) from t1 where x = 18446744073709551601;
1254
# Test for testable InnoDB status variables. This test
1255
# uses previous ones(pages_created, rows_deleted, ...).
1256
show status like "Innodb_buffer_pool_pages_total";
1257
show status like "Innodb_page_size";
1258
show status like "Innodb_rows_deleted";
1259
show status like "Innodb_rows_inserted";
1260
show status like "Innodb_rows_updated";
1262
# Test for row locks InnoDB status variables.
1263
show status like "Innodb_row_lock_waits";
1264
show status like "Innodb_row_lock_current_waits";
1265
show status like "Innodb_row_lock_time";
1266
show status like "Innodb_row_lock_time_max";
1267
show status like "Innodb_row_lock_time_avg";
1269
# Test for innodb_sync_spin_loops variable
1270
show variables like "innodb_sync_spin_loops";
1271
set global innodb_sync_spin_loops=1000;
1272
show variables like "innodb_sync_spin_loops";
1273
set global innodb_sync_spin_loops=0;
1274
show variables like "innodb_sync_spin_loops";
1275
set global innodb_sync_spin_loops=20;
1276
show variables like "innodb_sync_spin_loops";
1278
# Test for innodb_thread_concurrency variable
1279
show variables like "innodb_thread_concurrency";
1280
set global innodb_thread_concurrency=1001;
1281
show variables like "innodb_thread_concurrency";
1282
set global innodb_thread_concurrency=0;
1283
show variables like "innodb_thread_concurrency";
1284
set global innodb_thread_concurrency=16;
1285
show variables like "innodb_thread_concurrency";
1287
# Test for innodb_concurrency_tickets variable
1288
show variables like "innodb_concurrency_tickets";
1289
set global innodb_concurrency_tickets=1000;
1290
show variables like "innodb_concurrency_tickets";
1291
set global innodb_concurrency_tickets=0;
1292
show variables like "innodb_concurrency_tickets";
1293
set global innodb_concurrency_tickets=500;
1294
show variables like "innodb_concurrency_tickets";
1296
# Test for innodb_thread_sleep_delay variable
1297
show variables like "innodb_thread_sleep_delay";
1298
set global innodb_thread_sleep_delay=100000;
1299
show variables like "innodb_thread_sleep_delay";
1300
set global innodb_thread_sleep_delay=0;
1301
show variables like "innodb_thread_sleep_delay";
1302
set global innodb_thread_sleep_delay=10000;
1303
show variables like "innodb_thread_sleep_delay";
1309
let $default=`select @@storage_engine`;
1310
set storage_engine=INNODB;
1311
source include/varchar.inc;
1314
# Some errors/warnings on create
1317
# Embedded server doesn't chdir to data directory
1318
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1319
create table t1 (v varchar(65530), key(v));
1321
create table t1 (v varchar(65536));
1322
show create table t1;
1324
create table t1 (v varchar(65530) character set utf8);
1325
show create table t1;
1328
eval set storage_engine=$default;
1330
# InnoDB specific varchar tests
1331
create table t1 (v varchar(16384)) engine=innodb;
1335
# BUG#11039 Wrong key length in min()
1338
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1339
insert into t1 values ('8', '6'), ('4', '7');
1340
select min(a) from t1;
1341
select min(b) from t1 where a='8';
1345
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1348
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1349
insert into t1 (b) values (1);
1350
replace into t1 (b) values (2), (1), (3);
1353
insert into t1 (b) values (1);
1354
replace into t1 (b) values (2);
1355
replace into t1 (b) values (1);
1356
replace into t1 (b) values (3);
1360
create table t1 (rowid int not null auto_increment, val int not null,primary
1361
key (rowid), unique(val)) engine=innodb;
1362
replace into t1 (val) values ('1'),('2');
1363
replace into t1 (val) values ('1'),('2');
1364
--error ER_DUP_ENTRY
1365
insert into t1 (val) values ('1'),('2');
1370
# Test that update does not change internal auto-increment value
1373
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1374
insert into t1 (val) values (1);
1375
update t1 set a=2 where a=1;
1376
# We should get the following error because InnoDB does not update the counter
1377
--error ER_DUP_ENTRY
1378
insert into t1 (val) values (1);
1386
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1388
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1389
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1390
SELECT GRADE FROM t1 WHERE GRADE= 151;
1394
# Bug #12340 multitable delete deletes only one record
1396
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1397
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1398
insert into t2 values ('aa','cc');
1399
insert into t1 values ('aa','bb'),('aa','cc');
1400
delete t1 from t1,t2 where f1=f3 and f4='cc';
1405
# Test that the slow TRUNCATE implementation resets autoincrement columns
1410
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1414
id INTEGER NOT NULL,
1415
FOREIGN KEY (id) REFERENCES t1 (id)
1418
INSERT INTO t1 (id) VALUES (NULL);
1421
INSERT INTO t1 (id) VALUES (NULL);
1424
# continued from above; test that doing a slow TRUNCATE on a table with 0
1425
# rows resets autoincrement columns
1428
INSERT INTO t1 (id) VALUES (NULL);
1432
# Test that foreign keys in temporary tables are not accepted (bug #12084)
1439
CREATE TEMPORARY TABLE t2
1441
id INT NOT NULL PRIMARY KEY,
1443
FOREIGN KEY (b) REFERENCES test.t1(id)
1448
# Test that index column max sizes are honored (bug #13315)
1452
create table t1 (col1 varchar(2000), index (col1(767)))
1453
character set = latin1 engine = innodb;
1456
create table t2 (col1 char(255), index (col1))
1457
character set = latin1 engine = innodb;
1458
create table t3 (col1 binary(255), index (col1))
1459
character set = latin1 engine = innodb;
1460
create table t4 (col1 varchar(767), index (col1))
1461
character set = latin1 engine = innodb;
1462
create table t5 (col1 varchar(767) primary key)
1463
character set = latin1 engine = innodb;
1464
create table t6 (col1 varbinary(767) primary key)
1465
character set = latin1 engine = innodb;
1466
create table t7 (col1 text, index(col1(767)))
1467
character set = latin1 engine = innodb;
1468
create table t8 (col1 blob, index(col1(767)))
1469
character set = latin1 engine = innodb;
1471
# multi-column indexes are allowed to be longer
1472
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1473
character set = latin1 engine = innodb;
1475
show create table t9;
1477
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1479
# these should have their index length trimmed
1480
create table t1 (col1 varchar(768), index(col1))
1481
character set = latin1 engine = innodb;
1482
create table t2 (col1 varbinary(768), index(col1))
1483
character set = latin1 engine = innodb;
1484
create table t3 (col1 text, index(col1(768)))
1485
character set = latin1 engine = innodb;
1486
create table t4 (col1 blob, index(col1(768)))
1487
character set = latin1 engine = innodb;
1489
show create table t1;
1491
drop table t1, t2, t3, t4;
1493
# these should be refused
1495
create table t1 (col1 varchar(768) primary key)
1496
character set = latin1 engine = innodb;
1498
create table t2 (col1 varbinary(768) primary key)
1499
character set = latin1 engine = innodb;
1501
create table t3 (col1 text, primary key(col1(768)))
1502
character set = latin1 engine = innodb;
1504
create table t4 (col1 blob, primary key(col1(768)))
1505
character set = latin1 engine = innodb;
1508
# Test improved foreign key error messages (bug #3443)
1519
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1523
INSERT INTO t2 VALUES(2);
1525
INSERT INTO t1 VALUES(1);
1526
INSERT INTO t2 VALUES(1);
1529
DELETE FROM t1 WHERE id = 1;
1534
SET FOREIGN_KEY_CHECKS=0;
1536
SET FOREIGN_KEY_CHECKS=1;
1539
INSERT INTO t2 VALUES(3);
1543
# Test that checksum table uses a consistent read Bug #12669
1545
connect (a,localhost,root,,);
1546
connect (b,localhost,root,,);
1548
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1549
insert into t1 values (1),(2);
1553
insert into t1 values(3);
1556
# Here checksum should not see insert
1568
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1569
insert into t1 values (1),(2);
1574
insert into t1 values(3);
1577
# Here checksum sees insert
1586
# tests for bugs #9802 and #13778
1588
# test that FKs between invalid types are not accepted
1590
set foreign_key_checks=0;
1591
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1592
# Embedded server doesn't chdir to data directory
1593
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1594
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1595
set foreign_key_checks=1;
1598
# test that FKs between different charsets are not accepted in CREATE even
1601
set foreign_key_checks=0;
1602
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1603
# Embedded server doesn't chdir to data directory
1604
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1605
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1606
set foreign_key_checks=1;
1609
# test that invalid datatype conversions with ALTER are not allowed
1611
set foreign_key_checks=0;
1612
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1613
create table t1(a varchar(10) primary key) engine = innodb;
1614
alter table t1 modify column a int;
1615
set foreign_key_checks=1;
1618
# test that charset conversions with ALTER are allowed when f_k_c is 0
1620
set foreign_key_checks=0;
1621
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1622
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1623
alter table t1 convert to character set utf8;
1624
set foreign_key_checks=1;
1627
# test that RENAME does not allow invalid charsets when f_k_c is 0
1629
set foreign_key_checks=0;
1630
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1631
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1632
# Embedded server doesn't chdir to data directory
1633
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1634
rename table t3 to t1;
1635
set foreign_key_checks=1;
1638
# test that foreign key errors are reported correctly (Bug #15550)
1640
create table t1(a int primary key) row_format=redundant engine=innodb;
1641
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1642
create table t3(a int primary key) row_format=compact engine=innodb;
1643
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1645
insert into t1 values(1);
1646
insert into t3 values(1);
1647
insert into t2 values(2);
1648
insert into t4 values(2);
1649
insert into t2 values(1);
1650
insert into t4 values(1);
1662
drop table t4,t3,t2,t1;
1666
# Test that we can create a large (>1K) key
1668
create table t1 (a varchar(255) character set utf8,
1669
b varchar(255) character set utf8,
1670
c varchar(255) character set utf8,
1671
d varchar(255) character set utf8,
1672
key (a,b,c,d)) engine=innodb;
1674
--error ER_TOO_LONG_KEY
1675
create table t1 (a varchar(255) character set utf8,
1676
b varchar(255) character set utf8,
1677
c varchar(255) character set utf8,
1678
d varchar(255) character set utf8,
1679
e varchar(255) character set utf8,
1680
key (a,b,c,d,e)) engine=innodb;
1683
# test the padding of BINARY types and collations (Bug #14189)
1685
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1686
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1687
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1688
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1690
insert into t1 values (0x41),(0x4120),(0x4100);
1691
insert into t2 values (0x41),(0x4120),(0x4100);
1692
insert into t2 values (0x41),(0x4120);
1693
insert into t3 values (0x41),(0x4120),(0x4100);
1694
insert into t3 values (0x41),(0x4100);
1695
insert into t4 values (0x41),(0x4120),(0x4100);
1696
insert into t4 values (0x41),(0x4100);
1697
select hex(s1) from t1;
1698
select hex(s1) from t2;
1699
select hex(s1) from t3;
1700
select hex(s1) from t4;
1701
drop table t1,t2,t3,t4;
1703
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1704
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1706
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1707
insert into t2 values(0x42);
1708
insert into t2 values(0x41);
1709
select hex(s1) from t2;
1710
update t1 set s1=0x123456 where a=2;
1711
select hex(s1) from t2;
1712
update t1 set s1=0x12 where a=1;
1713
update t1 set s1=0x12345678 where a=1;
1714
update t1 set s1=0x123457 where a=1;
1715
update t1 set s1=0x1220 where a=1;
1716
select hex(s1) from t2;
1717
update t1 set s1=0x1200 where a=1;
1718
select hex(s1) from t2;
1719
update t1 set s1=0x4200 where a=1;
1720
select hex(s1) from t2;
1721
delete from t1 where a=1;
1722
delete from t1 where a=2;
1723
update t2 set s1=0x4120;
1725
delete from t1 where a!=3;
1726
select a,hex(s1) from t1;
1727
select hex(s1) from t2;
1731
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1732
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1734
insert into t1 values(1,0x4100),(2,0x41);
1735
insert into t2 values(0x41);
1736
select hex(s1) from t2;
1737
update t1 set s1=0x1234 where a=1;
1738
select hex(s1) from t2;
1739
update t1 set s1=0x12 where a=2;
1740
select hex(s1) from t2;
1741
delete from t1 where a=1;
1742
delete from t1 where a=2;
1743
select a,hex(s1) from t1;
1744
select hex(s1) from t2;
1747
# Ensure that <tablename>_ibfk_0 is not mistreated as a
1748
# generated foreign key identifier. (Bug #16387)
1750
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1751
CREATE TABLE t2(a INT) ENGINE=InnoDB;
1752
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1753
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1754
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1755
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1756
SHOW CREATE TABLE t2;
1760
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1763
connect (a,localhost,root,,);
1764
connect (b,localhost,root,,);
1766
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1767
insert into t1(a) values (1),(2),(3);
1771
update t1 set b = 5 where a = 2;
1774
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1778
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1779
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1780
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1781
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1782
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1792
# Another trigger test
1794
connect (a,localhost,root,,);
1795
connect (b,localhost,root,,);
1797
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1798
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1799
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1800
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1801
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1802
insert into t1(a) values (1),(2),(3);
1803
insert into t2(a) values (1),(2),(3);
1804
insert into t3(a) values (1),(2),(3);
1805
insert into t4(a) values (1),(2),(3);
1806
insert into t3(a) values (5),(7),(8);
1807
insert into t4(a) values (5),(7),(8);
1808
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1811
create trigger t1t before insert on t1 for each row begin
1812
INSERT INTO t2 SET a = NEW.a;
1815
create trigger t2t before insert on t2 for each row begin
1816
DELETE FROM t3 WHERE a = NEW.a;
1819
create trigger t3t before delete on t3 for each row begin
1820
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1823
create trigger t4t before update on t4 for each row begin
1824
UPDATE t5 SET b = b + 1 where a = NEW.a;
1829
update t1 set b = b + 5 where a = 1;
1830
update t2 set b = b + 5 where a = 1;
1831
update t3 set b = b + 5 where a = 1;
1832
update t4 set b = b + 5 where a = 1;
1833
insert into t5(a) values(20);
1836
insert into t1(a) values(7);
1837
insert into t2(a) values(8);
1838
delete from t2 where a = 3;
1839
update t4 set b = b + 1 where a = 3;
1845
drop table t1, t2, t3, t4, t5;
1851
# Test that cascading updates leading to duplicate keys give the correct
1852
# error message (bug #9680)
1856
field1 varchar(8) NOT NULL DEFAULT '',
1857
field2 varchar(8) NOT NULL DEFAULT '',
1858
PRIMARY KEY (field1, field2)
1862
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1863
FOREIGN KEY (field1) REFERENCES t1 (field1)
1864
ON DELETE CASCADE ON UPDATE CASCADE
1867
INSERT INTO t1 VALUES ('old', 'somevalu');
1868
INSERT INTO t1 VALUES ('other', 'anyvalue');
1870
INSERT INTO t2 VALUES ('old');
1871
INSERT INTO t2 VALUES ('other');
1873
--error ER_FOREIGN_DUPLICATE_KEY
1874
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1880
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1894
alter table t1 add constraint c2_fk foreign key (c2)
1895
references t2(c1) on delete cascade;
1896
show create table t1;
1898
alter table t1 drop foreign key c2_fk;
1899
show create table t1;
1904
# Bug #14360: problem with intervals
1907
create table t1(a date) engine=innodb;
1908
create table t2(a date, key(a)) engine=innodb;
1909
insert into t1 values('2005-10-01');
1910
insert into t2 values('2005-10-01');
1911
select * from t1, t2
1912
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1915
create table t1 (id int not null, f_id int not null, f int not null,
1916
primary key(f_id, id)) engine=innodb;
1917
create table t2 (id int not null,s_id int not null,s varchar(200),
1918
primary key(id)) engine=innodb;
1919
INSERT INTO t1 VALUES (8, 1, 3);
1920
INSERT INTO t1 VALUES (1, 2, 1);
1921
INSERT INTO t2 VALUES (1, 0, '');
1922
INSERT INTO t2 VALUES (8, 1, '');
1924
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
1925
WHERE mm.id IS NULL;
1926
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
1927
where mm.id is null lock in share mode;
1931
# Test case where X-locks on unused rows should be released in a
1932
# update (because READ COMMITTED isolation level)
1935
connect (a,localhost,root,,);
1936
connect (b,localhost,root,,);
1938
create table t1(a int not null, b int, primary key(a)) engine=innodb;
1939
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
1942
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1943
update t1 set b = 5 where b = 1;
1946
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1948
# X-lock to record (7,3) should be released in a update
1950
select * from t1 where a = 7 and b = 3 for update;
1961
# Test case where no locks should be released (because we are not
1962
# using READ COMMITTED isolation level)
1965
connect (a,localhost,root,,);
1966
connect (b,localhost,root,,);
1968
create table t1(a int not null, b int, primary key(a)) engine=innodb;
1969
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
1972
select * from t1 lock in share mode;
1973
update t1 set b = 5 where b = 1;
1977
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
1980
select * from t1 where a = 2 and b = 2 for update;
1982
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
1995
# Consistent read should be used in following selects
1997
# 1) INSERT INTO ... SELECT
1998
# 2) UPDATE ... = ( SELECT ...)
1999
# 3) CREATE ... SELECT
2001
connect (a,localhost,root,,);
2002
connect (b,localhost,root,,);
2004
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2005
insert into t1 values (1,2),(5,3),(4,2);
2006
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2007
insert into t2 values (8,6),(12,1),(3,1);
2010
select * from t2 for update;
2013
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2014
insert into t1 select * from t2;
2015
update t1 set b = (select e from t2 where a = d);
2016
create table t3(d int not null, e int, primary key(d)) engine=innodb
2024
drop table t1, t2, t3;
2027
# Consistent read should not be used if
2029
# (a) isolation level is serializable OR
2030
# (b) select ... lock in share mode OR
2031
# (c) select ... for update
2033
# in following queries:
2035
# 1) INSERT INTO ... SELECT
2036
# 2) UPDATE ... = ( SELECT ...)
2037
# 3) CREATE ... SELECT
2039
connect (a,localhost,root,,);
2040
connect (b,localhost,root,,);
2041
connect (c,localhost,root,,);
2042
connect (d,localhost,root,,);
2043
connect (e,localhost,root,,);
2044
connect (f,localhost,root,,);
2045
connect (g,localhost,root,,);
2046
connect (h,localhost,root,,);
2047
connect (i,localhost,root,,);
2048
connect (j,localhost,root,,);
2050
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2051
insert into t1 values (1,2),(5,3),(4,2);
2052
create table t2(a int not null, b int, primary key(a)) engine=innodb;
2053
insert into t2 values (8,6),(12,1),(3,1);
2054
create table t3(d int not null, b int, primary key(d)) engine=innodb;
2055
insert into t3 values (8,6),(12,1),(3,1);
2056
create table t5(a int not null, b int, primary key(a)) engine=innodb;
2057
insert into t5 values (1,2),(5,3),(4,2);
2058
create table t6(d int not null, e int, primary key(d)) engine=innodb;
2059
insert into t6 values (8,6),(12,1),(3,1);
2060
create table t8(a int not null, b int, primary key(a)) engine=innodb;
2061
insert into t8 values (1,2),(5,3),(4,2);
2062
create table t9(d int not null, e int, primary key(d)) engine=innodb;
2063
insert into t9 values (8,6),(12,1),(3,1);
2066
select * from t2 for update;
2069
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2071
insert into t1 select * from t2;
2074
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2076
update t3 set b = (select b from t2 where a = d);
2079
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2081
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2084
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2086
insert into t5 (select * from t2 lock in share mode);
2089
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2091
update t6 set e = (select b from t2 where a = d lock in share mode);
2094
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2096
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2099
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2101
insert into t8 (select * from t2 for update);
2104
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2106
update t9 set e = (select b from t2 where a = d for update);
2109
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2111
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2163
drop table t1, t2, t3, t5, t6, t8, t9;
2165
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2167
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2170
# Bug #17152: Wrong result with BINARY comparison on aliased column
2174
a BIGINT(20) NOT NULL,
2176
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2179
a BIGINT(20) NOT NULL,
2180
b VARCHAR(128) NOT NULL,
2183
KEY idx_t2_b_c (b,c(200)),
2184
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2186
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2188
INSERT INTO t1 VALUES (1);
2189
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2190
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2191
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2192
INSERT INTO t2 VALUES (1, 'customer_over', '1');
2194
SELECT * FROM t2 WHERE b = 'customer_over';
2195
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2196
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2197
/* Bang: Empty result set, above was expected: */
2198
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2199
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2204
# Test optimize on table with open transaction
2207
CREATE TABLE t1 ( a int ) ENGINE=innodb;
2209
INSERT INTO t1 VALUES (1);
2214
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2217
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2219
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2220
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2221
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2223
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2224
DELETE CASCADE ON UPDATE CASCADE;
2226
SHOW CREATE TABLE t2;
2230
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2231
# for which there is a foreign key constraint ON ... SET NULL.
2234
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2235
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2236
INSERT INTO t1 VALUES (1);
2237
INSERT INTO t2 VALUES (1);
2238
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2239
# mysqltest first does replace_regex, then replace_result
2240
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
2241
# Embedded server doesn't chdir to data directory
2242
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
2244
ALTER TABLE t2 MODIFY a INT NOT NULL;
2249
# Bug #26835: table corruption after delete+insert
2252
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2254
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2256
INSERT INTO t1 VALUES ('DDD');
2261
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2262
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2265
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2268
INSERT INTO t1 VALUES (0),(347),(0);
2271
SHOW CREATE TABLE t1;
2273
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2274
INSERT INTO t2 VALUES(42),(347),(348);
2275
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2276
SHOW CREATE TABLE t1;
2281
# Bug #21101 (Prints wrong error message if max row size is too large)
2285
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2286
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2287
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2288
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2289
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2290
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2291
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2292
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2296
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2298
DROP TABLE IF EXISTS t1;
2300
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2302
INSERT INTO t1 VALUES(-10);
2305
# NOTE: The server really needs to be restarted at this point
2306
# for the test to be useful.
2308
# Without the fix InnoDB would trip over an assertion here.
2309
INSERT INTO t1 VALUES(NULL);
2310
# The next value should be 1 and not -9 or a -ve number
2315
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
2318
CONNECT (c1,localhost,root,,);
2319
CONNECT (c2,localhost,root,,);
2321
SET TX_ISOLATION='read-committed';
2323
DROP TABLE IF EXISTS t1, t2;
2324
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2325
CREATE TABLE t2 LIKE t1;
2328
SET TX_ISOLATION='read-committed';
2330
INSERT INTO t1 VALUES (1);
2333
SELECT * FROM t1 WHERE a=1;
2336
CONNECT (c1,localhost,root,,);
2337
CONNECT (c2,localhost,root,,);
2339
SET TX_ISOLATION='read-committed';
2343
SET TX_ISOLATION='read-committed';
2345
INSERT INTO t1 VALUES (2);
2348
# The result set below should be the same for both selects
2349
SELECT * FROM t1 WHERE a=2;
2350
SELECT * FROM t1 WHERE a=2;
2356
#######################################################################
2358
# Please, DO NOT TOUCH this file as well as the innodb.result file. #
2359
# These files are to be modified ONLY BY INNOBASE guys. #
2361
# Use innodb_mysql.[test|result] files instead. #
2363
# If nevertheless you need to make some changes here, please, forward #
2364
# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com #
2365
# (otherwise your changes may be erased). #
2367
#######################################################################
added
removed
tests/t/innodb.test
