~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

683

607

# A simple test with some isolation levels

684

608

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

685

609

# this works.

708

632

DROP TABLE t1;

709

633

710

634

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

755

635

# Testing of IFNULL

756

636

757

637

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

769

649

# Test of read_through not existing const_table

770

650

771

651

772

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

652

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

773

653

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

774

654

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

775

655

drop table t1,t2;

777

657

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

778

658

insert into t1 values (10, 20);

779

659

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;

781

660

drop table t1,t2;

782

661

783

662

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

807

663

# Test of range_optimizer

808

664

809

665

847

703

drop table t1;

848

704

849

705

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

890

706

# Test that MySQL priorities clustered indexes

891

707

892

708

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

932

748

drop table t1;

933

749

934

750

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

954

751

# test autoincrement with TRUNCATE

955

752

956

753

972

769

drop table t2,t1;

973

770

974

771

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

993

772

# test for recursion depth limit

994

773

995

774

create table t1(

1010

789

# Test timestamps

1011

790

1012

791

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;

792

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

793

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

1016

794

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

1017

795

# Note that timestamp 3 is wrong

1018

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

796

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

1019

797

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

1020

798

'20020204120000' GROUP BY col1;

1021

799

drop table t1,t2;

1025

803

1026

804

1027

805

CREATE TABLE t1 (

1028

`id` int(10) NOT NULL auto_increment,

1029

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

1030

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

806

`id` int NOT NULL auto_increment,

807

`id_object` int default '0',

808

`id_version` int NOT NULL default '1',

1031

809

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

1032

810

`description` text,

1033

811

PRIMARY KEY (`id`),

1038

816

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

1039

817

1040

818

CREATE TABLE t2 (

1041

`id` int(10) NOT NULL auto_increment,

1042

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

819

`id` int NOT NULL auto_increment,

820

`id_version` int NOT NULL default '1',

1043

821

PRIMARY KEY (`id`),

1044

822

KEY `id_version` (`id_version`)

1045

823

) ENGINE=InnoDB;

1051

829

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

1052

830

drop table t1,t2;

1053

831

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;

832

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

833

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

834

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

1057

835

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

1058

836

insert t2 select * from t1;

1059

837

insert t3 select * from t1;

1083

861

# Test of automaticly created foreign keys

1084

862

1085

863

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;

864

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

865

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

1088

866

show create table t1;

1089

867

show create table t2;

1090

868

create index id on t2 (id);

1097

875

show create table t2;

1098

876

drop table t2;

1099

877

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

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;

1101

879

show create table t2;

1102

880

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

1103

881

show create table t2;

1104

882

drop table t2;

1105

883

1106

884

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

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;

885

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;

886

show create table t2;

887

drop table t2;

888

889

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;

890

show create table t2;

891

drop table t2;

892

893

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;

894

show create table t2;

895

drop table t2;

896

897

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;

898

show create table t2;

899

drop table t2;

900

901

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;

1124

902

show create table t2;

1125

903

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

1126

904

show create table t2;

1131

909

# Embedded server doesn't chdir to data directory

1132

910

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

1133

911

--error ER_WRONG_FK_DEF

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

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

1135

913

1136

914

# bug#3749

1137

915

1169

947

# Bug #4082: integer truncation

1170

948

1171

949

1172

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

950

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

1173

951

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

1174

952

select distinct concat(a, b) from t1;

1175

953

drop table t1;

1176

954

1177

955

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

1188

956

# check null values #1

1189

957

1190

958

1191

959

--disable_warnings

1192

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

960

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

1193

961

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

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;

962

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

963

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

1196

964

--enable_warnings

1197

965

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

1198

966

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;

1244

1012

1245

1013

1246

1014

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

1247

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

1015

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

1248

1016

select * from t1;

1249

1017

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

1250

1018

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

1254

1022

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

1255

1023

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

1256

1024

select * from t1 where x > -16;

1257

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

1025

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

1258

1026

drop table t1;

1259

1027

1260

1028

1029

## Not deterministic.

1261

1030

# Test for testable InnoDB status variables. This test

1262

1031

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

1263

show status like "Innodb_buffer_pool_pages_total";

1264

show status like "Innodb_page_size";

1265

show status like "Innodb_rows_deleted";

1266

show status like "Innodb_rows_inserted";

1267

show status like "Innodb_rows_updated";

1032

#show status like "Innodb_buffer_pool_pages_total";

1033

#show status like "Innodb_page_size";

1034

#show status like "Innodb_rows_deleted";

1035

#show status like "Innodb_rows_inserted";

1036

#show status like "Innodb_rows_updated";

1268

1037

1269

# Test for row locks InnoDB status variables.

1270

show status like "Innodb_row_lock_waits";

1271

show status like "Innodb_row_lock_current_waits";

1272

show status like "Innodb_row_lock_time";

1273

show status like "Innodb_row_lock_time_max";

1274

show status like "Innodb_row_lock_time_avg";

1038

## Test for row locks InnoDB status variables.

1039

#show status like "Innodb_row_lock_waits";

1040

#show status like "Innodb_row_lock_current_waits";

1041

#show status like "Innodb_row_lock_time";

1042

#show status like "Innodb_row_lock_time_max";

1043

#show status like "Innodb_row_lock_time_avg";

1275

1044

1276

1045

# Test for innodb_sync_spin_loops variable

1277

1046

show variables like "innodb_sync_spin_loops";

1323

1092

1324

1093

# Embedded server doesn't chdir to data directory

1325

1094

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

1326

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

1095

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

1327

1096

drop table t1;

1328

create table t1 (v varchar(65536));

1097

create table t1 (v varchar(16383));

1329

1098

show create table t1;

1330

1099

drop table t1;

1331

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

1100

create table t1 (v varchar(16383));

1332

1101

show create table t1;

1333

1102

drop table t1;

1334

1103

1335

1104

eval set storage_engine=$default;

1336

1105

1337

1106

# InnoDB specific varchar tests

1338

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

1107

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

1339

1108

drop table t1;

1340

1109

1341

1110

1352

1121

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

1353

1122

1354

1123

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;

1124

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

1356

1125

insert into t1 (b) values (1);

1357

1126

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

1358

1127

select * from t1;

1398

1167

DROP TABLE t1;

1399

1168

1400

1169

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

1412

1170

# Test that the slow TRUNCATE implementation resets autoincrement columns

1413

1171

# (bug #11946)

1414

1172

1457

1215

1458

1216

# prefix index

1459

1217

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

1460

character set = latin1 engine = innodb;

1218

engine = innodb;

1461

1219

1462

1220

# normal indexes

1463

1221

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

1464

character set = latin1 engine = innodb;

1465

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

1466

character set = latin1 engine = innodb;

1222

engine = innodb;

1467

1223

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

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;

1224

engine = innodb;

1225

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

1226

engine = innodb;

1227

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

1228

engine = innodb;

1473

1229

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

1474

character set = latin1 engine = innodb;

1230

engine = innodb;

1475

1231

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

1476

character set = latin1 engine = innodb;

1232

engine = innodb;

1477

1233

1478

1234

# multi-column indexes are allowed to be longer

1479

1235

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

1480

character set = latin1 engine = innodb;

1236

engine = innodb;

1481

1237

1482

1238

show create table t9;

1483

1239

1484

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

1240

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

1485

1241

1486

1242

# these should have their index length trimmed

1487

1243

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

1488

character set = latin1 engine = innodb;

1244

engine = innodb;

1489

1245

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

1490

character set = latin1 engine = innodb;

1246

engine = innodb;

1491

1247

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

1492

character set = latin1 engine = innodb;

1248

engine = innodb;

1493

1249

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

1494

character set = latin1 engine = innodb;

1250

engine = innodb;

1495

1251

1496

1252

show create table t1;

1497

1253

1500

1256

# these should be refused

1501

1257

--error 1071

1502

1258

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

1503

character set = latin1 engine = innodb;

1259

engine = innodb;

1504

1260

--error 1071

1505

1261

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

1506

character set = latin1 engine = innodb;

1262

engine = innodb;

1507

1263

--error 1071

1508

1264

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

1509

character set = latin1 engine = innodb;

1265

engine = innodb;

1510

1266

--error 1071

1511

1267

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

1512

character set = latin1 engine = innodb;

1268

engine = innodb;

1513

1269

1514

1270

1515

1271

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

1552

1308

connect (a,localhost,root,,);

1553

1309

connect (b,localhost,root,,);

1554

1310

connection a;

1555

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

1311

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

1556

1312

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

1557

1313

set autocommit=0;

1558

1314

checksum table t1;

1572

1328

# autocommit = 1

1573

1329

1574

1330

connection a;

1575

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

1331

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

1576

1332

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

1577

1333

set autocommit=1;

1578

1334

checksum table t1;

1603

1359

set foreign_key_checks=1;

1604

1360

drop table t2;

1605

1361

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

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

1614

set foreign_key_checks=1;

1615

drop table t1;

1616

1617

1362

# test that invalid datatype conversions with ALTER are not allowed

1618

1363

1619

1364

set foreign_key_checks=0;

1625

1369

set foreign_key_checks=1;

1626

1370

drop table t2,t1;

1627

1371

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

rename table t3 to t1;

1645

set foreign_key_checks=1;

1646

drop table t2,t3;

1647

1648

1372

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

1649

1373

1650

1374

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

1684

1407

1685

1408

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

1686

1409

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,

1410

create table t1 (a varchar(255),

1411

b varchar(255),

1412

c varchar(255),

1413

d varchar(255),

1691

1414

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

1692

1415

drop table t1;

1693

1416

--error ER_TOO_LONG_KEY

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,

1417

create table t1 (a varchar(255),

1418

b varchar(255),

1419

c varchar(255),

1420

d varchar(255),

1421

e varchar(255),

1699

1422

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

1700

1423

1701

1424

1702

1425

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

1703

1426

1704

1427

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

1705

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

1706

1428

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

1707

1429

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

1708

1430

1709

1431

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

1710

1432

-- error ER_DUP_ENTRY

1711

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

1712

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

1713

1433

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

1714

1434

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

1715

1435

-- error ER_DUP_ENTRY

1716

1436

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

1717

1437

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

1718

1438

select hex(s1) from t1;

1719

select hex(s1) from t2;

1720

1439

select hex(s1) from t3;

1721

1440

select hex(s1) from t4;

1722

drop table t1,t2,t3,t4;

1441

drop table t1,t3,t4;

1723

1442

1724

1443

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

1725

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

1444

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

1726

1445

1727

1446

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

1728

1447

insert into t2 values(0x42);

1729

1448

insert into t2 values(0x41);

1730

1449

select hex(s1) from t2;

1450

-- error 1451

1731

1451

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

1732

1452

select hex(s1) from t2;

1733

1453

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

1454

-- error 1406

1734

1455

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

1735

1456

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

1736

1457

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

1737

1458

select hex(s1) from t2;

1744

1460

select hex(s1) from t2;

1745

1461

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

1746

1462

select hex(s1) from t2;

1747

1463

delete from t1 where a=1;

1748

delete from t1 where a=2;

1749

1464

update t2 set s1=0x4120;

1750

1465

-- error 1451

1751

1466

delete from t1;

1786

1500

DROP TABLE t2,t1;

1787

1501

1788

1502

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

1880

1503

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

1881

1504

# error message (bug #9680)

1882

1505

1950

1573

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

1951

1574

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

1952

1575

commit;

1953

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

1954

WHERE mm.id IS NULL;

1955

1576

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

1956

1577

where mm.id is null lock in share mode;

1957

1578

drop table t1,t2;

2200

1821

2201

1822

2202

1823

CREATE TABLE t1 (

2203

a BIGINT(20) NOT NULL,

1824

a BIGINT NOT NULL,

2204

1825

PRIMARY KEY (a)

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1826

) ENGINE=INNODB;

2206

1827

2207

1828

CREATE TABLE t2 (

2208

a BIGINT(20) NOT NULL,

1829

a BIGINT NOT NULL,

2209

1830

b VARCHAR(128) NOT NULL,

2210

1831

c TEXT NOT NULL,

2211

1832

PRIMARY KEY (a,b),

2212

1833

KEY idx_t2_b_c (b,c(200)),

2213

1834

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

2214

1835

ON DELETE CASCADE

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

1836

) ENGINE=INNODB;

2216

1837

2217

1838

INSERT INTO t1 VALUES (1);

2218

1839

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

2294

1915

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

2295

1916

AUTO_INCREMENT=42;

2296

1917

2297

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

1918

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

2298

1919

SELECT * FROM t1;

2299

1920

2300

1921

SHOW CREATE TABLE t1;

2307

1928

DROP TABLE t1,t2;

2308

1929

2309

1930

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

2325

1931

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

2326

1932

2327

1933

DROP TABLE IF EXISTS t1;

2328

1934

CREATE TABLE t1(

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

1935

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2330

1936

) ENGINE=InnoDB;

2331

1937

INSERT INTO t1 VALUES(-10);

2332

1938

SELECT * FROM t1;

2340

1946

SELECT * FROM t1;

2341

1947

DROP TABLE t1;

2342

1948

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;

2384

2385

1949

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

2386

1950

# #

2387

1951

# Please, DO NOT TOUCH this file as well as the innodb.result file. #

Older »