~drizzle-trunk/drizzle/development

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;

210

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;

261

211

insert into t1 (a) values(1);

262

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

212

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

263

213

show table status like 't1';

264

214

drop table t1;

265

215

269

219

270

220

CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);

271

221

ALTER TABLE t1 DROP PRIMARY KEY;

222

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

272

223

SHOW CREATE TABLE t1;

273

224

--error ER_CANT_DROP_FIELD_OR_KEY

274

225

ALTER TABLE t1 DROP PRIMARY KEY;

286

237

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

287

238

288

239

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

289

CREATE TABLE T12207(a int) ENGINE=MYISAM;

240

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

290

241

--replace_result t12207 T12207

291

242

--error ER_ILLEGAL_HA

292

243

ALTER TABLE T12207 DISCARD TABLESPACE;

437

388

438

389

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

439

390

CREATE INDEX i1 ON t1(a);

391

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

440

392

SHOW CREATE TABLE t1;

441

393

DROP INDEX i1 ON t1;

394

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

442

395

SHOW CREATE TABLE t1;

443

396

DROP TABLE t1;

444

397

538

491

# Bug#25262 Auto Increment lost when changing Engine type

539

492

540

493

541

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

494

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

542

495

543

496

insert into t1 values (null);

544

497

insert into t1 values (null);

613

566

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

614

567

# is defined with prefix.

615

568

616

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

569

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

617

570

alter table t1 change t t text;

618

571

drop table t1;

619

572

653

606

--echo End of 5.0 tests

654

607

655

608

656

# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES

657

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

658

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

659

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

660

# See also innodb_mysql.test

661

662

--disable_warnings

663

drop table if exists t1, t2, t3;

664

--enable_warnings

665

create table t1 (i int);

666

create table t3 (j int);

667

insert into t1 values ();

668

insert into t3 values ();

669

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

670

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

671

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

672

lock table t1 write, t3 read;

673

# Example of so-called 'fast' ALTER TABLE

674

alter table t1 modify i int default 1;

675

insert into t1 values ();

676

select * from t1;

677

# And now full-blown ALTER TABLE

678

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

679

insert into t1 values ();

680

select * from t1;

681

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

682

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

683

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

684

--error ER_TABLE_NOT_LOCKED

685

select * from t1;

686

--error ER_TABLE_NOT_LOCKED

687

select * from t2;

688

select * from t3;

689

unlock tables;

690

insert into t2 values ();

691

select * from t2;

692

lock table t2 write, t3 read;

693

# Full ALTER TABLE with RENAME

694

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

695

--error ER_TABLE_NOT_LOCKED

696

select * from t1;

697

--error ER_TABLE_NOT_LOCKED

698

select * from t2;

699

select * from t3;

700

unlock tables;

701

insert into t1 values ();

702

select * from t1;

703

drop tables t1, t3;

704

705

706

707

609

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

708

610

709

611

# Check if special characters work and duplicates are detected.

723

625

CREATE TEMPORARY TABLE `tt+1` (c1 INT);

724

626

--error ER_TABLE_EXISTS_ERROR

725

627

ALTER TABLE `tt+1` RENAME `tt+2`;

628

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

726

629

SHOW CREATE TABLE `tt+1`;

630

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

727

631

SHOW CREATE TABLE `tt+2`;

728

632

DROP TABLE `tt+1`, `tt+2`;

729

633

750

654

SHOW TABLES;

751

655

INSERT INTO `#sql2` VALUES (1);

752

656

INSERT INTO `@0023sql1` VALUES (2);

657

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

753

658

SHOW CREATE TABLE `#sql2`;

659

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

754

660

SHOW CREATE TABLE `@0023sql1`;

755

661

DROP TABLE `#sql2`, `@0023sql1`;

756

662

757

663

664

758

665

# Bug #22369: Alter table rename combined with other alterations causes lost tables

759

666

760

667

# This problem happens if the data change is compatible.

799

706

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

800

707

SELECT * FROM t1;

801

708

DROP TABLE t1;

802

803

804

# BUG#29957 - alter_table.test fails

805

806

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

807

lock table t1 write;

808

insert into t1 values ();

809

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

810

unlock tables;

811

select * from t1;

812

check table t1;

813

drop table t1;

Older »