49
49
NAME varchar(80) DEFAULT '' NOT NULL,
50
50
PRIMARY KEY (GROUP_ID,LANG_ID),
52
show table status like "t1";
53
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
54
t1 InnoDB 0 DEFAULT 0 0 0 0 0 0 0 0 0 0 utf8_general_ci 0
52
55
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
53
56
show COLUMNS FROM t1;
54
Field Type Null Key Default Extra
57
NAME varchar(80) NO MUL NULL
57
Field Type Null Default Default is NULL On Update
58
GROUP_ID INTEGER FALSE 0 FALSE
59
LANG_ID INTEGER FALSE 0 FALSE
60
NAME VARCHAR FALSE 0 FALSE
59
62
create table t1 (n int);
60
63
insert into t1 values(9),(3),(12),(10);
116
119
key (n4, n1, n2, n3) );
118
121
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
122
Table Key_name Seq_in_index Column_name
137
140
set autocommit=0;
139
142
insert into t1 values(10,RAND()*1000,RAND()*1000,RAND());
153
156
Note 1031 Table storage engine for 't1' doesn't have this option
154
157
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
158
Table Key_name Seq_in_index Column_name
174
177
create table t1 (i int not null auto_increment primary key);
175
178
alter table t1 rename t2;
176
179
alter table t2 rename t1, add c char(10) comment "no comment";
177
180
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
181
Field Type Null Default Default is NULL On Update
182
i INTEGER FALSE 0 FALSE
183
c VARCHAR TRUE 0 TRUE
182
185
create table t1 (a int, b int);
183
186
set autocommit=0;
286
289
set autocommit=1;
287
290
alter table t1 add unique (a,b), add key (b);
288
291
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
292
Table Key_name Seq_in_index Column_name
293
296
analyze table t1;
294
297
Table Op Msg_type Msg_text
295
298
test.t1 analyze status OK
296
299
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
300
Table Key_name Seq_in_index Column_name
302
305
CREATE TEMPORARY TABLE t1 (
303
306
Host varchar(16) NOT NULL default '',
308
311
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
312
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
313
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
315
Table Op Msg_type Msg_text
328
316
test.t1 check status OK
351
339
insert into t1 (a,b,c,d,e,f,g,h,i) values(1,1,1,1,1,1,1,1,1);
352
340
show table status like 't1';
353
341
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
t1 MyISAM X DEFAULT 0 0 X X X X X X X X utf8_general_ci 0
354
343
alter table t1 modify a int;
355
344
show table status like 't1';
356
345
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
346
t1 MyISAM X DEFAULT 0 0 X X X X X X X X utf8_general_ci 0
358
348
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
349
insert into t1 (a) values(1);
360
350
show table status like 't1';
361
351
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
352
t1 MyISAM X DEFAULT 0 0 X X X X X X X X utf8_general_ci 0
363
354
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
364
355
ALTER TABLE t1 DROP PRIMARY KEY;
389
380
drop table if exists t1;
390
381
create table t1 (a int, key(a));
391
382
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
383
Table Key_name Seq_in_index Column_name
394
385
"this used not to disable the index"
395
386
alter table t1 modify a int;
396
387
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
388
Table Key_name Seq_in_index Column_name
399
390
alter table t1 enable keys;
401
392
Note 1031 Table storage engine for 't1' doesn't have this option
402
393
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
394
Table Key_name Seq_in_index Column_name
405
396
alter table t1 modify a bigint;
406
397
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
398
Table Key_name Seq_in_index Column_name
409
400
alter table t1 enable keys;
411
402
Note 1031 Table storage engine for 't1' doesn't have this option
412
403
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
404
Table Key_name Seq_in_index Column_name
415
406
alter table t1 add b char(10);
416
407
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
408
Table Key_name Seq_in_index Column_name
419
410
alter table t1 add c decimal(10,2);
420
411
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
412
Table Key_name Seq_in_index Column_name
423
414
"this however did"
425
416
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
417
Table 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
420
Field Type Null Default Default is NULL On Update
433
424
alter table t1 add d decimal(15,5);
434
425
"The key should still be disabled"
435
426
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
427
Table Key_name Seq_in_index Column_name
439
430
"Now will test with one unique index"
440
431
create table t1(a int, b char(10), unique(a));
441
432
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
433
Table Key_name Seq_in_index Column_name
445
436
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
437
Table Key_name Seq_in_index Column_name
448
439
alter table t1 enable keys;
450
441
Note 1031 Table storage engine for 't1' doesn't have this option
452
443
"Unique index, no change"
453
444
alter table t1 modify a int;
454
445
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
446
Table Key_name Seq_in_index Column_name
457
448
"Change the type implying data copy"
458
449
"Unique index, no change"
459
450
alter table t1 modify a bigint;
460
451
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
452
Table Key_name Seq_in_index Column_name
463
454
alter table t1 modify a bigint;
464
455
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
456
Table Key_name Seq_in_index Column_name
467
458
alter table t1 modify a int;
468
459
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
460
Table Key_name Seq_in_index Column_name
472
463
"Now will test with one unique and one non-unique index"
473
464
create table t1(a int, b char(10), unique(a), key(b));
474
465
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
466
Table Key_name Seq_in_index Column_name
479
470
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
471
Table Key_name Seq_in_index Column_name
483
474
alter table t1 enable keys;
485
476
Note 1031 Table storage engine for 't1' doesn't have this option
487
478
"The non-unique index will be disabled"
488
479
alter table t1 modify a int;
489
480
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
481
Table Key_name Seq_in_index Column_name
493
484
alter table t1 enable keys;
495
486
Note 1031 Table storage engine for 't1' doesn't have this option
496
487
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
488
Table Key_name Seq_in_index Column_name
500
491
"Change the type implying data copy"
501
492
"The non-unique index will be disabled"
502
493
alter table t1 modify a bigint;
503
494
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
495
Table Key_name Seq_in_index Column_name
507
498
"Change again the type, but leave the indexes as_is"
508
499
alter table t1 modify a int;
509
500
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
501
Table Key_name Seq_in_index Column_name
513
504
"Try the same. When data is no copied on similar tables, this is noop"
514
505
alter table t1 modify a int;
515
506
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
507
Table Key_name Seq_in_index Column_name
520
511
create database mysqltest;
521
512
create table t1 (c1 int);
551
542
DROP TABLE IF EXISTS bug24219_2;
552
543
CREATE TABLE bug24219 (a INT, INDEX(a));
553
544
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
545
Table Key_name Seq_in_index Column_name
556
547
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
558
549
Note 1031 Table storage engine for 'bug24219' doesn't have this option
559
550
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
551
Table Key_name Seq_in_index Column_name
562
553
DROP TABLE bug24219_2;
563
554
drop table if exists table_24562;
564
555
create table table_24562(
848
843
INDEX(`int_field`)
851
Field Type Null Key Default Extra
852
int_field int NO MUL NULL
853
char_field varchar(10) YES NULL
846
Field Type Null Default Default is NULL On Update
847
int_field INTEGER FALSE FALSE
848
char_field VARCHAR TRUE TRUE
854
849
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
850
Table Key_name Seq_in_index Column_name
851
t1 int_field 0 int_field
857
852
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
858
853
"Non-copy data change - new frm, but old data and index files"
859
854
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
865
Field Type Null Default Default is NULL On Update
866
unsigned_int_field INTEGER FALSE FALSE
867
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
869
Field Type Null Default Default is NULL On Update
870
unsigned_int_field INTEGER FALSE FALSE
871
char_field VARCHAR TRUE TRUE
877
872
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
874
Field Type Null Default Default is NULL On Update
875
unsigned_int_field BIGINT FALSE FALSE
876
char_field VARCHAR TRUE TRUE
883
878
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
884
879
INSERT INTO t1 VALUES (1, 2, NULL);