~drizzle-trunk/drizzle/development

create TEMPORARY table t1 (a int not null default 0, b int not null default 0, c int not null default 0, d int not null default 0, e int not null default 0, f int not null default 0, g int not null default 0, h int not null default 0,i int not null default 0, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;

260

create table t1 (a int not null default 0, b int not null default 0, c int not null default 0, d int not null default 0, e int not null default 0, f int not null default 0, g int not null default 0, h int not null default 0,i int not null default 0, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;

211

261

insert into t1 (a) values(1);

212

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

262

--replace_column 3 X 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

213

263

show table status like 't1';

214

264

drop table t1;

215

265

237

287

# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)

238

288

239

289

# Some platforms (Mac OS X, Windows) will send the error message using small letters.

240

CREATE TEMPORARY TABLE T12207(a int) ENGINE=MYISAM;

290

CREATE TABLE T12207(a int) ENGINE=MYISAM;

241

291

--replace_result t12207 T12207

242

292

--error ER_ILLEGAL_HA

243

293

ALTER TABLE T12207 DISCARD TABLESPACE;

383

433

drop table t1;

384

434

385

435

386

# BUG#23404 - ROW_FORMAT=COMPACT option is lost is an index is added to the

436

# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the

387

437

# table

388

438

389

CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT;

439

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

390

440

CREATE INDEX i1 ON t1(a);

391

441

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

392

442

SHOW CREATE TABLE t1;

491

541

# Bug#25262 Auto Increment lost when changing Engine type

492

542

493

543

494

create TEMPORARY table t1(id int primary key auto_increment) engine=MEMORY;

544

create table t1(id int primary key auto_increment) engine=heap;

495

545

496

546

insert into t1 values (null);

497

547

insert into t1 values (null);

508

558

insert into t1 values (null);

509

559

select * from t1;

510

560

511

# Alter to MEMORY again

512

alter table t1 engine = MEMORY;

561

# Alter to heap again

562

alter table t1 engine = heap;

513

563

insert into t1 values (null);

514

564

select * from t1;

515

565

566

616

# without # prefix is not allowed for TEXT columns, while index

567

617

# is defined with prefix.

568

618

569

create TEMPORARY table t1 (t varchar(255) default null, key t (t(80))) engine=myisam;

619

create table t1 (t varchar(255) default null, key t (t(80))) engine=myisam;

570

620

alter table t1 change t t text;

571

621

drop table t1;

572

622

574

624

# Bug#18038 MySQL server corrupts binary columns data

575

625

576

626

577

CREATE TABLE t1 (s CHAR(8));

627

CREATE TABLE t1 (s CHAR(8) BINARY);

578

628

INSERT INTO t1 VALUES ('test');

579

629

SELECT LENGTH(s) FROM t1;

580

ALTER TABLE t1 MODIFY s CHAR(10);

630

ALTER TABLE t1 MODIFY s CHAR(10) BINARY;

581

631

SELECT LENGTH(s) FROM t1;

582

632

DROP TABLE t1;

583

633

606

656

--echo End of 5.0 tests

607

657

608

658

659

# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES

660

# It should be consistent across all platforms and for all engines

661

# (Before 5.1 this was not true as behavior was different between

662

# Unix/Windows and transactional/non-transactional tables).

663

# See also innodb_mysql.test

664

665

--disable_warnings

666

drop table if exists t1, t2, t3;

667

--enable_warnings

668

create table t1 (i int);

669

create table t3 (j int);

670

insert into t1 values ();

671

insert into t3 values ();

672

# Table which is altered under LOCK TABLES it should stay in list of locked

673

# tables and be available after alter takes place unless ALTER contains RENAME

674

# clause. We should see the new definition of table, of course.

675

lock table t1 write, t3 read;

676

# Example of so-called 'fast' ALTER TABLE

677

alter table t1 modify i int default 1;

678

insert into t1 values ();

679

select * from t1;

680

# And now full-blown ALTER TABLE

681

alter table t1 change i c char(10) default "Two";

682

insert into t1 values ();

683

select * from t1;

684

# If table is renamed then it should be removed from the list

685

# of locked tables. 'Fast' ALTER TABLE with RENAME clause:

686

alter table t1 modify c char(10) default "Three", rename to t2;

687

--error ER_TABLE_NOT_LOCKED

688

select * from t1;

689

--error ER_TABLE_NOT_LOCKED

690

select * from t2;

691

select * from t3;

692

unlock tables;

693

insert into t2 values ();

694

select * from t2;

695

lock table t2 write, t3 read;

696

# Full ALTER TABLE with RENAME

697

alter table t2 change c vc varchar(100) default "Four", rename to t1;

698

--error ER_TABLE_NOT_LOCKED

699

select * from t1;

700

--error ER_TABLE_NOT_LOCKED

701

select * from t2;

702

select * from t3;

703

unlock tables;

704

insert into t1 values ();

705

select * from t1;

706

drop tables t1, t3;

707

708

709

609

710

# Bug#18775 - Temporary table from alter table visible to other threads

610

711

611

712

# Check if special characters work and duplicates are detected.

706

807

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

707

808

SELECT * FROM t1;

708

809

DROP TABLE t1;

810

811

812

# BUG#29957 - alter_table.test fails

813

814

create table t1 (c char(10) default "Two");

815

lock table t1 write;

816

insert into t1 values ();

817

alter table t1 modify c char(10) default "Three";

818

unlock tables;

819

select * from t1;

820

check table t1;

821

drop table t1;

Older »