~drizzle-trunk/drizzle/development : revision 771

14

Table Op Msg_type Msg_text

15

test.t1 check status OK

16

drop table t1;

17

create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a));

18

check table t1;

19

Table Op Msg_type Msg_text

20

test.t1 check status OK

21

repair table t1;

22

Table Op Msg_type Msg_text

23

test.t1 repair status OK

24

delete from t1 where (a & 1);

25

check table t1;

26

Table Op Msg_type Msg_text

27

test.t1 check status OK

28

repair table t1;

29

Table Op Msg_type Msg_text

30

test.t1 repair status OK

17

create table t1 (a int not null auto_increment, b blob not null, primary key (a));

18

check table t1;

19

Table Op Msg_type Msg_text

20

test.t1 check status OK

21

repair table t1;

22

Table Op Msg_type Msg_text

23

test.t1 repair note The storage engine for the table doesn't support repair

24

delete from t1 where (a mod 2) = 1;

25

check table t1;

26

Table Op Msg_type Msg_text

27

test.t1 check status OK

28

repair table t1;

29

Table Op Msg_type Msg_text

30

test.t1 repair note The storage engine for the table doesn't support repair

31

check table t1;

32

Table Op Msg_type Msg_text

33

test.t1 check status OK

40

show index from t1;

41

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

42

t1 0 PRIMARY 1 a A 5 NULL NULL BTREE

43

t1 1 b 1 b A 1 NULL NULL BTREE

43

t1 1 b 1 b A 5 NULL NULL BTREE

44

optimize table t1;

45

Table Op Msg_type Msg_text

46

test.t1 optimize status Table is already up to date

46

test.t1 optimize status OK

47

show index from t1;

48

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

49

t1 0 PRIMARY 1 a A 5 NULL NULL BTREE

50

t1 1 b 1 b A 1 NULL NULL BTREE

50

t1 1 b 1 b A 5 NULL NULL BTREE

51

drop table t1;

52

create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=myisam;

53

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

76

id select_type table type possible_keys key key_len ref rows Extra

77

1 SIMPLE t1 ALL NULL NULL NULL NULL 4

78

drop table t1;

79

CREATE TABLE t1 (a INT);

79

CREATE TABLE t1 (a INT) ENGINE=MyISAM;

80

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

81

LOCK TABLES t1 WRITE;

82

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

85

test.t1 optimize status OK

86

DROP TABLE t1;

87

create table t1 ( t1 char(255), key(t1(250)));

88

Warnings:

89

Warning 1071 Specified key was too long; max key length is 767 bytes

88

90

insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169');

89

91

insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203');

90

92

insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767');

245

247

int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982

246

248

int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990

247

249

int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998

248

int, i999 int, i1000 int, b blob) row_format=dynamic;

250

int, i999 int, i1000 int, b blob) engine=myisam row_format=dynamic;

249

251

insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,

250

252

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,

251

253

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,

298

300

test.t1 check status OK

299

301

drop table t1;

300

302

CREATE TABLE `t1` (

301

`post_id` mediumint(8) unsigned NOT NULL auto_increment,

302

`topic_id` mediumint(8) unsigned NOT NULL default '0',

303

`post_id` int NOT NULL auto_increment,

304

`topic_id` int NOT NULL default '0',

303

305

`post_time` datetime NOT NULL default '0000-00-00 00:00:00',

304

306

`post_text` text NOT NULL,

305

307

`icon_url` varchar(10) NOT NULL default '',

306

`sign` tinyint(1) unsigned NOT NULL default '0',

308

`sign` int NOT NULL default '0',

307

309

`post_edit` varchar(150) NOT NULL default '',

308

310

`poster_login` varchar(35) NOT NULL default '',

309

311

`ip` varchar(15) NOT NULL default '',

311

313

KEY `post_time` (`post_time`),

312

314

KEY `ip` (`ip`),

313

315

KEY `poster_login` (`poster_login`),

314

KEY `topic_id` (`topic_id`),

315

FULLTEXT KEY `post_text` (`post_text`)

316

KEY `topic_id` (`topic_id`)

317

# FULLTEXT KEY `post_text` (`post_text`)

316

318

) ENGINE=MyISAM;

317

319

INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test');

318

REPAIR TABLE t1;

319

Table Op Msg_type Msg_text

320

test.t1 repair status OK

321

320

CHECK TABLE t1;

322

321

Table Op Msg_type Msg_text

323

322

test.t1 check status OK

324

323

drop table t1;

325

CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e));

324

CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e)) ENGINE=MyISAM;

326

325

ERROR 42000: Specified key was too long; max key length is 1332 bytes

327

CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300));

326

CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300)) ENGINE=MyISAM;

328

327

ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);

329

328

ERROR 42000: Specified key was too long; max key length is 1332 bytes

330

329

DROP TABLE t1;

331

CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a));

330

CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)) ENGINE=MyISAM;

332

331

INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4);

333

332

create table t2 (a int not null, b int, c int, key(b), key(c), key(a));

334

333

INSERT into t2 values (1,1,1), (2,2,2);

368

367

1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where

369

368

explain select * from t1 force index (a) where a=0 or a=2;

370

369

id select_type table type possible_keys key key_len ref rows Extra

371

1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using MRR

370

1 SIMPLE t1 range a a 4 NULL 4 Using where; Using MRR

372

371

explain select * from t1 where c=1;

373

372

id select_type table type possible_keys key key_len ref rows Extra

374

373

1 SIMPLE t1 ref c,c_2 c 5 const 1

379

378

create table t1 (a int not null auto_increment primary key, b varchar(255));

380

379

insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100));

381

380

update t1 set b=repeat(left(b,1),200) where a=1;

382

delete from t1 where (a & 1)= 0;

381

delete from t1 where (a mod 2) = 0;

383

382

update t1 set b=repeat('e',200) where a=1;

384

383

flush tables;

385

384

check table t1;

405

404

test.t1 check status OK

406

405

repair table t1;

407

406

Table Op Msg_type Msg_text

408

test.t1 repair status OK

407

test.t1 repair note The storage engine for the table doesn't support repair

409

408

select concat(a,'.') from t1 where a='aaa';

410

409

concat(a,'.')

411

410

aaa .

431

430

DROP TABLE IF EXISTS t1;

432

431

Warnings:

433

432

Note 1051 Unknown table 't1'

434

CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a));

433

CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)) ENGINE=MyISAM;

435

434

INSERT t1 VALUES ("can \tcan");

436

435

INSERT t1 VALUES ("can can");

437

436

INSERT t1 VALUES ("can");

471

470

update t1 set b='b ' where a > 1;

472

471

ERROR 23000: Duplicate entry 'b ' for key 'b'

473

472

insert into t1 (b) values ('b');

474

ERROR 23000: Duplicate entry 'b' for key 'b'

473

ERROR 23000: Duplicate entry 'b ' for key 'b'

475

474

select * from t1;

476

475

a b

477

476

1 a

478

2 b

477

2 b

479

478

3 c

480

479

delete from t1 where b='b';

481

480

select a,concat(b,'.') from t1;

498

497

1

499

498

explain select sql_big_result distinct t1.a from t1,t2 order by t2.a;

500

499

id select_type table type possible_keys key key_len ref rows Extra

501

1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary

502

1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct

500

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort

501

1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct; Using join buffer

503

502

explain select distinct t1.a from t1,t2 order by t2.a;

504

503

id select_type table type possible_keys key key_len ref rows Extra

505

1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary

506

1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct

504

1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort

505

1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct; Using join buffer

507

506

drop table t1,t2;

508

507

create table t1 (

509

508

c1 varchar(32),

515

514

c1

516

515

a

517

516

drop table t1;

518

create table t1 (a int not null, primary key(a));

519

create table t2 (a int not null, b int not null, primary key(a,b));

520

insert into t1 values (1),(2),(3),(4),(5),(6);

521

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

522

lock tables t1 read local, t2 read local;

523

select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;

524

a a b

525

1 1 1

526

2 2 1

527

insert into t2 values(2,0);

528

select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;

529

a a b

530

1 1 1

531

2 2 1

532

unlock tables;

533

drop table t1,t2;

534

CREATE TABLE t1 (c1 varchar(250) NOT NULL);

535

CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1));

536

INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003');

537

INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004');

538

LOCK TABLES t1 READ LOCAL, t2 READ LOCAL;

539

SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2

540

WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;

541

t1c1 t2c1

542

INSERT INTO t2 VALUES ('test000001'), ('test000005');

543

SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2

544

WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;

545

t1c1 t2c1

546

UNLOCK TABLES;

547

DROP TABLE t1,t2;

548

CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM;

549

Got one of the listed errors

550

517

create table t1 (a int, b varchar(200), c text not null) checksum=1;

551

518

create table t2 (a int, b varchar(200), c text not null) checksum=0;

552

519

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

553

520

insert t2 select * from t1;

554

521

checksum table t1, t2, t3 quick;

555

522

Table Checksum

556

test.t1 2948697075

523

test.t1 NULL

557

524

test.t2 NULL

558

525

test.t3 NULL

559

526

Warnings:

573

540

Warnings:

574

541

Error 1146 Table 'test.t3' doesn't exist

575

542

drop table t1,t2;

576

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

577

show keys from t1;

578

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

579

t1 1 a 1 a A NULL NULL NULL YES BTREE

580

alter table t1 disable keys;

581

show keys from t1;

582

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

583

t1 1 a 1 a A NULL NULL NULL YES BTREE disabled

584

create table t2 (a int);

585

set @@rand_seed1=31415926,@@rand_seed2=2718281828;

586

insert t1 select * from t2;

587

show keys from t1;

588

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

589

t1 1 a 1 a A NULL NULL NULL YES BTREE disabled

590

alter table t1 enable keys;

591

show keys from t1;

592

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

593

t1 1 a 1 a A 1000 NULL NULL YES BTREE

594

alter table t1 engine=heap;

595

alter table t1 disable keys;

596

Warnings:

597

Note 1031 Table storage engine for 't1' doesn't have this option

598

show keys from t1;

599

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

600

t1 1 a 1 a NULL 500 NULL NULL YES HASH

601

drop table t1,t2;

602

543

create table t1 ( a tinytext, b char(1), index idx (a(1),b) );

603

544

insert into t1 values (null,''), (null,'');

604

545

explain select count(*) from t1 where a is null;

605

546

id select_type table type possible_keys key key_len ref rows Extra

606

1 SIMPLE t1 ref idx idx 4 const 1 Using where

547

1 SIMPLE t1 ref idx idx 7 const 1 Using where

607

548

select count(*) from t1 where a is null;

608

549

count(*)

609

550

2

610

551

drop table t1;

611

552

create table t1 (c1 int, c2 varchar(4) not null default '',

612

key(c2(3))) default charset=utf8;

553

key(c2(3)));

613

554

insert into t1 values (1,'A'), (2, 'B'), (3, 'A');

614

555

update t1 set c2='A B' where c1=2;

615

556

check table t1;

630

571

Table Checksum

631

572

test.t2 984116287

632

573

drop table t1, t2;

633

show variables like 'myisam_stats_method';

634

Variable_name Value

635

myisam_stats_method nulls_unequal

636

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

637

insert into t1 values (0),(1),(2),(3),(4);

638

insert into t1 select NULL from t1;

639

analyze table t1;

640

Table Op Msg_type Msg_text

641

test.t1 analyze status OK

642

show index from t1;

643

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

644

t1 1 a 1 a A 10 NULL NULL YES BTREE

645

insert into t1 values (11);

646

delete from t1 where a=11;

647

check table t1;

648

Table Op Msg_type Msg_text

649

test.t1 check status OK

650

show index from t1;

651

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

652

t1 1 a 1 a A 10 NULL NULL YES BTREE

653

set myisam_stats_method=nulls_equal;

654

show variables like 'myisam_stats_method';

655

Variable_name Value

656

myisam_stats_method nulls_equal

657

insert into t1 values (11);

658

delete from t1 where a=11;

659

analyze table t1;

660

Table Op Msg_type Msg_text

661

test.t1 analyze status OK

662

show index from t1;

663

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

664

t1 1 a 1 a A 5 NULL NULL YES BTREE

665

insert into t1 values (11);

666

delete from t1 where a=11;

667

check table t1;

668

Table Op Msg_type Msg_text

669

test.t1 check status OK

670

show index from t1;

671

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

672

t1 1 a 1 a A 5 NULL NULL YES BTREE

673

set myisam_stats_method=DEFAULT;

674

show variables like 'myisam_stats_method';

675

Variable_name Value

676

myisam_stats_method nulls_unequal

677

insert into t1 values (11);

678

delete from t1 where a=11;

679

analyze table t1;

680

Table Op Msg_type Msg_text

681

test.t1 analyze status OK

682

show index from t1;

683

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

684

t1 1 a 1 a A 10 NULL NULL YES BTREE

685

insert into t1 values (11);

686

delete from t1 where a=11;

687

check table t1;

688

Table Op Msg_type Msg_text

689

test.t1 check status OK

690

show index from t1;

691

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

692

t1 1 a 1 a A 10 NULL NULL YES BTREE

693

drop table t1;

694

set myisam_stats_method=nulls_ignored;

695

show variables like 'myisam_stats_method';

696

Variable_name Value

697

myisam_stats_method nulls_ignored

698

create table t1 (

699

a char(3), b char(4), c char(5), d char(6),

700

key(a,b,c,d)

701

);

702

insert into t1 values ('bcd','def1', NULL, 'zz');

703

insert into t1 values ('bcd','def2', NULL, 'zz');

704

insert into t1 values ('bce','def1', 'yuu', NULL);

705

insert into t1 values ('bce','def2', NULL, 'quux');

706

analyze table t1;

707

Table Op Msg_type Msg_text

708

test.t1 analyze status OK

709

show index from t1;

710

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

711

t1 1 a 1 a A 2 NULL NULL YES BTREE

712

t1 1 a 2 b A 4 NULL NULL YES BTREE

713

t1 1 a 3 c A 4 NULL NULL YES BTREE

714

t1 1 a 4 d A 4 NULL NULL YES BTREE

715

delete from t1;

716

analyze table t1;

717

Table Op Msg_type Msg_text

718

test.t1 analyze status OK

719

show index from t1;

720

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

721

t1 1 a 1 a A 0 NULL NULL YES BTREE

722

t1 1 a 2 b A 0 NULL NULL YES BTREE

723

t1 1 a 3 c A 0 NULL NULL YES BTREE

724

t1 1 a 4 d A 0 NULL NULL YES BTREE

725

set myisam_stats_method=DEFAULT;

726

drop table t1;

727

574

create table t1(

728

575

cip INT NOT NULL,

729

576

time TIME NOT NULL,

759

606

count(id1)

760

607

5

761

608

drop table t1;

762

CREATE TABLE t1(a TINYINT, KEY(a)) ENGINE=MyISAM;

609

CREATE TABLE t1(a int, KEY(a)) ENGINE=MyISAM;

763

610

INSERT INTO t1 VALUES(1);

764

611

SELECT MAX(a) FROM t1 IGNORE INDEX(a);

765

612

MAX(a)

780

627

xxxxxxxxx bbbbbb

781

628

xxxxxxxxx bbbbbb

782

629

DROP TABLE t1;

783

SET @@myisam_repair_threads=2;

630

SET GLOBAL myisam_repair_threads=2;

784

631

SHOW VARIABLES LIKE 'myisam_repair%';

785

632

Variable_name Value

786

633

myisam_repair_threads 2

787

634

CREATE TABLE t1 (

788

`_id` int(11) NOT NULL default '0',

635

`_id` int NOT NULL default '0',

789

636

`url` text,

790

637

`email` text,

791

638

`description` text,

792

`loverlap` int(11) default NULL,

793

`roverlap` int(11) default NULL,

794

`lneighbor_id` int(11) default NULL,

795

`rneighbor_id` int(11) default NULL,

796

`length_` int(11) default NULL,

797

`sequence` mediumtext,

639

`loverlap` int default NULL,

640

`roverlap` int default NULL,

641

`lneighbor_id` int default NULL,

642

`rneighbor_id` int default NULL,

643

`length_` int default NULL,

644

`sequence` text,

798

645

`name` text,

799

646

`_obj_class` text NOT NULL,

800

647

PRIMARY KEY (`_id`),

801

648

UNIQUE KEY `sequence_name_index` (`name`(50)),

802

649

KEY (`length_`)

803

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

650

) ENGINE=MyISAM;

804

651

INSERT INTO t1 VALUES

805

652

(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),

806

653

(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),

825

672

DELETE FROM t1 WHERE _id < 8;

826

673

SHOW TABLE STATUS LIKE 't1';

827

674

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

828

t1 MyISAM 10 Dynamic 2 # # # # 140 # # # # # #

675

t1 MyISAM 0 Dynamic 2 # # # # 168 # # # # # #

829

676

CHECK TABLE t1 EXTENDED;

830

677

Table Op Msg_type Msg_text

831

678

test.t1 check status OK

837

684

test.t1 check status OK

838

685

SHOW TABLE STATUS LIKE 't1';

839

686

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

840

t1 MyISAM 10 Dynamic 2 # # # # 0 # # # # # #

687

t1 MyISAM 0 Dynamic 2 # # # # 0 # # # # # #

841

688

SELECT _id FROM t1;

842

689

_id

843

690

8

844

691

9

845

692

DROP TABLE t1;

846

693

CREATE TABLE t1 (

847

`_id` int(11) NOT NULL default '0',

694

`_id` int NOT NULL default '0',

848

695

`url` text,

849

696

`email` text,

850

697

`description` text,

851

`loverlap` int(11) default NULL,

852

`roverlap` int(11) default NULL,

853

`lneighbor_id` int(11) default NULL,

854

`rneighbor_id` int(11) default NULL,

855

`length_` int(11) default NULL,

856

`sequence` mediumtext,

698

`loverlap` int default NULL,

699

`roverlap` int default NULL,

700

`lneighbor_id` int default NULL,

701

`rneighbor_id` int default NULL,

702

`length_` int default NULL,

703

`sequence` text,

857

704

`name` text,

858

705

`_obj_class` text NOT NULL,

859

706

PRIMARY KEY (`_id`),

860

707

UNIQUE KEY `sequence_name_index` (`name`(50)),

861

708

KEY (`length_`)

862

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

709

) ENGINE=MyISAM;

863

710

INSERT INTO t1 VALUES

864

711

(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),

865

712

(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),

884

731

DELETE FROM t1 WHERE _id < 8;

885

732

SHOW TABLE STATUS LIKE 't1';

886

733

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

887

t1 MyISAM 10 Dynamic 2 # # # # 140 # # # # # #

734

t1 MyISAM 0 Dynamic 2 # # # # 168 # # # # # #

888

735

CHECK TABLE t1 EXTENDED;

889

736

Table Op Msg_type Msg_text

890

737

test.t1 check status OK

896

743

test.t1 check status OK

897

744

SHOW TABLE STATUS LIKE 't1';

898

745

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

899

t1 MyISAM 10 Dynamic 2 # # # # 140 # # # # # #

746

t1 MyISAM 0 Dynamic 2 # # # # 168 # # # # # #

900

747

SELECT _id FROM t1;

901

748

_id

902

749

8

903

750

9

904

751

DROP TABLE t1;

905

SET @@myisam_repair_threads=1;

752

SET GLOBAL myisam_repair_threads=1;

906

753

SHOW VARIABLES LIKE 'myisam_repair%';

907

754

Variable_name Value

908

755

myisam_repair_threads 1

909

CREATE TABLE t1(a VARCHAR(16));

756

CREATE TABLE t1(a VARCHAR(16)) ENGINE=MyISAM;

910

757

INSERT INTO t1 VALUES('aaaaaaaa'),(NULL);

911

758

UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa';

912

759

SELECT * FROM t1;

914

761

aaaaaaaaaaaaaaaa

915

762

aaaaaaaaaaaaaaaa

916

763

DROP TABLE t1;

917

CREATE TABLE t1(a INT);

764

CREATE TABLE t1(a INT) ENGINE=MyISAM;

918

765

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

919

766

UPDATE t1,t1 AS t2 SET t1.a=t1.a+2 WHERE t1.a=t2.a-1;

920

767

SELECT * FROM t1 ORDER BY a;

922

769

2

923

770

3

924

771

DROP TABLE t1;

925

CREATE TABLE t1 (c1 TEXT) AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100;

772

CREATE TABLE t1 (c1 TEXT) ENGINE=MyISAM AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100;

926

773

SHOW TABLE STATUS LIKE 't1';

927

774

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

928

t1 MyISAM 10 Dynamic X X X 72057594037927935 X X X X X X latin1_swedish_ci X max_rows=4100100100 avg_row_length=70100

775

t1 MyISAM 0 Dynamic X X X 72057594037927935 X X X X X X utf8_general_ci X max_rows=4100100100 avg_row_length=70100

929

776

DROP TABLE t1;

930

777

CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM;

931

778

INSERT INTO t1 VALUES

1091

938

Note 1265 Data truncated for column 'c' at row 1

1092

939

select concat('*',v,'*',c,'*',t,'*') from t1;

1093

940

concat('*',v,'*',c,'*',t,'*')

1094

*+ *+*+ *

1095

*+ *+*+ *

941

*+ *+ *+ *

942

*+ *+ *+ *

1096

943

show create table t1;

1097

944

Table Create Table

1098

945

t1 CREATE TABLE `t1` (

1099

`v` varchar(10) DEFAULT NULL,

1100

`c` char(10) DEFAULT NULL,

946

`v` varchar(10),

947

`c` varchar(10),

1101

948

`t` text

1102

) ENGINE=MyISAM DEFAULT CHARSET=latin1

949

) ENGINE=MyISAM

1103

950

create table t2 like t1;

1104

951

show create table t2;

1105

952

Table Create Table

1106

953

t2 CREATE TABLE `t2` (

1107

`v` varchar(10) DEFAULT NULL,

1108

`c` char(10) DEFAULT NULL,

954

`v` varchar(10),

955

`c` varchar(10),

1109

956

`t` text

1110

) ENGINE=MyISAM DEFAULT CHARSET=latin1

957

) ENGINE=MyISAM

1111

958

create table t3 select * from t1;

1112

959

show create table t3;

1113

960

Table Create Table

1114

961

t3 CREATE TABLE `t3` (

1115

`v` varchar(10) DEFAULT NULL,

1116

`c` char(10) DEFAULT NULL,

962

`v` varchar(10),

963

`c` varchar(10),

1117

964

`t` text

1118

) ENGINE=MyISAM DEFAULT CHARSET=latin1

965

) ENGINE=MyISAM

1119

966

alter table t1 modify c varchar(10);

1120

967

show create table t1;

1121

968

Table Create Table

1122

969

t1 CREATE TABLE `t1` (

1123

`v` varchar(10) DEFAULT NULL,

1124

`c` varchar(10) DEFAULT NULL,

970

`v` varchar(10),

971

`c` varchar(10),

1125

972

`t` text

1126

) ENGINE=MyISAM DEFAULT CHARSET=latin1

973

) ENGINE=MyISAM

1127

974

alter table t1 modify v char(10);

1128

975

show create table t1;

1129

976

Table Create Table

1130

977

t1 CREATE TABLE `t1` (

1131

`v` char(10) DEFAULT NULL,

1132

`c` varchar(10) DEFAULT NULL,

978

`v` varchar(10),

979

`c` varchar(10),

1133

980

`t` text

1134

) ENGINE=MyISAM DEFAULT CHARSET=latin1

981

) ENGINE=MyISAM

1135

982

alter table t1 modify t varchar(10);

1136

983

Warnings:

1137

984

Note 1265 Data truncated for column 't' at row 2

1138

985

show create table t1;

1139

986

Table Create Table

1140

987

t1 CREATE TABLE `t1` (

1141

`v` char(10) DEFAULT NULL,

1142

`c` varchar(10) DEFAULT NULL,

1143

`t` varchar(10) DEFAULT NULL

1144

) ENGINE=MyISAM DEFAULT CHARSET=latin1

988

`v` varchar(10),

989

`c` varchar(10),

990

`t` varchar(10)

991

) ENGINE=MyISAM

1145

992

select concat('*',v,'*',c,'*',t,'*') from t1;

1146

993

concat('*',v,'*',c,'*',t,'*')

1147

*+*+*+ *

1148

*+*+*+ *

994

*+ *+ *+ *

995

*+ *+ *+ *

1149

996

drop table t1,t2,t3;

1150

997

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1151

998

show create table t1;

1152

999

Table Create Table

1153

1000

t1 CREATE TABLE `t1` (

1154

`v` varchar(10) DEFAULT NULL,

1155

`c` char(10) DEFAULT NULL,

1001

`v` varchar(10),

1002

`c` varchar(10),

1156

1003

`t` text,

1157

1004

KEY `v` (`v`),

1158

1005

KEY `c` (`c`),

1159

KEY `t` (`t`(10))

1160

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1006

KEY `t` (`t`())

1007

) ENGINE=MyISAM

1161

1008

select count(*) from t1;

1162

1009

count(*)

1163

1010

270

1200

1047

9

1201

1048

explain select count(*) from t1 where v='a ';

1202

1049

id select_type table type possible_keys key key_len ref rows Extra

1203

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1050

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1204

1051

explain select count(*) from t1 where c='a ';

1205

1052

id select_type table type possible_keys key key_len ref rows Extra

1206

1 SIMPLE t1 ref c c 11 const # Using where; Using index

1053

1 SIMPLE t1 ref c c 43 const # Using where; Using index

1207

1054

explain select count(*) from t1 where t='a ';

1208

1055

id select_type table type possible_keys key key_len ref rows Extra

1209

1 SIMPLE t1 ref t t 13 const # Using where

1056

1 SIMPLE t1 ref t t 43 const # Using where

1210

1057

explain select count(*) from t1 where v like 'a%';

1211

1058

id select_type table type possible_keys key key_len ref rows Extra

1212

1 SIMPLE t1 range v v 13 NULL # Using where; Using index

1059

1 SIMPLE t1 range v v 43 NULL # Using where; Using index

1213

1060

explain select count(*) from t1 where v between 'a' and 'a ';

1214

1061

id select_type table type possible_keys key key_len ref rows Extra

1215

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1062

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1216

1063

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1217

1064

id select_type table type possible_keys key key_len ref rows Extra

1218

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1065

1 SIMPLE t1 ref v v 43 const # Using where; Using index

1219

1066

alter table t1 add unique(v);

1220

1067

ERROR 23000: Duplicate entry '{ ' for key 'v_2'

1221

1068

alter table t1 add key(v);

1222

1069

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';

1223

1070

qq

1224

1071

*a*a*a*

1225

*a *a*a *

1226

*a *a*a *

1227

*a *a*a *

1228

*a *a*a *

1229

*a *a*a *

1230

*a *a*a *

1231

*a *a*a *

1232

*a *a*a *

1233

*a *a*a *

1072

*a *a *a *

1073

*a *a *a *

1074

*a *a *a *

1075

*a *a *a *

1076

*a *a *a *

1077

*a *a *a *

1078

*a *a *a *

1079

*a *a *a *

1080

*a *a *a *

1234

1081

explain select * from t1 where v='a';

1235

1082

id select_type table type possible_keys key key_len ref rows Extra

1236

1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition

1083

1 SIMPLE t1 ref v,v_2 # 43 const # Using where

1237

1084

select v,count(*) from t1 group by v limit 10;

1238

1085

v count(*)

1239

1086

a 1

1370

1217

show create table t1;

1371

1218

Table Create Table

1372

1219

t1 CREATE TABLE `t1` (

1373

`v` varchar(300) DEFAULT NULL,

1374

`c` char(10) DEFAULT NULL,

1220

`v` varchar(300),

1221

`c` varchar(10),

1375

1222

`t` text,

1376

1223

KEY `c` (`c`),

1377

KEY `t` (`t`(10)),

1224

KEY `t` (`t`()),

1378

1225

KEY `v` (`v`)

1379

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1226

) ENGINE=MyISAM

1380

1227

select count(*) from t1 where v='a';

1381

1228

count(*)

1382

1229

10

1397

1244

9

1398

1245

explain select count(*) from t1 where v='a ';

1399

1246

id select_type table type possible_keys key key_len ref rows Extra

1400

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1247

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1401

1248

explain select count(*) from t1 where v like 'a%';

1402

1249

id select_type table type possible_keys key key_len ref rows Extra

1403

1 SIMPLE t1 range v v 303 NULL # Using where; Using index

1250

1 SIMPLE t1 range v v 1203 NULL # Using where; Using index

1404

1251

explain select count(*) from t1 where v between 'a' and 'a ';

1405

1252

id select_type table type possible_keys key key_len ref rows Extra

1406

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1253

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1407

1254

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1408

1255

id select_type table type possible_keys key key_len ref rows Extra

1409

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1256

1 SIMPLE t1 ref v v 1203 const # Using where; Using index

1410

1257

explain select * from t1 where v='a';

1411

1258

id select_type table type possible_keys key key_len ref rows Extra

1412

1 SIMPLE t1 ref v v 303 const # Using index condition

1259

1 SIMPLE t1 ref v v 1203 const # Using where

1413

1260

select v,count(*) from t1 group by v limit 10;

1414

1261

v count(*)

1415

1262

a 1

1450

1297

show create table t1;

1451

1298

Table Create Table

1452

1299

t1 CREATE TABLE `t1` (

1453

`v` varchar(300) DEFAULT NULL,

1454

`c` char(10) DEFAULT NULL,

1300

`v` varchar(300),

1301

`c` varchar(10),

1455

1302

`t` text,

1456

1303

KEY `c` (`c`),

1457

KEY `t` (`t`(10)),

1458

KEY `v` (`v`(30))

1459

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1304

KEY `t` (`t`()),

1305

KEY `v` (`v`())

1306

) ENGINE=MyISAM

1460

1307

select count(*) from t1 where v='a';

1461

1308

count(*)

1462

1309

10

1477

1324

9

1478

1325

explain select count(*) from t1 where v='a ';

1479

1326

id select_type table type possible_keys key key_len ref rows Extra

1480

1 SIMPLE t1 ref v v 33 const # Using where

1327

1 SIMPLE t1 ref v v 123 const # Using where

1481

1328

explain select count(*) from t1 where v like 'a%';

1482

1329

id select_type table type possible_keys key key_len ref rows Extra

1483

1 SIMPLE t1 range v v 33 NULL # Using where

1330

1 SIMPLE t1 range v v 123 NULL # Using where

1484

1331

explain select count(*) from t1 where v between 'a' and 'a ';

1485

1332

id select_type table type possible_keys key key_len ref rows Extra

1486

1 SIMPLE t1 ref v v 33 const # Using where

1333

1 SIMPLE t1 ref v v 123 const # Using where

1487

1334

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1488

1335

id select_type table type possible_keys key key_len ref rows Extra

1489

1 SIMPLE t1 ref v v 33 const # Using where

1336

1 SIMPLE t1 ref v v 123 const # Using where

1490

1337

explain select * from t1 where v='a';

1491

1338

id select_type table type possible_keys key key_len ref rows Extra

1492

1 SIMPLE t1 ref v v 33 const # Using where

1339

1 SIMPLE t1 ref v v 123 const # Using where

1493

1340

select v,count(*) from t1 group by v limit 10;

1494

1341

v count(*)

1495

1342

a 1

1527

1374

h 10

1528

1375

i 10

1529

1376

alter table t1 modify v varchar(600), drop key v, add key v (v);

1377

Warnings:

1378

Warning 1071 Specified key was too long; max key length is 1332 bytes

1379

Warning 1071 Specified key was too long; max key length is 1332 bytes

1380

Warning 1071 Specified key was too long; max key length is 1332 bytes

1530

1381

show create table t1;

1531

1382

Table Create Table

1532

1383

t1 CREATE TABLE `t1` (

1533

`v` varchar(600) DEFAULT NULL,

1534

`c` char(10) DEFAULT NULL,

1384

`v` varchar(600),

1385

`c` varchar(10),

1535

1386

`t` text,

1536

1387

KEY `c` (`c`),

1537

KEY `t` (`t`(10)),

1538

KEY `v` (`v`)

1539

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1388

KEY `t` (`t`()),

1389

KEY `v` (`v`())

1390

) ENGINE=MyISAM

1540

1391

select v,count(*) from t1 group by v limit 10;

1541

1392

v count(*)

1542

1393

a 1

1577

1428

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

1578

1429

insert into t1 values ('a ');

1579

1430

insert into t1 values ('a ');

1580

ERROR 23000: Duplicate entry 'a' for key 'a'

1431

ERROR 23000: Duplicate entry 'a ' for key 'a'

1581

1432

alter table t1 modify a varchar(10);

1582

1433

insert into t1 values ('a '),('a '),('a '),('a ');

1583

1434

ERROR 23000: Duplicate entry 'a ' for key 'a'

1608

1459

show create table t1;

1609

1460

Table Create Table

1610

1461

t1 CREATE TABLE `t1` (

1611

`v` varchar(10) DEFAULT NULL,

1612

`c` char(10) DEFAULT NULL,

1462

`v` varchar(10),

1463

`c` varchar(10),

1613

1464

`t` text,

1614

KEY `v` (`v`(5)),

1615

KEY `c` (`c`(5)),

1616

KEY `t` (`t`(5))

1617

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1465

KEY `v` (`v`()),

1466

KEY `c` (`c`()),

1467

KEY `t` (`t`())

1468

) ENGINE=MyISAM

1618

1469

drop table t1;

1619

create table t1 (v char(10) character set utf8);

1470

create table t1 (v char(10));

1620

1471

show create table t1;

1621

1472

Table Create Table

1622

1473

t1 CREATE TABLE `t1` (

1623

`v` char(10) CHARACTER SET utf8 DEFAULT NULL

1624

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1474

`v` varchar(10)

1475

) ENGINE=MyISAM

1625

1476

drop table t1;

1626

1477

create table t1 (v varchar(10), c char(10)) row_format=fixed;

1627

1478

show create table t1;

1628

1479

Table Create Table

1629

1480

t1 CREATE TABLE `t1` (

1630

`v` varchar(10) DEFAULT NULL,

1631

`c` char(10) DEFAULT NULL

1632

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

1481

`v` varchar(10),

1482

`c` varchar(10)

1483

) ENGINE=MyISAM ROW_FORMAT=FIXED

1633

1484

insert into t1 values('a','a'),('a ','a ');

1634

1485

select concat('*',v,'*',c,'*') from t1;

1635

1486

concat('*',v,'*',c,'*')

1636

1487

*a*a*

1637

*a *a*

1638

drop table t1;

1639

create table t1 (v varchar(65530), key(v(10)));

1640

insert into t1 values(repeat('a',65530));

1641

select length(v) from t1 where v=repeat('a',65530);

1642

length(v)

1643

65530

1488

*a *a *

1644

1489

drop table t1;

1645

1490

create table t1(a int, b varchar(12), key ba(b, a));

1646

1491

insert into t1 values (1, 'A'), (20, NULL);

1647

1492

explain select * from t1 where a=20 and b is null;

1648

1493

id select_type table type possible_keys key key_len ref rows Extra

1649

1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index

1494

1 SIMPLE t1 ref ba ba 56 const,const 1 Using where; Using index

1650

1495

select * from t1 where a=20 and b is null;

1651

1496

a b

1652

1497

20 NULL

1653

1498

drop table t1;

1654

1499

create table t1 (v varchar(65530), key(v));

1655

Warnings:

1656

Warning 1071 Specified key was too long; max key length is 1332 bytes

1657

drop table if exists t1;

1500

ERROR 42000: Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead

1658

1501

create table t1 (v varchar(65536));

1659

Warnings:

1660

Note 1246 Converting column 'v' from VARCHAR to TEXT

1661

show create table t1;

1662

Table Create Table

1663

t1 CREATE TABLE `t1` (

1664

`v` mediumtext

1665

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1666

drop table t1;

1667

create table t1 (v varchar(65530) character set utf8);

1668

Warnings:

1669

Note 1246 Converting column 'v' from VARCHAR to TEXT

1670

show create table t1;

1671

Table Create Table

1672

t1 CREATE TABLE `t1` (

1673

`v` mediumtext CHARACTER SET utf8

1674

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1675

drop table t1;

1502

ERROR 42000: Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead

1503

create table t1 (v varchar(65530));

1504

ERROR 42000: Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead

1676

1505

create table t1 (v varchar(65535));

1677

ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

1678

set storage_engine=MyISAM;

1679

set @save_concurrent_insert=@@concurrent_insert;

1680

set global concurrent_insert=1;

1681

create table t1 (a int);

1682

insert into t1 values (1),(2),(3),(4),(5);

1683

lock table t1 read local;

1684

insert into t1 values(6),(7);

1685

unlock tables;

1686

delete from t1 where a>=3 and a<=4;

1687

lock table t1 read local;

1688

set global concurrent_insert=2;

1689

insert into t1 values (8),(9);

1690

unlock tables;

1691

insert into t1 values (10),(11),(12);

1692

select * from t1;

1693

a

1694

1

1695

2

1696

11

1697

10

1698

5

1699

6

1700

7

1701

8

1702

9

1703

12

1704

check table t1;

1705

Table Op Msg_type Msg_text

1706

test.t1 check status OK

1707

drop table t1;

1708

create table t1 (a int, b varchar(30) default "hello");

1709

insert into t1 (a) values (1),(2),(3),(4),(5);

1710

lock table t1 read local;

1711

insert into t1 (a) values(6),(7);

1712

unlock tables;

1713

delete from t1 where a>=3 and a<=4;

1714

lock table t1 read local;

1715

set global concurrent_insert=2;

1716

insert into t1 (a) values (8),(9);

1717

unlock tables;

1718

insert into t1 (a) values (10),(11),(12);

1719

select a from t1;

1720

a

1721

1

1722

2

1723

11

1724

10

1725

5

1726

6

1727

7

1728

8

1729

9

1730

12

1731

check table t1;

1732

Table Op Msg_type Msg_text

1733

test.t1 check status OK

1734

drop table t1;

1735

set global concurrent_insert=@save_concurrent_insert;

1506

ERROR 42000: Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead

1507

set storage_engine=InnoDB;

1736

1508

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

1737

1509

insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL);

1738

1510

analyze table t1;

1742

1514

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

1743

1515

t1 1 a 1 a A 8 NULL NULL YES BTREE

1744

1516

alter table t1 disable keys;

1517

Warnings:

1518

Note 1031 Table storage engine for 't1' doesn't have this option

1745

1519

alter table t1 enable keys;

1520

Warnings:

1521

Note 1031 Table storage engine for 't1' doesn't have this option

1746

1522

show keys from t1;

1747

1523

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

1748

1524

t1 1 a 1 a A 8 NULL NULL YES BTREE

1751

1527

create table t2 (c1 int) engine=myisam pack_keys=1;

1752

1528

create table t3 (c1 int) engine=myisam pack_keys=default;

1753

1529

create table t4 (c1 int) engine=myisam pack_keys=2;

1754

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1

1530

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '2' at line 1

1755

1531

drop table t1, t2, t3;

1756

1532

CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;

1757

1533

INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);

1782

1558

CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)) ENGINE=MYISAM;

1783

1559

SHOW TABLE STATUS LIKE 't1';

1784

1560

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1785

t1 MyISAM 10 Fixed 0 # # # 1024 # # # # # # #

1561

t1 MyISAM 0 Fixed 0 # # # 1024 # # # # # # #

1786

1562

INSERT INTO t1 VALUES (1,1);

1787

1563

SHOW TABLE STATUS LIKE 't1';

1788

1564

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1789

t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #

1790

ALTER TABLE t1 DISABLE KEYS;

1791

SHOW TABLE STATUS LIKE 't1';

1792

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1793

t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #

1794

ALTER TABLE t1 ENABLE KEYS;

1795

SHOW TABLE STATUS LIKE 't1';

1796

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1797

t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #

1798

ALTER TABLE t1 DISABLE KEYS;

1799

SHOW TABLE STATUS LIKE 't1';

1800

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1801

t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #

1802

ALTER TABLE t1 ENABLE KEYS;

1803

SHOW TABLE STATUS LIKE 't1';

1804

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1805

t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # #

1565

t1 MyISAM 0 Fixed 1 # # # 3072 # # # # # # #

1566

ALTER TABLE t1 DISABLE KEYS;

1567

SHOW TABLE STATUS LIKE 't1';

1568

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1569

t1 MyISAM 0 Fixed 1 # # # 3072 # # # # # # #

1570

ALTER TABLE t1 ENABLE KEYS;

1571

SHOW TABLE STATUS LIKE 't1';

1572

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1573

t1 MyISAM 0 Fixed 1 # # # 3072 # # # # # # #

1574

ALTER TABLE t1 DISABLE KEYS;

1575

SHOW TABLE STATUS LIKE 't1';

1576

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1577

t1 MyISAM 0 Fixed 1 # # # 3072 # # # # # # #

1578

ALTER TABLE t1 ENABLE KEYS;

1579

SHOW TABLE STATUS LIKE 't1';

1580

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

1581

t1 MyISAM 0 Fixed 1 # # # 3072 # # # # # # #

1806

1582

# Enable keys with parallel repair

1807

SET @@myisam_repair_threads=2;

1583

SET GLOBAL myisam_repair_threads=2;

1808

1584

ALTER TABLE t1 DISABLE KEYS;

1809

1585

ALTER TABLE t1 ENABLE KEYS;

1810

SET @@myisam_repair_threads=1;

1586

SET GLOBAL myisam_repair_threads=1;

1811

1587

CHECK TABLE t1 EXTENDED;

1812

1588

Table Op Msg_type Msg_text

1813

1589

test.t1 check status OK

1835

1611

4 5

1836

1612

DROP TABLE t1, t2;

1837

1613

End of 5.0 tests

1838

create table t1 (a int not null, key `a` (a) key_block_size=1024);

1614

create table t1 (a int not null, key `a` (a) key_block_size=1024) ENGINE=MyISAM;

1839

1615

show create table t1;

1840

1616

Table Create Table

1841

1617

t1 CREATE TABLE `t1` (

1842

`a` int(11) NOT NULL,

1618

`a` int NOT NULL,

1843

1619

KEY `a` (`a`) KEY_BLOCK_SIZE=1024

1844

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1620

) ENGINE=MyISAM

1845

1621

drop table t1;

1846

create table t1 (a int not null, key `a` (a) key_block_size=2048);

1622

create table t1 (a int not null, key `a` (a) key_block_size=2048) ENGINE=MyISAM;

1847

1623

show create table t1;

1848

1624

Table Create Table

1849

1625

t1 CREATE TABLE `t1` (

1850

`a` int(11) NOT NULL,

1626

`a` int NOT NULL,

1851

1627

KEY `a` (`a`) KEY_BLOCK_SIZE=2048

1852

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1853

drop table t1;

1854

create table t1 (a varchar(2048), key `a` (a));

1855

Warnings:

1856

Warning 1071 Specified key was too long; max key length is 1332 bytes

1857

show create table t1;

1858

Table Create Table

1859

t1 CREATE TABLE `t1` (

1860

`a` varchar(2048) DEFAULT NULL,

1861

KEY `a` (`a`(1332))

1862

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1863

drop table t1;

1864

create table t1 (a varchar(2048), key `a` (a) key_block_size=1024);

1865

Warnings:

1866

Warning 1071 Specified key was too long; max key length is 1332 bytes

1867

show create table t1;

1868

Table Create Table

1869

t1 CREATE TABLE `t1` (

1870

`a` varchar(2048) DEFAULT NULL,

1871

KEY `a` (`a`(1332)) KEY_BLOCK_SIZE=6144

1872

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1873

drop table t1;

1874

create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024;

1875

Warnings:

1876

Warning 1071 Specified key was too long; max key length is 1332 bytes

1877

show create table t1;

1878

Table Create Table

1879

t1 CREATE TABLE `t1` (

1880

`a` int(11) NOT NULL,

1881

`b` varchar(2048) DEFAULT NULL,

1628

) ENGINE=MyISAM

1629

drop table t1;

1630

create table t1 (a varchar(2048), key `a` (a)) ENGINE=MyISAM;

1631

Warnings:

1632

Warning 1071 Specified key was too long; max key length is 1332 bytes

1633

show create table t1;

1634

Table Create Table

1635

t1 CREATE TABLE `t1` (

1636

`a` varchar(2048),

1637

KEY `a` (`a`())

1638

) ENGINE=MyISAM

1639

drop table t1;

1640

create table t1 (a varchar(2048), key `a` (a) key_block_size=1024) ENGINE=MyISAM;

1641

Warnings:

1642

Warning 1071 Specified key was too long; max key length is 1332 bytes

1643

show create table t1;

1644

Table Create Table

1645

t1 CREATE TABLE `t1` (

1646

`a` varchar(2048),

1647

KEY `a` (`a`()) KEY_BLOCK_SIZE=6144

1648

) ENGINE=MyISAM

1649

drop table t1;

1650

create table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=1024;

1651

Warnings:

1652

Warning 1071 Specified key was too long; max key length is 1332 bytes

1653

show create table t1;

1654

Table Create Table

1655

t1 CREATE TABLE `t1` (

1656

`a` int NOT NULL,

1657

`b` varchar(2048),

1882

1658

KEY `a` (`a`),

1883

KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=6144

1884

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024

1659

KEY `b` (`b`()) KEY_BLOCK_SIZE=6144

1660

) ENGINE=MyISAM KEY_BLOCK_SIZE=1024

1885

1661

alter table t1 key_block_size=2048;

1886

1662

show create table t1;

1887

1663

Table Create Table

1888

1664

t1 CREATE TABLE `t1` (

1889

`a` int(11) NOT NULL,

1890

`b` varchar(2048) DEFAULT NULL,

1665

`a` int NOT NULL,

1666

`b` varchar(2048),

1891

1667

KEY `a` (`a`) KEY_BLOCK_SIZE=1024,

1892

KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192

1893

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048

1668

KEY `b` (`b`()) KEY_BLOCK_SIZE=8192

1669

) ENGINE=MyISAM KEY_BLOCK_SIZE=2048

1894

1670

alter table t1 add c int, add key (c);

1895

1671

show create table t1;

1896

1672

Table Create Table

1897

1673

t1 CREATE TABLE `t1` (

1898

`a` int(11) NOT NULL,

1899

`b` varchar(2048) DEFAULT NULL,

1900

`c` int(11) DEFAULT NULL,

1674

`a` int NOT NULL,

1675

`b` varchar(2048),

1676

`c` int,

1901

1677

KEY `a` (`a`) KEY_BLOCK_SIZE=1024,

1902

KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192,

1678

KEY `b` (`b`()) KEY_BLOCK_SIZE=8192,

1903

1679

KEY `c` (`c`)

1904

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048

1680

) ENGINE=MyISAM KEY_BLOCK_SIZE=2048

1905

1681

alter table t1 key_block_size=0;

1906

1682

alter table t1 add d int, add key (d);

1907

1683

show create table t1;

1908

1684

Table Create Table

1909

1685

t1 CREATE TABLE `t1` (

1910

`a` int(11) NOT NULL,

1911

`b` varchar(2048) DEFAULT NULL,

1912

`c` int(11) DEFAULT NULL,

1913

`d` int(11) DEFAULT NULL,

1686

`a` int NOT NULL,

1687

`b` varchar(2048),

1688

`c` int,

1689

`d` int,

1914

1690

KEY `a` (`a`) KEY_BLOCK_SIZE=1024,

1915

KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192,

1691

KEY `b` (`b`()) KEY_BLOCK_SIZE=8192,

1916

1692

KEY `c` (`c`) KEY_BLOCK_SIZE=2048,

1917

1693

KEY `d` (`d`)

1918

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1694

) ENGINE=MyISAM

1919

1695

drop table t1;

1920

create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192;

1696

create table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=8192;

1921

1697

Warnings:

1922

1698

Warning 1071 Specified key was too long; max key length is 1332 bytes

1923

1699

show create table t1;

1924

1700

Table Create Table

1925

1701

t1 CREATE TABLE `t1` (

1926

`a` int(11) NOT NULL,

1927

`b` varchar(2048) DEFAULT NULL,

1702

`a` int NOT NULL,

1703

`b` varchar(2048),

1928

1704

KEY `a` (`a`),

1929

KEY `b` (`b`(1332))

1930

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192

1705

KEY `b` (`b`())

1706

) ENGINE=MyISAM KEY_BLOCK_SIZE=8192

1931

1707

drop table t1;

1932

create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192;

1708

create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) ENGINE=MyISAM key_block_size=8192;

1933

1709

Warnings:

1934

1710

Warning 1071 Specified key was too long; max key length is 1332 bytes

1935

1711

show create table t1;

1936

1712

Table Create Table

1937

1713

t1 CREATE TABLE `t1` (

1938

`a` int(11) NOT NULL,

1939

`b` varchar(2048) DEFAULT NULL,

1714

`a` int NOT NULL,

1715

`b` varchar(2048),

1940

1716

KEY `a` (`a`) KEY_BLOCK_SIZE=1024,

1941

KEY `b` (`b`(1332))

1942

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192

1717

KEY `b` (`b`())

1718

) ENGINE=MyISAM KEY_BLOCK_SIZE=8192

1943

1719

drop table t1;

1944

create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384;

1720

create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) ENGINE=MyISAM key_block_size=16384;

1945

1721

show create table t1;

1946

1722

Table Create Table

1947

1723

t1 CREATE TABLE `t1` (

1948

`a` int(11) NOT NULL,

1949

`b` int(11) DEFAULT NULL,

1724

`a` int NOT NULL,

1725

`b` int,

1950

1726

KEY `a` (`a`) KEY_BLOCK_SIZE=1024,

1951

1727

KEY `b` (`b`) KEY_BLOCK_SIZE=8192

1952

) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16384

1728

) ENGINE=MyISAM KEY_BLOCK_SIZE=16384

1953

1729

drop table t1;

1954

create table t1 (a int not null, key `a` (a) key_block_size=512);

1730

create table t1 (a int not null, key `a` (a) key_block_size=512) ENGINE=MyISAM;

1955

1731

show create table t1;

1956

1732

Table Create Table

1957

1733

t1 CREATE TABLE `t1` (

1958

`a` int(11) NOT NULL,

1734

`a` int NOT NULL,

1959

1735

KEY `a` (`a`) KEY_BLOCK_SIZE=1024

1960

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1736

) ENGINE=MyISAM

1961

1737

drop table t1;

1962

create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);

1738

create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000) ENGINE=MyISAM;

1963

1739

Warnings:

1964

1740

Warning 1071 Specified key was too long; max key length is 1332 bytes

1965

1741

show create table t1;

1966

1742

Table Create Table

1967

1743

t1 CREATE TABLE `t1` (

1968

`a` varchar(2048) DEFAULT NULL,

1969

KEY `a` (`a`(1332)) KEY_BLOCK_SIZE=6144

1970

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1744

`a` varchar(2048),

1745

KEY `a` (`a`()) KEY_BLOCK_SIZE=6144

1746

) ENGINE=MyISAM

1971

1747

drop table t1;

1972

create table t1 (a int not null, key `a` (a) key_block_size=1025);

1748

create table t1 (a int not null, key `a` (a) key_block_size=1025) ENGINE=MyISAM;

1973

1749

show create table t1;

1974

1750

Table Create Table

1975

1751

t1 CREATE TABLE `t1` (

1976

`a` int(11) NOT NULL,

1752

`a` int NOT NULL,

1977

1753

KEY `a` (`a`) KEY_BLOCK_SIZE=2048

1978

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1754

) ENGINE=MyISAM

1979

1755

drop table t1;

1980

create table t1 (a int not null, key key_block_size=1024 (a));

1981

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1024 (a))' at line 1

1982

create table t1 (a int not null, key `a` key_block_size=1024 (a));

1983

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_block_size=1024 (a))' at line 1

1756

create table t1 (a int not null, key key_block_size=1024 (a)) ENGINE=MyISAM;

1757

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '=1024 (a)) ENGINE=MyISAM' at line 1

1758

create table t1 (a int not null, key `a` key_block_size=1024 (a)) ENGINE=MyISAM;

1759

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_block_size=1024 (a)) ENGINE=MyISAM' at line 1

1984

1760

CREATE TABLE t1 (

1985

1761

c1 INT,

1986

1762

c2 VARCHAR(300),

1987

1763

KEY (c1) KEY_BLOCK_SIZE 1024,

1988

1764

KEY (c2) KEY_BLOCK_SIZE 8192

1989

);

1765

) ENGINE=MyISAM;

1990

1766

INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),

1991

1767

(11, REPEAT('b', CEIL(RAND() * 300))),

1992

1768

(12, REPEAT('c', CEIL(RAND() * 300))),

2110

1886

CREATE TABLE t1 (

2111

1887

c1 CHAR(50),

2112

1888

c2 VARCHAR(1)

2113

) ENGINE=MyISAM DEFAULT CHARSET UTF8;

1889

) ENGINE=MyISAM;

2114

1890

INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');

2115

1891

SELECT COUNT(*) FROM t1;

2116

1892

COUNT(*)

2131

1907

CREATE TABLE t1 (

2132

1908

c1 CHAR(50),

2133

1909

c2 VARCHAR(1)

2134

) ENGINE=MyISAM DEFAULT CHARSET UTF8;

1910

) ENGINE=MyISAM;

2135

1911

INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');

2136

1912

SELECT COUNT(*) FROM t1;

2137

1913

COUNT(*)

2152

1928

CREATE TABLE t1 (

2153

1929

c1 CHAR(50),

2154

1930

c2 VARCHAR(1)

2155

) ENGINE=MyISAM DEFAULT CHARSET UTF8;

1931

) ENGINE=MyISAM;

2156

1932

INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');

2157

1933

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

2158

1934

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

2171

1947

c1 CHAR(50),

2172

1948

c2 VARCHAR(1),

2173

1949

KEY (c1)

2174

) ENGINE=MyISAM DEFAULT CHARSET UTF8;

1950

) ENGINE=MyISAM;

2175

1951

# Insert 100 rows. Query log disabled.

2176

1952

UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90;

2177

1953

SELECT COUNT(*) FROM t1;

2188

1964

Table Op Msg_type Msg_text

2189

1965

test.t1 check status OK

2190

1966

DROP TABLE t1;

2191

CREATE TABLE t1 (

2192

c1 VARCHAR(10) NOT NULL,

2193

c2 CHAR(10) DEFAULT NULL,

2194

c3 VARCHAR(10) NOT NULL,

2195

KEY (c1),

2196

KEY (c2)

2197

) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;

2198

2199

MyISAM file: MYSQLTEST_VARDIR/master-data/test/t1

2200

Record format: Packed

2201

Character set: utf8_general_ci (45)

2202

Data records: 0 Deleted blocks: 0

2203

Recordlength: 124

2204

2205

table description:

2206

Key Start Len Index Type

2207

1 2 40 multip. varchar

2208

2 43 40 multip. char NULL

2209

DROP TABLE t1;

2210

1967

End of 5.1 tests