52
52
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
53
53
show COLUMNS FROM t1;
54
Field Type Null Key Default Extra
57
NAME varchar(80) NO MUL NULL
54
Field Type Null Default Default_is_NULL On_Update
55
GROUP_ID INTEGER FALSE 0 FALSE
56
LANG_ID INTEGER FALSE 0 FALSE
57
NAME VARCHAR FALSE 0 FALSE
59
59
create table t1 (n int);
60
60
insert into t1 values(9),(3),(12),(10);
116
116
key (n4, n1, n2, n3) );
118
118
show keys from t1;
119
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
120
t1 0 n1 1 n1 A 0 NULL NULL BTREE
121
t1 1 n1_2 1 n1 A 0 NULL NULL BTREE
122
t1 1 n1_2 2 n2 A 0 NULL NULL YES BTREE
123
t1 1 n1_2 3 n3 A 0 NULL NULL YES BTREE
124
t1 1 n1_2 4 n4 A 0 NULL NULL YES BTREE
125
t1 1 n2 1 n2 A 0 NULL NULL YES BTREE
126
t1 1 n2 2 n3 A 0 NULL NULL YES BTREE
127
t1 1 n2 3 n4 A 0 NULL NULL YES BTREE
128
t1 1 n2 4 n1 A 0 NULL NULL BTREE
129
t1 1 n3 1 n3 A 0 NULL NULL YES BTREE
130
t1 1 n3 2 n4 A 0 NULL NULL YES BTREE
131
t1 1 n3 3 n1 A 0 NULL NULL BTREE
132
t1 1 n3 4 n2 A 0 NULL NULL YES BTREE
133
t1 1 n4 1 n4 A 0 NULL NULL YES BTREE
134
t1 1 n4 2 n1 A 0 NULL NULL BTREE
135
t1 1 n4 3 n2 A 0 NULL NULL YES BTREE
136
t1 1 n4 4 n3 A 0 NULL NULL YES BTREE
119
Table Unique Key_name Seq_in_index Column_name
137
137
set autocommit=0;
139
139
insert into t1 values(10,RAND()*1000,RAND()*1000,RAND());
153
153
Note 1031 Table storage engine for 't1' doesn't have this option
154
154
show keys from t1;
155
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
156
t1 0 n1 1 n1 A 2 NULL NULL BTREE
157
t1 1 n1_2 1 n1 A 2 NULL NULL BTREE
158
t1 1 n1_2 2 n2 A 2 NULL NULL YES BTREE
159
t1 1 n1_2 3 n3 A 2 NULL NULL YES BTREE
160
t1 1 n1_2 4 n4 A 2 NULL NULL YES BTREE
161
t1 1 n2 1 n2 A 2 NULL NULL YES BTREE
162
t1 1 n2 2 n3 A 2 NULL NULL YES BTREE
163
t1 1 n2 3 n4 A 2 NULL NULL YES BTREE
164
t1 1 n2 4 n1 A 2 NULL NULL BTREE
165
t1 1 n3 1 n3 A 2 NULL NULL YES BTREE
166
t1 1 n3 2 n4 A 2 NULL NULL YES BTREE
167
t1 1 n3 3 n1 A 2 NULL NULL BTREE
168
t1 1 n3 4 n2 A 2 NULL NULL YES BTREE
169
t1 1 n4 1 n4 A 2 NULL NULL YES BTREE
170
t1 1 n4 2 n1 A 2 NULL NULL BTREE
171
t1 1 n4 3 n2 A 2 NULL NULL YES BTREE
172
t1 1 n4 4 n3 A 2 NULL NULL YES BTREE
155
Table Unique Key_name Seq_in_index Column_name
174
174
create table t1 (i int not null auto_increment primary key);
175
175
alter table t1 rename t2;
176
176
alter table t2 rename t1, add c char(10) comment "no comment";
177
177
show columns from t1;
178
Field Type Null Key Default Extra
179
i int NO PRI NULL auto_increment
180
c varchar(10) YES NULL
178
Field Type Null Default Default_is_NULL On_Update
179
i INTEGER FALSE 0 FALSE
180
c VARCHAR TRUE 0 TRUE
182
182
create table t1 (a int, b int);
183
183
set autocommit=0;
286
286
set autocommit=1;
287
287
alter table t1 add unique (a,b), add key (b);
288
288
show keys from t1;
289
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
290
t1 0 a 1 a A 6 NULL NULL YES BTREE
291
t1 0 a 2 b A 300 NULL NULL YES BTREE
292
t1 1 b 1 b A 300 NULL NULL YES BTREE
289
Table Unique Key_name Seq_in_index Column_name
293
293
analyze table t1;
294
294
Table Op Msg_type Msg_text
295
295
test.t1 analyze status OK
296
296
show keys from t1;
297
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
298
t1 0 a 1 a A 6 NULL NULL YES BTREE
299
t1 0 a 2 b A 300 NULL NULL YES BTREE
300
t1 1 b 1 b A 300 NULL NULL YES BTREE
297
Table Unique Key_name Seq_in_index Column_name
302
302
CREATE TEMPORARY TABLE t1 (
303
303
Host varchar(16) NOT NULL default '',
308
308
ALTER TABLE t1 DISABLE KEYS;
310
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
311
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
312
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
313
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
314
309
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
316
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
317
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
318
t1 0 PRIMARY 2 User A 2 NULL NULL BTREE
319
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
320
310
ALTER TABLE t1 ENABLE KEYS;
322
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
323
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
324
t1 0 PRIMARY 2 User A 2 NULL NULL BTREE
325
t1 1 Host 1 Host A NULL NULL NULL BTREE
327
312
Table Op Msg_type Msg_text
328
313
test.t1 check status OK
350
335
create TEMPORARY table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
351
336
insert into t1 (a,b,c,d,e,f,g,h,i) values(1,1,1,1,1,1,1,1,1);
352
337
show table status like 't1';
353
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
338
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
339
# test t1 TEMPORARY MyISAM # # # # #
354
340
alter table t1 modify a int;
355
341
show table status like 't1';
356
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
342
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
343
# test t1 TEMPORARY MyISAM # # # # #
358
345
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;
359
346
insert into t1 (a) values(1);
360
347
show table status like 't1';
361
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
348
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
349
# test t1 TEMPORARY MyISAM # # # # #
363
351
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
364
352
ALTER TABLE t1 DROP PRIMARY KEY;
389
377
drop table if exists t1;
390
378
create table t1 (a int, key(a));
391
379
show indexes from t1;
392
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
393
t1 1 a 1 a A 0 NULL NULL YES BTREE
380
Table Unique Key_name Seq_in_index Column_name
394
382
"this used not to disable the index"
395
383
alter table t1 modify a int;
396
384
show indexes from t1;
397
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
398
t1 1 a 1 a A 0 NULL NULL YES BTREE
385
Table Unique Key_name Seq_in_index Column_name
399
387
alter table t1 enable keys;
401
389
Note 1031 Table storage engine for 't1' doesn't have this option
402
390
show indexes from t1;
403
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
404
t1 1 a 1 a A 0 NULL NULL YES BTREE
391
Table Unique Key_name Seq_in_index Column_name
405
393
alter table t1 modify a bigint;
406
394
show indexes from t1;
407
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
408
t1 1 a 1 a A 0 NULL NULL YES BTREE
395
Table Unique Key_name Seq_in_index Column_name
409
397
alter table t1 enable keys;
411
399
Note 1031 Table storage engine for 't1' doesn't have this option
412
400
show indexes from t1;
413
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
414
t1 1 a 1 a A 0 NULL NULL YES BTREE
401
Table Unique Key_name Seq_in_index Column_name
415
403
alter table t1 add b char(10);
416
404
show indexes from t1;
417
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
418
t1 1 a 1 a A 0 NULL NULL YES BTREE
405
Table Unique Key_name Seq_in_index Column_name
419
407
alter table t1 add c decimal(10,2);
420
408
show indexes from t1;
421
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
422
t1 1 a 1 a A 0 NULL NULL YES BTREE
409
Table Unique Key_name Seq_in_index Column_name
423
411
"this however did"
425
413
show indexes from t1;
426
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
427
t1 1 a 1 a A 0 NULL NULL YES BTREE
414
Table Unique Key_name Seq_in_index Column_name
429
Field Type Null Key Default Extra
430
a bigint YES MUL NULL
431
b varchar(10) YES NULL
432
c decimal(10,2) YES NULL
417
Field Type Null Default Default_is_NULL On_Update
433
421
alter table t1 add d decimal(15,5);
434
422
"The key should still be disabled"
435
423
show indexes from t1;
436
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
437
t1 1 a 1 a A 0 NULL NULL YES BTREE
424
Table Unique Key_name Seq_in_index Column_name
439
427
"Now will test with one unique index"
440
428
create table t1(a int, b char(10), unique(a));
441
429
show indexes from t1;
442
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
443
t1 0 a 1 a A 0 NULL NULL YES BTREE
430
Table Unique Key_name Seq_in_index Column_name
445
433
show indexes from t1;
446
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
447
t1 0 a 1 a A 0 NULL NULL YES BTREE
434
Table Unique Key_name Seq_in_index Column_name
448
436
alter table t1 enable keys;
450
438
Note 1031 Table storage engine for 't1' doesn't have this option
452
440
"Unique index, no change"
453
441
alter table t1 modify a int;
454
442
show indexes from t1;
455
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
456
t1 0 a 1 a A 0 NULL NULL YES BTREE
443
Table Unique Key_name Seq_in_index Column_name
457
445
"Change the type implying data copy"
458
446
"Unique index, no change"
459
447
alter table t1 modify a bigint;
460
448
show indexes from t1;
461
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
462
t1 0 a 1 a A 0 NULL NULL YES BTREE
449
Table Unique Key_name Seq_in_index Column_name
463
451
alter table t1 modify a bigint;
464
452
show indexes from t1;
465
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
466
t1 0 a 1 a A 0 NULL NULL YES BTREE
453
Table Unique Key_name Seq_in_index Column_name
467
455
alter table t1 modify a int;
468
456
show indexes from t1;
469
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
470
t1 0 a 1 a A 0 NULL NULL YES BTREE
457
Table Unique Key_name Seq_in_index Column_name
472
460
"Now will test with one unique and one non-unique index"
473
461
create table t1(a int, b char(10), unique(a), key(b));
474
462
show indexes from t1;
475
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
476
t1 0 a 1 a A 0 NULL NULL YES BTREE
477
t1 1 b 1 b A 0 NULL NULL YES BTREE
463
Table Unique Key_name Seq_in_index Column_name
479
467
show indexes from t1;
480
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
481
t1 0 a 1 a A 0 NULL NULL YES BTREE
482
t1 1 b 1 b A 0 NULL NULL YES BTREE
468
Table Unique Key_name Seq_in_index Column_name
483
471
alter table t1 enable keys;
485
473
Note 1031 Table storage engine for 't1' doesn't have this option
487
475
"The non-unique index will be disabled"
488
476
alter table t1 modify a int;
489
477
show indexes from t1;
490
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
491
t1 0 a 1 a A 0 NULL NULL YES BTREE
492
t1 1 b 1 b A 0 NULL NULL YES BTREE
478
Table Unique Key_name Seq_in_index Column_name
493
481
alter table t1 enable keys;
495
483
Note 1031 Table storage engine for 't1' doesn't have this option
496
484
show indexes from t1;
497
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
498
t1 0 a 1 a A 0 NULL NULL YES BTREE
499
t1 1 b 1 b A 0 NULL NULL YES BTREE
485
Table Unique Key_name Seq_in_index Column_name
500
488
"Change the type implying data copy"
501
489
"The non-unique index will be disabled"
502
490
alter table t1 modify a bigint;
503
491
show indexes from t1;
504
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
505
t1 0 a 1 a A 0 NULL NULL YES BTREE
506
t1 1 b 1 b A 0 NULL NULL YES BTREE
492
Table Unique Key_name Seq_in_index Column_name
507
495
"Change again the type, but leave the indexes as_is"
508
496
alter table t1 modify a int;
509
497
show indexes from t1;
510
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
511
t1 0 a 1 a A 0 NULL NULL YES BTREE
512
t1 1 b 1 b A 0 NULL NULL YES BTREE
498
Table Unique Key_name Seq_in_index Column_name
513
501
"Try the same. When data is no copied on similar tables, this is noop"
514
502
alter table t1 modify a int;
515
503
show indexes from t1;
516
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
517
t1 0 a 1 a A 0 NULL NULL YES BTREE
518
t1 1 b 1 b A 0 NULL NULL YES BTREE
504
Table Unique Key_name Seq_in_index Column_name
520
508
create database mysqltest;
521
509
create table t1 (c1 int);
551
539
DROP TABLE IF EXISTS bug24219_2;
552
540
CREATE TABLE bug24219 (a INT, INDEX(a));
553
541
SHOW INDEX FROM bug24219;
554
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
555
bug24219 1 a 1 a A 0 NULL NULL YES BTREE
542
Table Unique Key_name Seq_in_index Column_name
556
544
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
558
546
Note 1031 Table storage engine for 'bug24219' doesn't have this option
559
547
SHOW INDEX FROM bug24219_2;
560
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
561
bug24219_2 1 a 1 a A 0 NULL NULL YES BTREE
548
Table Unique Key_name Seq_in_index Column_name
549
bug24219_2 FALSE a 1 a
562
550
DROP TABLE bug24219_2;
563
551
drop table if exists table_24562;
564
552
create table table_24562(
848
840
INDEX(`int_field`)
851
Field Type Null Key Default Extra
852
int_field int NO MUL NULL
853
char_field varchar(10) YES NULL
843
Field Type Null Default Default_is_NULL On_Update
844
int_field INTEGER FALSE FALSE
845
char_field VARCHAR TRUE TRUE
854
846
SHOW INDEXES FROM t1;
855
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
856
t1 1 int_field 1 int_field A 0 NULL NULL BTREE
847
Table Unique Key_name Seq_in_index Column_name
848
t1 FALSE int_field 1 int_field
857
849
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
858
850
"Non-copy data change - new frm, but old data and index files"
859
851
ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2;
870
Field Type Null Key Default Extra
871
unsigned_int_field int NO MUL NULL
872
char_field varchar(10) YES NULL
862
Field Type Null Default Default_is_NULL On_Update
863
unsigned_int_field INTEGER FALSE FALSE
864
char_field VARCHAR TRUE TRUE
874
Field Type Null Key Default Extra
875
unsigned_int_field int NO MUL NULL
876
char_field varchar(10) YES NULL
866
Field Type Null Default Default_is_NULL On_Update
867
unsigned_int_field INTEGER FALSE FALSE
868
char_field VARCHAR TRUE TRUE
877
869
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;
879
Field Type Null Key Default Extra
880
unsigned_int_field bigint NO MUL NULL
881
char_field varchar(10) YES NULL
871
Field Type Null Default Default_is_NULL On_Update
872
unsigned_int_field BIGINT FALSE FALSE
873
char_field VARCHAR TRUE TRUE
883
875
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
884
876
INSERT INTO t1 VALUES (1, 2, NULL);