~drizzle-trunk/drizzle/development

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

select * from t1;

678

select * from t2;

679

select * from t2;

680

drop table t1,t2;

681

682

609

683

# A simple test with some isolation levels

610

684

# TODO: Make this into a test using replication to really test how

611

685

# this works.

634

708

DROP TABLE t1;

635

709

636

710

711

# Test of multi-table-update

712

713

create table t1 (n int(10), d int(10)) engine=innodb;

714

create table t2 (n int(10), d int(10)) engine=innodb;

715

insert into t1 values(1,1),(1,2);

716

insert into t2 values(1,10),(2,20);

717

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

718

select * from t1;

719

select * from t2;

720

drop table t1,t2;

721

722

723

# Bug #29136 erred multi-delete on trans table does not rollback

724

725

726

# prepare

727

--disable_warnings

728

drop table if exists t1, t2;

729

--enable_warnings

730

CREATE TABLE t1 (a int, PRIMARY KEY (a));

731

CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;

732

create trigger trg_del_t2 after delete on t2 for each row

733

insert into t1 values (1);

734

insert into t1 values (1);

735

insert into t2 values (1),(2);

736

737

738

# exec cases A, B - see multi_update.test

739

740

# A. send_error() w/o send_eof() branch

741

742

--error ER_DUP_ENTRY

743

delete t2 from t2;

744

745

# check

746

747

select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;

748

749

# cleanup bug#29136

750

751

drop table t1, t2;

752

753

754

637

755

# Testing of IFNULL

638

756

639

757

create table t1 (a int, b int) engine=innodb;

651

769

# Test of read_through not existing const_table

652

770

653

771

654

create TEMPORARY table t1 (a varchar(10) not null) engine=myisam;

772

create table t1 (a varchar(10) not null) engine=myisam;

655

773

create table t2 (b varchar(10) not null unique) engine=innodb;

656

774

select t1.a from t1,t2 where t1.a=t2.b;

657

775

drop table t1,t2;

659

777

create table t2 (a int not null, b int, primary key (a)) engine = innodb;

660

778

insert into t1 values (10, 20);

661

779

insert into t2 values (10, 20);

780

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

662

781

drop table t1,t2;

663

782

664

783

784

# Test of multi-table-delete with foreign key constraints

785

786

787

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

788

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;

789

insert into t1 set id=1;

790

insert into t2 set id=1, t1_id=1;

791

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

792

select * from t1;

793

select * from t2;

794

drop table t2,t1;

795

CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

796

CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;

797

INSERT INTO t1 VALUES(1);

798

INSERT INTO t2 VALUES(1, 1);

799

SELECT * from t1;

800

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

801

SELECT * from t1;

802

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

803

SELECT * from t1;

804

DROP TABLE t1,t2;

805

806

665

807

# Test of range_optimizer

666

808

667

809

705

847

drop table t1;

706

848

707

849

850

# Test multi update with different join methods

851

852

853

CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;

854

CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;

855

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);

856

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

857

858

# Full join, without key

859

update t1,t2 set t1.a=t1.a+100;

860

select * from t1;

861

862

# unique key

863

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

864

select * from t1;

865

866

# ref key

867

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

868

select * from t1;

869

870

# Range key (in t1)

871

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;

872

select * from t1;

873

select * from t2;

874

875

drop table t1,t2;

876

CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

877

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;

878

SET AUTOCOMMIT=0;

879

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

880

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

881

ROLLBACK;

882

SELECT * FROM t1;

883

drop table t1,t2;

884

create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;

885

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

886

select distinct parent,child from t1 order by parent;

887

drop table t1;

888

889

708

890

# Test that MySQL priorities clustered indexes

709

891

710

892

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;

750

932

drop table t1;

751

933

752

934

935

# Test of multi-table-updates (bug #1980).

936

937

938

create table t1 ( c char(8) not null ) engine=innodb;

939

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

940

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

941

942

alter table t1 add b char(8) not null;

943

alter table t1 add a char(8) not null;

944

alter table t1 add primary key (a,b,c);

945

update t1 set a=c, b=c;

946

947

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;

948

insert into t2 select * from t1;

949

950

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

951

drop table t1,t2;

952

953

753

954

# test autoincrement with TRUNCATE

754

955

755

956

771

972

drop table t2,t1;

772

973

773

974

975

# Test of multi updated and foreign keys

976

977

978

create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;

979

insert into `t1`values ( 1 ) ;

980

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;

981

insert into `t2`values ( 1 ) ;

982

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;

983

insert into `t3`values ( 1 ) ;

984

--error 1451

985

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

986

--error 1451

987

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;

988

--error 1054

989

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

990

drop table t3,t2,t1;

991

992

774

993

# test for recursion depth limit

775

994

776

995

create table t1(

791

1010

# Test timestamps

792

1011

793

1012

794

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

795

CREATE TABLE t2 (col1 int, stamp TIMESTAMP,INDEX stamp_idx (stamp)) ENGINE=InnoDB;

1013

CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;

1014

CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

1015

(stamp))ENGINE=InnoDB;

796

1016

insert into t1 values (1),(2),(3);

797

1017

# Note that timestamp 3 is wrong

798

insert into t2 values (1, 20020204110000),(2, 20020204110001),(4,20020204110002 ),(5,20020204110003);

1018

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

799

1019

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

800

1020

'20020204120000' GROUP BY col1;

801

1021

drop table t1,t2;

805

1025

806

1026

807

1027

CREATE TABLE t1 (

808

`id` int NOT NULL auto_increment,

809

`id_object` int default '0',

810

`id_version` int NOT NULL default '1',

1028

`id` int(10) NOT NULL auto_increment,

1029

`id_object` int(10) default '0',

1030

`id_version` int(10) NOT NULL default '1',

811

1031

`label` varchar(100) NOT NULL default '',

812

1032

`description` text,

813

1033

PRIMARY KEY (`id`),

818

1038

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);

819

1039

820

1040

CREATE TABLE t2 (

821

`id` int NOT NULL auto_increment,

822

`id_version` int NOT NULL default '1',

1041

`id` int(10) NOT NULL auto_increment,

1042

`id_version` int(10) NOT NULL default '1',

823

1043

PRIMARY KEY (`id`),

824

1044

KEY `id_version` (`id_version`)

825

1045

) ENGINE=InnoDB;

831

1051

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

832

1052

drop table t1,t2;

833

1053

834

create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=myisam;

835

create table t2 (a int, b varchar(200), c text not null) engine=innodb;

836

create table t3 (a int, b varchar(200), c text not null) engine=innodb;

1054

create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;

1055

create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;

1056

create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;

837

1057

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

838

1058

insert t2 select * from t1;

839

1059

insert t3 select * from t1;

840

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

1060

checksum table t1, t2, t3, t4 quick;

1061

checksum table t1, t2, t3, t4;

1062

checksum table t1, t2, t3, t4 extended;

1063

#show table status;

841

1064

drop table t1,t2,t3;

842

1065

843

1066

860

1083

# Test of automaticly created foreign keys

861

1084

862

1085

863

create table t1 (id int not null, id2 int not null, unique (id,id2)) engine=innodb;

864

create table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;

1086

create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;

1087

create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;

865

1088

show create table t1;

866

1089

show create table t2;

867

1090

create index id on t2 (id);

869

1092

create index id2 on t2 (id);

870

1093

show create table t2;

871

1094

drop index id2 on t2;

872

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1095

--error 1025,1025

873

1096

drop index id on t2;

874

1097

show create table t2;

875

1098

drop table t2;

876

1099

877

create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;

1100

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;

878

1101

show create table t2;

879

1102

create unique index id on t2 (id,id2);

880

1103

show create table t2;

881

1104

drop table t2;

882

1105

883

1106

# Check foreign key columns created in different order than key columns

884

create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;

885

show create table t2;

886

drop table t2;

887

888

create table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;

889

show create table t2;

890

drop table t2;

891

892

create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;

893

show create table t2;

894

drop table t2;

895

896

create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;

897

show create table t2;

898

drop table t2;

899

900

create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;

1107

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;

1108

show create table t2;

1109

drop table t2;

1110

1111

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;

1112

show create table t2;

1113

drop table t2;

1114

1115

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;

1116

show create table t2;

1117

drop table t2;

1118

1119

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;

1120

show create table t2;

1121

drop table t2;

1122

1123

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;

901

1124

show create table t2;

902

1125

alter table t2 add index id_test (id), add index id_test2 (id,id2);

903

1126

show create table t2;

908

1131

# Embedded server doesn't chdir to data directory

909

1132

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

910

1133

--error ER_WRONG_FK_DEF

911

create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;

1134

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;

912

1135

913

1136

# bug#3749

914

1137

923

1146

924

1147

# Bug #6126: Duplicate columns in keys gives misleading error message

925

1148

926

--error ER_DUP_FIELDNAME

1149

--error 1060

927

1150

create table t1 (c char(10), index (c,c)) engine=innodb;

928

--error ER_DUP_FIELDNAME

1151

--error 1060

929

1152

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;

930

--error ER_DUP_FIELDNAME

1153

--error 1060

931

1154

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;

932

--error ER_DUP_FIELDNAME

1155

--error 1060

933

1156

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;

934

1157

create table t1 (c1 char(10), c2 char(10)) engine=innodb;

935

--error ER_DUP_FIELDNAME

1158

--error 1060

936

1159

alter table t1 add key (c1,c1);

937

--error ER_DUP_FIELDNAME

1160

--error 1060

938

1161

alter table t1 add key (c2,c1,c1);

939

--error ER_DUP_FIELDNAME

1162

--error 1060

940

1163

alter table t1 add key (c1,c2,c1);

941

--error ER_DUP_FIELDNAME

1164

--error 1060

942

1165

alter table t1 add key (c1,c1,c2);

943

1166

drop table t1;

944

1167

946

1169

# Bug #4082: integer truncation

947

1170

948

1171

949

create table t1(a int, b int) engine=innodb;

1172

create table t1(a int(1) , b int(1)) engine=innodb;

950

1173

insert into t1 values ('1111', '3333');

951

1174

select distinct concat(a, b) from t1;

952

1175

drop table t1;

953

1176

954

1177

1178

# BUG#7709 test case - Boolean fulltext query against unsupported

1179

# engines does not fail

1180

1181

1182

CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;

1183

--error 1214

1184

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1185

DROP TABLE t1;

1186

1187

955

1188

# check null values #1

956

1189

957

1190

958

1191

--disable_warnings

959

CREATE TABLE t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB;

1192

CREATE TABLE t1 (a_id int(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

960

1193

INSERT INTO t1 VALUES (1),(2),(3);

961

CREATE TABLE t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

962

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB;

1194

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),

1195

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;

963

1196

--enable_warnings

964

1197

INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);

965

1198

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;

1011

1244

1012

1245

1013

1246

create table t1 (x bigint not null primary key) engine=innodb;

1014

insert into t1(x) values (0x0ffffffffffffff0),(0x0ffffffffffffff1);

1247

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1015

1248

select * from t1;

1016

1249

select count(*) from t1 where x>0;

1017

1250

select count(*) from t1 where x=0;

1021

1254

explain select count(*) from t1 where x > -16;

1022

1255

select count(*) from t1 where x > -16;

1023

1256

select * from t1 where x > -16;

1024

select count(*) from t1 where x = 1152921504606846961;

1257

select count(*) from t1 where x = 18446744073709551601;

1025

1258

drop table t1;

1026

1259

1027

1260

1028

## Not deterministic.

1029

1261

# Test for testable InnoDB status variables. This test

1030

1262

# uses previous ones(pages_created, rows_deleted, ...).

1031

--replace_column 2 #

1032

1263

show status like "Innodb_buffer_pool_pages_total";

1033

--replace_column 2 #

1034

1264

show status like "Innodb_page_size";

1035

--replace_column 2 #

1036

1265

show status like "Innodb_rows_deleted";

1037

--replace_column 2 #

1038

1266

show status like "Innodb_rows_inserted";

1039

--replace_column 2 #

1040

1267

show status like "Innodb_rows_updated";

1041

1268

1042

## Test for row locks InnoDB status variables.

1043

--replace_column 2 #

1269

# Test for row locks InnoDB status variables.

1044

1270

show status like "Innodb_row_lock_waits";

1045

--replace_column 2 #

1046

1271

show status like "Innodb_row_lock_current_waits";

1047

--replace_column 2 #

1048

1272

show status like "Innodb_row_lock_time";

1049

--replace_column 2 #

1050

1273

show status like "Innodb_row_lock_time_max";

1051

--replace_column 2 #

1052

1274

show status like "Innodb_row_lock_time_avg";

1053

1275

1054

1276

# Test for innodb_sync_spin_loops variable

1101

1323

1102

1324

# Embedded server doesn't chdir to data directory

1103

1325

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1104

create table t1 (v varchar(16383), key(v));

1326

create table t1 (v varchar(65530), key(v));

1105

1327

drop table t1;

1106

create table t1 (v varchar(16383));

1328

create table t1 (v varchar(65536));

1107

1329

show create table t1;

1108

1330

drop table t1;

1109

create table t1 (v varchar(16383));

1331

create table t1 (v varchar(65530) character set utf8);

1110

1332

show create table t1;

1111

1333

drop table t1;

1112

1334

1113

1335

eval set storage_engine=$default;

1114

1336

1115

1337

# InnoDB specific varchar tests

1116

create table t1 (v varchar(16383)) engine=innodb;

1338

create table t1 (v varchar(16384)) engine=innodb;

1117

1339

drop table t1;

1118

1340

1119

1341

1130

1352

# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error

1131

1353

1132

1354

1133

CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;

1355

CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;

1134

1356

insert into t1 (b) values (1);

1135

1357

replace into t1 (b) values (2), (1), (3);

1136

1358

select * from t1;

1152

1374

drop table t1;

1153

1375

1154

1376

1377

# Test that update does not change internal auto-increment value

1378

1379

1380

create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;

1381

insert into t1 (val) values (1);

1382

update t1 set a=2 where a=1;

1383

# We should get the following error because InnoDB does not update the counter

1384

--error ER_DUP_ENTRY

1385

insert into t1 (val) values (1);

1386

select * from t1;

1387

drop table t1;

1388

1155

1389

# Bug #10465

1156

1390

1157

1391

1164

1398

DROP TABLE t1;

1165

1399

1166

1400

1401

# Bug #12340 multitable delete deletes only one record

1402

1403

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;

1404

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;

1405

insert into t2 values ('aa','cc');

1406

insert into t1 values ('aa','bb'),('aa','cc');

1407

delete t1 from t1,t2 where f1=f3 and f4='cc';

1408

select * from t1;

1409

drop table t1,t2;

1410

1411

1167

1412

# Test that the slow TRUNCATE implementation resets autoincrement columns

1168

1413

# (bug #11946)

1169

1414

1197

1442

id INT PRIMARY KEY

1198

1443

) ENGINE=InnoDB;

1199

1444

1200

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1445

--error 1005,1005

1201

1446

CREATE TEMPORARY TABLE t2

1202

1447

(

1203

1448

id INT NOT NULL PRIMARY KEY,

1212

1457

1213

1458

# prefix index

1214

1459

create table t1 (col1 varchar(2000), index (col1(767)))

1215

engine = innodb;

1460

character set = latin1 engine = innodb;

1216

1461

1217

1462

# normal indexes

1218

1463

create table t2 (col1 char(255), index (col1))

1219

engine = innodb;

1464

character set = latin1 engine = innodb;

1465

create table t3 (col1 binary(255), index (col1))

1466

character set = latin1 engine = innodb;

1220

1467

create table t4 (col1 varchar(767), index (col1))

1221

engine = innodb;

1222

create table t5 (col1 varchar(190) primary key)

1223

engine = innodb;

1224

create table t6 (col1 varbinary(254) primary key)

1225

engine = innodb;

1468

character set = latin1 engine = innodb;

1469

create table t5 (col1 varchar(767) primary key)

1470

character set = latin1 engine = innodb;

1471

create table t6 (col1 varbinary(767) primary key)

1472

character set = latin1 engine = innodb;

1226

1473

create table t7 (col1 text, index(col1(767)))

1227

engine = innodb;

1474

character set = latin1 engine = innodb;

1228

1475

create table t8 (col1 blob, index(col1(767)))

1229

engine = innodb;

1476

character set = latin1 engine = innodb;

1230

1477

1231

1478

# multi-column indexes are allowed to be longer

1232

1479

create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))

1233

engine = innodb;

1480

character set = latin1 engine = innodb;

1234

1481

1235

1482

show create table t9;

1236

1483

1237

drop table t1, t2, t4, t5, t6, t7, t8, t9;

1484

drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;

1238

1485

1239

1486

# these should have their index length trimmed

1240

1487

create table t1 (col1 varchar(768), index(col1))

1241

engine = innodb;

1488

character set = latin1 engine = innodb;

1242

1489

create table t2 (col1 varbinary(768), index(col1))

1243

engine = innodb;

1490

character set = latin1 engine = innodb;

1244

1491

create table t3 (col1 text, index(col1(768)))

1245

engine = innodb;

1492

character set = latin1 engine = innodb;

1246

1493

create table t4 (col1 blob, index(col1(768)))

1247

engine = innodb;

1494

character set = latin1 engine = innodb;

1248

1495

1249

1496

show create table t1;

1250

1497

1251

1498

drop table t1, t2, t3, t4;

1252

1499

1253

1500

# these should be refused

1254

--error ER_TOO_LONG_KEY

1501

--error 1071

1255

1502

create table t1 (col1 varchar(768) primary key)

1256

engine = innodb;

1257

--error ER_TOO_LONG_KEY

1258

create table t2 (col1 varbinary(1024) primary key)

1259

engine = innodb;

1260

--error ER_TOO_LONG_KEY

1503

character set = latin1 engine = innodb;

1504

--error 1071

1505

create table t2 (col1 varbinary(768) primary key)

1506

character set = latin1 engine = innodb;

1507

--error 1071

1261

1508

create table t3 (col1 text, primary key(col1(768)))

1262

engine = innodb;

1263

--error ER_TOO_LONG_KEY

1264

create table t4 (col1 blob, primary key(col1(1024)))

1265

engine = innodb;

1509

character set = latin1 engine = innodb;

1510

--error 1071

1511

create table t4 (col1 blob, primary key(col1(768)))

1512

character set = latin1 engine = innodb;

1266

1513

1267

1514

1268

1515

# Test improved foreign key error messages (bug #3443)

1279

1526

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1280

1527

) ENGINE=InnoDB;

1281

1528

1282

--error ER_NO_REFERENCED_ROW_2

1529

--error 1452

1283

1530

INSERT INTO t2 VALUES(2);

1284

1531

1285

1532

INSERT INTO t1 VALUES(1);

1286

1533

INSERT INTO t2 VALUES(1);

1287

1534

1288

--error ER_ROW_IS_REFERENCED_2

1535

--error 1451

1289

1536

DELETE FROM t1 WHERE id = 1;

1290

1537

1291

--error ER_ROW_IS_REFERENCED

1538

--error 1217

1292

1539

DROP TABLE t1;

1293

1540

1294

1541

SET FOREIGN_KEY_CHECKS=0;

1295

1542

DROP TABLE t1;

1296

1543

SET FOREIGN_KEY_CHECKS=1;

1297

1544

1298

--error ER_NO_REFERENCED_ROW_2

1545

--error 1452

1299

1546

INSERT INTO t2 VALUES(3);

1300

1547

1301

1548

DROP TABLE t2;

1549

1550

# Test that checksum table uses a consistent read Bug #12669

1551

1552

connect (a,localhost,root,,);

1553

connect (b,localhost,root,,);

1554

connection a;

1555

create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;

1556

insert into t1 values (1),(2);

1557

set autocommit=0;

1558

checksum table t1;

1559

connection b;

1560

insert into t1 values(3);

1561

connection a;

1562

1563

# Here checksum should not see insert

1564

1565

checksum table t1;

1566

connection a;

1567

commit;

1568

checksum table t1;

1569

commit;

1570

drop table t1;

1571

1572

# autocommit = 1

1573

1574

connection a;

1575

create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;

1576

insert into t1 values (1),(2);

1577

set autocommit=1;

1578

checksum table t1;

1579

connection b;

1580

set autocommit=1;

1581

insert into t1 values(3);

1582

connection a;

1583

1584

# Here checksum sees insert

1585

1586

checksum table t1;

1587

drop table t1;

1588

1589

connection default;

1590

disconnect a;

1591

disconnect b;

1302

1592

1303

1593

# tests for bugs #9802 and #13778

1304

1594

1313

1603

set foreign_key_checks=1;

1314

1604

drop table t2;

1315

1605

1606

# test that FKs between different charsets are not accepted in CREATE even

1607

# when f_k_c is 0

1608

1609

set foreign_key_checks=0;

1610

create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;

1611

# Embedded server doesn't chdir to data directory

1612

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1613

-- error 1005

1614

create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;

1615

set foreign_key_checks=1;

1616

drop table t1;

1617

1316

1618

# test that invalid datatype conversions with ALTER are not allowed

1317

1619

1318

1620

set foreign_key_checks=0;

1323

1625

set foreign_key_checks=1;

1324

1626

drop table t2,t1;

1325

1627

1628

# test that charset conversions with ALTER are allowed when f_k_c is 0

1629

1630

set foreign_key_checks=0;

1631

create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;

1632

create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;

1633

alter table t1 convert to character set utf8;

1634

set foreign_key_checks=1;

1635

drop table t2,t1;

1636

1637

# test that RENAME does not allow invalid charsets when f_k_c is 0

1638

1639

set foreign_key_checks=0;

1640

create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;

1641

create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;

1642

# Embedded server doesn't chdir to data directory

1643

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1644

-- error 1025

1645

rename table t3 to t1;

1646

set foreign_key_checks=1;

1647

drop table t2,t3;

1648

1326

1649

# test that foreign key errors are reported correctly (Bug #15550)

1327

1650

1328

1651

create table t1(a int primary key) row_format=redundant engine=innodb;

1361

1684

1362

1685

# Test that we can create a large (>1K) key

1363

1686

1364

create table t1 (a varchar(255),

1365

b varchar(255),

1366

c varchar(255),

1367

d varchar(255),

1368

key (a(200),b(200),c(200),d(200))) engine=innodb;

1687

create table t1 (a varchar(255) character set utf8,

1688

b varchar(255) character set utf8,

1689

c varchar(255) character set utf8,

1690

d varchar(255) character set utf8,

1691

key (a,b,c,d)) engine=innodb;

1369

1692

drop table t1;

1370

1693

--error ER_TOO_LONG_KEY

1371

create table t1 (a varchar(255),

1372

b varchar(255),

1373

c varchar(255),

1374

d varchar(255),

1375

e varchar(255),

1694

create table t1 (a varchar(255) character set utf8,

1695

b varchar(255) character set utf8,

1696

c varchar(255) character set utf8,

1697

d varchar(255) character set utf8,

1698

e varchar(255) character set utf8,

1376

1699

key (a,b,c,d,e)) engine=innodb;

1377

1700

1378

1701

1379

1702

# test the padding of BINARY types and collations (Bug #14189)

1380

1703

1381

1704

create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;

1382

create table t3 (s1 varchar(2) ,primary key (s1)) engine=innodb;

1383

create table t4 (s1 char(2) ,primary key (s1)) engine=innodb;

1705

create table t2 (s1 binary(2),primary key (s1)) engine=innodb;

1706

create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;

1707

create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;

1384

1708

1385

1709

insert into t1 values (0x41),(0x4120),(0x4100);

1386

1710

-- error ER_DUP_ENTRY

1711

insert into t2 values (0x41),(0x4120),(0x4100);

1712

insert into t2 values (0x41),(0x4120);

1713

-- error ER_DUP_ENTRY

1387

1714

insert into t3 values (0x41),(0x4120),(0x4100);

1388

1715

insert into t3 values (0x41),(0x4100);

1389

1716

-- error ER_DUP_ENTRY

1390

1717

insert into t4 values (0x41),(0x4120),(0x4100);

1391

1718

insert into t4 values (0x41),(0x4100);

1392

1719

select hex(s1) from t1;

1720

select hex(s1) from t2;

1393

1721

select hex(s1) from t3;

1394

1722

select hex(s1) from t4;

1395

drop table t1,t3,t4;

1723

drop table t1,t2,t3,t4;

1396

1724

1397

1725

create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;

1398

create table t2 (s1 varbinary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1726

create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1399

1727

1400

1728

insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);

1729

-- error 1452

1401

1730

insert into t2 values(0x42);

1402

1731

insert into t2 values(0x41);

1403

1732

select hex(s1) from t2;

1404

1733

update t1 set s1=0x123456 where a=2;

1405

1734

select hex(s1) from t2;

1735

-- error 1451

1406

1736

update t1 set s1=0x12 where a=1;

1737

-- error 1451

1407

1738

update t1 set s1=0x12345678 where a=1;

1739

-- error 1451

1408

1740

update t1 set s1=0x123457 where a=1;

1409

1741

update t1 set s1=0x1220 where a=1;

1410

1742

select hex(s1) from t2;

1414

1744

select hex(s1) from t2;

1415

1745

update t1 set s1=0x4200 where a=1;

1416

1746

select hex(s1) from t2;

1747

-- error 1451

1417

1748

delete from t1 where a=1;

1749

delete from t1 where a=2;

1418

1750

update t2 set s1=0x4120;

1419

1751

-- error 1451

1420

1752

delete from t1;

1424

1756

1425

1757

drop table t2,t1;

1426

1758

1427

create table t1 (a int primary key,s1 varchar(2) not null unique) engine=innodb;

1428

create table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1759

create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;

1760

create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1429

1761

1430

1762

insert into t1 values(1,0x4100),(2,0x41);

1431

1763

insert into t2 values(0x41);

1454

1786

DROP TABLE t2,t1;

1455

1787

1456

1788

1789

# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing

1790

1791

1792

connect (a,localhost,root,,);

1793

connect (b,localhost,root,,);

1794

connection a;

1795

create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1796

insert into t1(a) values (1),(2),(3);

1797

commit;

1798

connection b;

1799

set autocommit = 0;

1800

update t1 set b = 5 where a = 2;

1801

connection a;

1802

delimiter |;

1803

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

1804

delimiter ;|

1805

set autocommit = 0;

1806

connection a;

1807

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

1808

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

1809

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

1810

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

1811

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

1812

connection b;

1813

commit;

1814

connection a;

1815

commit;

1816

drop trigger t1t;

1817

drop table t1;

1818

disconnect a;

1819

disconnect b;

1820

1821

# Another trigger test

1822

1823

connect (a,localhost,root,,);

1824

connect (b,localhost,root,,);

1825

connection a;

1826

create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1827

create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1828

create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1829

create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1830

create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;

1831

insert into t1(a) values (1),(2),(3);

1832

insert into t2(a) values (1),(2),(3);

1833

insert into t3(a) values (1),(2),(3);

1834

insert into t4(a) values (1),(2),(3);

1835

insert into t3(a) values (5),(7),(8);

1836

insert into t4(a) values (5),(7),(8);

1837

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

1838

1839

delimiter |;

1840

create trigger t1t before insert on t1 for each row begin

1841

INSERT INTO t2 SET a = NEW.a;

1842

end |

1843

1844

create trigger t2t before insert on t2 for each row begin

1845

DELETE FROM t3 WHERE a = NEW.a;

1846

end |

1847

1848

create trigger t3t before delete on t3 for each row begin

1849

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

1850

end |

1851

1852

create trigger t4t before update on t4 for each row begin

1853

UPDATE t5 SET b = b + 1 where a = NEW.a;

1854

end |

1855

delimiter ;|

1856

commit;

1857

set autocommit = 0;

1858

update t1 set b = b + 5 where a = 1;

1859

update t2 set b = b + 5 where a = 1;

1860

update t3 set b = b + 5 where a = 1;

1861

update t4 set b = b + 5 where a = 1;

1862

insert into t5(a) values(20);

1863

connection b;

1864

set autocommit = 0;

1865

insert into t1(a) values(7);

1866

insert into t2(a) values(8);

1867

delete from t2 where a = 3;

1868

update t4 set b = b + 1 where a = 3;

1869

commit;

1870

drop trigger t1t;

1871

drop trigger t2t;

1872

drop trigger t3t;

1873

drop trigger t4t;

1874

drop table t1, t2, t3, t4, t5;

1875

connection default;

1876

disconnect a;

1877

disconnect b;

1878

1879

1457

1880

# Test that cascading updates leading to duplicate keys give the correct

1458

1881

# error message (bug #9680)

1459

1882

1527

1950

INSERT INTO t2 VALUES (1, 0, '');

1528

1951

INSERT INTO t2 VALUES (8, 1, '');

1529

1952

commit;

1953

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

1954

WHERE mm.id IS NULL;

1530

1955

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1531

1956

where mm.id is null lock in share mode;

1532

1957

drop table t1,t2;

1580

2005

1581

2006

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

1582

2007

1583

--error ER_LOCK_WAIT_TIMEOUT

2008

--error 1205

1584

2009

select * from t1 where a = 2 and b = 2 for update;

1585

2010

1586

2011

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

1587

2012

1588

--error ER_LOCK_WAIT_TIMEOUT

2013

--error 1205

1589

2014

connection a;

1590

2015

commit;

1591

2016

connection b;

1715

2140

create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;

1716

2141

1717

2142

connection b;

1718

--error ER_LOCK_WAIT_TIMEOUT

2143

--error 1205

1719

2144

reap;

1720

2145

1721

2146

connection c;

1722

--error ER_LOCK_WAIT_TIMEOUT

2147

--error 1205

1723

2148

reap;

1724

2149

1725

2150

connection d;

1726

--error ER_LOCK_WAIT_TIMEOUT

2151

--error 1205

1727

2152

reap;

1728

2153

1729

2154

connection e;

1730

--error ER_LOCK_WAIT_TIMEOUT

2155

--error 1205

1731

2156

reap;

1732

2157

1733

2158

connection f;

1734

--error ER_LOCK_WAIT_TIMEOUT

2159

--error 1205

1735

2160

reap;

1736

2161

1737

2162

connection g;

1738

--error ER_LOCK_WAIT_TIMEOUT

2163

--error 1205

1739

2164

reap;

1740

2165

1741

2166

connection h;

1742

--error ER_LOCK_WAIT_TIMEOUT

2167

--error 1205

1743

2168

reap;

1744

2169

1745

2170

connection i;

1746

--error ER_LOCK_WAIT_TIMEOUT

2171

--error 1205

1747

2172

reap;

1748

2173

1749

2174

connection j;

1750

--error ER_LOCK_WAIT_TIMEOUT

2175

--error 1205

1751

2176

reap;

1752

2177

1753

2178

connection a;

1767

2192

drop table t1, t2, t3, t5, t6, t8, t9;

1768

2193

1769

2194

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

1770

--error ER_CANT_CREATE_TABLE

2195

--error 1005

1771

2196

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

1772

2197

1773

2198

1775

2200

1776

2201

1777

2202

CREATE TABLE t1 (

1778

a BIGINT NOT NULL,

2203

a BIGINT(20) NOT NULL,

1779

2204

PRIMARY KEY (a)

1780

) ENGINE=INNODB;

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1781

2206

1782

2207

CREATE TABLE t2 (

1783

a BIGINT NOT NULL,

2208

a BIGINT(20) NOT NULL,

1784

2209

b VARCHAR(128) NOT NULL,

1785

2210

c TEXT NOT NULL,

1786

2211

PRIMARY KEY (a,b),

1787

2212

KEY idx_t2_b_c (b,c(200)),

1788

2213

CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)

1789

2214

ON DELETE CASCADE

1790

) ENGINE=INNODB;

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1791

2216

1792

2217

INSERT INTO t1 VALUES (1);

1793

2218

INSERT INTO t2 VALUES (1, 'bar', 'vbar');

1811

2236

CREATE TABLE t1 ( a int ) ENGINE=innodb;

1812

2237

BEGIN;

1813

2238

INSERT INTO t1 VALUES (1);

1814

ALTER TABLE t1 ENGINE=innodb;

2239

OPTIMIZE TABLE t1;

1815

2240

DROP TABLE t1;

1816

2241

1817

2242

1844

2269

--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/

1845

2270

# Embedded server doesn't chdir to data directory

1846

2271

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1847

--error ER_ERROR_ON_RENAME

2272

--error 1025

1848

2273

ALTER TABLE t2 MODIFY a INT NOT NULL;

1849

2274

DELETE FROM t1;

1850

2275

DROP TABLE t2,t1;

1869

2294

CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB

1870

2295

AUTO_INCREMENT=42;

1871

2296

1872

INSERT INTO t1 VALUES (NULL),(347),(NULL);

2297

INSERT INTO t1 VALUES (0),(347),(0);

1873

2298

SELECT * FROM t1;

1874

2299

1875

2300

SHOW CREATE TABLE t1;

1882

2307

DROP TABLE t1,t2;

1883

2308

1884

2309

2310

# Bug #21101 (Prints wrong error message if max row size is too large)

2311

2312

--error 1118

2313

CREATE TABLE t1 (

2314

c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),

2315

c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),

2316

c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),

2317

c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),

2318

c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),

2319

c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),

2320

c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),

2321

c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)

2322

) ENGINE = InnoDB;

2323

2324

1885

2325

# Bug #31860 InnoDB assumes AUTOINC values can only be positive.

1886

2326

1887

2327

DROP TABLE IF EXISTS t1;

1888

2328

CREATE TABLE t1(

1889

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

1890

2330

) ENGINE=InnoDB;

1891

2331

INSERT INTO t1 VALUES(-10);

1892

2332

SELECT * FROM t1;

1900

2340

SELECT * FROM t1;

1901

2341

DROP TABLE t1;

1902

2342

1903

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1904

2343

2344

# Bug #21409 Incorrect result returned when in READ-COMMITTED with

2345

# query_cache ON

2346

2347

CONNECT (c1,localhost,root,,);

2348

CONNECT (c2,localhost,root,,);

2349

CONNECTION c1;

2350

SET TX_ISOLATION='read-committed';

2351

SET AUTOCOMMIT=0;

2352

DROP TABLE IF EXISTS t1, t2;

2353

CREATE TABLE t1 ( a int ) ENGINE=InnoDB;

2354

CREATE TABLE t2 LIKE t1;

2355

SELECT * FROM t2;

2356

CONNECTION c2;

2357

SET TX_ISOLATION='read-committed';

2358

SET AUTOCOMMIT=0;

2359

INSERT INTO t1 VALUES (1);

2360

COMMIT;

2361

CONNECTION c1;

2362

SELECT * FROM t1 WHERE a=1;

2363

DISCONNECT c1;

2364

DISCONNECT c2;

2365

CONNECT (c1,localhost,root,,);

2366

CONNECT (c2,localhost,root,,);

2367

CONNECTION c1;

2368

SET TX_ISOLATION='read-committed';

2369

SET AUTOCOMMIT=0;

2370

SELECT * FROM t2;

2371

CONNECTION c2;

2372

SET TX_ISOLATION='read-committed';

2373

SET AUTOCOMMIT=0;

2374

INSERT INTO t1 VALUES (2);

2375

COMMIT;

2376

CONNECTION c1;

2377

# The result set below should be the same for both selects

2378

SELECT * FROM t1 WHERE a=2;

2379

SELECT * FROM t1 WHERE a=2;

2380

DROP TABLE t1;

2381

DROP TABLE t2;

2382

DISCONNECT c1;

2383

DISCONNECT c2;

1905

2384

1906

2385

#######################################################################

1907

2386

# #

Older »