~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

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

266

insert into t1 (a) values(1);

267

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

268

show table status like 't1';

269

drop table t1;

270

271

272

# Test that data get converted when character set is changed

273

# Test that data doesn't get converted when src or dst is BINARY/BLOB

274

275

set names koi8r;

276

create table t1 (a char(10) character set koi8r);

277

insert into t1 values ('��');

278

select a,hex(a) from t1;

279

alter table t1 change a a char(10) character set cp1251;

280

select a,hex(a) from t1;

281

alter table t1 change a a binary(4);

282

select a,hex(a) from t1;

283

alter table t1 change a a char(10) character set cp1251;

284

select a,hex(a) from t1;

285

alter table t1 change a a char(10) character set koi8r;

286

select a,hex(a) from t1;

287

alter table t1 change a a varchar(10) character set cp1251;

288

select a,hex(a) from t1;

289

alter table t1 change a a char(10) character set koi8r;

290

select a,hex(a) from t1;

291

alter table t1 change a a text character set cp1251;

292

select a,hex(a) from t1;

293

alter table t1 change a a char(10) character set koi8r;

294

select a,hex(a) from t1;

295

delete from t1;

296

297

298

# Test ALTER TABLE .. CHARACTER SET ..

299

300

show create table t1;

301

alter table t1 DEFAULT CHARACTER SET latin1;

302

show create table t1;

303

alter table t1 CONVERT TO CHARACTER SET latin1;

304

show create table t1;

305

alter table t1 DEFAULT CHARACTER SET cp1251;

306

show create table t1;

307

308

drop table t1;

309

310

311

# Bug#2821

312

# Test that table CHARACTER SET does not affect blobs

313

314

create table t1 (myblob longblob,mytext longtext)

315

default charset latin1 collate latin1_general_cs;

316

show create table t1;

317

alter table t1 character set latin2;

318

show create table t1;

319

drop table t1;

320

321

322

# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)

323

324

325

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

326

ALTER TABLE t1 DROP PRIMARY KEY;

327

SHOW CREATE TABLE t1;

328

--error ER_CANT_DROP_FIELD_OR_KEY

329

ALTER TABLE t1 DROP PRIMARY KEY;

330

DROP TABLE t1;

331

332

# BUG#3899

333

create table t1 (a int, b int, key(a));

334

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

335

--error ER_CANT_DROP_FIELD_OR_KEY

336

alter table t1 drop key no_such_key;

337

alter table t1 drop key a;

338

drop table t1;

339

340

341

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

342

343

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

344

CREATE TABLE T12207(a int) ENGINE=MYISAM;

345

--replace_result t12207 T12207

346

--error ER_ILLEGAL_HA

347

ALTER TABLE T12207 DISCARD TABLESPACE;

348

DROP TABLE T12207;

349

350

351

# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns

352

353

# The column's character set was changed but the actual data was not

354

# modified. In other words, the values were reinterpreted

355

# as UTF8 instead of being converted.

356

create table t1 (a text) character set koi8r;

357

insert into t1 values (_koi8r'��');

358

select hex(a) from t1;

359

alter table t1 convert to character set cp1251;

360

select hex(a) from t1;

361

drop table t1;

362

363

364

# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"

365

# MySQL should not think that packed field with non-zero decimals is

366

# geometry field and allow to create prefix index which is

367

# shorter than packed field length.

368

369

create table t1 ( a timestamp );

370

--error ER_WRONG_SUB_KEY

371

alter table t1 add unique ( a(1) );

372

drop table t1;

373

374

375

# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table

376

377

# This problem happens if the data change is compatible.

378

# Changing to the same type is compatible for example.

379

380

--disable_warnings

381

drop table if exists t1;

382

--enable_warnings

383

create table t1 (a int, key(a));

384

show indexes from t1;

385

--echo "this used not to disable the index"

386

alter table t1 modify a int, disable keys;

387

show indexes from t1;

388

389

alter table t1 enable keys;

390

show indexes from t1;

391

392

alter table t1 modify a bigint, disable keys;

393

show indexes from t1;

394

395

alter table t1 enable keys;

396

show indexes from t1;

397

398

alter table t1 add b char(10), disable keys;

399

show indexes from t1;

400

401

alter table t1 add c decimal(10,2), enable keys;

402

show indexes from t1;

403

404

--echo "this however did"

405

alter table t1 disable keys;

406

show indexes from t1;

407

408

desc t1;

409

410

alter table t1 add d decimal(15,5);

411

--echo "The key should still be disabled"

412

show indexes from t1;

413

414

drop table t1;

415

416

--echo "Now will test with one unique index"

417

create table t1(a int, b char(10), unique(a));

418

show indexes from t1;

419

alter table t1 disable keys;

420

show indexes from t1;

421

alter table t1 enable keys;

422

423

--echo "If no copy on noop change, this won't touch the data file"

424

--echo "Unique index, no change"

425

alter table t1 modify a int, disable keys;

426

show indexes from t1;

427

428

--echo "Change the type implying data copy"

429

--echo "Unique index, no change"

430

alter table t1 modify a bigint, disable keys;

431

show indexes from t1;

432

433

alter table t1 modify a bigint;

434

show indexes from t1;

435

436

alter table t1 modify a int;

437

show indexes from t1;

438

439

drop table t1;

440

441

--echo "Now will test with one unique and one non-unique index"

442

create table t1(a int, b char(10), unique(a), key(b));

443

show indexes from t1;

444

alter table t1 disable keys;

445

show indexes from t1;

446

alter table t1 enable keys;

447

448

449

--echo "If no copy on noop change, this won't touch the data file"

450

--echo "The non-unique index will be disabled"

451

alter table t1 modify a int, disable keys;

452

show indexes from t1;

453

alter table t1 enable keys;

454

show indexes from t1;

455

456

--echo "Change the type implying data copy"

457

--echo "The non-unique index will be disabled"

458

alter table t1 modify a bigint, disable keys;

459

show indexes from t1;

460

461

--echo "Change again the type, but leave the indexes as_is"

462

alter table t1 modify a int;

463

show indexes from t1;

464

--echo "Try the same. When data is no copied on similar tables, this is noop"

465

alter table t1 modify a int;

466

show indexes from t1;

467

468

drop table t1;

469

470

471

472

# Bug#11493 - Alter table rename to default database does not work without

473

# db name qualifying

474

475

create database mysqltest;

476

create table t1 (c1 int);

477

# Move table to other database.

478

alter table t1 rename mysqltest.t1;

479

# Assure that it has moved.

480

--error ER_BAD_TABLE_ERROR

481

drop table t1;

482

# Move table back.

483

alter table mysqltest.t1 rename t1;

484

# Assure that it is back.

485

drop table t1;

486

# Now test for correct message if no database is selected.

487

# Create t1 in 'test'.

488

create table t1 (c1 int);

489

# Change to other db.

490

use mysqltest;

491

# Drop the current db. This de-selects any db.

492

drop database mysqltest;

493

# Now test for correct message.

494

--error ER_NO_DB_ERROR

495

alter table test.t1 rename t1;

496

# Check that explicit qualifying works even with no selected db.

497

alter table test.t1 rename test.t1;

498

# Go back to standard 'test' db.

499

use test;

500

drop table t1;

501

502

503

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

504

# table

505

506

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

507

CREATE INDEX i1 ON t1(a);

508

SHOW CREATE TABLE t1;

509

DROP INDEX i1 ON t1;

510

SHOW CREATE TABLE t1;

511

DROP TABLE t1;

512

513

514

# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash

515

516

--disable_warnings

517

DROP TABLE IF EXISTS bug24219;

518

DROP TABLE IF EXISTS bug24219_2;

519

--enable_warnings

520

521

CREATE TABLE bug24219 (a INT, INDEX(a));

522

523

SHOW INDEX FROM bug24219;

524

525

ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;

526

527

SHOW INDEX FROM bug24219_2;

528

529

DROP TABLE bug24219_2;

530

531

532

# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)

533

534

535

--disable_warnings

536

drop table if exists table_24562;

537

--enable_warnings

538

539

create table table_24562(

540

section int,

541

subsection int,

542

title varchar(50));

543

544

insert into table_24562 values

545

(1, 0, "Introduction"),

546

(1, 1, "Authors"),

547

(1, 2, "Acknowledgements"),

548

(2, 0, "Basics"),

549

(2, 1, "Syntax"),

550

(2, 2, "Client"),

551

(2, 3, "Server"),

552

(3, 0, "Intermediate"),

553

(3, 1, "Complex queries"),

554

(3, 2, "Stored Procedures"),

555

(3, 3, "Stored Functions"),

556

(4, 0, "Advanced"),

557

(4, 1, "Replication"),

558

(4, 2, "Load balancing"),

559

(4, 3, "High availability"),

560

(5, 0, "Conclusion");

561

562

select * from table_24562;

563

564

alter table table_24562 add column reviewer varchar(20),

565

order by title;

566

567

select * from table_24562;

568

569

update table_24562 set reviewer="Me" where section=2;

570

update table_24562 set reviewer="You" where section=3;

571

572

alter table table_24562

573

order by section ASC, subsection DESC;

574

575

select * from table_24562;

576

577

alter table table_24562

578

order by table_24562.subsection ASC, table_24562.section DESC;

579

580

select * from table_24562;

581

582

--error ER_PARSE_ERROR

583

alter table table_24562 order by 12;

584

--error ER_PARSE_ERROR

585

alter table table_24562 order by (section + 12);

586

--error ER_PARSE_ERROR

587

alter table table_24562 order by length(title);

588

--error ER_PARSE_ERROR

589

alter table table_24562 order by (select 12 from dual);

590

591

--error ER_BAD_FIELD_ERROR

592

alter table table_24562 order by no_such_col;

593

594

drop table table_24562;

595

596

# End of 4.1 tests

597

598

599

# Bug #14693 (ALTER SET DEFAULT doesn't work)

600

601

602

create table t1 (mycol int(10) not null);

603

alter table t1 alter column mycol set default 0;

604

desc t1;

605

drop table t1;

606

607

608

# Bug#25262 Auto Increment lost when changing Engine type

609

610

611

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

612

613

insert into t1 values (null);

614

insert into t1 values (null);

615

616

select * from t1;

617

618

# Set auto increment to 50

619

alter table t1 auto_increment = 50;

620

621

# Alter to myisam

622

alter table t1 engine = myisam;

623

624

# This insert should get id 50

625

insert into t1 values (null);

626

select * from t1;

627

628

# Alter to heap again

629

alter table t1 engine = heap;

630

insert into t1 values (null);

631

select * from t1;

632

633

drop table t1;

634

635

636

# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the

637

# NO_ZERO_DATE mode.

638

639

set @orig_sql_mode = @@sql_mode;

640

set sql_mode="no_zero_date";

641

create table t1(f1 int);

642

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

643

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

644

--error 1292

645

alter table t1 add column f3 datetime not null;

646

--error 1292

647

alter table t1 add column f3 date not null;

648

--error 1292

649

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

650

add column f41 date not null;

651

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

652

add column f41 date not null default '2002-02-02';

653

select * from t1;

654

drop table t1;

655

set sql_mode= @orig_sql_mode;

656

657

658

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

659

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

660

661

662

create table t1 (v varchar(32));

663

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

664

select * from t1;

665

# Fast alter, no copy performed

666

alter table t1 change v v2 varchar(32);

667

select * from t1;

668

# Fast alter, no copy performed

669

alter table t1 change v2 v varchar(64);

670

select * from t1;

671

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

672

select * from t1;

673

# Regular alter table

674

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

675

select * from t1;

676

update t1 set i=5 where i=3;

677

select * from t1;

678

alter table t1 change i i bigint;

679

select * from t1;

680

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

681

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

682

drop table t1;

683

684

685

# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index

686

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

687

# is defined with prefix.

688

689

create table t1 (t varchar(255) default null, key t (t(80)))

690

engine=myisam default charset=latin1;

691

alter table t1 change t t text;

692

drop table t1;

693

694

695

# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER

696

# TABLE

697

698

CREATE TABLE t1 (a varchar(500));

699

700

ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);

701

SHOW CREATE TABLE t1;

702

ALTER TABLE t1 ADD KEY(b(50));

703

SHOW CREATE TABLE t1;

704

705

ALTER TABLE t1 ADD c POINT;

706

SHOW CREATE TABLE t1;

707

708

--error ER_WRONG_SUB_KEY

709

CREATE TABLE t2 (a INT, KEY (a(20)));

710

711

ALTER TABLE t1 ADD d INT;

712

--error ER_WRONG_SUB_KEY

713

ALTER TABLE t1 ADD KEY (d(20));

714

715

# the 5.1 part of the test

716

--error ER_WRONG_SUB_KEY

717

ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));

718

719

DROP TABLE t1;

720

721

722

# Bug#18038 MySQL server corrupts binary columns data

723

724

725

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

726

INSERT INTO t1 VALUES ('test');

727

SELECT LENGTH(s) FROM t1;

728

ALTER TABLE t1 MODIFY s CHAR(10) BINARY;

729

SELECT LENGTH(s) FROM t1;

730

DROP TABLE t1;

731

732

CREATE TABLE t1 (s BINARY(8));

733

INSERT INTO t1 VALUES ('test');

734

SELECT LENGTH(s) FROM t1;

735

SELECT HEX(s) FROM t1;

736

ALTER TABLE t1 MODIFY s BINARY(10);

737

SELECT HEX(s) FROM t1;

738

SELECT LENGTH(s) FROM t1;

739

DROP TABLE t1;

740

741

742

# Bug#19386: Multiple alter causes crashed table

743

# The trailing column would get corrupted data, or server could not even read

744

# it.

745

746

747

CREATE TABLE t1 (v VARCHAR(3), b INT);

748

INSERT INTO t1 VALUES ('abc', 5);

749

SELECT * FROM t1;

750

ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);

751

SELECT * FROM t1;

752

DROP TABLE t1;

753

754

755

756

# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types

757

758

create table t1 (a tinytext character set latin1);

759

alter table t1 convert to character set utf8;

760

show create table t1;

761

drop table t1;

762

create table t1 (a mediumtext character set latin1);

763

alter table t1 convert to character set utf8;

764

show create table t1;

765

drop table t1;

766

767

--echo End of 5.0 tests

768

769

770

# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES

771

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

772

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

773

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

774

# See also innodb_mysql.test

775

776

--disable_warnings

777

drop table if exists t1, t2, t3;

778

--enable_warnings

779

create table t1 (i int);

780

create table t3 (j int);

781

insert into t1 values ();

782

insert into t3 values ();

783

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

784

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

785

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

786

lock table t1 write, t3 read;

787

# Example of so-called 'fast' ALTER TABLE

788

alter table t1 modify i int default 1;

789

insert into t1 values ();

790

select * from t1;

791

# And now full-blown ALTER TABLE

792

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

793

insert into t1 values ();

794

select * from t1;

795

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

796

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

797

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

798

--error ER_TABLE_NOT_LOCKED

799

select * from t1;

800

--error ER_TABLE_NOT_LOCKED

801

select * from t2;

802

select * from t3;

803

unlock tables;

804

insert into t2 values ();

805

select * from t2;

806

lock table t2 write, t3 read;

807

# Full ALTER TABLE with RENAME

808

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

809

--error ER_TABLE_NOT_LOCKED

810

select * from t1;

811

--error ER_TABLE_NOT_LOCKED

812

select * from t2;

813

select * from t3;

814

unlock tables;

815

insert into t1 values ();

816

select * from t1;

817

drop tables t1, t3;

818

819

820

821

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

822

823

# Check if special characters work and duplicates are detected.

824

--disable_warnings

825

DROP TABLE IF EXISTS `t+1`, `t+2`;

826

--enable_warnings

827

CREATE TABLE `t+1` (c1 INT);

828

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

829

CREATE TABLE `t+1` (c1 INT);

830

--error ER_TABLE_EXISTS_ERROR

831

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

832

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

833

834

# Same for temporary tables though these names do not become file names.

835

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

836

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

837

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

838

--error ER_TABLE_EXISTS_ERROR

839

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

840

SHOW CREATE TABLE `tt+1`;

841

SHOW CREATE TABLE `tt+2`;

842

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

843

844

# Check if special characters as in tmp_file_prefix work.

845

CREATE TABLE `#sql1` (c1 INT);

846

CREATE TABLE `@0023sql2` (c1 INT);

847

SHOW TABLES;

848

RENAME TABLE `#sql1` TO `@0023sql1`;

849

RENAME TABLE `@0023sql2` TO `#sql2`;

850

SHOW TABLES;

851

ALTER TABLE `@0023sql1` RENAME `#sql-1`;

852

ALTER TABLE `#sql2` RENAME `@0023sql-2`;

853

SHOW TABLES;

854

INSERT INTO `#sql-1` VALUES (1);

855

INSERT INTO `@0023sql-2` VALUES (2);

856

DROP TABLE `#sql-1`, `@0023sql-2`;

857

858

# Same for temporary tables though these names do not become file names.

859

CREATE TEMPORARY TABLE `#sql1` (c1 INT);

860

CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);

861

SHOW TABLES;

862

ALTER TABLE `#sql1` RENAME `@0023sql1`;

863

ALTER TABLE `@0023sql2` RENAME `#sql2`;

864

SHOW TABLES;

865

INSERT INTO `#sql2` VALUES (1);

866

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

867

SHOW CREATE TABLE `#sql2`;

868

SHOW CREATE TABLE `@0023sql1`;

869

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

870

871

872

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

873

874

# This problem happens if the data change is compatible.

875

# Changing to the same type is compatible for example.

876

877

--disable_warnings

878

DROP TABLE IF EXISTS t1;

879

DROP TABLE IF EXISTS t2;

880

--enable_warnings

881

CREATE TABLE t1 (

882

int_field INTEGER UNSIGNED NOT NULL,

883

char_field CHAR(10),

884

INDEX(`int_field`)

885

);

886

887

DESCRIBE t1;

888

889

SHOW INDEXES FROM t1;

890

891

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

892

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

893

ALTER TABLE t1

894

CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,

895

RENAME t2;

896

897

--error ER_NO_SUCH_TABLE

898

SELECT * FROM t1 ORDER BY int_field;

899

SELECT * FROM t2 ORDER BY unsigned_int_field;

900

DESCRIBE t2;

901

DESCRIBE t2;

902

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;

903

DESCRIBE t2;

904

905

DROP TABLE t2;

906

907

908

# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.

909

910

CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);

911

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

912

SELECT * FROM t1;

913

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;

914

SELECT * FROM t1;

915

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

916

SELECT * FROM t1;

917

DROP TABLE t1;

918

919

920

# BUG#29957 - alter_table.test fails

921

922

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

923

lock table t1 write;

924

insert into t1 values ();

925

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

926

unlock tables;

927

select * from t1;

928

check table t1;

929

drop table t1;

930

931

932

# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets

933

934

935

--disable_warnings

936

DROP TABLE IF EXISTS t1;

937

--enable_warnings

938

CREATE TABLE t1 (id int, c int) character set latin1;

939

INSERT INTO t1 VALUES (1,1);

940

--enable_info

941

ALTER TABLE t1 CHANGE c d int;

942

ALTER TABLE t1 CHANGE d c int;

943

ALTER TABLE t1 MODIFY c VARCHAR(10);

944

ALTER TABLE t1 CHANGE c d varchar(10);

945

ALTER TABLE t1 CHANGE d c varchar(10);

946

--disable_info

947

DROP TABLE t1;

948

949

--disable_warnings

950

DROP TABLE IF EXISTS t1;

951

--enable_warnings

952

CREATE TABLE t1 (id int, c int) character set utf8;

953

INSERT INTO t1 VALUES (1,1);

954

--enable_info

955

ALTER TABLE t1 CHANGE c d int;

956

ALTER TABLE t1 CHANGE d c int;

957

ALTER TABLE t1 MODIFY c VARCHAR(10);

958

ALTER TABLE t1 CHANGE c d varchar(10);

959

ALTER TABLE t1 CHANGE d c varchar(10);

960

--disable_info

961

DROP TABLE t1;

962

963

964

# Bug#39372 "Smart" ALTER TABLE not so smart after all.

965

966

create table t1(f1 int not null, f2 int not null, key (f1), key (f2));

967

let $count= 50;

968

--disable_query_log

969

while ($count)

970

{

971

EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1);

972

EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2);

973

dec $count ;

974

}

975

--enable_query_log

976

977

select index_length into @unpaked_keys_size from

978

information_schema.tables where table_name='t1';

979

alter table t1 pack_keys=1;

980

select index_length into @paked_keys_size from

981

information_schema.tables where table_name='t1';

982

select (@unpaked_keys_size > @paked_keys_size);

983

984

select max_data_length into @orig_max_data_length from

985

information_schema.tables where table_name='t1';

986

alter table t1 max_rows=100;

987

select max_data_length into @changed_max_data_length from

988

information_schema.tables where table_name='t1';

989

select (@orig_max_data_length > @changed_max_data_length);

990

991

drop table t1;

992

993

994

# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1

995

996

CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY,

997

b ENUM('a', 'b', 'c') NOT NULL);

998

INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a');

999

ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL;

1000

SELECT * FROM t1;

1001

DROP TABLE t1;

1002

1003

--echo #

1004

--echo # Bug#45567: Fast ALTER TABLE broken for enum and set

1005

--echo #

1006

1007

--disable_warnings

1008

DROP TABLE IF EXISTS t1;

1009

--enable_warnings

1010

1011

CREATE TABLE t1 (a ENUM('a1','a2'));

1012

INSERT INTO t1 VALUES ('a1'),('a2');

1013

--enable_info

1014

--echo # No copy: No modification

1015

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2');

1016

--echo # No copy: Add new enumeration to the end

1017

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3');

1018

--echo # Copy: Modify and add new to the end

1019

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5');

1020

--echo # Copy: Remove from the end

1021

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx');

1022

--echo # Copy: Add new enumeration

1023

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx');

1024

--echo # No copy: Add new enumerations to the end

1025

ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6');

1026

--disable_info

1027

DROP TABLE t1;

1028

1029

CREATE TABLE t1 (a SET('a1','a2'));

1030

INSERT INTO t1 VALUES ('a1'),('a2');

1031

--enable_info

1032

--echo # No copy: No modification

1033

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2');

1034

--echo # No copy: Add new to the end

1035

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3');

1036

--echo # Copy: Modify and add new to the end

1037

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5');

1038

--echo # Copy: Remove from the end

1039

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx');

1040

--echo # Copy: Add new member

1041

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx');

1042

--echo # No copy: Add new to the end

1043

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6');

1044

--echo # Copy: Numerical incrase (pack lenght)

1045

ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10');

1046

--disable_info

1047

DROP TABLE t1;

1048

1049

1050

# Bug#43508: Renaming timestamp or date column triggers table copy

1051

1052

1053

CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL,

1054

f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8;

1055

1056

INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2);

1057

1058

--echo this should affect no rows as there is no real change

1059

--enable_info

1060

ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;

1061

--disable_info

1062

DROP TABLE t1;

1063

1064

1065

--echo #

1066

--echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux)

1067

--echo # or freezes (win) the server

1068

--echo #

1069

1070

CREATE TABLE t1 (a TEXT, id INT, b INT);

1071

ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;

1072

1073

DROP TABLE t1;

1074

1075

1076

--echo #

1077

--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION

1078

--echo # FIRST CAN CAUSE DATA TO BE CORRUPTED".

1079

--echo #

1080

--disable_warnings

1081

drop table if exists t1;

1082

--enable_warnings

1083

--echo # Use MyISAM engine as the fact that InnoDB doesn't support

1084

--echo # in-place ALTER TABLE in cases when columns are being renamed

1085

--echo # hides some bugs.

1086

create table t1 (i int, j int) engine=myisam;

1087

insert into t1 value (1, 2);

1088

--echo # First, test for original problem described in the bug report.

1089

select * from t1;

1090

--echo # Change of column order by the below ALTER TABLE statement should

1091

--echo # affect both column names and column contents.

1092

alter table t1 modify column j int first;

1093

select * from t1;

1094

--echo # Now test for similar problem with the same root.

1095

--echo # The below ALTER TABLE should change not only the name but

1096

--echo # also the value for the last column of the table.

1097

alter table t1 drop column i, add column k int default 0;

1098

select * from t1;

1099

--echo # Clean-up.

1100

drop table t1;

1101

1102

1103

--echo End of 5.1 tests

1104

1105

1106

# Bug #31031 ALTER TABLE regression in 5.0

1107

1108

# The ALTER TABLE operation failed with

1109

# ERROR 1089 (HY000): Incorrect sub part key; ...

1110

1111

CREATE TABLE t1(c CHAR(10),

1112

i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);

1113

INSERT INTO t1 VALUES('a',2),('b',4),('c',6);

1114

ALTER TABLE t1

1115

DROP i,

1116

ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,

1117

AUTO_INCREMENT = 1;

1118

DROP TABLE t1;

1119

1120

1121

1122

# Bug#50542 5.5.x doesn't check length of key prefixes:

1123

# corruption and crash results

1124

1125

# This case is related to Bug#31031 (above)

1126

# A statement where the index key is larger/wider than

1127

# the column type, should cause an error

1128

1129

--error ER_WRONG_SUB_KEY

1130

CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255)));

1131

1132

# Test other variants of creating indices

1133

CREATE TABLE t1 (a CHAR(1));

1134

# ALTER TABLE

1135

--error ER_WRONG_SUB_KEY

1136

ALTER TABLE t1 ADD PRIMARY KEY (a(20));

1137

--error ER_WRONG_SUB_KEY

1138

ALTER TABLE t1 ADD KEY (a(20));

1139

# CREATE INDEX

1140

--error ER_WRONG_SUB_KEY

1141

CREATE UNIQUE INDEX i1 ON t1 (a(20));

1142

--error ER_WRONG_SUB_KEY

1143

CREATE INDEX i2 ON t1 (a(20));

1144

# cleanup

1145

DROP TABLE t1;

1146

1147

1148

1149

# Bug #45052 ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns

1150

# The alter table fails if 2 or more new fields added and

1151

# also added a key with these fields

1152

1153

CREATE TABLE t1 (id int);

1154

INSERT INTO t1 VALUES (1), (2);

1155

ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2);

1156

DROP TABLE t1;

1157

1158

1159

--echo #

1160

--echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full

1161

--echo # table copy".

1162

--echo #

1163

--disable_warnings

1164

DROP TABLE IF EXISTS t1;

1165

--enable_warnings

1166

CREATE TABLE t1 (a INT, b MEDIUMINT);

1167

INSERT INTO t1 VALUES (1, 1), (2, 2);

1168

--echo # The below ALTER should not copy table and so no rows should

1169

--echo # be shown as affected.

1170

--enable_info

1171

ALTER TABLE t1 CHANGE a id INT;

1172

--disable_info

1173

DROP TABLE t1;

1174

1175

1176

--echo #

1177

--echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG

1178

--echo #

1179

1180

--disable_warnings

1181

DROP DATABASE IF EXISTS db1;

1182

--enable_warnings

1183

1184

CREATE DATABASE db1 CHARACTER SET utf8;

1185

CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100)));

1186

ALTER TABLE db1.t1 ADD baz INT;

1187

1188

DROP DATABASE db1;

1189

1190

1191

--echo # Additional coverage for refactoring which is made as part

1192

--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege

1193

--echo # to allow temp table operations".

1194

--echo #

1195

--echo # At some point the below test case failed on assertion.

1196

1197

--disable_warnings

1198

DROP TABLE IF EXISTS t1;

1199

--enable_warnings

1200

1201

CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM;

1202

1203

--error ER_ILLEGAL_HA

1204

ALTER TABLE t1 DISCARD TABLESPACE;

1205

1206

DROP TABLE t1;

1207

1208

1209

--echo #

1210

--echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME

1211

--echo # CLAUSE FAILS OR ABORTS SERVER.

1212

--echo #

1213

--disable_warnings

1214

drop table if exists t1;

1215

--enable_warnings

1216

create table t1 (a int);

1217

prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2';

1218

execute stmt1;

1219

rename table t2 to t1;

1220

--echo # The below statement should succeed and not emit error or abort server.

1221

execute stmt1;

1222

deallocate prepare stmt1;

1223

drop table t2;

1224

1225

1226

--echo #

1227

--echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED

1228

--echo #

1229

1230

--disable_warnings

1231

DROP TABLE IF EXISTS t1;

1232

--enable_warnings

1233

1234

CREATE TABLE t1(a INT) engine=innodb;

1235

INSERT INTO t1 VALUES (1), (2);

1236

1237

--enable_info

1238

--echo # This should not do anything

1239

ALTER TABLE t1;

1240

--echo # Check that we rebuild the table

1241

ALTER TABLE t1 engine=innodb;

1242

--echo # This should also rebuild the table

1243

ALTER TABLE t1 FORCE;

1244

--disable_info

1245

1246

DROP TABLE t1;

1247

1248

--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't

1249

--echo # identify correct column name.

1250

--echo #

1251

1252

CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default '');

1253

ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2,

1254

MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1;

1255

SHOW CREATE TABLE t1;

1256

DROP TABLE t1;

1257

1258

1259

--echo #

1260

--echo # WL#5534 Online ALTER, Phase 1

1261

--echo #

1262

1263

--echo # Single thread tests.

1264

--echo # See innodb_mysql_sync.test for multi thread tests.

1265

1266

--disable_warnings

1267

DROP TABLE IF EXISTS t1;

1268

--enable_warnings

1269

1270

CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB;

1271

CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM;

1272

INSERT INTO t1 VALUES (1,1), (2,2);

1273

INSERT INTO m1 VALUES (1,1), (2,2);

1274

1275

--echo #

1276

--echo # 1: Test ALGORITHM keyword

1277

--echo #

1278

1279

--echo # --enable_info allows us to see how many rows were updated

1280

--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0.

1281

1282

--enable_info

1283

ALTER TABLE t1 ADD INDEX i1(b);

1284

ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT;

1285

ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY;

1286

ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE;

1287

--error ER_UNKNOWN_ALTER_ALGORITHM

1288

ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID;

1289

1290

ALTER TABLE m1 ENABLE KEYS;

1291

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT;

1292

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY;

1293

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE;

1294

--disable_info

1295

1296

ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;

1297

1298

--echo #

1299

--echo # 2: Test ALGORITHM + old_alter_table

1300

--echo #

1301

1302

--enable_info

1303

SET SESSION old_alter_table= 1;

1304

ALTER TABLE t1 ADD INDEX i1(b);

1305

ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT;

1306

ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY;

1307

ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE;

1308

SET SESSION old_alter_table= 0;

1309

--disable_info

1310

1311

ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;

1312

1313

--echo #

1314

--echo # 3: Test unsupported in-place operation

1315

--echo #

1316

1317

ALTER TABLE t1 ADD COLUMN (c1 INT);

1318

ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT;

1319

ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY;

1320

ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE;

1321

1322

ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4;

1323

1324

--echo #

1325

--echo # 4: Test LOCK keyword

1326

--echo #

1327

1328

--enable_info

1329

ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT;

1330

ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE;

1331

ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED;

1332

ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE;

1333

--error ER_UNKNOWN_ALTER_LOCK

1334

ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID;

1335

--disable_info

1336

1337

ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT;

1338

--error ER_ALTER_OPERATION_NOT_SUPPORTED

1339

ALTER TABLE m1 ENABLE KEYS, LOCK= NONE;

1340

--error ER_ALTER_OPERATION_NOT_SUPPORTED

1341

ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED;

1342

ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE;

1343

1344

ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;

1345

1346

--echo #

1347

--echo # 5: Test ALGORITHM + LOCK

1348

--echo #

1349

1350

--enable_info

1351

ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE;

1352

ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED;

1353

ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE;

1354

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

1355

ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE;

1356

ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED;

1357

ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE;

1358

1359

--error ER_ALTER_OPERATION_NOT_SUPPORTED

1360

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE;

1361

--error ER_ALTER_OPERATION_NOT_SUPPORTED

1362

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED;

1363

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE;

1364

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

1365

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE;

1366

# This works because the lock will be SNW for the copy phase.

1367

# It will still require exclusive lock for actually enabling keys.

1368

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED;

1369

ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE;

1370

--disable_info

1371

1372

DROP TABLE t1, m1;

1373

1374

--echo #

1375

--echo # 6: Possible deadlock involving thr_lock.c

1376

--echo #

1377

1378

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

1379

INSERT INTO t1 VALUES (1,1), (2,2);

1380

1381

START TRANSACTION;

1382

INSERT INTO t1 VALUES (3,3);

1383

1384

--echo # Connection con1

1385

connect (con1, localhost, root);

1386

--echo # Sending:

1387

--send ALTER TABLE t1 DISABLE KEYS

1388

1389

--echo # Connection default

1390

connection default;

1391

--echo # Waiting until ALTER TABLE is blocked.

1392

let $wait_condition=

1393

SELECT COUNT(*) = 1 FROM information_schema.processlist

1394

WHERE state = "Waiting for table metadata lock" AND

1395

info = "ALTER TABLE t1 DISABLE KEYS";

1396

--source include/wait_condition.inc

1397

UPDATE t1 SET b = 4;

1398

COMMIT;

1399

1400

--echo # Connection con1

1401

connection con1;

1402

--echo # Reaping: ALTER TABLE t1 DISABLE KEYS

1403

--reap

1404

disconnect con1;

1405

--source include/wait_until_disconnected.inc

1406

1407

--echo # Connection default

1408

connection default;

1409

DROP TABLE t1;

1410

1411

--echo #

1412

--echo # 7: Which operations require copy and which can be done in-place?

1413

--echo #

1414

--echo # Test which ALTER TABLE operations are done in-place and

1415

--echo # which operations are done using temporary table copy.

1416

--echo #

1417

--echo # --enable_info allows us to see how many rows were updated

1418

--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0.

1419

--echo #

1420

1421

--disable_warnings

1422

DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3;

1423

--enable_warnings

1424

1425

--echo # Single operation tests

1426

1427

CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB;

1428

CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM;

1429

CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB;

1430

CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM;

1431

INSERT INTO ti1 VALUES (1,1,1), (2,2,2);

1432

INSERT INTO ti2 VALUES (1,1,1), (2,2,2);

1433

INSERT INTO tm1 VALUES (1,1,1), (2,2,2);

1434

INSERT INTO tm2 VALUES (1,1,1), (2,2,2);

1435

1436

--enable_info

1437

ALTER TABLE ti1;

1438

ALTER TABLE tm1;

1439

1440

ALTER TABLE ti1 ADD COLUMN d VARCHAR(200);

1441

ALTER TABLE tm1 ADD COLUMN d VARCHAR(200);

1442

ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200);

1443

ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200);

1444

ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST;

1445

ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST;

1446

ALTER TABLE ti1 ADD COLUMN f INT AFTER a;

1447

ALTER TABLE tm1 ADD COLUMN f INT AFTER a;

1448

1449

ALTER TABLE ti1 ADD INDEX ii1(b);

1450

ALTER TABLE tm1 ADD INDEX im1(b);

1451

ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c);

1452

ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c);

1453

ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d);

1454

ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d);

1455

ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2);

1456

ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2);

1457

1458

# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE

1459

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

1460

ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;

1461

ALTER TABLE ti1 ADD PRIMARY KEY(a);

1462

ALTER TABLE tm1 ADD PRIMARY KEY(a);

1463

1464

ALTER TABLE ti1 DROP INDEX ii3;

1465

ALTER TABLE tm1 DROP INDEX im3;

1466

1467

ALTER TABLE ti1 DROP COLUMN d2;

1468

ALTER TABLE tm1 DROP COLUMN d2;

1469

1470

ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a);

1471

ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a);

1472

1473

ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1;

1474

ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1;

1475

ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT;

1476

ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT;

1477

1478

# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE

1479

ALTER TABLE ti1 CHANGE COLUMN f g INT;

1480

ALTER TABLE tm1 CHANGE COLUMN f g INT;

1481

ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20);

1482

ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20);

1483

ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c');

1484

ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c');

1485

ALTER TABLE ti1 MODIFY COLUMN e INT;

1486

ALTER TABLE tm1 MODIFY COLUMN e INT;

1487

# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE

1488

ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h;

1489

ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h;

1490

ALTER TABLE ti1 MODIFY COLUMN e INT FIRST;

1491

ALTER TABLE tm1 MODIFY COLUMN e INT FIRST;

1492

# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE

1493

--disable_info

1494

# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.

1495

SET @orig_sql_mode = @@sql_mode;

1496

SET @@sql_mode = 'STRICT_TRANS_TABLES';

1497

--enable_info

1498

ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL;

1499

--disable_info

1500

SET @@sql_mode = @orig_sql_mode;

1501

--enable_info

1502

ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL;

1503

# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE

1504

ALTER TABLE ti1 MODIFY COLUMN c INT NULL;

1505

ALTER TABLE tm1 MODIFY COLUMN c INT NULL;

1506

# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE

1507

ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30);

1508

ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30);

1509

ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d;

1510

ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d;

1511

1512

ALTER TABLE ti1 DROP COLUMN h;

1513

ALTER TABLE tm1 DROP COLUMN h;

1514

1515

ALTER TABLE ti1 DROP INDEX ii2;

1516

ALTER TABLE tm1 DROP INDEX im2;

1517

ALTER TABLE ti1 DROP PRIMARY KEY;

1518

ALTER TABLE tm1 DROP PRIMARY KEY;

1519

1520

ALTER TABLE ti1 DROP FOREIGN KEY fi1;

1521

ALTER TABLE tm1 DROP FOREIGN KEY fm1;

1522

1523

ALTER TABLE ti1 RENAME TO ti3;

1524

ALTER TABLE tm1 RENAME TO tm3;

1525

ALTER TABLE ti3 RENAME TO ti1;

1526

ALTER TABLE tm3 RENAME TO tm1;

1527

1528

ALTER TABLE ti1 ORDER BY b;

1529

ALTER TABLE tm1 ORDER BY b;

1530

1531

ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16;

1532

ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16;

1533

ALTER TABLE ti1 DEFAULT CHARACTER SET utf8;

1534

ALTER TABLE tm1 DEFAULT CHARACTER SET utf8;

1535

1536

ALTER TABLE ti1 FORCE;

1537

ALTER TABLE tm1 FORCE;

1538

1539

ALTER TABLE ti1 AUTO_INCREMENT 3;

1540

ALTER TABLE tm1 AUTO_INCREMENT 3;

1541

ALTER TABLE ti1 AVG_ROW_LENGTH 10;

1542

ALTER TABLE tm1 AVG_ROW_LENGTH 10;

1543

ALTER TABLE ti1 CHECKSUM 1;

1544

ALTER TABLE tm1 CHECKSUM 1;

1545

ALTER TABLE ti1 COMMENT 'test';

1546

ALTER TABLE tm1 COMMENT 'test';

1547

ALTER TABLE ti1 MAX_ROWS 100;

1548

ALTER TABLE tm1 MAX_ROWS 100;

1549

ALTER TABLE ti1 MIN_ROWS 1;

1550

ALTER TABLE tm1 MIN_ROWS 1;

1551

ALTER TABLE ti1 PACK_KEYS 1;

1552

ALTER TABLE tm1 PACK_KEYS 1;

1553

1554

--disable_info

1555

DROP TABLE ti1, ti2, tm1, tm2;

1556

1557

--echo # Tests of >1 operation (InnoDB)

1558

1559

CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB;

1560

INSERT INTO ti1(b) VALUES (1), (2);

1561

1562

--enable_info

1563

ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b);

1564

1565

ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5;

1566

--disable_info

1567

INSERT INTO ti3(b) VALUES (5);

1568

--enable_info

1569

ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7;

1570

--disable_info

1571

INSERT INTO ti3(b) VALUES (7);

1572

SELECT * FROM ti3;

1573

1574

DROP TABLE ti3;

1575

1576

--echo #

1577

--echo # 8: Scenario in which ALTER TABLE was returning an unwarranted

1578

--echo # ER_ILLEGAL_HA error at some point during work on this WL.

1579

--echo #

1580

1581

CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM;

1582

ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT;

1583

DROP TABLE tm1;

1584

1585

--echo #

1586

--echo # Bug#11815557 60269: MYSQL SHOULD REJECT ATTEMPTS TO CREATE SYSTEM

1587

--echo # TABLES IN INCORRECT ENGINE

1588

--echo #

1589

--echo # Note: This test assumes that only MyISAM supports system tables.

1590

--echo # If other engines are made to support system tables,

1591

--echo # then this test needs to be updated

1592

--echo #

1593

1594

use mysql;

1595

--error ER_UNSUPPORTED_ENGINE

1596

ALTER TABLE db ENGINE=innodb;

1597

--error ER_UNSUPPORTED_ENGINE

1598

ALTER TABLE user ENGINE=memory;

1599

--error ER_UNSUPPORTED_ENGINE

1600

ALTER TABLE proc ENGINE=heap;

1601

--error ER_UNSUPPORTED_ENGINE

1602

ALTER TABLE func ENGINE=csv;

1603

--error ER_UNSUPPORTED_ENGINE

1604

ALTER TABLE event ENGINE=merge;

1605

--error ER_UNSUPPORTED_ENGINE

1606

ALTER TABLE servers ENGINE=innodb;

1607

--error ER_UNSUPPORTED_ENGINE

1608

ALTER TABLE procs_priv ENGINE=memory;

1609

--error ER_UNSUPPORTED_ENGINE

1610

ALTER TABLE tables_priv ENGINE=heap;

1611

--error ER_UNSUPPORTED_ENGINE

1612

ALTER TABLE columns_priv ENGINE=csv;

1613

--error ER_UNSUPPORTED_ENGINE

1614

ALTER TABLE time_zone ENGINE=merge;

1615

--error ER_UNSUPPORTED_ENGINE

1616

ALTER TABLE help_topic ENGINE=innodb;

1617

1618

--error ER_UNSUPPORTED_ENGINE

1619

CREATE TABLE db (dummy int) ENGINE=innodb;

1620

--error ER_UNSUPPORTED_ENGINE

1621

CREATE TABLE user (dummy int) ENGINE=memory;

1622

--error ER_UNSUPPORTED_ENGINE

1623

CREATE TABLE proc (dummy int) ENGINE=heap;

1624

--error ER_UNSUPPORTED_ENGINE

1625

CREATE TABLE func (dummy int) ENGINE=csv;

1626

--error ER_UNSUPPORTED_ENGINE

1627

CREATE TABLE event (dummy int) ENGINE=merge;

1628

--error ER_UNSUPPORTED_ENGINE

1629

CREATE TABLE servers (dummy int) ENGINE=innodb;

1630

--error ER_UNSUPPORTED_ENGINE

1631

CREATE TABLE procs_priv (dummy int) ENGINE=memory;

1632

--error ER_UNSUPPORTED_ENGINE

1633

CREATE TABLE tables_priv (dummy int) ENGINE=heap;

1634

--error ER_UNSUPPORTED_ENGINE

1635

CREATE TABLE columns_priv (dummy int) ENGINE=csv;

1636

--error ER_UNSUPPORTED_ENGINE

1637

CREATE TABLE time_zone (dummy int) ENGINE=merge;

1638

--error ER_UNSUPPORTED_ENGINE

1639

CREATE TABLE help_topic (dummy int) ENGINE=innodb;

1640

use test;

1641

--echo # End of Bug#11815557

Older »