488
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;
489
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
490
LOCK TABLES t1 WRITE;
492
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
498
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;
499
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
500
LOCK TABLES t1 WRITE;
503
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
505
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
507
select id,id3 from t1;
486
512
# Test prefix key
488
514
create table t1 (a char(20), unique (a(5))) engine=innodb;
634
# Test of multi-table-delete
638
number bigint NOT NULL default '0',
639
cname char(15) NOT NULL default '',
640
carrier_id int NOT NULL default '0',
641
privacy int NOT NULL default '0',
642
last_mod_date timestamp NOT NULL,
643
last_mod_id int NOT NULL default '0',
644
last_app_date timestamp NULL,
645
last_app_id int default '-1',
646
version int NOT NULL default '0',
647
assigned_scps int default '0',
648
status int default '0'
650
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,NULL,-1,2,3,1);
651
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
652
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,NULL,-1,1,24,1);
653
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
654
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
655
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
657
number bigint NOT NULL default '0',
658
cname char(15) NOT NULL default '',
659
carrier_id int NOT NULL default '0',
660
privacy int NOT NULL default '0',
661
last_mod_date timestamp NOT NULL,
662
last_mod_id int NOT NULL default '0',
663
last_app_date timestamp NULL,
664
last_app_id int default '-1',
665
version int NOT NULL default '0',
666
assigned_scps int default '0',
667
status int default '0'
669
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,NULL,-1,2,3,1);
670
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
671
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,NULL,-1,1,24,1);
672
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
675
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);
608
682
# A simple test with some isolation levels
609
683
# TODO: Make this into a test using replication to really test how
658
744
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
659
745
insert into t1 values (10, 20);
660
746
insert into t2 values (10, 20);
747
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
661
748
drop table t1,t2;
751
# Test of multi-table-delete with foreign key constraints
754
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
755
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;
756
insert into t1 set id=1;
757
insert into t2 set id=1, t1_id=1;
758
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
762
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
763
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
764
INSERT INTO t1 VALUES(1);
765
INSERT INTO t2 VALUES(1, 1);
767
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
769
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
664
774
# Test of range_optimizer
817
# Test multi update with different join methods
820
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
821
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
822
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);
823
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
825
# Full join, without key
826
update t1,t2 set t1.a=t1.a+100;
830
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
834
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
838
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;
843
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
844
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
846
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
847
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
851
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
852
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
853
select distinct parent,child from t1 order by parent;
707
857
# Test that MySQL priorities clustered indexes
709
859
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
902
# Test of multi-table-updates (bug #1980).
905
create table t1 ( c char(8) not null ) engine=innodb;
906
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
907
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
909
alter table t1 add b char(8) not null;
910
alter table t1 add a char(8) not null;
911
alter table t1 add primary key (a,b,c);
912
update t1 set a=c, b=c;
914
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;
915
insert into t2 select * from t1;
917
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
752
921
# test autoincrement with TRUNCATE
770
939
drop table t2,t1;
942
# Test of multi updated and foreign keys
945
create table `t1` (`id` int not null ,primary key ( `id` )) engine = innodb;
946
insert into `t1`values ( 1 ) ;
947
create table `t2` (`id` int not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
948
insert into `t2`values ( 1 ) ;
949
create table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
950
insert into `t3`values ( 1 ) ;
952
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
954
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;
956
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
773
960
# test for recursion depth limit
830
1017
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
831
1018
drop table t1,t2;
833
create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=myisam;
834
create table t2 (a int, b varchar(200), c text not null) engine=innodb;
835
create table t3 (a int, b varchar(200), c text not null) engine=innodb;
1020
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1021
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1022
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
836
1023
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
837
1024
insert t2 select * from t1;
838
1025
insert t3 select * from t1;
839
--replace_column 1 # 6 # 7 # 8 # 9 # 10 #
1026
checksum table t1, t2, t3, t4 quick;
1027
checksum table t1, t2, t3, t4;
1028
checksum table t1, t2, t3, t4 extended;
840
1030
drop table t1,t2,t3;
923
1113
# Bug #6126: Duplicate columns in keys gives misleading error message
925
--error ER_DUP_FIELDNAME
926
1116
create table t1 (c char(10), index (c,c)) engine=innodb;
927
--error ER_DUP_FIELDNAME
928
1118
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
929
--error ER_DUP_FIELDNAME
930
1120
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
931
--error ER_DUP_FIELDNAME
932
1122
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
933
1123
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
934
--error ER_DUP_FIELDNAME
935
1125
alter table t1 add key (c1,c1);
936
--error ER_DUP_FIELDNAME
937
1127
alter table t1 add key (c2,c1,c1);
938
--error ER_DUP_FIELDNAME
939
1129
alter table t1 add key (c1,c2,c1);
940
--error ER_DUP_FIELDNAME
941
1131
alter table t1 add key (c1,c1,c2);
1027
1217
## Not deterministic.
1028
1218
# Test for testable InnoDB status variables. This test
1029
1219
# uses previous ones(pages_created, rows_deleted, ...).
1030
--replace_column 2 #
1031
show status like "Innodb_buffer_pool_pages_total";
1032
--replace_column 2 #
1033
show status like "Innodb_page_size";
1034
--replace_column 2 #
1035
show status like "Innodb_rows_deleted";
1036
--replace_column 2 #
1037
show status like "Innodb_rows_inserted";
1038
--replace_column 2 #
1039
show status like "Innodb_rows_updated";
1220
#show status like "Innodb_buffer_pool_pages_total";
1221
#show status like "Innodb_page_size";
1222
#show status like "Innodb_rows_deleted";
1223
#show status like "Innodb_rows_inserted";
1224
#show status like "Innodb_rows_updated";
1041
1226
## Test for row locks InnoDB status variables.
1042
--replace_column 2 #
1043
show status like "Innodb_row_lock_waits";
1044
--replace_column 2 #
1045
show status like "Innodb_row_lock_current_waits";
1046
--replace_column 2 #
1047
show status like "Innodb_row_lock_time";
1048
--replace_column 2 #
1049
show status like "Innodb_row_lock_time_max";
1050
--replace_column 2 #
1051
show status like "Innodb_row_lock_time_avg";
1227
#show status like "Innodb_row_lock_waits";
1228
#show status like "Innodb_row_lock_current_waits";
1229
#show status like "Innodb_row_lock_time";
1230
#show status like "Innodb_row_lock_time_max";
1231
#show status like "Innodb_row_lock_time_avg";
1053
1233
# Test for innodb_sync_spin_loops variable
1054
1234
show variables like "innodb_sync_spin_loops";
1278
1481
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1279
1482
) ENGINE=InnoDB;
1281
--error ER_NO_REFERENCED_ROW_2
1282
1485
INSERT INTO t2 VALUES(2);
1284
1487
INSERT INTO t1 VALUES(1);
1285
1488
INSERT INTO t2 VALUES(1);
1287
--error ER_ROW_IS_REFERENCED_2
1288
1491
DELETE FROM t1 WHERE id = 1;
1290
--error ER_ROW_IS_REFERENCED
1293
1496
SET FOREIGN_KEY_CHECKS=0;
1295
1498
SET FOREIGN_KEY_CHECKS=1;
1297
--error ER_NO_REFERENCED_ROW_2
1298
1501
INSERT INTO t2 VALUES(3);
1505
# Test that checksum table uses a consistent read Bug #12669
1507
connect (a,localhost,root,,);
1508
connect (b,localhost,root,,);
1510
create table t1(a int not null) engine=innodb;
1511
insert into t1 values (1),(2);
1515
insert into t1 values(3);
1518
# Here checksum should not see insert
1530
create table t1(a int not null) engine=innodb;
1531
insert into t1 values (1),(2);
1536
insert into t1 values(3);
1539
# Here checksum sees insert
1302
1548
# tests for bugs #9802 and #13778