~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

INSERT INTO t2 VALUES (1,000001,00,'Omaha','teethe','neat','') , (2,011401,37,'breaking','dreaded','Steinberg','W') , (3,011402,37,'Romans','scholastics','jarring','') , (4,011403,37,'intercepted','audiology','tinily','');

1333

SELECT * FROM t2;

1334

1335

1336

# For bug #12836

1337

# Delete was allowing all rows to be removed

1338

--error 1031

1339

DELETE FROM t2;

1340

SELECT * FROM t2;

1341

INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W');

1342

INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring','');

1343

INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily','');

1344

SELECT * FROM t2;

1345

--error 1031

1346

TRUNCATE TABLE t2;

1347

SELECT * FROM t2;

1348

1349

# Adding support for CHECK table

1350

CHECK TABLE t2;

1351

SELECT * FROM t2;

1352

1353

1354

# Test INSERT DELAYED and wait until the table has one more record

1355

SELECT COUNT(auto) FROM t2;

1356

INSERT DELAYED INTO t2 VALUES (99999,011403,37,'the','delayed','insert','');

1357

1358

# Insert another record since in Archive delayed values are only

1359

# guaranteed to materialize based on either:

1360

# 1) A new row showing up from a normal insert

1361

# 2) A flush table has occurred.

1362

INSERT INTO t2 VALUES (100000,000001,00,'after','delayed','insert','');

1363

1364

# Wait for the delayed insert to appear

1365

while (`SELECT COUNT(auto)!=1215 FROM t2`)

1366

{

1367

sleep 0.1;

1368

}

1369

SELECT COUNT(auto) FROM t2;

1370

1371

# Adding test for ALTER TABLE

1372

ALTER TABLE t2 DROP COLUMN fld6;

1373

SHOW CREATE TABLE t2;

1374

SELECT * FROM t2 WHERE auto != 100000;

1375

1376

1377

# Adding tests for autoincrement

1378

# First the simple stuff

1379

1380

CREATE TABLE `t5` (

1381

`a` int(11) NOT NULL auto_increment,

1382

b char(12),

1383

PRIMARY KEY (`a`)

1384

) DEFAULT CHARSET=latin1;

1385

1386

INSERT INTO t5 VALUES (NULL, "foo");

1387

INSERT INTO t5 VALUES (NULL, "foo");

1388

INSERT INTO t5 VALUES (NULL, "foo");

1389

INSERT INTO t5 VALUES (NULL, "foo");

1390

INSERT INTO t5 VALUES (NULL, "foo");

1391

INSERT INTO t5 VALUES (32, "foo");

1392

--error 1022

1393

INSERT INTO t5 VALUES (23, "foo");

1394

INSERT INTO t5 VALUES (NULL, "foo");

1395

INSERT INTO t5 VALUES (NULL, "foo");

1396

--error 1022

1397

INSERT INTO t5 VALUES (3, "foo");

1398

INSERT INTO t5 VALUES (NULL, "foo");

1399

SELECT * FROM t5;

1400

1401

SELECT * FROM t5 WHERE a=3;

1402

1403

DROP TABLE t5;

1404

1405

CREATE TABLE `t5` (

1406

`a` int(11) NOT NULL auto_increment,

1407

b char(12),

1408

KEY (`a`)

1409

) DEFAULT CHARSET=latin1 AUTO_INCREMENT=5;

1410

1411

INSERT INTO t5 VALUES (NULL, "foo");

1412

INSERT INTO t5 VALUES (NULL, "foo");

1413

INSERT INTO t5 VALUES (NULL, "foo");

1414

INSERT INTO t5 VALUES (NULL, "foo");

1415

INSERT INTO t5 VALUES (NULL, "foo");

1416

INSERT INTO t5 VALUES (32, "foo");

1417

INSERT INTO t5 VALUES (23, "foo");

1418

INSERT INTO t5 VALUES (NULL, "foo");

1419

INSERT INTO t5 VALUES (NULL, "foo");

1420

INSERT INTO t5 VALUES (3, "foo");

1421

INSERT INTO t5 VALUES (NULL, "foo");

1422

SELECT * FROM t5;

1423

OPTIMIZE TABLE t5;

1424

SELECT * FROM t5;

1425

1426

SELECT * FROM t5 WHERE a=32;

1427

SELECT * FROM t5 WHERE a=3;

1428

1429

DROP TABLE t5;

1430

1431

CREATE TABLE `t5` (

1432

`a` int(11) NOT NULL auto_increment,

1433

b blob(12),

1434

KEY (`a`)

1435

) DEFAULT CHARSET=latin1;

1436

1437

INSERT INTO t5 VALUES (NULL, "foo");

1438

INSERT INTO t5 VALUES (NULL, "We the people");

1439

INSERT INTO t5 VALUES (NULL, "in order to form a more pefect union");

1440

INSERT INTO t5 VALUES (NULL, "establish justice");

1441

INSERT INTO t5 VALUES (NULL, "foo grok ");

1442

INSERT INTO t5 VALUES (32, "ensure domestic tranquility");

1443

INSERT INTO t5 VALUES (23, "provide for the common defense");

1444

INSERT INTO t5 VALUES (NULL, "fo fooo");

1445

INSERT INTO t5 VALUES (NULL, "foo just naother bit of text");

1446

INSERT INTO t5 VALUES (3, "foo this is mine to think about");

1447

INSERT INTO t5 VALUES (NULL, "promote the general welfare");

1448

SELECT * FROM t5;

1449

SELECT b FROM t5;

1450

SELECT b FROM t5 WHERE a =3;

1451

SELECT b FROM t5 WHERE a IN (32, 23, 5);

1452

1453

1454

#More blob tests

1455

1456

DROP TABLE t5;

1457

1458

CREATE TABLE `t5` (

1459

`a` int(11) NOT NULL auto_increment,

1460

b blob(12),

1461

c blob(12),

1462

KEY (`a`)

1463

) DEFAULT CHARSET=latin1;

1464

1465

INSERT INTO t5 VALUES (NULL, "foo", "grok this!");

1466

INSERT INTO t5 VALUES (NULL, "We the people", NULL);

1467

INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty");

1468

INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and");

1469

INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL);

1470

INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity");

1471

INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain");

1472

SELECT * FROM t5;

1473

SELECT b FROM t5;

1474

SELECT b FROM t5 WHERE a =3;

1475

SELECT b FROM t5 WHERE a IN (32, 23, 5);

1476

SELECT c FROM t5;

1477

SELECT c FROM t5 WHERE a =3;

1478

SELECT c FROM t5 WHERE a IN (32, 23, 5);

1479

1480

# Adding this in case someone tries to add fast ALTER TABLE and doesn't tes

1481

# it.

1482

# Some additional tests for new, faster ALTER TABLE. Note that most of the

1483

# whole ALTER TABLE code is being tested all around the test suite already.

1484

1485

1486

DROP TABLE t1;

1487

CREATE TABLE t1 (v varchar(32)) ;

1488

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

1489

select * from t1;

1490

# Fast alter, no copy performed

1491

ALTER TABLE t1 change v v2 varchar(32);

1492

select * from t1;

1493

# Fast alter, no copy performed

1494

ALTER TABLE t1 change v2 v varchar(64);

1495

select * from t1;

1496

# Regular ALTER TABLE

1497

ALTER TABLE t1 add i int auto_increment not null primary key first;

1498

select * from t1;

1499

1500

# Testing cleared row key

1501

DROP TABLE t5;

1502

1503

CREATE TABLE `t5` (

1504

`a` int(11) NOT NULL auto_increment,

1505

b varchar(250),

1506

c varchar(800),

1507

KEY (`a`)

1508

) DEFAULT CHARSET=latin1;

1509

1510

INSERT INTO t5 VALUES (NULL, "foo", "grok this!");

1511

INSERT INTO t5 VALUES (NULL, "We the people", NULL);

1512

INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty");

1513

INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and");

1514

INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL);

1515

INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity");

1516

INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain");

1517

INSERT INTO t5 VALUES (NULL, "abcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabc", "do ordain");

1518

1519

SELECT * FROM t5;

1520

1521

CREATE TABLE `t6` (

1522

`a` int(11) NOT NULL auto_increment,

1523

b blob(12),

1524

c int,

1525

KEY (`a`)

1526

) DEFAULT CHARSET=latin1;

1527

SELECT * FROM t6;

1528

INSERT INTO t6 VALUES (NULL, "foo", NULL);

1529

INSERT INTO t6 VALUES (NULL, "We the people", 5);

1530

INSERT INTO t6 VALUES (NULL, "in order to form a more pefect union", 9);

1531

INSERT INTO t6 VALUES (NULL, "establish justice", NULL);

1532

INSERT INTO t6 VALUES (NULL, NULL, NULL);

1533

INSERT INTO t6 VALUES (32, "ensure domestic tranquility", NULL);

1534

INSERT INTO t6 VALUES (23, "provide for the common defense", 30);

1535

INSERT INTO t6 VALUES (NULL, "fo fooo", 70);

1536

INSERT INTO t6 VALUES (NULL, NULL, 98);

1537

INSERT INTO t6 VALUES (NULL, "promote the general welfare", 50);

1538

SELECT * FROM t6;

1539

SELECT * FROM t6 ORDER BY a;

1540

SELECT * FROM t6 ORDER BY a DESC;

1541

1542

SHOW CREATE TABLE t6;

1543

1544

1545

1546

# Cleanup, test is over

1547

1548

1549

1550

--disable_warnings

1551

DROP TABLE t1, t2, t4, t5, t6;

1552

--enable_warnings

1553

1554

1555

# BUG#26138 - REPAIR TABLE with option USE_FRM erases all records in ARCHIVE

1556

# table

1557

1558

create table t1 (i int) engine=archive;

1559

insert into t1 values (1);

1560

repair table t1 use_frm;

1561

select * from t1;

1562

drop table t1;

1563

1564

1565

# BUG#29207 - archive table reported as corrupt by check table

1566

1567

create table t1(a longblob) engine=archive;

1568

insert into t1 set a='';

1569

insert into t1 set a='a';

1570

check table t1 extended;

1571

drop table t1;

1572

1573

1574

# BUG#31036 - Using order by with archive table crashes server

1575

1576

1577

CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE;

1578

1579

let $bug31036=41;

1580

--disable_query_log

1581

while($bug31036)

1582

{

1583

INSERT INTO t1(a) VALUES (REPEAT('a', 510));

1584

dec $bug31036;

1585

}

1586

--enable_query_log

1587

INSERT INTO t1(a) VALUES ('');

1588

1589

--disable_result_log

1590

SELECT * FROM t1 ORDER BY a;

1591

--enable_result_log

1592

1593

DROP TABLE t1;

1594

1595

1596

# BUG#31833 - ORDER BY leads to wrong result when ARCHIVE, BLOB and table

1597

# cache is full

1598

1599

CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b BLOB, KEY(a)) ENGINE=archive;

1600

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

1601

FLUSH TABLE t1;

1602

SELECT * FROM t1 ORDER BY a;

Older »