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

676

select * from t1;

677

select * from t2;

678

select * from t2;

679

drop table t1,t2;

680

681

611

682

# A simple test with some isolation levels

612

683

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

613

684

# this works.

636

707

DROP TABLE t1;

637

708

638

709

710

# Test of multi-table-update

711

712

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

713

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

714

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

715

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

716

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

717

select * from t1;

718

select * from t2;

719

drop table t1,t2;

720

721

639

722

# Testing of IFNULL

640

723

641

724

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

653

736

# Test of read_through not existing const_table

654

737

655

738

656

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

739

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

657

740

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

658

741

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

659

742

drop table t1,t2;

661

744

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

662

745

insert into t1 values (10, 20);

663

746

insert into t2 values (10, 20);

747

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

664

748

drop table t1,t2;

665

749

666

750

751

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

752

753

754

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

755

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;

756

insert into t1 set id=1;

757

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

758

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

759

select * from t1;

760

select * from t2;

761

drop table t2,t1;

762

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

763

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

764

INSERT INTO t1 VALUES(1);

765

INSERT INTO t2 VALUES(1, 1);

766

SELECT * from t1;

767

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

768

SELECT * from t1;

769

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

770

SELECT * from t1;

771

DROP TABLE t1,t2;

772

773

667

774

# Test of range_optimizer

668

775

669

776

707

814

drop table t1;

708

815

709

816

817

# Test multi update with different join methods

818

819

820

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

821

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

822

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

823

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

824

825

# Full join, without key

826

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

827

select * from t1;

828

829

# unique key

830

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

831

select * from t1;

832

833

# ref key

834

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

835

select * from t1;

836

837

# Range key (in t1)

838

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

839

select * from t1;

840

select * from t2;

841

842

drop table t1,t2;

843

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

844

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

845

SET AUTOCOMMIT=0;

846

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

847

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

848

ROLLBACK;

849

SELECT * FROM t1;

850

drop table t1,t2;

851

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

852

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

853

select distinct parent,child from t1 order by parent;

854

drop table t1;

855

856

710

857

# Test that MySQL priorities clustered indexes

711

858

712

859

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

752

899

drop table t1;

753

900

754

901

902

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

903

904

905

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

906

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

907

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

908

909

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

910

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

911

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

912

update t1 set a=c, b=c;

913

914

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

915

insert into t2 select * from t1;

916

917

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

918

drop table t1,t2;

919

920

755

921

# test autoincrement with TRUNCATE

756

922

757

923

773

939

drop table t2,t1;

774

940

775

941

942

# Test of multi updated and foreign keys

943

944

945

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

946

insert into `t1`values ( 1 ) ;

947

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

948

insert into `t2`values ( 1 ) ;

949

create table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;

950

insert into `t3`values ( 1 ) ;

951

--error 1451

952

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

953

--error 1451

954

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

955

--error 1054

956

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

957

drop table t3,t2,t1;

958

959

776

960

# test for recursion depth limit

777

961

778

962

create table t1(

782

966

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

783

967

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

784

968

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

785

--error ER_ROW_IS_REFERENCED_2

969

-- error 1451

786

970

delete from t1 where id=0;

787

971

delete from t1 where id=15;

788

972

delete from t1 where id=0;

833

1017

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

834

1018

drop table t1,t2;

835

1019

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;

1020

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

1021

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

1022

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

839

1023

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

840

1024

insert t2 select * from t1;

841

1025

insert t3 select * from t1;

842

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

1026

checksum table t1, t2, t3, t4 quick;

1027

checksum table t1, t2, t3, t4;

1028

checksum table t1, t2, t3, t4 extended;

1029

#show table status;

843

1030

drop table t1,t2,t3;

844

1031

845

1032

871

1058

create index id2 on t2 (id);

872

1059

show create table t2;

873

1060

drop index id2 on t2;

874

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1061

--error 1025,1025

875

1062

drop index id on t2;

876

1063

show create table t2;

877

1064

drop table t2;

908

1095

# Test error handling

909

1096

910

1097

# Embedded server doesn't chdir to data directory

911

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

1098

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

912

1099

--error ER_WRONG_FK_DEF

913

1100

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

914

1101

925

1112

926

1113

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

927

1114

928

--error ER_DUP_FIELDNAME

1115

--error 1060

929

1116

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

930

--error ER_DUP_FIELDNAME

1117

--error 1060

931

1118

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

932

--error ER_DUP_FIELDNAME

1119

--error 1060

933

1120

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

934

--error ER_DUP_FIELDNAME

1121

--error 1060

935

1122

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

936

1123

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

937

--error ER_DUP_FIELDNAME

1124

--error 1060

938

1125

alter table t1 add key (c1,c1);

939

--error ER_DUP_FIELDNAME

1126

--error 1060

940

1127

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

941

--error ER_DUP_FIELDNAME

1128

--error 1060

942

1129

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

943

--error ER_DUP_FIELDNAME

1130

--error 1060

944

1131

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

945

1132

drop table t1;

946

1133

1030

1217

## Not deterministic.

1031

1218

# Test for testable InnoDB status variables. This test

1032

1219

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

1033

--replace_column 2 #

1034

show status like "Innodb_buffer_pool_pages_total";

1035

--replace_column 2 #

1036

show status like "Innodb_page_size";

1037

--replace_column 2 #

1038

show status like "Innodb_rows_deleted";

1039

--replace_column 2 #

1040

show status like "Innodb_rows_inserted";

1041

--replace_column 2 #

1042

show status like "Innodb_rows_updated";

1220

#show status like "Innodb_buffer_pool_pages_total";

1221

#show status like "Innodb_page_size";

1222

#show status like "Innodb_rows_deleted";

1223

#show status like "Innodb_rows_inserted";

1224

#show status like "Innodb_rows_updated";

1043

1225

1044

1226

## Test for row locks InnoDB status variables.

1045

--replace_column 2 #

1046

show status like "Innodb_row_lock_waits";

1047

--replace_column 2 #

1048

show status like "Innodb_row_lock_current_waits";

1049

--replace_column 2 #

1050

show status like "Innodb_row_lock_time";

1051

--replace_column 2 #

1052

show status like "Innodb_row_lock_time_max";

1053

--replace_column 2 #

1054

show status like "Innodb_row_lock_time_avg";

1227

#show status like "Innodb_row_lock_waits";

1228

#show status like "Innodb_row_lock_current_waits";

1229

#show status like "Innodb_row_lock_time";

1230

#show status like "Innodb_row_lock_time_max";

1231

#show status like "Innodb_row_lock_time_avg";

1055

1232

1056

1233

# Test for innodb_sync_spin_loops variable

1057

set @my_innodb_sync_spin_loops = @@global.innodb_sync_spin_loops;

1058

1234

show variables like "innodb_sync_spin_loops";

1059

1235

set global innodb_sync_spin_loops=1000;

1060

1236

show variables like "innodb_sync_spin_loops";

1062

1238

show variables like "innodb_sync_spin_loops";

1063

1239

set global innodb_sync_spin_loops=20;

1064

1240

show variables like "innodb_sync_spin_loops";

1065

set @@global.innodb_sync_spin_loops = @my_innodb_sync_spin_loops;

1066

1241

1067

1242

# Test for innodb_thread_concurrency variable

1068

set @my_innodb_thread_concurrency = @@global.innodb_thread_concurrency;

1069

1243

show variables like "innodb_thread_concurrency";

1070

1244

set global innodb_thread_concurrency=1001;

1071

1245

show variables like "innodb_thread_concurrency";

1073

1247

show variables like "innodb_thread_concurrency";

1074

1248

set global innodb_thread_concurrency=16;

1075

1249

show variables like "innodb_thread_concurrency";

1076

set @@global.innodb_thread_concurrency = @my_innodb_thread_concurrency;

1077

1250

1078

1251

# Test for innodb_concurrency_tickets variable

1079

1252

show variables like "innodb_concurrency_tickets";

1106

1279

1107

1280

1108

1281

# Embedded server doesn't chdir to data directory

1109

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

1282

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

1110

1283

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

1111

1284

drop table t1;

1112

1285

create table t1 (v varchar(16383));

1158

1331

drop table t1;

1159

1332

1160

1333

1334

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

1335

1336

1337

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

1338

insert into t1 (val) values (1);

1339

update t1 set a=2 where a=1;

1340

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

1341

--error ER_DUP_ENTRY

1342

insert into t1 (val) values (1);

1343

select * from t1;

1344

drop table t1;

1345

1161

1346

# Bug #10465

1162

1347

1163

1348

1170

1355

DROP TABLE t1;

1171

1356

1172

1357

1358

# Bug #12340 multitable delete deletes only one record

1359

1360

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

1361

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

1362

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

1363

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

1364

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

1365

select * from t1;

1366

drop table t1,t2;

1367

1368

1173

1369

# Test that the slow TRUNCATE implementation resets autoincrement columns

1174

1370

# (bug #11946)

1175

1371

1203

1399

id INT PRIMARY KEY

1204

1400

) ENGINE=InnoDB;

1205

1401

1206

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1402

--error 1005,1005

1207

1403

CREATE TEMPORARY TABLE t2

1208

1404

(

1209

1405

id INT NOT NULL PRIMARY KEY,

1257

1453

drop table t1, t2, t3, t4;

1258

1454

1259

1455

# these should be refused

1260

--error ER_TOO_LONG_KEY

1456

--error 1071

1261

1457

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

1262

1458

engine = innodb;

1263

--error ER_TOO_LONG_KEY

1264

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

1459

--error 1071

1460

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

1265

1461

engine = innodb;

1266

--error ER_TOO_LONG_KEY

1462

--error 1071

1267

1463

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

1268

1464

engine = innodb;

1269

--error ER_TOO_LONG_KEY

1270

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

1465

--error 1071

1466

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

1271

1467

engine = innodb;

1272

1468

1273

1469

1285

1481

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

1286

1482

) ENGINE=InnoDB;

1287

1483

1288

--error ER_NO_REFERENCED_ROW_2

1484

--error 1452

1289

1485

INSERT INTO t2 VALUES(2);

1290

1486

1291

1487

INSERT INTO t1 VALUES(1);

1292

1488

INSERT INTO t2 VALUES(1);

1293

1489

1294

--error ER_ROW_IS_REFERENCED_2

1490

--error 1451

1295

1491

DELETE FROM t1 WHERE id = 1;

1296

1492

1297

--error ER_ROW_IS_REFERENCED

1493

--error 1217

1298

1494

DROP TABLE t1;

1299

1495

1300

1496

SET FOREIGN_KEY_CHECKS=0;

1301

1497

DROP TABLE t1;

1302

1498

SET FOREIGN_KEY_CHECKS=1;

1303

1499

1304

--error ER_NO_REFERENCED_ROW_2

1500

--error 1452

1305

1501

INSERT INTO t2 VALUES(3);

1306

1502

1307

1503

DROP TABLE t2;

1504

1505

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

1506

1507

connect (a,localhost,root,,);

1508

connect (b,localhost,root,,);

1509

connection a;

1510

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

1511

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

1512

set autocommit=0;

1513

checksum table t1;

1514

connection b;

1515

insert into t1 values(3);

1516

connection a;

1517

1518

# Here checksum should not see insert

1519

1520

checksum table t1;

1521

connection a;

1522

commit;

1523

checksum table t1;

1524

commit;

1525

drop table t1;

1526

1527

# autocommit = 1

1528

1529

connection a;

1530

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

1531

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

1532

set autocommit=1;

1533

checksum table t1;

1534

connection b;

1535

set autocommit=1;

1536

insert into t1 values(3);

1537

connection a;

1538

1539

# Here checksum sees insert

1540

1541

checksum table t1;

1542

drop table t1;

1543

1544

connection default;

1545

disconnect a;

1546

disconnect b;

1308

1547

1309

1548

# tests for bugs #9802 and #13778

1310

1549

1313

1552

set foreign_key_checks=0;

1314

1553

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

1315

1554

# Embedded server doesn't chdir to data directory

1316

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

1317

--error ER_CANT_CREATE_TABLE

1555

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

1556

-- error 1005

1318

1557

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

1319

1558

set foreign_key_checks=1;

1320

1559

drop table t2;

1324

1563

set foreign_key_checks=0;

1325

1564

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

1326

1565

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

1327

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1566

-- error 1025,1025

1328

1567

alter table t1 modify column a int;

1329

1568

set foreign_key_checks=1;

1330

1569

drop table t2,t1;

1338

1577

1339

1578

insert into t1 values(1);

1340

1579

insert into t3 values(1);

1341

--error ER_NO_REFERENCED_ROW_2

1580

-- error 1452

1342

1581

insert into t2 values(2);

1343

--error ER_NO_REFERENCED_ROW_2

1582

-- error 1452

1344

1583

insert into t4 values(2);

1345

1584

insert into t2 values(1);

1346

1585

insert into t4 values(1);

1347

--error ER_ROW_IS_REFERENCED_2

1586

-- error 1451

1348

1587

update t1 set a=2;

1349

--error ER_NO_REFERENCED_ROW_2

1588

-- error 1452

1350

1589

update t2 set a=2;

1351

--error ER_ROW_IS_REFERENCED_2

1590

-- error 1451

1352

1591

update t3 set a=2;

1353

--error ER_NO_REFERENCED_ROW_2

1592

-- error 1452

1354

1593

update t4 set a=2;

1355

--error ER_ROW_IS_REFERENCED_2

1594

-- error 1451

1356

1595

truncate t1;

1357

--error ER_ROW_IS_REFERENCED_2

1596

-- error 1451

1358

1597

truncate t3;

1359

1598

truncate t2;

1360

1599

truncate t4;

1371

1610

b varchar(255),

1372

1611

c varchar(255),

1373

1612

d varchar(255),

1374

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

1613

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

1375

1614

drop table t1;

1376

1615

--error ER_TOO_LONG_KEY

1377

1616

create table t1 (a varchar(255),

1385

1624

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

1386

1625

1387

1626

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;

1627

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

1628

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

1390

1629

1391

1630

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

1392

--error ER_DUP_ENTRY

1631

-- error ER_DUP_ENTRY

1393

1632

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

1394

1633

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

1395

--error ER_DUP_ENTRY

1634

-- error ER_DUP_ENTRY

1396

1635

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

1397

1636

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

1398

1637

select hex(s1) from t1;

1407

1646

insert into t2 values(0x42);

1408

1647

insert into t2 values(0x41);

1409

1648

select hex(s1) from t2;

1410

--error ER_ROW_IS_REFERENCED_2

1649

-- error 1451

1411

1650

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

1412

1651

select hex(s1) from t2;

1413

1652

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

1414

--error ER_DATA_TOO_LONG

1653

-- error 1406

1415

1654

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

1416

1655

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

1417

1656

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

1422

1661

select hex(s1) from t2;

1423

1662

delete from t1 where a=1;

1424

1663

update t2 set s1=0x4120;

1425

--error ER_ROW_IS_REFERENCED_2

1664

-- error 1451

1426

1665

delete from t1;

1427

1666

delete from t1 where a!=3;

1428

1667

select a,hex(s1) from t1;

1430

1669

1431

1670

drop table t2,t1;

1432

1671

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;

1672

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

1673

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

1435

1674

1436

1675

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

1437

1676

insert into t2 values(0x41);

1441

1680

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

1442

1681

select hex(s1) from t2;

1443

1682

delete from t1 where a=1;

1444

--error ER_ROW_IS_REFERENCED_2

1683

-- error 1451

1445

1684

delete from t1 where a=2;

1446

1685

select a,hex(s1) from t1;

1447

1686

select hex(s1) from t2;

1533

1772

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

1534

1773

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

1535

1774

commit;

1775

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

1776

WHERE mm.id IS NULL;

1536

1777

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

1537

1778

where mm.id is null lock in share mode;

1538

1779

drop table t1,t2;

1586

1827

1587

1828

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

1588

1829

1589

--error ER_LOCK_WAIT_TIMEOUT

1830

--error 1205

1590

1831

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

1591

1832

1592

1833

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

1593

1834

1594

--error ER_LOCK_WAIT_TIMEOUT

1835

--error 1205

1595

1836

connection a;

1596

1837

commit;

1597

1838

connection b;

1623

1864

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1624

1865

insert into t1 select * from t2;

1625

1866

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

1626

commit;

1627

1867

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

1628

1868

select * from t2;

1629

1869

commit;

1722

1962

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

1723

1963

1724

1964

connection b;

1725

--error ER_LOCK_WAIT_TIMEOUT

1965

--error 1205

1726

1966

reap;

1727

1967

1728

1968

connection c;

1729

--error ER_LOCK_WAIT_TIMEOUT

1969

--error 1205

1730

1970

reap;

1731

1971

1732

1972

connection d;

1733

--error ER_LOCK_WAIT_TIMEOUT

1973

--error 1205

1734

1974

reap;

1735

1975

1736

1976

connection e;

1737

--error ER_LOCK_WAIT_TIMEOUT

1977

--error 1205

1738

1978

reap;

1739

1979

1740

1980

connection f;

1741

--error ER_LOCK_WAIT_TIMEOUT

1981

--error 1205

1742

1982

reap;

1743

1983

1744

1984

connection g;

1745

--error ER_LOCK_WAIT_TIMEOUT

1985

--error 1205

1746

1986

reap;

1747

1987

1748

1988

connection h;

1749

--error ER_LOCK_WAIT_TIMEOUT

1989

--error 1205

1750

1990

reap;

1751

1991

1752

1992

connection i;

1753

--error ER_LOCK_WAIT_TIMEOUT

1993

--error 1205

1754

1994

reap;

1755

1995

1756

1996

connection j;

1757

--error ER_LOCK_WAIT_TIMEOUT

1997

--error 1205

1758

1998

reap;

1759

1999

1760

2000

connection a;

1774

2014

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

1775

2015

1776

2016

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

1777

--error ER_CANT_CREATE_TABLE, ER_WRONG_COLUMN_NAME

2017

--error 1005

1778

2018

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

1779

2019

1780

2020

1818

2058

CREATE TABLE t1 ( a int ) ENGINE=innodb;

1819

2059

BEGIN;

1820

2060

INSERT INTO t1 VALUES (1);

1821

COMMIT;

1822

ALTER TABLE t1 ENGINE=innodb;

2061

OPTIMIZE TABLE t1;

1823

2062

DROP TABLE t1;

1824

2063

1825

2064

1851

2090

# mysqltest first does replace_regex, then replace_result

1852

2091

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

1853

2092

# Embedded server doesn't chdir to data directory

1854

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

1855

--error ER_ERROR_ON_RENAME

2093

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

2094

--error 1025

1856

2095

ALTER TABLE t2 MODIFY a INT NOT NULL;

1857

2096

DELETE FROM t1;

1858

2097

DROP TABLE t2,t1;

1877

2116

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

1878

2117

AUTO_INCREMENT=42;

1879

2118

1880

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

2119

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

1881

2120

SELECT * FROM t1;

1882

2121

1883

2122

SHOW CREATE TABLE t1;

1908

2147

SELECT * FROM t1;

1909

2148

DROP TABLE t1;

1910

2149

1911

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

1912

1913

1914

2150

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

1915

2151

# #

1916

2152

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

Older »