~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

611

683

# A simple test with some isolation levels

612

684

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

613

685

# this works.

636

708

DROP TABLE t1;

637

709

638

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

639

755

# Testing of IFNULL

640

756

641

757

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

653

769

# Test of read_through not existing const_table

654

770

655

771

656

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

772

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

657

773

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

658

774

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

659

775

drop table t1,t2;

661

777

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

662

778

insert into t1 values (10, 20);

663

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;

664

781

drop table t1,t2;

665

782

666

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

667

807

# Test of range_optimizer

668

808

669

809

707

847

drop table t1;

708

848

709

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

710

890

# Test that MySQL priorities clustered indexes

711

891

712

892

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

752

932

drop table t1;

753

933

754

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

755

954

# test autoincrement with TRUNCATE

756

955

757

956

773

972

drop table t2,t1;

774

973

775

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

776

993

# test for recursion depth limit

777

994

778

995

create table t1(

782

999

foreign key(pid) references t1(id) on delete cascade) engine=innodb;

783

1000

insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),

784

1001

(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);

785

--error ER_ROW_IS_REFERENCED_2

1002

-- error 1451

786

1003

delete from t1 where id=0;

787

1004

delete from t1 where id=15;

788

1005

delete from t1 where id=0;

793

1010

# Test timestamps

794

1011

795

1012

796

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

797

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;

798

1016

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

799

1017

# Note that timestamp 3 is wrong

800

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

801

1019

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

802

1020

'20020204120000' GROUP BY col1;

803

1021

drop table t1,t2;

807

1025

808

1026

809

1027

CREATE TABLE t1 (

810

`id` int NOT NULL auto_increment,

811

`id_object` int default '0',

812

`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',

813

1031

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

814

1032

`description` text,

815

1033

PRIMARY KEY (`id`),

820

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

821

1039

822

1040

CREATE TABLE t2 (

823

`id` int NOT NULL auto_increment,

824

`id_version` int NOT NULL default '1',

1041

`id` int(10) NOT NULL auto_increment,

1042

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

825

1043

PRIMARY KEY (`id`),

826

1044

KEY `id_version` (`id_version`)

827

1045

) ENGINE=InnoDB;

833

1051

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

834

1052

drop table t1,t2;

835

1053

836

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

837

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

838

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;

839

1057

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

840

1058

insert t2 select * from t1;

841

1059

insert t3 select * from t1;

842

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

843

1064

drop table t1,t2,t3;

844

1065

845

1066

862

1083

# Test of automaticly created foreign keys

863

1084

864

1085

865

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

866

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;

867

1088

show create table t1;

868

1089

show create table t2;

869

1090

create index id on t2 (id);

871

1092

create index id2 on t2 (id);

872

1093

show create table t2;

873

1094

drop index id2 on t2;

874

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1095

--error 1025,1025

875

1096

drop index id on t2;

876

1097

show create table t2;

877

1098

drop table t2;

878

1099

879

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;

880

1101

show create table t2;

881

1102

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

882

1103

show create table t2;

883

1104

drop table t2;

884

1105

885

1106

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

886

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;

887

show create table t2;

888

drop table t2;

889

890

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;

891

show create table t2;

892

drop table t2;

893

894

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;

895

show create table t2;

896

drop table t2;

897

898

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;

899

show create table t2;

900

drop table t2;

901

902

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;

903

1124

show create table t2;

904

1125

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

905

1126

show create table t2;

908

1129

# Test error handling

909

1130

910

1131

# Embedded server doesn't chdir to data directory

911

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

1132

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

912

1133

--error ER_WRONG_FK_DEF

913

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;

914

1135

915

1136

# bug#3749

916

1137

925

1146

926

1147

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

927

1148

928

--error ER_DUP_FIELDNAME

1149

--error 1060

929

1150

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

930

--error ER_DUP_FIELDNAME

1151

--error 1060

931

1152

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

932

--error ER_DUP_FIELDNAME

1153

--error 1060

933

1154

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

934

--error ER_DUP_FIELDNAME

1155

--error 1060

935

1156

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

936

1157

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

937

--error ER_DUP_FIELDNAME

1158

--error 1060

938

1159

alter table t1 add key (c1,c1);

939

--error ER_DUP_FIELDNAME

1160

--error 1060

940

1161

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

941

--error ER_DUP_FIELDNAME

1162

--error 1060

942

1163

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

943

--error ER_DUP_FIELDNAME

1164

--error 1060

944

1165

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

945

1166

drop table t1;

946

1167

948

1169

# Bug #4082: integer truncation

949

1170

950

1171

951

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

1172

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

952

1173

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

953

1174

select distinct concat(a, b) from t1;

954

1175

drop table t1;

955

1176

956

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

957

1188

# check null values #1

958

1189

959

1190

960

1191

--disable_warnings

961

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;

962

1193

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

963

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

964

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;

965

1196

--enable_warnings

966

1197

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

967

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;

1013

1244

1014

1245

1015

1246

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

1016

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

1247

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

1017

1248

select * from t1;

1018

1249

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

1019

1250

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

1023

1254

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

1024

1255

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

1025

1256

select * from t1 where x > -16;

1026

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

1257

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

1027

1258

drop table t1;

1028

1259

1029

1260

1030

## Not deterministic.

1031

1261

# Test for testable InnoDB status variables. This test

1032

1262

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

1033

--replace_column 2 #

1034

1263

show status like "Innodb_buffer_pool_pages_total";

1035

--replace_column 2 #

1036

1264

show status like "Innodb_page_size";

1037

--replace_column 2 #

1038

1265

show status like "Innodb_rows_deleted";

1039

--replace_column 2 #

1040

1266

show status like "Innodb_rows_inserted";

1041

--replace_column 2 #

1042

1267

show status like "Innodb_rows_updated";

1043

1268

1044

## Test for row locks InnoDB status variables.

1045

--replace_column 2 #

1269

# Test for row locks InnoDB status variables.

1046

1270

show status like "Innodb_row_lock_waits";

1047

--replace_column 2 #

1048

1271

show status like "Innodb_row_lock_current_waits";

1049

--replace_column 2 #

1050

1272

show status like "Innodb_row_lock_time";

1051

--replace_column 2 #

1052

1273

show status like "Innodb_row_lock_time_max";

1053

--replace_column 2 #

1054

1274

show status like "Innodb_row_lock_time_avg";

1055

1275

1056

1276

# Test for innodb_sync_spin_loops variable

1057

set @my_innodb_sync_spin_loops = @@global.innodb_sync_spin_loops;

1058

1277

show variables like "innodb_sync_spin_loops";

1059

1278

set global innodb_sync_spin_loops=1000;

1060

1279

show variables like "innodb_sync_spin_loops";

1062

1281

show variables like "innodb_sync_spin_loops";

1063

1282

set global innodb_sync_spin_loops=20;

1064

1283

show variables like "innodb_sync_spin_loops";

1065

set @@global.innodb_sync_spin_loops = @my_innodb_sync_spin_loops;

1066

1284

1067

1285

# Test for innodb_thread_concurrency variable

1068

set @my_innodb_thread_concurrency = @@global.innodb_thread_concurrency;

1069

1286

show variables like "innodb_thread_concurrency";

1070

1287

set global innodb_thread_concurrency=1001;

1071

1288

show variables like "innodb_thread_concurrency";

1073

1290

show variables like "innodb_thread_concurrency";

1074

1291

set global innodb_thread_concurrency=16;

1075

1292

show variables like "innodb_thread_concurrency";

1076

set @@global.innodb_thread_concurrency = @my_innodb_thread_concurrency;

1077

1293

1078

1294

# Test for innodb_concurrency_tickets variable

1079

1295

show variables like "innodb_concurrency_tickets";

1106

1322

1107

1323

1108

1324

# Embedded server doesn't chdir to data directory

1109

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

1110

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

1325

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

1326

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

1111

1327

drop table t1;

1112

create table t1 (v varchar(16383));

1328

create table t1 (v varchar(65536));

1113

1329

show create table t1;

1114

1330

drop table t1;

1115

create table t1 (v varchar(16383));

1331

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

1116

1332

show create table t1;

1117

1333

drop table t1;

1118

1334

1119

1335

eval set storage_engine=$default;

1120

1336

1121

1337

# InnoDB specific varchar tests

1122

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

1338

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

1123

1339

drop table t1;

1124

1340

1125

1341

1136

1352

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

1137

1353

1138

1354

1139

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;

1140

1356

insert into t1 (b) values (1);

1141

1357

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

1142

1358

select * from t1;

1158

1374

drop table t1;

1159

1375

1160

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

1161

1389

# Bug #10465

1162

1390

1163

1391

1170

1398

DROP TABLE t1;

1171

1399

1172

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

1173

1412

# Test that the slow TRUNCATE implementation resets autoincrement columns

1174

1413

# (bug #11946)

1175

1414

1203

1442

id INT PRIMARY KEY

1204

1443

) ENGINE=InnoDB;

1205

1444

1206

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1445

--error 1005,1005

1207

1446

CREATE TEMPORARY TABLE t2

1208

1447

(

1209

1448

id INT NOT NULL PRIMARY KEY,

1218

1457

1219

1458

# prefix index

1220

1459

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

1221

engine = innodb;

1460

character set = latin1 engine = innodb;

1222

1461

1223

1462

# normal indexes

1224

1463

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

1225

engine = innodb;

1464

character set = latin1 engine = innodb;

1465

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

1466

character set = latin1 engine = innodb;

1226

1467

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

1227

engine = innodb;

1228

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

1229

engine = innodb;

1230

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

1231

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;

1232

1473

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

1233

engine = innodb;

1474

character set = latin1 engine = innodb;

1234

1475

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

1235

engine = innodb;

1476

character set = latin1 engine = innodb;

1236

1477

1237

1478

# multi-column indexes are allowed to be longer

1238

1479

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

1239

engine = innodb;

1480

character set = latin1 engine = innodb;

1240

1481

1241

1482

show create table t9;

1242

1483

1243

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

1484

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

1244

1485

1245

1486

# these should have their index length trimmed

1246

1487

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

1247

engine = innodb;

1488

character set = latin1 engine = innodb;

1248

1489

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

1249

engine = innodb;

1490

character set = latin1 engine = innodb;

1250

1491

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

1251

engine = innodb;

1492

character set = latin1 engine = innodb;

1252

1493

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

1253

engine = innodb;

1494

character set = latin1 engine = innodb;

1254

1495

1255

1496

show create table t1;

1256

1497

1257

1498

drop table t1, t2, t3, t4;

1258

1499

1259

1500

# these should be refused

1260

--error ER_TOO_LONG_KEY

1501

--error 1071

1261

1502

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

1262

engine = innodb;

1263

--error ER_TOO_LONG_KEY

1264

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

1265

engine = innodb;

1266

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

1267

1508

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

1268

engine = innodb;

1269

--error ER_TOO_LONG_KEY

1270

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

1271

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;

1272

1513

1273

1514

1274

1515

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

1285

1526

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

1286

1527

) ENGINE=InnoDB;

1287

1528

1288

--error ER_NO_REFERENCED_ROW_2

1529

--error 1452

1289

1530

INSERT INTO t2 VALUES(2);

1290

1531

1291

1532

INSERT INTO t1 VALUES(1);

1292

1533

INSERT INTO t2 VALUES(1);

1293

1534

1294

--error ER_ROW_IS_REFERENCED_2

1535

--error 1451

1295

1536

DELETE FROM t1 WHERE id = 1;

1296

1537

1297

--error ER_ROW_IS_REFERENCED

1538

--error 1217

1298

1539

DROP TABLE t1;

1299

1540

1300

1541

SET FOREIGN_KEY_CHECKS=0;

1301

1542

DROP TABLE t1;

1302

1543

SET FOREIGN_KEY_CHECKS=1;

1303

1544

1304

--error ER_NO_REFERENCED_ROW_2

1545

--error 1452

1305

1546

INSERT INTO t2 VALUES(3);

1306

1547

1307

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;

1308

1592

1309

1593

# tests for bugs #9802 and #13778

1310

1594

1313

1597

set foreign_key_checks=0;

1314

1598

create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;

1315

1599

# Embedded server doesn't chdir to data directory

1316

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

1317

--error ER_CANT_CREATE_TABLE

1600

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

1601

-- error 1005

1318

1602

create table t1(a char(10) primary key, b varchar(20)) engine = innodb;

1319

1603

set foreign_key_checks=1;

1320

1604

drop table t2;

1321

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

1322

1618

# test that invalid datatype conversions with ALTER are not allowed

1323

1619

1324

1620

set foreign_key_checks=0;

1325

1621

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

1326

1622

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

1327

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1623

-- error 1025,1025

1328

1624

alter table t1 modify column a int;

1329

1625

set foreign_key_checks=1;

1330

1626

drop table t2,t1;

1331

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

1332

1649

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

1333

1650

1334

1651

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

1338

1655

1339

1656

insert into t1 values(1);

1340

1657

insert into t3 values(1);

1341

--error ER_NO_REFERENCED_ROW_2

1658

-- error 1452

1342

1659

insert into t2 values(2);

1343

--error ER_NO_REFERENCED_ROW_2

1660

-- error 1452

1344

1661

insert into t4 values(2);

1345

1662

insert into t2 values(1);

1346

1663

insert into t4 values(1);

1347

--error ER_ROW_IS_REFERENCED_2

1664

-- error 1451

1348

1665

update t1 set a=2;

1349

--error ER_NO_REFERENCED_ROW_2

1666

-- error 1452

1350

1667

update t2 set a=2;

1351

--error ER_ROW_IS_REFERENCED_2

1668

-- error 1451

1352

1669

update t3 set a=2;

1353

--error ER_NO_REFERENCED_ROW_2

1670

-- error 1452

1354

1671

update t4 set a=2;

1355

--error ER_ROW_IS_REFERENCED_2

1672

-- error 1451

1356

1673

truncate t1;

1357

--error ER_ROW_IS_REFERENCED_2

1674

-- error 1451

1358

1675

truncate t3;

1359

1676

truncate t2;

1360

1677

truncate t4;

1367

1684

1368

1685

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

1369

1686

1370

create table t1 (a varchar(255),

1371

b varchar(255),

1372

c varchar(255),

1373

d varchar(255),

1374

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;

1375

1692

drop table t1;

1376

1693

--error ER_TOO_LONG_KEY

1377

create table t1 (a varchar(255),

1378

b varchar(255),

1379

c varchar(255),

1380

d varchar(255),

1381

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,

1382

1699

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

1383

1700

1384

1701

1385

1702

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

1386

1703

1387

1704

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

1388

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

1389

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;

1390

1708

1391

1709

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

1392

--error ER_DUP_ENTRY

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

1393

1714

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

1394

1715

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

1395

--error ER_DUP_ENTRY

1716

-- error ER_DUP_ENTRY

1396

1717

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

1397

1718

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

1398

1719

select hex(s1) from t1;

1720

select hex(s1) from t2;

1399

1721

select hex(s1) from t3;

1400

1722

select hex(s1) from t4;

1401

drop table t1,t3,t4;

1723

drop table t1,t2,t3,t4;

1402

1724

1403

1725

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

1404

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;

1405

1727

1406

1728

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

1729

-- error 1452

1407

1730

insert into t2 values(0x42);

1408

1731

insert into t2 values(0x41);

1409

1732

select hex(s1) from t2;

1410

--error ER_ROW_IS_REFERENCED_2

1411

1733

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

1412

1734

select hex(s1) from t2;

1735

-- error 1451

1413

1736

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

1414

--error ER_DATA_TOO_LONG

1737

-- error 1451

1415

1738

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

1739

-- error 1451

1416

1740

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

1417

1741

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

1418

1742

select hex(s1) from t2;

1420

1744

select hex(s1) from t2;

1421

1745

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

1422

1746

select hex(s1) from t2;

1747

-- error 1451

1423

1748

delete from t1 where a=1;

1749

delete from t1 where a=2;

1424

1750

update t2 set s1=0x4120;

1425

--error ER_ROW_IS_REFERENCED_2

1751

-- error 1451

1426

1752

delete from t1;

1427

1753

delete from t1 where a!=3;

1428

1754

select a,hex(s1) from t1;

1430

1756

1431

1757

drop table t2,t1;

1432

1758

1433

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

1434

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;

1435

1761

1436

1762

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

1437

1763

insert into t2 values(0x41);

1441

1767

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

1442

1768

select hex(s1) from t2;

1443

1769

delete from t1 where a=1;

1444

--error ER_ROW_IS_REFERENCED_2

1770

-- error 1451

1445

1771

delete from t1 where a=2;

1446

1772

select a,hex(s1) from t1;

1447

1773

select hex(s1) from t2;

1460

1786

DROP TABLE t2,t1;

1461

1787

1462

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

1463

1880

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

1464

1881

# error message (bug #9680)

1465

1882

1533

1950

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

1534

1951

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

1535

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;

1536

1955

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

1537

1956

where mm.id is null lock in share mode;

1538

1957

drop table t1,t2;

1586

2005

1587

2006

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

1588

2007

1589

--error ER_LOCK_WAIT_TIMEOUT

2008

--error 1205

1590

2009

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

1591

2010

1592

2011

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

1593

2012

1594

--error ER_LOCK_WAIT_TIMEOUT

2013

--error 1205

1595

2014

connection a;

1596

2015

commit;

1597

2016

connection b;

1623

2042

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1624

2043

insert into t1 select * from t2;

1625

2044

update t1 set b = (select e from t2 where a = d);

1626

commit;

1627

2045

create table t3(d int not null, e int, primary key(d)) engine=innodb

1628

2046

select * from t2;

1629

2047

commit;

1722

2140

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

1723

2141

1724

2142

connection b;

1725

--error ER_LOCK_WAIT_TIMEOUT

2143

--error 1205

1726

2144

reap;

1727

2145

1728

2146

connection c;

1729

--error ER_LOCK_WAIT_TIMEOUT

2147

--error 1205

1730

2148

reap;

1731

2149

1732

2150

connection d;

1733

--error ER_LOCK_WAIT_TIMEOUT

2151

--error 1205

1734

2152

reap;

1735

2153

1736

2154

connection e;

1737

--error ER_LOCK_WAIT_TIMEOUT

2155

--error 1205

1738

2156

reap;

1739

2157

1740

2158

connection f;

1741

--error ER_LOCK_WAIT_TIMEOUT

2159

--error 1205

1742

2160

reap;

1743

2161

1744

2162

connection g;

1745

--error ER_LOCK_WAIT_TIMEOUT

2163

--error 1205

1746

2164

reap;

1747

2165

1748

2166

connection h;

1749

--error ER_LOCK_WAIT_TIMEOUT

2167

--error 1205

1750

2168

reap;

1751

2169

1752

2170

connection i;

1753

--error ER_LOCK_WAIT_TIMEOUT

2171

--error 1205

1754

2172

reap;

1755

2173

1756

2174

connection j;

1757

--error ER_LOCK_WAIT_TIMEOUT

2175

--error 1205

1758

2176

reap;

1759

2177

1760

2178

connection a;

1774

2192

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

1775

2193

1776

2194

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

1777

--error ER_CANT_CREATE_TABLE, ER_WRONG_COLUMN_NAME

2195

--error 1005

1778

2196

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

1779

2197

1780

2198

1782

2200

1783

2201

1784

2202

CREATE TABLE t1 (

1785

a BIGINT NOT NULL,

2203

a BIGINT(20) NOT NULL,

1786

2204

PRIMARY KEY (a)

1787

) ENGINE=INNODB;

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1788

2206

1789

2207

CREATE TABLE t2 (

1790

a BIGINT NOT NULL,

2208

a BIGINT(20) NOT NULL,

1791

2209

b VARCHAR(128) NOT NULL,

1792

2210

c TEXT NOT NULL,

1793

2211

PRIMARY KEY (a,b),

1794

2212

KEY idx_t2_b_c (b,c(200)),

1795

2213

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

1796

2214

ON DELETE CASCADE

1797

) ENGINE=INNODB;

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1798

2216

1799

2217

INSERT INTO t1 VALUES (1);

1800

2218

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

1818

2236

CREATE TABLE t1 ( a int ) ENGINE=innodb;

1819

2237

BEGIN;

1820

2238

INSERT INTO t1 VALUES (1);

1821

COMMIT;

1822

ALTER TABLE t1 ENGINE=innodb;

2239

OPTIMIZE TABLE t1;

1823

2240

DROP TABLE t1;

1824

2241

1825

2242

1851

2268

# mysqltest first does replace_regex, then replace_result

1852

2269

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

1853

2270

# Embedded server doesn't chdir to data directory

1854

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

1855

--error ER_ERROR_ON_RENAME

2271

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

2272

--error 1025

1856

2273

ALTER TABLE t2 MODIFY a INT NOT NULL;

1857

2274

DELETE FROM t1;

1858

2275

DROP TABLE t2,t1;

1877

2294

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

1878

2295

AUTO_INCREMENT=42;

1879

2296

1880

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

2297

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

1881

2298

SELECT * FROM t1;

1882

2299

1883

2300

SHOW CREATE TABLE t1;

1890

2307

DROP TABLE t1,t2;

1891

2308

1892

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

1893

2325

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

1894

2326

1895

2327

DROP TABLE IF EXISTS t1;

1896

2328

CREATE TABLE t1(

1897

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

1898

2330

) ENGINE=InnoDB;

1899

2331

INSERT INTO t1 VALUES(-10);

1900

2332

SELECT * FROM t1;

1908

2340

SELECT * FROM t1;

1909

2341

DROP TABLE t1;

1910

2342

1911

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1912

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;

1913

2384

1914

2385

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

1915

2386

# #

Older »