~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

266

216

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 varbinary(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

317

217

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

318

218

319

219

320

220

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

321

221

ALTER TABLE t1 DROP PRIMARY KEY;

222

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

322

223

SHOW CREATE TABLE t1;

323

224

--error ER_CANT_DROP_FIELD_OR_KEY

324

225

ALTER TABLE t1 DROP PRIMARY KEY;

336

237

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

337

238

338

239

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

339

CREATE TABLE T12207(a int) ENGINE=MYISAM;

240

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

340

241

--replace_result t12207 T12207

341

242

--error ER_ILLEGAL_HA

342

243

ALTER TABLE T12207 DISCARD TABLESPACE;

343

244

DROP TABLE T12207;

344

245

345

246

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

359

247

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

360

248

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

361

249

# geometry field and allow to create prefix index which is

500

388

501

389

CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;

502

390

CREATE INDEX i1 ON t1(a);

391

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

503

392

SHOW CREATE TABLE t1;

504

393

DROP INDEX i1 ON t1;

394

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

505

395

SHOW CREATE TABLE t1;

506

396

DROP TABLE t1;

507

397

601

491

# Bug#25262 Auto Increment lost when changing Engine type

602

492

603

493

604

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

494

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

605

495

606

496

insert into t1 values (null);

607

497

insert into t1 values (null);

676

566

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

677

567

# is defined with prefix.

678

568

679

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

680

engine=myisam default charset=latin1;

569

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

681

570

alter table t1 change t t text;

682

571

drop table t1;

683

572

717

606

--echo End of 5.0 tests

718

607

719

608

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

771

609

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

772

610

773

611

# Check if special characters work and duplicates are detected.

787

625

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

788

626

--error ER_TABLE_EXISTS_ERROR

789

627

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

628

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

790

629

SHOW CREATE TABLE `tt+1`;

630

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

791

631

SHOW CREATE TABLE `tt+2`;

792

632

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

793

633

814

654

SHOW TABLES;

815

655

INSERT INTO `#sql2` VALUES (1);

816

656

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

657

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

817

658

SHOW CREATE TABLE `#sql2`;

659

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

818

660

SHOW CREATE TABLE `@0023sql1`;

819

661

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

820

662

821

663

664

822

665

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

823

666

824

667

# This problem happens if the data change is compatible.

829

672

DROP TABLE IF EXISTS t2;

830

673

--enable_warnings

831

674

CREATE TABLE t1 (

832

int_field INTEGER UNSIGNED NOT NULL,

675

int_field INTEGER NOT NULL,

833

676

char_field CHAR(10),

834

677

INDEX(`int_field`)

835

678

);

840

683

841

684

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

842

685

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

843

ALTER TABLE t1

844

CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,

845

RENAME t2;

686

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

846

687

847

688

--error ER_NO_SUCH_TABLE

848

689

SELECT * FROM t1 ORDER BY int_field;

849

690

SELECT * FROM t2 ORDER BY unsigned_int_field;

850

691

DESCRIBE t2;

851

692

DESCRIBE t2;

852

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;

693

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;

853

694

DESCRIBE t2;

854

695

855

696

DROP TABLE t2;

865

706

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

866

707

SELECT * FROM t1;

867

708

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 »