~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;

223

261

insert into t1 (a) values(1);

224

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

262

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

225

263

show table status like 't1';

226

264

drop table t1;

227

265

231

269

232

270

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

233

271

ALTER TABLE t1 DROP PRIMARY KEY;

234

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

235

272

SHOW CREATE TABLE t1;

236

273

--error ER_CANT_DROP_FIELD_OR_KEY

237

274

ALTER TABLE t1 DROP PRIMARY KEY;

249

286

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

250

287

251

288

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

252

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

289

CREATE TABLE T12207(a int) ENGINE=MYISAM;

253

290

--replace_result t12207 T12207

254

291

--error ER_ILLEGAL_HA

255

292

ALTER TABLE T12207 DISCARD TABLESPACE;

395

432

drop table t1;

396

433

397

434

398

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

435

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

399

436

# table

400

437

401

CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT;

438

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

402

439

CREATE INDEX i1 ON t1(a);

403

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

404

440

SHOW CREATE TABLE t1;

405

441

DROP INDEX i1 ON t1;

406

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

407

442

SHOW CREATE TABLE t1;

408

443

DROP TABLE t1;

409

444

503

538

# Bug#25262 Auto Increment lost when changing Engine type

504

539

505

540

506

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

541

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

507

542

508

543

insert into t1 values (null);

509

544

insert into t1 values (null);

520

555

insert into t1 values (null);

521

556

select * from t1;

522

557

523

# Alter to MEMORY again

524

alter table t1 engine = MEMORY;

558

# Alter to heap again

559

alter table t1 engine = heap;

525

560

insert into t1 values (null);

526

561

select * from t1;

527

562

534

569

#create table t1(f1 int);

535

570

#alter table t1 add column f2 datetime not null, add column f21 date not null;

536

571

#insert into t1 values(1,'2000-01-01','2000-01-01');

537

#--error ER_TRUNCATED_WRONG_VALUE

572

#--error 1292

538

573

#alter table t1 add column f3 datetime not null;

539

#--error ER_TRUNCATED_WRONG_VALUE

574

#--error 1292

540

575

#alter table t1 add column f3 date not null;

541

#--error ER_TRUNCATED_WRONG_VALUE

576

#--error 1292

542

577

#alter table t1 add column f4 datetime not null default '2002-02-02',

543

578

# add column f41 date not null;

544

579

#alter table t1 add column f4 datetime not null default '2002-02-02',

578

613

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

579

614

# is defined with prefix.

580

615

581

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

616

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

582

617

alter table t1 change t t text;

583

618

drop table t1;

584

619

586

621

# Bug#18038 MySQL server corrupts binary columns data

587

622

588

623

589

CREATE TABLE t1 (s CHAR(8));

624

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

590

625

INSERT INTO t1 VALUES ('test');

591

626

SELECT LENGTH(s) FROM t1;

592

ALTER TABLE t1 MODIFY s CHAR(10);

627

ALTER TABLE t1 MODIFY s CHAR(10) BINARY;

593

628

SELECT LENGTH(s) FROM t1;

594

629

DROP TABLE t1;

595

630

618

653

--echo End of 5.0 tests

619

654

620

655

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

621

707

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

622

708

623

709

# Check if special characters work and duplicates are detected.

637

723

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

638

724

--error ER_TABLE_EXISTS_ERROR

639

725

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

640

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

641

726

SHOW CREATE TABLE `tt+1`;

642

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

643

727

SHOW CREATE TABLE `tt+2`;

644

728

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

645

729

666

750

SHOW TABLES;

667

751

INSERT INTO `#sql2` VALUES (1);

668

752

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

669

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

670

753

SHOW CREATE TABLE `#sql2`;

671

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

672

754

SHOW CREATE TABLE `@0023sql1`;

673

755

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

674

756

675

757

676

677

758

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

678

759

679

760

# This problem happens if the data change is compatible.

684

765

DROP TABLE IF EXISTS t2;

685

766

--enable_warnings

686

767

CREATE TABLE t1 (

687

int_field INTEGER NOT NULL,

768

int_field INTEGER UNSIGNED NOT NULL,

688

769

char_field CHAR(10),

689

770

INDEX(`int_field`)

690

771

);

695

776

696

777

INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");

697

778

--echo "Non-copy data change - new frm, but old data and index files"

698

ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2;

779

ALTER TABLE t1

780

CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,

781

RENAME t2;

699

782

700

--error ER_TABLE_UNKNOWN

783

--error ER_NO_SUCH_TABLE

701

784

SELECT * FROM t1 ORDER BY int_field;

702

785

SELECT * FROM t2 ORDER BY unsigned_int_field;

703

786

DESCRIBE t2;

704

787

DESCRIBE t2;

705

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;

788

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;

706

789

DESCRIBE t2;

707

790

708

791

DROP TABLE t2;

718

801

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

719

802

SELECT * FROM t1;

720

803

DROP TABLE t1;

804

805

806

# BUG#29957 - alter_table.test fails

807

808

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

809

lock table t1 write;

810

insert into t1 values ();

811

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

812

unlock tables;

813

select * from t1;

814

check table t1;

815

drop table t1;

Older »