~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 3 X 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

262

--replace_column 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

216

266

267

# Test that data get converted when character set is changed

268

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

269

270

set names koi8r;

271

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

272

insert into t1 values ('��');

273

select a,hex(a) from t1;

274

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

275

select a,hex(a) from t1;

276

alter table t1 change a a binary(4);

277

select a,hex(a) from t1;

278

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

279

select a,hex(a) from t1;

280

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

281

select a,hex(a) from t1;

282

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

283

select a,hex(a) from t1;

284

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

285

select a,hex(a) from t1;

286

alter table t1 change a a text character set cp1251;

287

select a,hex(a) from t1;

288

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

289

select a,hex(a) from t1;

290

delete from t1;

291

292

293

# Test ALTER TABLE .. CHARACTER SET ..

294

295

show create table t1;

296

alter table t1 DEFAULT CHARACTER SET latin1;

297

show create table t1;

298

alter table t1 CONVERT TO CHARACTER SET latin1;

299

show create table t1;

300

alter table t1 DEFAULT CHARACTER SET cp1251;

301

show create table t1;

302

303

drop table t1;

304

305

306

# Bug#2821

307

# Test that table CHARACTER SET does not affect blobs

308

309

create table t1 (myblob longblob,mytext longtext)

310

default charset latin1 collate latin1_general_cs;

311

show create table t1;

312

alter table t1 character set latin2;

313

show create table t1;

314

drop table t1;

315

316

217

317

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

218

318

219

319

220

320

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

221

321

ALTER TABLE t1 DROP PRIMARY KEY;

222

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

223

322

SHOW CREATE TABLE t1;

224

323

--error ER_CANT_DROP_FIELD_OR_KEY

225

324

ALTER TABLE t1 DROP PRIMARY KEY;

237

336

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

238

337

239

338

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

240

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

339

CREATE TABLE T12207(a int) ENGINE=MYISAM;

241

340

--replace_result t12207 T12207

242

341

--error ER_ILLEGAL_HA

243

342

ALTER TABLE T12207 DISCARD TABLESPACE;

244

343

DROP TABLE T12207;

245

344

246

345

346

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

347

348

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

349

# modified. In other words, the values were reinterpreted

350

# as UTF8 instead of being converted.

351

create table t1 (a text) character set koi8r;

352

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

353

select hex(a) from t1;

354

alter table t1 convert to character set cp1251;

355

select hex(a) from t1;

356

drop table t1;

357

358

247

359

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

248

360

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

249

361

# geometry field and allow to create prefix index which is

383

495

drop table t1;

384

496

385

497

386

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

498

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

387

499

# table

388

500

389

CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT;

501

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

390

502

CREATE INDEX i1 ON t1(a);

391

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

392

503

SHOW CREATE TABLE t1;

393

504

DROP INDEX i1 ON t1;

394

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

395

505

SHOW CREATE TABLE t1;

396

506

DROP TABLE t1;

397

507

482

592

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

483

593

484

594

485

create table t1 (mycol int not null);

595

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

486

596

alter table t1 alter column mycol set default 0;

487

597

desc t1;

488

598

drop table t1;

491

601

# Bug#25262 Auto Increment lost when changing Engine type

492

602

493

603

494

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

604

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

495

605

496

606

insert into t1 values (null);

497

607

insert into t1 values (null);

508

618

insert into t1 values (null);

509

619

select * from t1;

510

620

511

# Alter to MEMORY again

512

alter table t1 engine = MEMORY;

621

# Alter to heap again

622

alter table t1 engine = heap;

513

623

insert into t1 values (null);

514

624

select * from t1;

515

625

566

676

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

567

677

# is defined with prefix.

568

678

569

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

679

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

680

engine=myisam default charset=latin1;

570

681

alter table t1 change t t text;

571

682

drop table t1;

572

683

574

685

# Bug#18038 MySQL server corrupts binary columns data

575

686

576

687

577

CREATE TABLE t1 (s CHAR(8));

688

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

578

689

INSERT INTO t1 VALUES ('test');

579

690

SELECT LENGTH(s) FROM t1;

580

ALTER TABLE t1 MODIFY s CHAR(10);

691

ALTER TABLE t1 MODIFY s CHAR(10) BINARY;

581

692

SELECT LENGTH(s) FROM t1;

582

693

DROP TABLE t1;

583

694

584

CREATE TABLE t1 (s varbinary(8));

695

CREATE TABLE t1 (s BINARY(8));

585

696

INSERT INTO t1 VALUES ('test');

586

697

SELECT LENGTH(s) FROM t1;

587

698

SELECT HEX(s) FROM t1;

588

ALTER TABLE t1 MODIFY s varbinary(10);

699

ALTER TABLE t1 MODIFY s BINARY(10);

589

700

SELECT HEX(s) FROM t1;

590

701

SELECT LENGTH(s) FROM t1;

591

702

DROP TABLE t1;

606

717

--echo End of 5.0 tests

607

718

608

719

720

# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES

721

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

722

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

723

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

724

# See also innodb_mysql.test

725

726

--disable_warnings

727

drop table if exists t1, t2, t3;

728

--enable_warnings

729

create table t1 (i int);

730

create table t3 (j int);

731

insert into t1 values ();

732

insert into t3 values ();

733

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

734

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

735

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

736

lock table t1 write, t3 read;

737

# Example of so-called 'fast' ALTER TABLE

738

alter table t1 modify i int default 1;

739

insert into t1 values ();

740

select * from t1;

741

# And now full-blown ALTER TABLE

742

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

743

insert into t1 values ();

744

select * from t1;

745

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

746

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

747

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

748

--error ER_TABLE_NOT_LOCKED

749

select * from t1;

750

--error ER_TABLE_NOT_LOCKED

751

select * from t2;

752

select * from t3;

753

unlock tables;

754

insert into t2 values ();

755

select * from t2;

756

lock table t2 write, t3 read;

757

# Full ALTER TABLE with RENAME

758

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

759

--error ER_TABLE_NOT_LOCKED

760

select * from t1;

761

--error ER_TABLE_NOT_LOCKED

762

select * from t2;

763

select * from t3;

764

unlock tables;

765

insert into t1 values ();

766

select * from t1;

767

drop tables t1, t3;

768

769

770

609

771

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

610

772

611

773

# Check if special characters work and duplicates are detected.

625

787

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

626

788

--error ER_TABLE_EXISTS_ERROR

627

789

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

628

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

629

790

SHOW CREATE TABLE `tt+1`;

630

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

631

791

SHOW CREATE TABLE `tt+2`;

632

792

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

633

793

654

814

SHOW TABLES;

655

815

INSERT INTO `#sql2` VALUES (1);

656

816

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

657

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

658

817

SHOW CREATE TABLE `#sql2`;

659

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

660

818

SHOW CREATE TABLE `@0023sql1`;

661

819

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

662

820

663

821

664

665

822

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

666

823

667

824

# This problem happens if the data change is compatible.

672

829

DROP TABLE IF EXISTS t2;

673

830

--enable_warnings

674

831

CREATE TABLE t1 (

675

int_field INTEGER NOT NULL,

832

int_field INTEGER UNSIGNED NOT NULL,

676

833

char_field CHAR(10),

677

834

INDEX(`int_field`)

678

835

);

683

840

684

841

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

685

842

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

686

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

843

ALTER TABLE t1

844

CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,

845

RENAME t2;

687

846

688

847

--error ER_NO_SUCH_TABLE

689

848

SELECT * FROM t1 ORDER BY int_field;

690

849

SELECT * FROM t2 ORDER BY unsigned_int_field;

691

850

DESCRIBE t2;

692

851

DESCRIBE t2;

693

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;

852

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;

694

853

DESCRIBE t2;

695

854

696

855

DROP TABLE t2;

706

865

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

707

866

SELECT * FROM t1;

708

867

DROP TABLE t1;

868

869

870

# BUG#29957 - alter_table.test fails

871

872

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

873

lock table t1 write;

874

insert into t1 values ();

875

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

876

unlock tables;

877

select * from t1;

878

check table t1;

879

drop table t1;

Older »