~drizzle-trunk/drizzle/development

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

897

896

insert into `t3`values ( 1 ) ;

898

--error ER_ROW_IS_REFERENCED_2

897

--error 1451

899

898

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

900

--error ER_ROW_IS_REFERENCED_2

899

--error 1451

901

900

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;

902

--error ER_BAD_FIELD_ERROR

901

--error 1054

903

902

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

904

903

drop table t3,t2,t1;

905

904

913

912

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

914

913

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

915

914

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

916

--error ER_ROW_IS_REFERENCED_2

915

-- error 1451

917

916

delete from t1 where id=0;

918

917

delete from t1 where id=15;

919

918

delete from t1 where id=0;

930

929

eval create $temp table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

931

930

(stamp))ENGINE=$engine_type;

932

931

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

933

insert into t2 values (1, 20020204130000);

934

insert into t2 values (2, 20020204130000);

935

--error ER_INVALID_TIMESTAMP_VALUE # Bad timestamp

936

insert into t2 values (4, 20020204310000);

937

insert into t2 values (5, 20020204230000);

938

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1;

932

# Note that timestamp 3 is wrong

933

--error 1685 # Bad timestamp

934

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

935

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

936

'20020204120000' GROUP BY col1;

939

937

drop table t1,t2;

940

938

941

939

1011

1009

# Bug#2160: Extra error message for create $temp table LIKE with InnoDB

1012

1010

1013

1011

eval create $temp table t1 (a int) engine=$engine_type;

1014

--error ER_CANT_CREATE_TABLE

1012

--error 1005

1015

1013

create table t2 like t1;

1016

1014

create table t2 like t1 engine=innodb;

1017

1015

show create table t2;

1032

1030

create index id2 on t2 (id);

1033

1031

show create table t2;

1034

1032

drop index id2 on t2;

1035

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1033

--error 1025,1025

1036

1034

drop index id on t2;

1037

1035

show create table t2;

1038

1036

drop table t2;

1071

1069

# Clean up filename -- embedded server reports whole path without .frm,

1072

1070

# regular server reports relative path with .frm (argh!)

1073

1071

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1074

--error ER_CANT_CREATE_TABLE

1072

--error 1005

1075

1073

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

1076

1074

1077

1075

# bug#3749

1124

1122

1125

1123

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

1126

1124

1127

--error ER_DUP_FIELDNAME

1125

--error 1060

1128

1126

eval create $temp table t1 (c char(10), index (c,c)) engine=$engine_type;

1129

--error ER_DUP_FIELDNAME

1127

--error 1060

1130

1128

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1131

--error ER_DUP_FIELDNAME

1129

--error 1060

1132

1130

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1133

--error ER_DUP_FIELDNAME

1131

--error 1060

1134

1132

eval create $temp table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1135

1133

eval create $temp table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1136

--error ER_DUP_FIELDNAME

1134

--error 1060

1137

1135

alter table t1 add key (c1,c1);

1138

--error ER_DUP_FIELDNAME

1136

--error 1060

1139

1137

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

1140

--error ER_DUP_FIELDNAME

1138

--error 1060

1141

1139

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

1142

--error ER_DUP_FIELDNAME

1140

--error 1060

1143

1141

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

1144

1142

drop table t1;

1145

1143

1160

1158

1161

1159

1162

1160

eval create $temp table t1 ( a char(10) ) ENGINE=$engine_type;

1163

--error ER_TABLE_CANT_HANDLE_FT

1161

--error 1214

1164

1162

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1165

1163

DROP TABLE t1;

1166

1164

}

1229

1227

1230

1228

eval create $temp table t1 (x bigint not null primary key) engine=$engine_type;

1231

1229

# The below is out of range for a BIGINT (signed)

1232

--error ER_WARN_DATA_OUT_OF_RANGE

1230

--error 1264

1233

1231

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

1234

1232

select * from t1;

1235

1233

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

1435

1433

id INT PRIMARY KEY

1436

1434

) ENGINE=$engine_type;

1437

1435

1438

--error ER_CANT_CREATE_TABLE, ER_CANT_CREATE_TABLE

1436

--error 1005,1005

1439

1437

eval CREATE TEMPORARY TABLE t2

1440

1438

(

1441

1439

id INT NOT NULL PRIMARY KEY,

1509

1507

{

1510

1508

1511

1509

# these should be refused

1512

--error ER_TOO_LONG_KEY

1510

--error 1071

1513

1511

eval create $temp table t1 (col1 varchar(768) primary key)

1514

1512

character set = latin1 engine = $engine_type;

1515

--error ER_TOO_LONG_KEY

1513

--error 1071

1516

1514

eval create $temp table t2 (col1 varbinary(768) primary key)

1517

1515

character set = latin1 engine = $engine_type;

1518

--error ER_TOO_LONG_KEY

1516

--error 1071

1519

1517

eval create $temp table t3 (col1 text, primary key(col1(768)))

1520

1518

character set = latin1 engine = $engine_type;

1521

--error ER_TOO_LONG_KEY

1519

--error 1071

1522

1520

eval create $temp table t4 (col1 blob, primary key(col1(768)))

1523

1521

character set = latin1 engine = $engine_type;

1524

1522

1541

1539

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

1542

1540

) ENGINE=$engine_type;

1543

1541

1544

--error ER_NO_REFERENCED_ROW_2

1542

--error 1452

1545

1543

INSERT INTO t2 VALUES(2);

1546

1544

1547

1545

INSERT INTO t1 VALUES(1);

1548

1546

INSERT INTO t2 VALUES(1);

1549

1547

1550

--error ER_ROW_IS_REFERENCED_2

1548

--error 1451

1551

1549

DELETE FROM t1 WHERE id = 1;

1552

1550

1553

--error ER_ROW_IS_REFERENCED

1551

--error 1217

1554

1552

DROP TABLE t1;

1555

1553

1556

1554

SET FOREIGN_KEY_CHECKS=0;

1557

1555

DROP TABLE t1;

1558

1556

SET FOREIGN_KEY_CHECKS=1;

1559

1557

1560

--error ER_NO_REFERENCED_ROW_2

1558

--error 1452

1561

1559

INSERT INTO t2 VALUES(3);

1562

1560

1563

1561

DROP TABLE t2;

1573

1571

set foreign_key_checks=0;

1574

1572

eval create $temp table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type;

1575

1573

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

1576

--error ER_CANT_CREATE_TABLE

1574

-- error 1005

1577

1575

eval create $temp table t1(a char(10) primary key, b varchar(20)) engine = $engine_type;

1578

1576

set foreign_key_checks=1;

1579

1577

drop table t2;

1584

1582

set foreign_key_checks=0;

1585

1583

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1586

1584

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

1587

--error ER_CANT_CREATE_TABLE

1585

-- error 1005

1588

1586

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1589

1587

set foreign_key_checks=1;

1590

1588

drop table t1;

1594

1592

set foreign_key_checks=0;

1595

1593

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type;

1596

1594

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type;

1597

--error ER_ERROR_ON_RENAME, ER_ERROR_ON_RENAME

1595

-- error 1025,1025

1598

1596

alter table t1 modify column a int;

1599

1597

set foreign_key_checks=1;

1600

1598

drop table t2,t1;

1614

1612

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1615

1613

eval create $temp table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1616

1614

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

1617

--error ER_ERROR_ON_RENAME

1615

-- error 1025

1618

1616

rename table t3 to t1;

1619

1617

set foreign_key_checks=1;

1620

1618

drop table t2,t3;

1628

1626

1629

1627

insert into t1 values(1);

1630

1628

insert into t3 values(1);

1631

--error ER_NO_REFERENCED_ROW_2

1629

-- error 1452

1632

1630

insert into t2 values(2);

1633

--error ER_NO_REFERENCED_ROW_2

1631

-- error 1452

1634

1632

insert into t4 values(2);

1635

1633

insert into t2 values(1);

1636

1634

insert into t4 values(1);

1637

--error ER_ROW_IS_REFERENCED_2

1635

-- error 1451

1638

1636

update t1 set a=2;

1639

--error ER_NO_REFERENCED_ROW_2

1637

-- error 1452

1640

1638

update t2 set a=2;

1641

--error ER_ROW_IS_REFERENCED_2

1639

-- error 1451

1642

1640

update t3 set a=2;

1643

--error ER_NO_REFERENCED_ROW_2

1641

-- error 1452

1644

1642

update t4 set a=2;

1645

--error ER_ROW_IS_REFERENCED_2

1643

-- error 1451

1646

1644

truncate t1;

1647

--error ER_ROW_IS_REFERENCED_2

1645

-- error 1451

1648

1646

truncate t3;

1649

1647

truncate t2;

1650

1648

truncate t4;

1688

1686

eval create $temp table t4 (s1 char(2),primary key (s1)) engine=$engine_type;

1689

1687

1690

1688

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

1691

--error ER_DUP_ENTRY

1689

-- error ER_DUP_ENTRY

1692

1690

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

1693

1691

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

1694

--error ER_DUP_ENTRY

1692

-- error ER_DUP_ENTRY

1695

1693

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

1696

1694

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

1697

--error ER_DUP_ENTRY

1695

-- error ER_DUP_ENTRY

1698

1696

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

1699

1697

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

1700

1698

select hex(s1) from t1;

1710

1708

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

1711

1709

1712

1710

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

1713

--error ER_NO_REFERENCED_ROW_2

1711

-- error 1452

1714

1712

insert into t2 values(0x42);

1715

1713

insert into t2 values(0x41);

1716

1714

select hex(s1) from t2;

1717

1715

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

1718

1716

select hex(s1) from t2;

1719

--error ER_ROW_IS_REFERENCED_2

1717

-- error 1451

1720

1718

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

1721

--error ER_ROW_IS_REFERENCED_2

1719

-- error 1451

1722

1720

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

1723

--error ER_ROW_IS_REFERENCED_2

1721

-- error 1451

1724

1722

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

1725

1723

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

1726

1724

select hex(s1) from t2;

1728

1726

select hex(s1) from t2;

1729

1727

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

1730

1728

select hex(s1) from t2;

1731

--error ER_ROW_IS_REFERENCED_2

1729

-- error 1451

1732

1730

delete from t1 where a=1;

1733

1731

delete from t1 where a=2;

1734

1732

update t2 set s1=0x4120;

1735

--error ER_ROW_IS_REFERENCED_2

1733

-- error 1451

1736

1734

delete from t1;

1737

1735

delete from t1 where a!=3;

1738

1736

select a,hex(s1) from t1;

1751

1749

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

1752

1750

select hex(s1) from t2;

1753

1751

delete from t1 where a=1;

1754

--error ER_ROW_IS_REFERENCED_2

1752

-- error 1451

1755

1753

delete from t1 where a=2;

1756

1754

select a,hex(s1) from t1;

1757

1755

select hex(s1) from t2;

1909

1907

1910

1908

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

1911

1909

1912

--error ER_XXXLOCK_WAIT_TIMEOUTTTT

1910

--error 1205

1913

1911

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

1914

1912

1915

1913

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

1916

1914

1917

--error ER_LOCK_WAIT_TIMEOUT

1915

--error 1205

1918

1916

connection a;

1919

1917

commit;

1920

1918

connection b;

2054

2052

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

2055

2053

2056

2054

connection b;

2057

--error ER_LOCK_WAIT_TIMEOUT

2055

--error 1205

2058

2056

reap;

2059

2057

2060

2058

connection c;

2061

--error ER_LOCK_WAIT_TIMEOUT

2059

--error 1205

2062

2060

reap;

2063

2061

2064

2062

connection d;

2065

--error ER_LOCK_WAIT_TIMEOUT

2063

--error 1205

2066

2064

reap;

2067

2065

2068

2066

connection e;

2069

--error ER_LOCK_WAIT_TIMEOUT

2067

--error 1205

2070

2068

reap;

2071

2069

2072

2070

connection f;

2073

--error ER_LOCK_WAIT_TIMEOUT

2071

--error 1205

2074

2072

reap;

2075

2073

2076

2074

connection g;

2077

--error ER_LOCK_WAIT_TIMEOUT

2075

--error 1205

2078

2076

reap;

2079

2077

2080

2078

connection h;

2081

--error ER_LOCK_WAIT_TIMEOUT

2079

--error 1205

2082

2080

reap;

2083

2081

2084

2082

connection i;

2085

--error ER_LOCK_WAIT_TIMEOUT

2083

--error 1205

2086

2084

reap;

2087

2085

2088

2086

connection j;

2089

--error ER_LOCK_WAIT_TIMEOUT

2087

--error 1205

2090

2088

reap;

2091

2089

2092

2090

connection a;

2110

2108

if (test_foreign_keys)

2111

2109

{

2112

2110

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

2113

--error ER_CANT_CREATE_TABLE

2111

--error 1005

2114

2112

eval create $temp table t1 (DB_ROW_ID int) engine=$engine_type;

2115

2113

2116

2114

2164

2162

eval create $temp table t1 ( a int ) ENGINE=$engine_type;

2165

2163

BEGIN;

2166

2164

INSERT INTO t1 VALUES (1);

2167

COMMIT;

2168

2165

eval ALTER TABLE t1 ENGINE=$engine_type;

2169

2166

DROP TABLE t1;

2170

2167

Older »