~drizzle-trunk/drizzle/development

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;

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;

981

948

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;

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;

983

950

insert into `t3`values ( 1 ) ;

984

951

--error 1451

985

952

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

1010

977

# Test timestamps

1011

978

1012

979

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;

980

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

981

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

1016

982

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

1017

983

# Note that timestamp 3 is wrong

1018

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

984

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

1019

985

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

1020

986

'20020204120000' GROUP BY col1;

1021

987

drop table t1,t2;

1025

991

1026

992

1027

993

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

994

`id` int NOT NULL auto_increment,

995

`id_object` int default '0',

996

`id_version` int NOT NULL default '1',

1031

997

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

1032

998

`description` text,

1033

999

PRIMARY KEY (`id`),

1038

1004

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

1005

1040

1006

CREATE TABLE t2 (

1041

`id` int(10) NOT NULL auto_increment,

1042

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

1007

`id` int NOT NULL auto_increment,

1008

`id_version` int NOT NULL default '1',

1043

1009

PRIMARY KEY (`id`),

1044

1010

KEY `id_version` (`id_version`)

1045

1011

) ENGINE=InnoDB;

1083

1049

# Test of automaticly created foreign keys

1084

1050

1085

1051

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;

1052

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

1053

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

1088

1054

show create table t1;

1089

1055

show create table t2;

1090

1056

create index id on t2 (id);

1097

1063

show create table t2;

1098

1064

drop table t2;

1099

1065

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;

1066

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

1067

show create table t2;

1102

1068

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

1103

1069

show create table t2;

1104

1070

drop table t2;

1105

1071

1106

1072

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

1073

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;

1074

show create table t2;

1075

drop table t2;

1076

1077

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;

1078

show create table t2;

1079

drop table t2;

1080

1081

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;

1082

show create table t2;

1083

drop table t2;

1084

1085

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;

1086

show create table t2;

1087

drop table t2;

1088

1089

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

1090

show create table t2;

1125

1091

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

1126

1092

show create table t2;

1131

1097

# Embedded server doesn't chdir to data directory

1132

1098

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

1133

1099

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

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;

1135

1101

1136

1102

# bug#3749

1137

1103

1169

1135

# Bug #4082: integer truncation

1170

1136

1171

1137

1172

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

1138

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

1173

1139

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

1174

1140

select distinct concat(a, b) from t1;

1175

1141

drop table t1;

1176

1142

1177

1143

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

1144

# check null values #1

1189

1145

1190

1146

1191

1147

--disable_warnings

1192

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

1148

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

1193

1149

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;

1150

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

1151

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

1196

1152

--enable_warnings

1197

1153

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

1198

1154

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

1200

1245

1201

1246

1202

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

1247

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

1203

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

1248

1204

select * from t1;

1249

1205

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

1250

1206

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

1254

1210

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

1255

1211

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

1256

1212

select * from t1 where x > -16;

1257

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

1213

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

1258

1214

drop table t1;

1259

1215

1260

1216

1217

## Not deterministic.

1261

1218

# Test for testable InnoDB status variables. This test

1262

1219

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

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

1268

1225

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

1226

## Test for row locks InnoDB status variables.

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

1275

1232

1276

1233

# Test for innodb_sync_spin_loops variable

1277

1234

show variables like "innodb_sync_spin_loops";

1323

1280

1324

1281

# Embedded server doesn't chdir to data directory

1325

1282

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

1326

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

1283

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

1327

1284

drop table t1;

1328

create table t1 (v varchar(65536));

1285

create table t1 (v varchar(16383));

1329

1286

show create table t1;

1330

1287

drop table t1;

1331

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

1288

create table t1 (v varchar(16383));

1332

1289

show create table t1;

1333

1290

drop table t1;

1334

1291

1335

1292

eval set storage_engine=$default;

1336

1293

1337

1294

# InnoDB specific varchar tests

1338

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

1295

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

1339

1296

drop table t1;

1340

1297

1341

1298

1352

1309

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

1353

1310

1354

1311

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;

1312

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

1356

1313

insert into t1 (b) values (1);

1357

1314

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

1358

1315

select * from t1;

1457

1414

1458

1415

# prefix index

1459

1416

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

1460

character set = latin1 engine = innodb;

1417

engine = innodb;

1461

1418

1462

1419

# normal indexes

1463

1420

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;

1421

engine = innodb;

1467

1422

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;

1423

engine = innodb;

1424

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

1425

engine = innodb;

1426

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

1427

engine = innodb;

1473

1428

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

1474

character set = latin1 engine = innodb;

1429

engine = innodb;

1475

1430

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

1476

character set = latin1 engine = innodb;

1431

engine = innodb;

1477

1432

1478

1433

# multi-column indexes are allowed to be longer

1479

1434

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

1480

character set = latin1 engine = innodb;

1435

engine = innodb;

1481

1436

1482

1437

show create table t9;

1483

1438

1484

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

1439

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

1485

1440

1486

1441

# these should have their index length trimmed

1487

1442

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

1488

character set = latin1 engine = innodb;

1443

engine = innodb;

1489

1444

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

1490

character set = latin1 engine = innodb;

1445

engine = innodb;

1491

1446

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

1492

character set = latin1 engine = innodb;

1447

engine = innodb;

1493

1448

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

1494

character set = latin1 engine = innodb;

1449

engine = innodb;

1495

1450

1496

1451

show create table t1;

1497

1452

1500

1455

# these should be refused

1501

1456

--error 1071

1502

1457

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

1503

character set = latin1 engine = innodb;

1458

engine = innodb;

1504

1459

--error 1071

1505

1460

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

1506

character set = latin1 engine = innodb;

1461

engine = innodb;

1507

1462

--error 1071

1508

1463

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

1509

character set = latin1 engine = innodb;

1464

engine = innodb;

1510

1465

--error 1071

1511

1466

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

1512

character set = latin1 engine = innodb;

1467

engine = innodb;

1513

1468

1514

1469

1515

1470

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

1552

1507

connect (a,localhost,root,,);

1553

1508

connect (b,localhost,root,,);

1554

1509

connection a;

1555

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

1510

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

1556

1511

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

1557

1512

set autocommit=0;

1558

1513

checksum table t1;

1572

1527

# autocommit = 1

1573

1528

1574

1529

connection a;

1575

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

1530

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

1576

1531

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

1577

1532

set autocommit=1;

1578

1533

checksum table t1;

1603

1558

set foreign_key_checks=1;

1604

1559

drop table t2;

1605

1560

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

1561

# test that invalid datatype conversions with ALTER are not allowed

1618

1562

1619

1563

set foreign_key_checks=0;

1625

1568

set foreign_key_checks=1;

1626

1569

drop table t2,t1;

1627

1570

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

1571

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

1649

1572

1650

1573

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

1684

1606

1685

1607

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

1686

1608

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,

1609

create table t1 (a varchar(255),

1610

b varchar(255),

1611

c varchar(255),

1612

d varchar(255),

1691

1613

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

1692

1614

drop table t1;

1693

1615

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

1616

create table t1 (a varchar(255),

1617

b varchar(255),

1618

c varchar(255),

1619

d varchar(255),

1620

e varchar(255),

1699

1621

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

1700

1622

1701

1623

1702

1624

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

1703

1625

1704

1626

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

1705

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

1706

1627

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

1707

1628

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

1708

1629

1709

1630

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

1710

1631

-- error ER_DUP_ENTRY

1711

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

1712

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

1713

1632

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

1714

1633

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

1715

1634

-- error ER_DUP_ENTRY

1716

1635

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

1717

1636

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

1718

1637

select hex(s1) from t1;

1719

select hex(s1) from t2;

1720

1638

select hex(s1) from t3;

1721

1639

select hex(s1) from t4;

1722

drop table t1,t2,t3,t4;

1640

drop table t1,t3,t4;

1723

1641

1724

1642

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;

1643

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

1726

1644

1727

1645

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

1728

1646

insert into t2 values(0x42);

1729

1647

insert into t2 values(0x41);

1730

1648

select hex(s1) from t2;

1649

-- error 1451

1731

1650

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

1732

1651

select hex(s1) from t2;

1733

1652

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

1653

-- error 1406

1734

1654

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

1735

1655

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

1736

1656

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

1737

1657

select hex(s1) from t2;

1744

1659

select hex(s1) from t2;

1745

1660

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

1746

1661

select hex(s1) from t2;

1747

1662

delete from t1 where a=1;

1748

delete from t1 where a=2;

1749

1663

update t2 set s1=0x4120;

1750

1664

-- error 1451

1751

1665

delete from t1;

1786

1699

DROP TABLE t2,t1;

1787

1700

1788

1701

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

1702

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

1881

1703

# error message (bug #9680)

1882

1704

2200

2022

2201

2023

2202

2024

CREATE TABLE t1 (

2203

a BIGINT(20) NOT NULL,

2025

a BIGINT NOT NULL,

2204

2026

PRIMARY KEY (a)

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2027

) ENGINE=INNODB;

2206

2028

2207

2029

CREATE TABLE t2 (

2208

a BIGINT(20) NOT NULL,

2030

a BIGINT NOT NULL,

2209

2031

b VARCHAR(128) NOT NULL,

2210

2032

c TEXT NOT NULL,

2211

2033

PRIMARY KEY (a,b),

2212

2034

KEY idx_t2_b_c (b,c(200)),

2213

2035

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

2214

2036

ON DELETE CASCADE

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2037

) ENGINE=INNODB;

2216

2038

2217

2039

INSERT INTO t1 VALUES (1);

2218

2040

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

2307

2129

DROP TABLE t1,t2;

2308

2130

2309

2131

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

2132

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

2326

2133

2327

2134

DROP TABLE IF EXISTS t1;

2328

2135

CREATE TABLE t1(

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

2136

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2330

2137

) ENGINE=InnoDB;

2331

2138

INSERT INTO t1 VALUES(-10);

2332

2139

SELECT * FROM t1;

2340

2147

SELECT * FROM t1;

2341

2148

DROP TABLE t1;

2342

2149

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

2150

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

2386

2151

# #

2387

2152

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

Older »