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;
24
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
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 tinyint(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 tinyint(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';
403
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
404
select count(*) from t1 where sca_code='PD' and sca_pic is null;
405
select count(*) from t1 where sca_pic >= 'n';
406
select sca_pic from t1 where sca_pic is null;
407
update t1 set sca_pic="test" where sca_pic is null;
408
delete from t1 where sca_code='pd';
412
# Test of opening table twice and timestamps
415
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
416
insert into t1 (a) values(1),(2),(3);
417
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
418
select a from t1 natural join t1 as t2 where b >= @a order by a;
419
update t1 set a=5 where a=1;
424
# Test with variable length primary key
426
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
427
insert into t1 values("hello",1),("world",2);
428
select * from t1 order by b desc;
435
# Test of create index with NULL columns
437
create table t1 (i int, j int ) ENGINE=innodb;
438
insert into t1 values (1,2);
439
select * from t1 where i=1 and j=2;
440
create index ax1 on t1 (i,j);
441
select * from t1 where i=1 and j=2;
445
# Test min-max optimization
449
a int3 unsigned NOT NULL,
450
b int1 unsigned NOT NULL,
454
INSERT INTO t1 VALUES (1, 1);
455
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
459
# Test INSERT DELAYED
462
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
463
# Can't test this in 3.23
464
# INSERT DELAYED INTO t1 VALUES (1);
465
INSERT INTO t1 VALUES (1);
471
# Crash when using many tables (Test case by Jeremy D Zawodny)
474
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;
475
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);
477
explain select * from t1 where a > 0 and a < 50;
484
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;
485
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
486
LOCK TABLES t1 WRITE;
488
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
494
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;
495
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
496
LOCK TABLES t1 WRITE;
499
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
501
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
503
select id,id3 from t1;
510
create table t1 (a char(20), unique (a(5))) engine=innodb;
512
create table t1 (a char(20), index (a(5))) engine=innodb;
513
show create table t1;
517
# Test using temporary table and auto_increment
520
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
521
insert into t1 values (NULL),(NULL),(NULL);
522
delete from t1 where a=3;
523
insert into t1 values (NULL);
525
alter table t1 add b int;
532
id int auto_increment primary key,
533
name varchar(32) not null,
538
insert into t1 values (1,'one','one value',101),
539
(2,'two','two value',102),(3,'three','three value',103);
541
replace into t1 (value,name,uid) values ('other value','two',102);
542
delete from t1 where uid=102;
544
replace into t1 (value,name,uid) values ('other value','two',102);
546
replace into t1 (value,name,uid) values ('other value','two',102);
554
create database mysqltest;
555
create table mysqltest.t1 (a int not null) engine= innodb;
556
insert into mysqltest.t1 values(1);
557
create table mysqltest.t2 (a int not null) engine= myisam;
558
insert into mysqltest.t2 values(1);
559
create table mysqltest.t3 (a int not null) engine= heap;
560
insert into mysqltest.t3 values(1);
562
drop database mysqltest;
563
# Don't check error message
565
show tables from mysqltest;
568
# Test truncate table with and without auto_commit
572
create table t1 (a int not null) engine= innodb;
573
insert into t1 values(1),(2);
579
insert into t1 values(1),(2);
586
create table t1 (a int not null) engine= innodb;
587
insert into t1 values(1),(2);
589
insert into t1 values(1),(2);
592
insert into t1 values(1),(2);
598
# Test of how ORDER BY works when doing it on the whole table
601
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
602
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
604
explain select * from t1 order by a;
606
explain select * from t1 order by b;
608
explain select * from t1 order by c;
610
explain select a from t1 order by a;
612
explain select b from t1 order by b;
614
explain select a,b from t1 order by b;
616
explain select a,b from t1;
618
explain select a,b,c from t1;
625
create table t1 (t int not null default 1, key (t)) engine=innodb;
630
# Test of multi-table-delete
634
number bigint(20) NOT NULL default '0',
635
cname char(15) NOT NULL default '',
636
carrier_id smallint(6) NOT NULL default '0',
637
privacy tinyint(4) NOT NULL default '0',
638
last_mod_date timestamp NOT NULL,
639
last_mod_id smallint(6) NOT NULL default '0',
640
last_app_date timestamp NOT NULL,
641
last_app_id smallint(6) default '-1',
642
version smallint(6) NOT NULL default '0',
643
assigned_scps int(11) default '0',
644
status tinyint(4) default '0'
646
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
647
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
648
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
649
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
650
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
651
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
653
number bigint(20) NOT NULL default '0',
654
cname char(15) NOT NULL default '',
655
carrier_id smallint(6) NOT NULL default '0',
656
privacy tinyint(4) NOT NULL default '0',
657
last_mod_date timestamp NOT NULL,
658
last_mod_id smallint(6) NOT NULL default '0',
659
last_app_date timestamp NOT NULL,
660
last_app_id smallint(6) default '-1',
661
version smallint(6) NOT NULL default '0',
662
assigned_scps int(11) default '0',
663
status tinyint(4) default '0'
665
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
666
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
667
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
668
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
671
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);
678
# A simple test with some isolation levels
679
# TODO: Make this into a test using replication to really test how
683
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
686
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
687
SELECT @@tx_isolation,@@global.tx_isolation;
688
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
689
select id, code, name from t1 order by id;
693
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
694
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
695
select id, code, name from t1 order by id;
699
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
700
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
701
select id, code, name from t1 order by id;
706
# Test of multi-table-update
708
create table t1 (n int(10), d int(10)) engine=innodb;
709
create table t2 (n int(10), d int(10)) engine=innodb;
710
insert into t1 values(1,1),(1,2);
711
insert into t2 values(1,10),(2,20);
712
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
718
# Bug #29136 erred multi-delete on trans table does not rollback
723
drop table if exists t1, t2;
725
CREATE TABLE t1 (a int, PRIMARY KEY (a));
726
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
727
create trigger trg_del_t2 after delete on t2 for each row
728
insert into t1 values (1);
729
insert into t1 values (1);
730
insert into t2 values (1),(2);
733
# exec cases A, B - see multi_update.test
735
# A. send_error() w/o send_eof() branch
742
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
750
# Bug #29136 erred multi-delete on trans table does not rollback
755
drop table if exists t1, t2;
757
CREATE TABLE t1 (a int, PRIMARY KEY (a));
758
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
759
create trigger trg_del_t2 after delete on t2 for each row
760
insert into t1 values (1);
761
insert into t1 values (1);
762
insert into t2 values (1),(2);
765
# exec cases A, B - see multi_update.test
767
# A. send_error() w/o send_eof() branch
774
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
784
create table t1 (a int, b int) engine=innodb;
785
insert into t1 values(20,null);
786
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
788
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
789
t2.b=t3.a order by 1;
790
insert into t1 values(10,null);
791
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
792
t2.b=t3.a order by 1;
796
# Test of read_through not existing const_table
799
create table t1 (a varchar(10) not null) engine=myisam;
800
create table t2 (b varchar(10) not null unique) engine=innodb;
801
select t1.a from t1,t2 where t1.a=t2.b;
803
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
804
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
805
insert into t1 values (10, 20);
806
insert into t2 values (10, 20);
807
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
811
# Test of multi-table-delete with foreign key constraints
814
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
815
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;
816
insert into t1 set id=1;
817
insert into t2 set id=1, t1_id=1;
818
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
822
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
823
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
824
INSERT INTO t1 VALUES(1);
825
INSERT INTO t2 VALUES(1, 1);
827
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
829
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
834
# Test of range_optimizer
839
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
841
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
843
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
845
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
848
INSERT INTO t1 VALUES("this-key", "will disappear");
849
INSERT INTO t2 VALUES("this-key", "will also disappear");
850
DELETE FROM t3 WHERE id1="my-test-1";
860
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
866
# Check update with conflicting key
869
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
870
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
871
# We need the a < 1000 test here to quard against the halloween problems
872
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
877
# Test multi update with different join methods
880
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
881
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
882
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);
883
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
885
# Full join, without key
886
update t1,t2 set t1.a=t1.a+100;
890
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
894
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
898
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;
903
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
904
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
906
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
907
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
911
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
912
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
913
select distinct parent,child from t1 order by parent;
917
# Test that MySQL priorities clustered indexes
919
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
920
create table t2 (a int not null auto_increment primary key, b int);
921
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
922
insert into t2 (a) select b from t1;
923
insert into t1 (b) select b from t2;
924
insert into t2 (a) select b from t1;
925
insert into t1 (a) select b from t2;
926
insert into t2 (a) select b from t1;
927
insert into t1 (a) select b from t2;
928
insert into t2 (a) select b from t1;
929
insert into t1 (a) select b from t2;
930
insert into t2 (a) select b from t1;
931
insert into t1 (a) select b from t2;
932
select count(*) from t1;
934
explain select * from t1 where c between 1 and 2500;
937
explain select * from t1 where c between 1 and 2500;
941
# Test of UPDATE ... ORDER BY
944
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
946
insert into t1 (id) values (null),(null),(null),(null),(null);
947
update t1 set fk=69 where fk is null order by id limit 1;
951
create table t1 (a int not null, b int not null, key (a));
952
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);
954
update t1 set b=(@tmp:=@tmp+1) order by a;
955
update t1 set b=99 where a=1 order by b asc limit 1;
956
update t1 set b=100 where a=1 order by b desc limit 2;
957
update t1 set a=a+10+b where a=1 order by b;
958
select * from t1 order by a,b;
962
# Test of multi-table-updates (bug #1980).
965
create table t1 ( c char(8) not null ) engine=innodb;
966
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
967
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
969
alter table t1 add b char(8) not null;
970
alter table t1 add a char(8) not null;
971
alter table t1 add primary key (a,b,c);
972
update t1 set a=c, b=c;
974
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;
975
insert into t2 select * from t1;
977
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
981
# test autoincrement with TRUNCATE
985
create table t1 (a integer auto_increment primary key) engine=innodb;
986
insert into t1 (a) values (NULL),(NULL);
988
insert into t1 (a) values (NULL),(NULL);
993
# Test dictionary handling with spaceand quoting
996
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
997
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;
998
#show create table t2;
1002
# Test of multi updated and foreign keys
1005
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1006
insert into `t1`values ( 1 ) ;
1007
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;
1008
insert into `t2`values ( 1 ) ;
1009
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;
1010
insert into `t3`values ( 1 ) ;
1012
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1014
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;
1016
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1017
drop table t3,t2,t1;
1020
# test for recursion depth limit
1026
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1027
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1028
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1029
delete from t1 where id=0;
1030
delete from t1 where id=15;
1031
delete from t1 where id=0;
1039
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1040
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1041
(stamp))ENGINE=InnoDB;
1042
insert into t1 values (1),(2),(3);
1043
# Note that timestamp 3 is wrong
1044
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1045
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1046
'20020204120000' GROUP BY col1;
1050
# Test by Francois MASUREL
1054
`id` int(10) unsigned NOT NULL auto_increment,
1055
`id_object` int(10) unsigned default '0',
1056
`id_version` int(10) unsigned NOT NULL default '1',
1057
`label` varchar(100) NOT NULL default '',
1060
KEY `id_object` (`id_object`),
1061
KEY `id_version` (`id_version`)
1064
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);
1067
`id` int(10) unsigned NOT NULL auto_increment,
1068
`id_version` int(10) unsigned NOT NULL default '1',
1070
KEY `id_version` (`id_version`)
1073
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1075
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1076
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1077
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1080
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1081
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1082
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1083
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1084
insert t2 select * from t1;
1085
insert t3 select * from t1;
1086
checksum table t1, t2, t3, t4 quick;
1087
checksum table t1, t2, t3, t4;
1088
checksum table t1, t2, t3, t4 extended;
1090
drop table t1,t2,t3;
1093
# Test problem with refering to different fields in same table in UNION
1096
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1097
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1098
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1104
create table t1 (a int) engine=innodb;
1105
create table t2 like t1;
1109
# Test of automaticly created foreign keys
1112
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1113
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1114
show create table t1;
1115
show create table t2;
1116
create index id on t2 (id);
1117
show create table t2;
1118
create index id2 on t2 (id);
1119
show create table t2;
1120
drop index id2 on t2;
1121
--error ER_DROP_INDEX_FK
1122
drop index id on t2;
1123
show create table t2;
1126
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;
1127
show create table t2;
1128
create unique index id on t2 (id,id2);
1129
show create table t2;
1132
# Check foreign key columns created in different order than key columns
1133
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;
1134
show create table t2;
1137
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;
1138
show create table t2;
1141
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;
1142
show create table t2;
1145
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;
1146
show create table t2;
1149
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;
1150
show create table t2;
1151
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1152
show create table t2;
1155
# Test error handling
1157
# Embedded server doesn't chdir to data directory
1158
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1159
--error ER_WRONG_FK_DEF
1160
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;
1164
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1165
show create table t2;
1167
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;
1168
show create table t2;
1173
# Bug #6126: Duplicate columns in keys gives misleading error message
1176
create table t1 (c char(10), index (c,c)) engine=innodb;
1178
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1180
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1182
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1183
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1185
alter table t1 add key (c1,c1);
1187
alter table t1 add key (c2,c1,c1);
1189
alter table t1 add key (c1,c2,c1);
1191
alter table t1 add key (c1,c1,c2);
1195
# Bug #4082: integer truncation
1198
create table t1(a int(1) , b int(1)) engine=innodb;
1199
insert into t1 values ('1111', '3333');
1200
select distinct concat(a, b) from t1;
1204
# BUG#7709 test case - Boolean fulltext query against unsupported
1205
# engines does not fail
1208
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1210
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1214
# check null values #1
1218
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1219
INSERT INTO t1 VALUES (1),(2),(3);
1220
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),
1221
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1223
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1224
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;
1229
# Bug#11816 - Truncate table doesn't work with temporary innodb tables
1230
# This is not an innodb bug, but we test it using innodb.
1232
create temporary table t1 (a int) engine=innodb;
1233
insert into t1 values (4711);
1235
insert into t1 values (42);
1238
# Show that it works with permanent tables too.
1239
create table t1 (a int) engine=innodb;
1240
insert into t1 values (4711);
1242
insert into t1 values (42);
1247
# Bug #13025 Server crash during filesort
1250
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;
1251
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1252
select * from t1 order by a,b,c,d;
1253
explain select * from t1 order by a,b,c,d;
1257
# BUG#11039,#13218 Wrong key length in min()
1260
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1261
insert into t1 values ('8', '6'), ('4', '7');
1262
select min(a) from t1;
1263
select min(b) from t1 where a='8';
1269
# range optimizer problem
1272
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1273
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1275
select count(*) from t1 where x>0;
1276
select count(*) from t1 where x=0;
1277
select count(*) from t1 where x<0;
1278
select count(*) from t1 where x < -16;
1279
select count(*) from t1 where x = -16;
1280
explain select count(*) from t1 where x > -16;
1281
select count(*) from t1 where x > -16;
1282
select * from t1 where x > -16;
1283
select count(*) from t1 where x = 18446744073709551601;
1287
# Test for testable InnoDB status variables. This test
1288
# uses previous ones(pages_created, rows_deleted, ...).
1289
--replace_result 512 511
1290
show status like "Innodb_buffer_pool_pages_total";
1291
show status like "Innodb_page_size";
1292
show status like "Innodb_rows_deleted";
1293
show status like "Innodb_rows_inserted";
1294
show status like "Innodb_rows_updated";
1296
# Test for row locks InnoDB status variables.
1297
show status like "Innodb_row_lock_waits";
1298
show status like "Innodb_row_lock_current_waits";
1299
show status like "Innodb_row_lock_time";
1300
show status like "Innodb_row_lock_time_max";
1301
show status like "Innodb_row_lock_time_avg";
1303
# Test for innodb_sync_spin_loops variable
1304
show variables like "innodb_sync_spin_loops";
1305
set global innodb_sync_spin_loops=1000;
1306
show variables like "innodb_sync_spin_loops";
1307
set global innodb_sync_spin_loops=0;
1308
show variables like "innodb_sync_spin_loops";
1309
set global innodb_sync_spin_loops=20;
1310
show variables like "innodb_sync_spin_loops";
1312
# Test for innodb_thread_concurrency variable
1313
show variables like "innodb_thread_concurrency";
1314
set global innodb_thread_concurrency=1001;
1315
show variables like "innodb_thread_concurrency";
1316
set global innodb_thread_concurrency=0;
1317
show variables like "innodb_thread_concurrency";
1318
set global innodb_thread_concurrency=16;
1319
show variables like "innodb_thread_concurrency";
1321
# Test for innodb_concurrency_tickets variable
1322
show variables like "innodb_concurrency_tickets";
1323
set global innodb_concurrency_tickets=1000;
1324
show variables like "innodb_concurrency_tickets";
1325
set global innodb_concurrency_tickets=0;
1326
show variables like "innodb_concurrency_tickets";
1327
set global innodb_concurrency_tickets=500;
1328
show variables like "innodb_concurrency_tickets";
1330
# Test for innodb_thread_sleep_delay variable
1331
show variables like "innodb_thread_sleep_delay";
1332
set global innodb_thread_sleep_delay=100000;
1333
show variables like "innodb_thread_sleep_delay";
1334
set global innodb_thread_sleep_delay=0;
1335
show variables like "innodb_thread_sleep_delay";
1336
set global innodb_thread_sleep_delay=10000;
1337
show variables like "innodb_thread_sleep_delay";
1343
let $default=`select @@storage_engine`;
1344
set storage_engine=INNODB;
1345
source include/varchar.inc;
1348
# Some errors/warnings on create
1351
# Embedded server doesn't chdir to data directory
1352
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1353
create table t1 (v varchar(65530), key(v));
1355
create table t1 (v varchar(65536));
1356
show create table t1;
1358
create table t1 (v varchar(65530) character set utf8);
1359
show create table t1;
1362
eval set storage_engine=$default;
1364
# InnoDB specific varchar tests
1365
create table t1 (v varchar(16384)) engine=innodb;
1369
# BUG#11039 Wrong key length in min()
1372
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1373
insert into t1 values ('8', '6'), ('4', '7');
1374
select min(a) from t1;
1375
select min(b) from t1 where a='8';
1379
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1382
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1383
insert into t1 (b) values (1);
1384
replace into t1 (b) values (2), (1), (3);
1387
insert into t1 (b) values (1);
1388
replace into t1 (b) values (2);
1389
replace into t1 (b) values (1);
1390
replace into t1 (b) values (3);
1394
create table t1 (rowid int not null auto_increment, val int not null,primary
1395
key (rowid), unique(val)) engine=innodb;
1396
replace into t1 (val) values ('1'),('2');
1397
replace into t1 (val) values ('1'),('2');
1398
--error ER_DUP_ENTRY
1399
insert into t1 (val) values ('1'),('2');
1404
# Test that update does not change internal auto-increment value
1407
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1408
insert into t1 (val) values (1);
1409
update t1 set a=2 where a=1;
1410
# We should get the following error because InnoDB does not update the counter
1411
--error ER_DUP_ENTRY
1412
insert into t1 (val) values (1);
1420
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1422
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1423
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1424
SELECT GRADE FROM t1 WHERE GRADE= 151;
1428
# Bug #12340 multitable delete deletes only one record
1430
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1431
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1432
insert into t2 values ('aa','cc');
1433
insert into t1 values ('aa','bb'),('aa','cc');
1434
delete t1 from t1,t2 where f1=f3 and f4='cc';
1439
# Test that the slow TRUNCATE implementation resets autoincrement columns
1444
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1448
id INTEGER NOT NULL,
1449
FOREIGN KEY (id) REFERENCES t1 (id)
1452
INSERT INTO t1 (id) VALUES (NULL);
1455
INSERT INTO t1 (id) VALUES (NULL);
1458
# continued from above; test that doing a slow TRUNCATE on a table with 0
1459
# rows resets autoincrement columns
1462
INSERT INTO t1 (id) VALUES (NULL);
1466
# Test that foreign keys in temporary tables are not accepted (bug #12084)
1473
CREATE TEMPORARY TABLE t2
1475
id INT NOT NULL PRIMARY KEY,
1477
FOREIGN KEY (b) REFERENCES test.t1(id)
1482
# Test that index column max sizes are honored (bug #13315)
1486
create table t1 (col1 varchar(2000), index (col1(767)))
1487
character set = latin1 engine = innodb;
1490
create table t2 (col1 char(255), index (col1))
1491
character set = latin1 engine = innodb;
1492
create table t3 (col1 binary(255), index (col1))
1493
character set = latin1 engine = innodb;
1494
create table t4 (col1 varchar(767), index (col1))
1495
character set = latin1 engine = innodb;
1496
create table t5 (col1 varchar(767) primary key)
1497
character set = latin1 engine = innodb;
1498
create table t6 (col1 varbinary(767) primary key)
1499
character set = latin1 engine = innodb;
1500
create table t7 (col1 text, index(col1(767)))
1501
character set = latin1 engine = innodb;
1502
create table t8 (col1 blob, index(col1(767)))
1503
character set = latin1 engine = innodb;
1505
# multi-column indexes are allowed to be longer
1506
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1507
character set = latin1 engine = innodb;
1509
show create table t9;
1511
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1513
# these should have their index length trimmed
1514
create table t1 (col1 varchar(768), index(col1))
1515
character set = latin1 engine = innodb;
1516
create table t2 (col1 varbinary(768), index(col1))
1517
character set = latin1 engine = innodb;
1518
create table t3 (col1 text, index(col1(768)))
1519
character set = latin1 engine = innodb;
1520
create table t4 (col1 blob, index(col1(768)))
1521
character set = latin1 engine = innodb;
1523
show create table t1;
1525
drop table t1, t2, t3, t4;
1527
# these should be refused
1529
create table t1 (col1 varchar(768) primary key)
1530
character set = latin1 engine = innodb;
1532
create table t2 (col1 varbinary(768) primary key)
1533
character set = latin1 engine = innodb;
1535
create table t3 (col1 text, primary key(col1(768)))
1536
character set = latin1 engine = innodb;
1538
create table t4 (col1 blob, primary key(col1(768)))
1539
character set = latin1 engine = innodb;
1542
# Test improved foreign key error messages (bug #3443)
1553
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1557
INSERT INTO t2 VALUES(2);
1559
INSERT INTO t1 VALUES(1);
1560
INSERT INTO t2 VALUES(1);
1563
DELETE FROM t1 WHERE id = 1;
1568
SET FOREIGN_KEY_CHECKS=0;
1570
SET FOREIGN_KEY_CHECKS=1;
1573
INSERT INTO t2 VALUES(3);
1577
# Test that checksum table uses a consistent read Bug #12669
1579
connect (a,localhost,root,,);
1580
connect (b,localhost,root,,);
1582
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1583
insert into t1 values (1),(2);
1587
insert into t1 values(3);
1590
# Here checksum should not see insert
1602
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1603
insert into t1 values (1),(2);
1608
insert into t1 values(3);
1611
# Here checksum sees insert
1620
# tests for bugs #9802 and #13778
1622
# test that FKs between invalid types are not accepted
1624
set foreign_key_checks=0;
1625
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1626
# Embedded server doesn't chdir to data directory
1627
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1628
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1629
set foreign_key_checks=1;
1632
# test that FKs between different charsets are not accepted in CREATE even
1635
set foreign_key_checks=0;
1636
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1637
# Embedded server doesn't chdir to data directory
1638
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1639
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1640
set foreign_key_checks=1;
1643
# test that invalid datatype conversions with ALTER are not allowed
1645
set foreign_key_checks=0;
1646
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1647
create table t1(a varchar(10) primary key) engine = innodb;
1648
alter table t1 modify column a int;
1649
set foreign_key_checks=1;
1652
# test that charset conversions with ALTER are allowed when f_k_c is 0
1654
set foreign_key_checks=0;
1655
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1656
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1657
alter table t1 convert to character set utf8;
1658
set foreign_key_checks=1;
1661
# test that RENAME does not allow invalid charsets when f_k_c is 0
1663
set foreign_key_checks=0;
1664
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1665
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1666
# Embedded server doesn't chdir to data directory
1667
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1668
rename table t3 to t1;
1669
set foreign_key_checks=1;
1672
# test that foreign key errors are reported correctly (Bug #15550)
1674
create table t1(a int primary key) row_format=redundant engine=innodb;
1675
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1676
create table t3(a int primary key) row_format=compact engine=innodb;
1677
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1679
insert into t1 values(1);
1680
insert into t3 values(1);
1681
insert into t2 values(2);
1682
insert into t4 values(2);
1683
insert into t2 values(1);
1684
insert into t4 values(1);
1696
drop table t4,t3,t2,t1;
1700
# Test that we can create a large (>1K) key
1702
create table t1 (a varchar(255) character set utf8,
1703
b varchar(255) character set utf8,
1704
c varchar(255) character set utf8,
1705
d varchar(255) character set utf8,
1706
key (a,b,c,d)) engine=innodb;
1708
--error ER_TOO_LONG_KEY
1709
create table t1 (a varchar(255) character set utf8,
1710
b varchar(255) character set utf8,
1711
c varchar(255) character set utf8,
1712
d varchar(255) character set utf8,
1713
e varchar(255) character set utf8,
1714
key (a,b,c,d,e)) engine=innodb;
1717
# test the padding of BINARY types and collations (Bug #14189)
1719
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1720
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1721
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1722
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1724
insert into t1 values (0x41),(0x4120),(0x4100);
1725
insert into t2 values (0x41),(0x4120),(0x4100);
1726
insert into t2 values (0x41),(0x4120);
1727
insert into t3 values (0x41),(0x4120),(0x4100);
1728
insert into t3 values (0x41),(0x4100);
1729
insert into t4 values (0x41),(0x4120),(0x4100);
1730
insert into t4 values (0x41),(0x4100);
1731
select hex(s1) from t1;
1732
select hex(s1) from t2;
1733
select hex(s1) from t3;
1734
select hex(s1) from t4;
1735
drop table t1,t2,t3,t4;
1737
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1738
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1740
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1741
insert into t2 values(0x42);
1742
insert into t2 values(0x41);
1743
select hex(s1) from t2;
1744
update t1 set s1=0x123456 where a=2;
1745
select hex(s1) from t2;
1746
update t1 set s1=0x12 where a=1;
1747
update t1 set s1=0x12345678 where a=1;
1748
update t1 set s1=0x123457 where a=1;
1749
update t1 set s1=0x1220 where a=1;
1750
select hex(s1) from t2;
1751
update t1 set s1=0x1200 where a=1;
1752
select hex(s1) from t2;
1753
update t1 set s1=0x4200 where a=1;
1754
select hex(s1) from t2;
1755
delete from t1 where a=1;
1756
delete from t1 where a=2;
1757
update t2 set s1=0x4120;
1759
delete from t1 where a!=3;
1760
select a,hex(s1) from t1;
1761
select hex(s1) from t2;
1765
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1766
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1768
insert into t1 values(1,0x4100),(2,0x41);
1769
insert into t2 values(0x41);
1770
select hex(s1) from t2;
1771
update t1 set s1=0x1234 where a=1;
1772
select hex(s1) from t2;
1773
update t1 set s1=0x12 where a=2;
1774
select hex(s1) from t2;
1775
delete from t1 where a=1;
1776
delete from t1 where a=2;
1777
select a,hex(s1) from t1;
1778
select hex(s1) from t2;
1781
# Ensure that <tablename>_ibfk_0 is not mistreated as a
1782
# generated foreign key identifier. (Bug #16387)
1784
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1785
CREATE TABLE t2(a INT) ENGINE=InnoDB;
1786
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1787
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1788
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1789
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1790
SHOW CREATE TABLE t2;
1794
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1797
connect (a,localhost,root,,);
1798
connect (b,localhost,root,,);
1800
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1801
insert into t1(a) values (1),(2),(3);
1805
update t1 set b = 5 where a = 2;
1808
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1812
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1813
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1814
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1815
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1816
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1826
# Another trigger test
1828
connect (a,localhost,root,,);
1829
connect (b,localhost,root,,);
1831
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1832
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1833
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1834
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1835
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1836
insert into t1(a) values (1),(2),(3);
1837
insert into t2(a) values (1),(2),(3);
1838
insert into t3(a) values (1),(2),(3);
1839
insert into t4(a) values (1),(2),(3);
1840
insert into t3(a) values (5),(7),(8);
1841
insert into t4(a) values (5),(7),(8);
1842
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1845
create trigger t1t before insert on t1 for each row begin
1846
INSERT INTO t2 SET a = NEW.a;
1849
create trigger t2t before insert on t2 for each row begin
1850
DELETE FROM t3 WHERE a = NEW.a;
1853
create trigger t3t before delete on t3 for each row begin
1854
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1857
create trigger t4t before update on t4 for each row begin
1858
UPDATE t5 SET b = b + 1 where a = NEW.a;
1863
update t1 set b = b + 5 where a = 1;
1864
update t2 set b = b + 5 where a = 1;
1865
update t3 set b = b + 5 where a = 1;
1866
update t4 set b = b + 5 where a = 1;
1867
insert into t5(a) values(20);
1870
insert into t1(a) values(7);
1871
insert into t2(a) values(8);
1872
delete from t2 where a = 3;
1873
update t4 set b = b + 1 where a = 3;
1879
drop table t1, t2, t3, t4, t5;
1885
# Test that cascading updates leading to duplicate keys give the correct
1886
# error message (bug #9680)
1890
field1 varchar(8) NOT NULL DEFAULT '',
1891
field2 varchar(8) NOT NULL DEFAULT '',
1892
PRIMARY KEY (field1, field2)
1896
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1897
FOREIGN KEY (field1) REFERENCES t1 (field1)
1898
ON DELETE CASCADE ON UPDATE CASCADE
1901
INSERT INTO t1 VALUES ('old', 'somevalu');
1902
INSERT INTO t1 VALUES ('other', 'anyvalue');
1904
INSERT INTO t2 VALUES ('old');
1905
INSERT INTO t2 VALUES ('other');
1907
--error ER_FOREIGN_DUPLICATE_KEY
1908
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1914
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1928
alter table t1 add constraint c2_fk foreign key (c2)
1929
references t2(c1) on delete cascade;
1930
show create table t1;
1932
alter table t1 drop foreign key c2_fk;
1933
show create table t1;
1938
# Bug #14360: problem with intervals
1941
create table t1(a date) engine=innodb;
1942
create table t2(a date, key(a)) engine=innodb;
1943
insert into t1 values('2005-10-01');
1944
insert into t2 values('2005-10-01');
1945
select * from t1, t2
1946
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1949
create table t1 (id int not null, f_id int not null, f int not null,
1950
primary key(f_id, id)) engine=innodb;
1951
create table t2 (id int not null,s_id int not null,s varchar(200),
1952
primary key(id)) engine=innodb;
1953
INSERT INTO t1 VALUES (8, 1, 3);
1954
INSERT INTO t1 VALUES (1, 2, 1);
1955
INSERT INTO t2 VALUES (1, 0, '');
1956
INSERT INTO t2 VALUES (8, 1, '');
1958
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
1959
WHERE mm.id IS NULL;
1960
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
1961
where mm.id is null lock in share mode;
1965
# Test case where X-locks on unused rows should be released in a
1966
# update (because READ COMMITTED isolation level)
1969
connect (a,localhost,root,,);
1970
connect (b,localhost,root,,);
1972
create table t1(a int not null, b int, primary key(a)) engine=innodb;
1973
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
1976
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1977
update t1 set b = 5 where b = 1;
1980
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1982
# X-lock to record (7,3) should be released in a update
1984
select * from t1 where a = 7 and b = 3 for update;
1995
# Test case where no locks should be released (because we are not
1996
# using READ COMMITTED isolation level)
1999
connect (a,localhost,root,,);
2000
connect (b,localhost,root,,);
2002
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2003
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2006
select * from t1 lock in share mode;
2007
update t1 set b = 5 where b = 1;
2011
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
2014
select * from t1 where a = 2 and b = 2 for update;
2016
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
2029
# Consistent read should be used in following selects
2031
# 1) INSERT INTO ... SELECT
2032
# 2) UPDATE ... = ( SELECT ...)
2033
# 3) CREATE ... SELECT
2035
connect (a,localhost,root,,);
2036
connect (b,localhost,root,,);
2038
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2039
insert into t1 values (1,2),(5,3),(4,2);
2040
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2041
insert into t2 values (8,6),(12,1),(3,1);
2044
select * from t2 for update;
2047
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2048
insert into t1 select * from t2;
2049
update t1 set b = (select e from t2 where a = d);
2050
create table t3(d int not null, e int, primary key(d)) engine=innodb
2058
drop table t1, t2, t3;
2061
# Consistent read should not be used if
2063
# (a) isolation level is serializable OR
2064
# (b) select ... lock in share mode OR
2065
# (c) select ... for update
2067
# in following queries:
2069
# 1) INSERT INTO ... SELECT
2070
# 2) UPDATE ... = ( SELECT ...)
2071
# 3) CREATE ... SELECT
2073
connect (a,localhost,root,,);
2074
connect (b,localhost,root,,);
2075
connect (c,localhost,root,,);
2076
connect (d,localhost,root,,);
2077
connect (e,localhost,root,,);
2078
connect (f,localhost,root,,);
2079
connect (g,localhost,root,,);
2080
connect (h,localhost,root,,);
2081
connect (i,localhost,root,,);
2082
connect (j,localhost,root,,);
2084
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2085
insert into t1 values (1,2),(5,3),(4,2);
2086
create table t2(a int not null, b int, primary key(a)) engine=innodb;
2087
insert into t2 values (8,6),(12,1),(3,1);
2088
create table t3(d int not null, b int, primary key(d)) engine=innodb;
2089
insert into t3 values (8,6),(12,1),(3,1);
2090
create table t5(a int not null, b int, primary key(a)) engine=innodb;
2091
insert into t5 values (1,2),(5,3),(4,2);
2092
create table t6(d int not null, e int, primary key(d)) engine=innodb;
2093
insert into t6 values (8,6),(12,1),(3,1);
2094
create table t8(a int not null, b int, primary key(a)) engine=innodb;
2095
insert into t8 values (1,2),(5,3),(4,2);
2096
create table t9(d int not null, e int, primary key(d)) engine=innodb;
2097
insert into t9 values (8,6),(12,1),(3,1);
2100
select * from t2 for update;
2103
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2105
insert into t1 select * from t2;
2108
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2110
update t3 set b = (select b from t2 where a = d);
2113
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2115
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2118
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2120
insert into t5 (select * from t2 lock in share mode);
2123
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2125
update t6 set e = (select b from t2 where a = d lock in share mode);
2128
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2130
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2133
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2135
insert into t8 (select * from t2 for update);
2138
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2140
update t9 set e = (select b from t2 where a = d for update);
2143
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2145
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2197
drop table t1, t2, t3, t5, t6, t8, t9;
2199
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2201
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2204
# Bug #17152: Wrong result with BINARY comparison on aliased column
2208
a BIGINT(20) NOT NULL,
2210
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2213
a BIGINT(20) NOT NULL,
2214
b VARCHAR(128) NOT NULL,
2217
KEY idx_t2_b_c (b,c(200)),
2218
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2220
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2222
INSERT INTO t1 VALUES (1);
2223
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2224
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2225
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2226
INSERT INTO t2 VALUES (1, 'customer_over', '1');
2228
SELECT * FROM t2 WHERE b = 'customer_over';
2229
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2230
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2231
/* Bang: Empty result set, above was expected: */
2232
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2233
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2238
# Test optimize on table with open transaction
2241
CREATE TABLE t1 ( a int ) ENGINE=innodb;
2243
INSERT INTO t1 VALUES (1);
2248
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2251
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2253
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2254
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2255
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2257
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2258
DELETE CASCADE ON UPDATE CASCADE;
2260
SHOW CREATE TABLE t2;
2264
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2265
# for which there is a foreign key constraint ON ... SET NULL.
2268
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2269
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2270
INSERT INTO t1 VALUES (1);
2271
INSERT INTO t2 VALUES (1);
2272
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2273
# mysqltest first does replace_regex, then replace_result
2274
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
2275
# Embedded server doesn't chdir to data directory
2276
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
2278
ALTER TABLE t2 MODIFY a INT NOT NULL;
2283
# Bug #26835: table corruption after delete+insert
2286
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2288
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2290
INSERT INTO t1 VALUES ('DDD');
2295
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2296
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2299
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2302
INSERT INTO t1 VALUES (0),(347),(0);
2305
SHOW CREATE TABLE t1;
2307
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2308
INSERT INTO t2 VALUES(42),(347),(348);
2309
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2310
SHOW CREATE TABLE t1;
2315
# Bug #21101 (Prints wrong error message if max row size is too large)
2319
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2320
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2321
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2322
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2323
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2324
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2325
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2326
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2330
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2332
DROP TABLE IF EXISTS t1;
2334
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2336
INSERT INTO t1 VALUES(-10);
2339
# NOTE: The server really needs to be restarted at this point
2340
# for the test to be useful.
2342
# Without the fix InnoDB would trip over an assertion here.
2343
INSERT INTO t1 VALUES(NULL);
2344
# The next value should be 1 and not -9 or a -ve number
2349
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
2352
CONNECT (c1,localhost,root,,);
2353
CONNECT (c2,localhost,root,,);
2355
SET TX_ISOLATION='read-committed';
2357
DROP TABLE IF EXISTS t1, t2;
2358
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2359
CREATE TABLE t2 LIKE t1;
2362
SET TX_ISOLATION='read-committed';
2364
INSERT INTO t1 VALUES (1);
2367
SELECT * FROM t1 WHERE a=1;
2370
CONNECT (c1,localhost,root,,);
2371
CONNECT (c2,localhost,root,,);
2373
SET TX_ISOLATION='read-committed';
2377
SET TX_ISOLATION='read-committed';
2379
INSERT INTO t1 VALUES (2);
2382
# The result set below should be the same for both selects
2383
SELECT * FROM t1 WHERE a=2;
2384
SELECT * FROM t1 WHERE a=2;
2392
# Bug #29157 UPDATE, changed rows incorrect
2394
create table t1 (i int, j int) engine=innodb;
2395
insert into t1 (i, j) values (1, 1), (2, 2);
2397
update t1 set j = 2;
2402
# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
2405
create table t1 (id int) comment='this is a comment' engine=innodb;
2406
select table_comment, data_free > 0 as data_free_is_set
2407
from information_schema.tables
2408
where table_schema='test' and table_name = 't1';
2416
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2417
c2 VARCHAR(128) NOT NULL,
2419
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
2422
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2423
c2 INT(10) UNSIGNED DEFAULT NULL,
2425
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
2427
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2428
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
2429
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2434
# Bug #29507 TRUNCATE shows to many rows effected
2437
CREATE TABLE t1 (c1 int default NULL,
2439
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2444
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2450
# Bug#35537 Innodb doesn't increment handler_update and handler_delete.
2453
CONNECT (c1,localhost,root,,);
2455
DROP TABLE IF EXISTS bug35537;
2456
CREATE TABLE bug35537 (
2460
INSERT INTO bug35537 VALUES (1);
2463
SHOW SESSION STATUS LIKE 'Handler_update%';
2464
SHOW SESSION STATUS LIKE 'Handler_delete%';
2466
UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
2467
DELETE FROM bug35537 WHERE c1 = 2;
2469
SHOW SESSION STATUS LIKE 'Handler_update%';
2470
SHOW SESSION STATUS LIKE 'Handler_delete%';
2472
DROP TABLE bug35537;
2477
#######################################################################
2479
# Please, DO NOT TOUCH this file as well as the innodb.result file. #
2480
# These files are to be modified ONLY BY INNOBASE guys. #
2482
# Use innodb_mysql.[test|result] files instead. #
2484
# If nevertheless you need to make some changes here, please, forward #
2485
# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com #
2486
# (otherwise your changes may be erased). #
2488
#######################################################################
added
removed
storage/innobase/mysql-test/innodb.test
