~drizzle-trunk/drizzle/development : revision 1071

12

drop table if exists t1,t2,t3,t4;

13

--enable_warnings

14

15

CREATE TABLE t1 (

15

CREATE TEMPORARY TABLE t1 (

16

Period int DEFAULT 0 NOT NULL,

17

Varor_period int DEFAULT 0 NOT NULL

18

) ENGINE = CSV;

27

# Create test table

28

#

29

30

CREATE TABLE t2 (

30

CREATE TEMPORARY TABLE t2 (

31

auto int not null,

32

fld1 int DEFAULT 0 NOT NULL,

33

companynr int DEFAULT 0 NOT NULL,

1305

1306

1307

DROP TABLE t1;

1308

CREATE TABLE t1 (

1308

CREATE TEMPORARY TABLE t1 (

1309

Period int DEFAULT 0 NOT NULL,

1310

Varor_period int DEFAULT 0 NOT NULL

1311

) ENGINE = CSV;

1324

DROP TABLE IF EXISTS bug13894;

1325

--enable_warnings

1326

1327

CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV;

1327

CREATE TEMPORARY TABLE bug13894 ( val integer not null ) ENGINE = CSV;

1328

INSERT INTO bug13894 VALUES (5);

1329

INSERT INTO bug13894 VALUES (10);

1330

INSERT INTO bug13894 VALUES (11);

1342

DROP TABLE IF EXISTS bug14672;

1343

--enable_warnings

1344

1345

CREATE TABLE bug14672 (c1 integer not null) engine = CSV;

1345

CREATE TEMPORARY TABLE bug14672 (c1 integer not null) engine = CSV;

1346

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

1347

SELECT * FROM bug14672;

1348

DELETE FROM bug14672 WHERE c1 = 2;

1356

# End of 4.1 tests

1357

1358

#

1359

# Test REPAIR/CHECK TABLE (5.1)

1360

#

1361

1362

# Check that repair on the newly created table works fine

1363

1364

CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV;

1365

1366

CHECK TABLE test_repair_table;

1367

REPAIR TABLE test_repair_table;

1368

1369

DROP TABLE test_repair_table;

1370

1371

#

1372

# Check autorepair. Here we also check that we can work w/o metafile

1373

# restore the meta-file

1374

#

1375

1376

CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV;

1377

--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table2.CSM

1378

1379

# Should give a warning and perform autorepair. We also disable ps-protocol

1380

# here, as mysql-test eats up warnings in ps-protocol mode

1381

1382

--disable_ps_protocol

1383

SELECT * from test_repair_table2;

1384

--enable_ps_protocol

1385

# this should work ok, as the table is already repaired

1386

SELECT * from test_repair_table2;

1387

# check that the metafile appeared again.

1388

--file_exists $MYSQLTEST_VARDIR/master-data/test/test_repair_table2.CSM

1389

CHECK TABLE test_repair_table2;

1390

DROP TABLE test_repair_table2;

1391

1392

1393

# Corrupt csv file and see if we can repair it

1394

CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV;

1395

--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV

1396

--write_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV

1397

"1"

1398

"4"

1399

"3

1400

EOF

1401

CHECK TABLE test_repair_table3;

1402

REPAIR TABLE test_repair_table3;

1403

SELECT * FROM test_repair_table3;

1404

DROP TABLE test_repair_table3;

1405

1406

# Test with more sophisticated table

1407

1408

CREATE TABLE test_repair_table4 (

1409

num int not null,

1410

magic_no int DEFAULT 0 NOT NULL,

1411

company_name char(30) DEFAULT '' NOT NULL,

1412

founded char(4) DEFAULT '' NOT NULL

1413

) ENGINE = CSV;

1414

1415

--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table4.CSM

1416

--disable_ps_protocol

1417

SELECT * FROM test_repair_table4;

1418

--enable_ps_protocol

1419

SELECT * FROM test_repair_table4;

1420

CHECK TABLE test_repair_table4;

1421

1422

INSERT INTO test_repair_table4 VALUES (2,101,'SAP','1972');

1423

INSERT INTO test_repair_table4 VALUES (1,101,'Microsoft','1978');

1424

INSERT INTO test_repair_table4 VALUES (2,101,'MySQL','1995');

1425

1426

# list table content

1427

SELECT * FROM test_repair_table4;

1428

CHECK TABLE test_repair_table4;

1429

REPAIR TABLE test_repair_table4;

1430

# check that nothing changed

1431

SELECT * FROM test_repair_table4;

1432

# verify that check/repair did non corrupt the table itself

1433

CHECK TABLE test_repair_table4;

1434

REPAIR TABLE test_repair_table4;

1435

SELECT * FROM test_repair_table4;

1436

DROP TABLE test_repair_table4;

1437

1438

# Run CHECK/REPAIR on the CSV file with a single row, which misses a column.

1439

1440

CREATE TABLE test_repair_table5 (

1441

num int not null,

1442

magic_no int DEFAULT 0 NOT NULL,

1443

company_name char(30) DEFAULT '' NOT NULL,

1444

founded char(4) DEFAULT '' NOT NULL

1445

) ENGINE = CSV;

1446

1447

# Corrupt a table -- put a file with wrong # of columns

1448

--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table5.CSV

1449

--write_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table5.CSV

1450

"1","101","IBM"

1451

EOF

1452

1453

CHECK TABLE test_repair_table5;

1454

REPAIR TABLE test_repair_table5;

1455

SELECT * FROM test_repair_table5;

1456

INSERT INTO test_repair_table5 VALUES (1, 102, "CORRECT", 1876);

1457

SELECT * FROM test_repair_table5;

1458

1459

# Corrupt a table -- put a row with wrong # of columns at end of file

1460

--append_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table5.CSV

1461

"1","101","IBM"

1462

EOF

1463

1464

FLUSH TABLES;

1465

CHECK TABLE test_repair_table5;

1466

REPAIR TABLE test_repair_table5;

1467

# The correct record inserted should still be in the file

1468

SELECT * FROM test_repair_table5;

1469

INSERT INTO test_repair_table5 VALUES (1, 102, "CORRECT2", 1876);

1470

SELECT * FROM test_repair_table5;

1471

1472

# Corrupt table again -- put a row with wrong # of columns at end of file

1473

--append_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table5.CSV

1474

"1","101","IBM"

1475

EOF

1476

1477

FLUSH TABLES;

1478

CHECK TABLE test_repair_table5;

1479

REPAIR TABLE test_repair_table5;

1480

# The two correct records inserted should still be in the file

1481

SELECT * FROM test_repair_table5;

1482

DROP TABLE test_repair_table5;

1483

1484

#

1485

1359

# BUG#13406 - incorrect amount of "records deleted"

1486

1360

#

1487

1361

1488

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

1362

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

1489

1363

insert t1 values (1);

1490

1364

--enable_info

1491

1365

delete from t1; # delete_row

1513

1387

drop table t1;

1514

1388

1515

1389

#

1516

# Some additional tests for new, faster alter table. Note that most of the

1517

# whole alter table code is being tested all around the test suite already.

1518

#

1519

1520

create table t1 (v varchar(32) not null);

1521

insert into t1 values ('def'),('abc'),('hij'),('3r4f');

1522

select * from t1;

1523

# Fast alter, no copy performed

1524

alter table t1 change v v2 varchar(32);

1525

select * from t1;

1526

# Fast alter, no copy performed

1527

alter table t1 change v2 v varchar(64);

1528

select * from t1;

1529

update t1 set v = 'lmn' where v = 'hij';

1530

select * from t1;

1531

# Regular alter table

1532

alter table t1 add i int auto_increment not null primary key first;

1533

select * from t1;

1534

update t1 set i=5 where i=3;

1535

select * from t1;

1536

alter table t1 change i i bigint;

1537

select * from t1;

1538

alter table t1 add unique key (i, v);

1539

select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');

1540

drop table t1;

1541

1542

#

1543

1390

# Bug #15205 Select from CSV table without the datafile causes crash

1544

1391

#

1545

1392

# NOTE: the bug is not deterministic

1567

1414

#drop table bug15205;

1568

1415

#drop table bug15205_2;

1569

1416

1570

1571

#

1572

# Bug#22080 "CHECK fails to identify some corruption"

1573

#

1574

1575

create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV;

1576

create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV;

1577

create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV;

1578

insert into bug22080_1 values(1,'string');

1579

insert into bug22080_1 values(2,'string');

1580

insert into bug22080_1 values(3,'string');

1581

1582

# Create first corrupt file as described in bug report

1583

--remove_file $MYSQLTEST_VARDIR/master-data/test/bug22080_2.CSV

1584

--write_file $MYSQLTEST_VARDIR/master-data/test/bug22080_2.CSV

1585

1,"string"

1586

2","string"

1587

3,"string"

1588

EOF

1589

1590

# Create second corrupt file as described in bug report

1591

--remove_file $MYSQLTEST_VARDIR/master-data/test/bug22080_3.CSV

1592

--write_file $MYSQLTEST_VARDIR/master-data/test/bug22080_3.CSV

1593

1,"string"

1594

"2",string"

1595

3,"string"

1596

EOF

1597

1598

check table bug22080_2;

1599

1600

check table bug22080_3;

1601

1602

drop tables bug22080_1,bug22080_2,bug22080_3;

1603

1604

1417

#

1605

1418

# Testing float type

1606

1419

#

1607

create table float_test (id float not null,string varchar(64) not null) Engine=CSV;

1420

create temporary table float_test (id float not null,string varchar(64) not null) Engine=CSV;

1608

1421

insert into float_test values(1.0,'string');

1609

1422

insert into float_test values(2.23,'serg.g');

1610

1423

insert into float_test values(0.03,'string');

1618

1431

# Bug #21328 mysqld issues warnings on ALTER CSV table to MyISAM

1619

1432

#

1620

1433

1621

CREATE TABLE `bug21328` (

1434

CREATE TEMPORARY TABLE `bug21328` (

1622

1435

`col1` int NOT NULL,

1623

1436

`col2` int NOT NULL,

1624

1437

`col3` int NOT NULL

1632

1445

# BUG#28971 - ALTER TABLE followed by UPDATE for a CSV table make server

1633

1446

# crash

1634

1447

#

1635

create table t1(a blob not null, b int not null) engine=csv;

1448

create temporary table t1(a blob not null, b int not null) engine=csv;

1636

1449

insert into t1 values('a', 1);

1637

1450

flush tables;

1638

1451

update t1 set b=2;

1642

1455

#

1643

1456

# Bug #29353: negative values

1644

1457

#

1645

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

1458

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

1646

1459

insert into t1 values(-1), (-123.34), (2), (-23);

1647

1460

select * from t1;

1648

1461

check table t1;

1649

1462

drop table t1;

1650

1463

1651

create table t1(a int not null, b int not null) engine=csv;

1652

--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV

1653

--write_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV

1654

1, 1E-2

1655

-2E2, .9

1656

-10E-1, -.9

1657

-1, -100.1

1658

1a, -2b

1659

EOF

1660

repair table t1;

1661

check table t1;

1662

select * from t1;

1663

check table t1;

1664

drop table t1;

1665

1666

1464

#

1667

1465

# Bug #29411: deleting from a csv table leads to the table corruption

1668

1466

#

1669

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

1467

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

1670

1468

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

1671

1469

delete from t1 limit 2;

1672

1470

check table t1;

1679

1477

#

1680

1478

# Bug #31473: does not work with NULL value in datetime field

1681

1479

#

1682

create table t1(a datetime not null) engine=csv;

1683

--error 1364

1684

insert into t1 values();

1685

select * from t1;

1686

drop table t1;

1687

create table t1(a varchar(32) not null) engine=csv;

1688

--error 1364

1689

insert into t1 values();

1690

select * from t1;

1691

drop table t1;

1692

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

1693

--error 1364

1694

insert into t1 values();

1695

select * from t1;

1696

drop table t1;

1697

create table t1(a blob not null) engine=csv;

1480

create temporary table t1(a datetime not null) engine=csv;

1481

--error 1364

1482

insert into t1 values();

1483

select * from t1;

1484

drop table t1;

1485

create temporary table t1(a varchar(32) not null) engine=csv;

1486

--error 1364

1487

insert into t1 values();

1488

select * from t1;

1489

drop table t1;

1490

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

1491

--error 1364

1492

insert into t1 values();

1493

select * from t1;

1494

drop table t1;

1495

create temporary table t1(a blob not null) engine=csv;

1698

1496

--error 1364

1699

1497

insert into t1 values();

1700

1498

select * from t1;

1701

1499

drop table t1;

1702

1500

# We prevent creation of table with nullable ENUM

1703

1501

--error ER_CHECK_NOT_IMPLEMENTED

1704

create table t1(a enum('foo','bar') default null) engine=csv;

1502

create temporary table t1(a enum('foo','bar') default null) engine=csv;

1705

1503

--error ER_CHECK_NOT_IMPLEMENTED

1706

create table t1(a enum('foo','bar') default 'foo') engine=csv;

1504

create temporary table t1(a enum('foo','bar') default 'foo') engine=csv;

1707

1505

# Enum columns must be specified as NOT NULL

1708

create table t1(a enum('foo','bar') default 'foo' not null) engine=csv;

1506

create temporary table t1(a enum('foo','bar') default 'foo' not null) engine=csv;

1709

1507

insert into t1 values();

1710

1508

select * from t1;

1711

1509

drop table t1;

1715

1513

# with error 1005.

1716

1514

#

1717

1515

--error ER_CHECK_NOT_IMPLEMENTED

1718

CREATE TABLE t1(a INT) ENGINE=CSV;

1516

CREATE TEMPORARY TABLE t1(a INT) ENGINE=CSV;

1719

1517

SHOW WARNINGS;

1720

1518

1721

1519

#

1722

1520

# BUG#33067 - .

1723

1521

#

1724

create table t1 (c1 blob not null) engine=csv;

1522

create temporary table t1 (c1 blob not null) engine=csv;

1725

1523

insert into t1 values("This");

1726

1524

--enable_info

1727

1525

update t1 set c1="That" where c1="This";

1738

1536

drop table if exists t1;

1739

1537

--enable_warnings

1740

1538

1741

create table t1 (val int not null) engine=csv;

1742

--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV

1743

1744

--error 1017

1745

select * from t1;

1746

1747

1539

--echo End of 5.1 tests