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

920

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

921

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;

922

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

923

insert into `t3`values ( 1 ) ;

984

924

--error 1451

985

925

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

1010

950

# Test timestamps

1011

951

1012

952

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;

953

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

954

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

1016

955

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

1017

956

# Note that timestamp 3 is wrong

1018

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

957

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

1019

958

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

1020

959

'20020204120000' GROUP BY col1;

1021

960

drop table t1,t2;

1025

964

1026

965

1027

966

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

967

`id` int NOT NULL auto_increment,

968

`id_object` int default '0',

969

`id_version` int NOT NULL default '1',

1031

970

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

1032

971

`description` text,

1033

972

PRIMARY KEY (`id`),

1038

977

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

978

1040

979

CREATE TABLE t2 (

1041

`id` int(10) NOT NULL auto_increment,

1042

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

980

`id` int NOT NULL auto_increment,

981

`id_version` int NOT NULL default '1',

1043

982

PRIMARY KEY (`id`),

1044

983

KEY `id_version` (`id_version`)

1045

984

) ENGINE=InnoDB;

1083

1022

# Test of automaticly created foreign keys

1084

1023

1085

1024

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;

1025

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

1026

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

1088

1027

show create table t1;

1089

1028

show create table t2;

1090

1029

create index id on t2 (id);

1097

1036

show create table t2;

1098

1037

drop table t2;

1099

1038

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;

1039

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

1040

show create table t2;

1102

1041

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

1103

1042

show create table t2;

1104

1043

drop table t2;

1105

1044

1106

1045

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

1046

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;

1047

show create table t2;

1048

drop table t2;

1049

1050

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;

1051

show create table t2;

1052

drop table t2;

1053

1054

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;

1055

show create table t2;

1056

drop table t2;

1057

1058

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;

1059

show create table t2;

1060

drop table t2;

1061

1062

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

1063

show create table t2;

1125

1064

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

1126

1065

show create table t2;

1131

1070

# Embedded server doesn't chdir to data directory

1132

1071

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

1133

1072

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

1073

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

1074

1136

1075

# bug#3749

1137

1076

1169

1108

# Bug #4082: integer truncation

1170

1109

1171

1110

1172

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

1111

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

1173

1112

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

1174

1113

select distinct concat(a, b) from t1;

1175

1114

drop table t1;

1176

1115

1177

1116

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

1117

# check null values #1

1189

1118

1190

1119

1191

1120

--disable_warnings

1192

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

1121

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

1193

1122

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;

1123

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

1124

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

1196

1125

--enable_warnings

1197

1126

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

1198

1127

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

1173

1245

1174

1246

1175

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

1247

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

1176

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

1248

1177

select * from t1;

1249

1178

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

1250

1179

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

1254

1183

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

1255

1184

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

1256

1185

select * from t1 where x > -16;

1257

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

1186

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

1258

1187

drop table t1;

1259

1188

1260

1189

1190

## Not deterministic.

1261

1191

# Test for testable InnoDB status variables. This test

1262

1192

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

1193

#show status like "Innodb_buffer_pool_pages_total";

1194

#show status like "Innodb_page_size";

1195

#show status like "Innodb_rows_deleted";

1196

#show status like "Innodb_rows_inserted";

1197

#show status like "Innodb_rows_updated";

1268

1198

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

1199

## Test for row locks InnoDB status variables.

1200

#show status like "Innodb_row_lock_waits";

1201

#show status like "Innodb_row_lock_current_waits";

1202

#show status like "Innodb_row_lock_time";

1203

#show status like "Innodb_row_lock_time_max";

1204

#show status like "Innodb_row_lock_time_avg";

1275

1205

1276

1206

# Test for innodb_sync_spin_loops variable

1277

1207

show variables like "innodb_sync_spin_loops";

1323

1253

1324

1254

# Embedded server doesn't chdir to data directory

1325

1255

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

1326

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

1256

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

1327

1257

drop table t1;

1328

create table t1 (v varchar(65536));

1258

create table t1 (v varchar(16383));

1329

1259

show create table t1;

1330

1260

drop table t1;

1331

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

1261

create table t1 (v varchar(16383));

1332

1262

show create table t1;

1333

1263

drop table t1;

1334

1264

1335

1265

eval set storage_engine=$default;

1336

1266

1337

1267

# InnoDB specific varchar tests

1338

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

1268

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

1339

1269

drop table t1;

1340

1270

1341

1271

1352

1282

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

1353

1283

1354

1284

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;

1285

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

1356

1286

insert into t1 (b) values (1);

1357

1287

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

1358

1288

select * from t1;

1457

1387

1458

1388

# prefix index

1459

1389

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

1460

character set = latin1 engine = innodb;

1390

engine = innodb;

1461

1391

1462

1392

# normal indexes

1463

1393

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;

1394

engine = innodb;

1467

1395

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;

1396

engine = innodb;

1397

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

1398

engine = innodb;

1399

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

1400

engine = innodb;

1473

1401

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

1474

character set = latin1 engine = innodb;

1402

engine = innodb;

1475

1403

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

1476

character set = latin1 engine = innodb;

1404

engine = innodb;

1477

1405

1478

1406

# multi-column indexes are allowed to be longer

1479

1407

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

1480

character set = latin1 engine = innodb;

1408

engine = innodb;

1481

1409

1482

1410

show create table t9;

1483

1411

1484

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

1412

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

1485

1413

1486

1414

# these should have their index length trimmed

1487

1415

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

1488

character set = latin1 engine = innodb;

1416

engine = innodb;

1489

1417

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

1490

character set = latin1 engine = innodb;

1418

engine = innodb;

1491

1419

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

1492

character set = latin1 engine = innodb;

1420

engine = innodb;

1493

1421

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

1494

character set = latin1 engine = innodb;

1422

engine = innodb;

1495

1423

1496

1424

show create table t1;

1497

1425

1500

1428

# these should be refused

1501

1429

--error 1071

1502

1430

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

1503

character set = latin1 engine = innodb;

1431

engine = innodb;

1504

1432

--error 1071

1505

1433

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

1506

character set = latin1 engine = innodb;

1434

engine = innodb;

1507

1435

--error 1071

1508

1436

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

1509

character set = latin1 engine = innodb;

1437

engine = innodb;

1510

1438

--error 1071

1511

1439

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

1512

character set = latin1 engine = innodb;

1440

engine = innodb;

1513

1441

1514

1442

1515

1443

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

1552

1480

connect (a,localhost,root,,);

1553

1481

connect (b,localhost,root,,);

1554

1482

connection a;

1555

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

1483

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

1556

1484

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

1557

1485

set autocommit=0;

1558

1486

checksum table t1;

1572

1500

# autocommit = 1

1573

1501

1574

1502

connection a;

1575

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

1503

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

1576

1504

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

1577

1505

set autocommit=1;

1578

1506

checksum table t1;

1603

1531

set foreign_key_checks=1;

1604

1532

drop table t2;

1605

1533

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

1534

# test that invalid datatype conversions with ALTER are not allowed

1618

1535

1619

1536

set foreign_key_checks=0;

1625

1541

set foreign_key_checks=1;

1626

1542

drop table t2,t1;

1627

1543

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

1544

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

1649

1545

1650

1546

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

1684

1579

1685

1580

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

1686

1581

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,

1582

create table t1 (a varchar(255),

1583

b varchar(255),

1584

c varchar(255),

1585

d varchar(255),

1691

1586

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

1692

1587

drop table t1;

1693

1588

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

1589

create table t1 (a varchar(255),

1590

b varchar(255),

1591

c varchar(255),

1592

d varchar(255),

1593

e varchar(255),

1699

1594

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

1700

1595

1701

1596

1702

1597

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

1703

1598

1704

1599

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

1705

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

1706

1600

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

1707

1601

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

1708

1602

1709

1603

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

1710

1604

-- error ER_DUP_ENTRY

1711

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

1712

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

1713

1605

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

1714

1606

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

1715

1607

-- error ER_DUP_ENTRY

1716

1608

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

1717

1609

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

1718

1610

select hex(s1) from t1;

1719

select hex(s1) from t2;

1720

1611

select hex(s1) from t3;

1721

1612

select hex(s1) from t4;

1722

drop table t1,t2,t3,t4;

1613

drop table t1,t3,t4;

1723

1614

1724

1615

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;

1616

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

1726

1617

1727

1618

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

1728

1619

insert into t2 values(0x42);

1729

1620

insert into t2 values(0x41);

1730

1621

select hex(s1) from t2;

1622

-- error 1451

1731

1623

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

1732

1624

select hex(s1) from t2;

1733

1625

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

1626

-- error 1406

1734

1627

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

1735

1628

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

1736

1629

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

1737

1630

select hex(s1) from t2;

1744

1632

select hex(s1) from t2;

1745

1633

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

1746

1634

select hex(s1) from t2;

1747

1635

delete from t1 where a=1;

1748

delete from t1 where a=2;

1749

1636

update t2 set s1=0x4120;

1750

1637

-- error 1451

1751

1638

delete from t1;

1786

1672

DROP TABLE t2,t1;

1787

1673

1788

1674

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

1675

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

1881

1676

# error message (bug #9680)

1882

1677

2200

1995

2201

1996

2202

1997

CREATE TABLE t1 (

2203

a BIGINT(20) NOT NULL,

1998

a BIGINT NOT NULL,

2204

1999

PRIMARY KEY (a)

2205

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2000

) ENGINE=INNODB;

2206

2001

2207

2002

CREATE TABLE t2 (

2208

a BIGINT(20) NOT NULL,

2003

a BIGINT NOT NULL,

2209

2004

b VARCHAR(128) NOT NULL,

2210

2005

c TEXT NOT NULL,

2211

2006

PRIMARY KEY (a,b),

2212

2007

KEY idx_t2_b_c (b,c(200)),

2213

2008

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

2214

2009

ON DELETE CASCADE

2215

) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2010

) ENGINE=INNODB;

2216

2011

2217

2012

INSERT INTO t1 VALUES (1);

2218

2013

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

2294

2089

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

2295

2090

AUTO_INCREMENT=42;

2296

2091

2297

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

2092

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

2298

2093

SELECT * FROM t1;

2299

2094

2300

2095

SHOW CREATE TABLE t1;

2307

2102

DROP TABLE t1,t2;

2308

2103

2309

2104

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

2105

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

2326

2106

2327

2107

DROP TABLE IF EXISTS t1;

2328

2108

CREATE TABLE t1(

2329

id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY

2109

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

2330

2110

) ENGINE=InnoDB;

2331

2111

INSERT INTO t1 VALUES(-10);

2332

2112

SELECT * FROM t1;

2340

2120

SELECT * FROM t1;

2341

2121

DROP TABLE t1;

2342

2122

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

2123

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

2386

2124

# #

2387

2125

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

Older »