5
drop table if exists t1,t2;
6
drop database if exists mysqltest;
10
col1 int not null auto_increment primary key,
11
col2 varchar(30) not null,
12
col3 varchar (20) not null,
13
col4 varchar(4) not null,
14
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
15
col6 int not null, to_be_deleted int);
16
insert into t1 values (2,4,3,5,"PENDING",1,7);
18
add column col4_5 varchar(20) not null after col4,
19
add column col7 varchar(30) not null after col5,
20
add column col8 datetime not null, drop column to_be_deleted,
21
change column col2 fourth varchar(30) not null after col3,
22
modify column col6 int not null first;
26
create table t1 (bandID INT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
27
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
28
alter table t1 add column new_col int;
34
# Check that pack_keys and dynamic length rows are not forced.
37
GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL,
38
LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL,
39
NAME varchar(80) DEFAULT '' NOT NULL,
40
PRIMARY KEY (GROUP_ID,LANG_ID),
42
#show table status like "t1";
43
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
45
SHOW FULL COLUMNS FROM t1;
49
# Test of ALTER TABLE ... ORDER BY
52
create table t1 (n int);
53
insert into t1 values(9),(3),(12),(10);
54
alter table t1 order by n;
59
id int(11) unsigned NOT NULL default '0',
60
category_id tinyint(4) unsigned NOT NULL default '0',
61
type_id tinyint(4) unsigned NOT NULL default '0',
63
user_id int(11) unsigned NOT NULL default '0',
64
status enum('new','old') NOT NULL default 'new',
68
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;
72
# The following combination found a hang-bug in MyISAM
75
CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam;
76
insert into t1 values (null,"hello");
78
ALTER TABLE t1 ADD Column new_col int not null;
84
# Drop and add an auto_increment column
87
create table t1 (i int unsigned not null auto_increment primary key);
88
insert into t1 values (null),(null),(null),(null);
89
alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i);
94
# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1
97
create table t1 (name char(15));
98
insert into t1 (name) values ("current");
99
create database mysqltest;
100
create table mysqltest.t1 (name char(15));
101
insert into mysqltest.t1 (name) values ("mysqltest");
103
select * from mysqltest.t1;
104
--error ER_TABLE_EXISTS_ERROR
105
alter table t1 rename mysqltest.t1;
107
select * from mysqltest.t1;
109
drop database mysqltest;
112
# ALTER TABLE ... ENABLE/DISABLE KEYS
114
create table t1 (n1 int not null, n2 int, n3 int, n4 float,
116
key (n1, n2, n3, n4),
117
key (n2, n3, n4, n1),
118
key (n3, n4, n1, n2),
119
key (n4, n1, n2, n3) );
126
eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND());
129
alter table t1 enable keys;
134
# Alter table and rename
137
create table t1 (i int unsigned not null auto_increment primary key);
138
alter table t1 rename t2;
139
alter table t2 rename t1, add c char(10) comment "no comment";
140
show columns from t1;
145
create table t1 (a int, b int);
149
eval insert into t1 values(1,$1), (2,$1), (3, $1);
152
alter table t1 add unique (a,b), add key (b);
159
# Test ALTER TABLE ENABLE/DISABLE keys when things are locked
163
Host varchar(16) binary NOT NULL default '',
164
User varchar(16) binary NOT NULL default '',
165
PRIMARY KEY (Host,User)
168
ALTER TABLE t1 DISABLE KEYS;
169
LOCK TABLES t1 WRITE;
170
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
172
ALTER TABLE t1 ENABLE KEYS;
182
Host varchar(16) binary NOT NULL default '',
183
User varchar(16) binary NOT NULL default '',
184
PRIMARY KEY (Host,User),
188
ALTER TABLE t1 DISABLE KEYS;
190
LOCK TABLES t1 WRITE;
191
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
193
ALTER TABLE t1 ENABLE KEYS;
198
# Test RENAME with LOCK TABLES
199
LOCK TABLES t1 WRITE;
200
ALTER TABLE t1 RENAME t2;
209
Host varchar(16) binary NOT NULL default '',
210
User varchar(16) binary NOT NULL default '',
211
PRIMARY KEY (Host,User),
215
LOCK TABLES t1 WRITE;
216
ALTER TABLE t1 DISABLE KEYS;
221
# BUG#4717 - check for valid table names
223
create table t1 (a int);
224
--error ER_WRONG_TABLE_NAME
225
alter table t1 rename to ``;
226
--error ER_WRONG_TABLE_NAME
227
rename table t1 to ``;
231
# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns
233
drop table if exists t1;
234
create table t1 ( a varchar(10) not null primary key ) engine=myisam;
236
alter table t1 modify a varchar(10);
238
alter table t1 modify a varchar(10) not null;
239
drop table if exists t1;
241
# The following is also part of bug #6236 (CREATE TABLE didn't properly count
242
# not null columns for primary keys)
244
create 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;
245
insert into t1 (a) values(1);
246
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
247
show table status like 't1';
248
alter table t1 modify a int;
249
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
250
show table status like 't1';
252
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;
253
insert into t1 (a) values(1);
254
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
255
show table status like 't1';
259
# Test that data get converted when character set is changed
260
# Test that data doesn't get converted when src or dst is BINARY/BLOB
263
create table t1 (a char(10) character set koi8r);
264
insert into t1 values ('����');
265
select a,hex(a) from t1;
266
alter table t1 change a a char(10) character set cp1251;
267
select a,hex(a) from t1;
268
alter table t1 change a a binary(4);
269
select a,hex(a) from t1;
270
alter table t1 change a a char(10) character set cp1251;
271
select a,hex(a) from t1;
272
alter table t1 change a a char(10) character set koi8r;
273
select a,hex(a) from t1;
274
alter table t1 change a a varchar(10) character set cp1251;
275
select a,hex(a) from t1;
276
alter table t1 change a a char(10) character set koi8r;
277
select a,hex(a) from t1;
278
alter table t1 change a a text 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;
285
# Test ALTER TABLE .. CHARACTER SET ..
287
show create table t1;
288
alter table t1 DEFAULT CHARACTER SET latin1;
289
show create table t1;
290
alter table t1 CONVERT TO CHARACTER SET latin1;
291
show create table t1;
292
alter table t1 DEFAULT CHARACTER SET cp1251;
293
show create table t1;
299
# Test that table CHARACTER SET does not affect blobs
301
create table t1 (myblob longblob,mytext longtext)
302
default charset latin1 collate latin1_general_cs;
303
show create table t1;
304
alter table t1 character set latin2;
305
show create table t1;
309
# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)
312
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
313
ALTER TABLE t1 DROP PRIMARY KEY;
314
SHOW CREATE TABLE t1;
315
--error ER_CANT_DROP_FIELD_OR_KEY
316
ALTER TABLE t1 DROP PRIMARY KEY;
320
create table t1 (a int, b int, key(a));
321
insert into t1 values (1,1), (2,2);
322
--error ER_CANT_DROP_FIELD_OR_KEY
323
alter table t1 drop key no_such_key;
324
alter table t1 drop key a;
328
# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)
330
# Some platforms (Mac OS X, Windows) will send the error message using small letters.
331
CREATE TABLE T12207(a int) ENGINE=MYISAM;
332
--replace_result t12207 T12207
333
--error ER_ILLEGAL_HA
334
ALTER TABLE T12207 DISCARD TABLESPACE;
338
# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns
340
# The column's character set was changed but the actual data was not
341
# modified. In other words, the values were reinterpreted
342
# as UTF8 instead of being converted.
343
create table t1 (a text) character set koi8r;
344
insert into t1 values (_koi8r'����');
345
select hex(a) from t1;
346
alter table t1 convert to character set cp1251;
347
select hex(a) from t1;
351
# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"
352
# MySQL should not think that packed field with non-zero decimals is
353
# geometry field and allow to create prefix index which is
354
# shorter than packed field length.
356
create table t1 ( a timestamp );
357
--error ER_WRONG_SUB_KEY
358
alter table t1 add unique ( a(1) );
362
# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table
364
# This problem happens if the data change is compatible.
365
# Changing to the same type is compatible for example.
368
drop table if exists t1;
370
create table t1 (a int, key(a));
371
show indexes from t1;
372
--echo "this used not to disable the index"
373
alter table t1 modify a int;
374
show indexes from t1;
376
alter table t1 enable keys;
377
show indexes from t1;
379
alter table t1 modify a bigint;
380
show indexes from t1;
382
alter table t1 enable keys;
383
show indexes from t1;
385
alter table t1 add b char(10);
386
show indexes from t1;
388
alter table t1 add c decimal(10,2);
389
show indexes from t1;
391
--echo "this however did"
393
show indexes from t1;
397
alter table t1 add d decimal(15,5);
398
--echo "The key should still be disabled"
399
show indexes from t1;
403
--echo "Now will test with one unique index"
404
create table t1(a int, b char(10), unique(a));
405
show indexes from t1;
407
show indexes from t1;
408
alter table t1 enable keys;
410
--echo "If no copy on noop change, this won't touch the data file"
411
--echo "Unique index, no change"
412
alter table t1 modify a int;
413
show indexes from t1;
415
--echo "Change the type implying data copy"
416
--echo "Unique index, no change"
417
alter table t1 modify a bigint;
418
show indexes from t1;
420
alter table t1 modify a bigint;
421
show indexes from t1;
423
alter table t1 modify a int;
424
show indexes from t1;
428
--echo "Now will test with one unique and one non-unique index"
429
create table t1(a int, b char(10), unique(a), key(b));
430
show indexes from t1;
432
show indexes from t1;
433
alter table t1 enable keys;
436
--echo "If no copy on noop change, this won't touch the data file"
437
--echo "The non-unique index will be disabled"
438
alter table t1 modify a int;
439
show indexes from t1;
440
alter table t1 enable keys;
441
show indexes from t1;
443
--echo "Change the type implying data copy"
444
--echo "The non-unique index will be disabled"
445
alter table t1 modify a bigint;
446
show indexes from t1;
448
--echo "Change again the type, but leave the indexes as_is"
449
alter table t1 modify a int;
450
show indexes from t1;
451
--echo "Try the same. When data is no copied on similar tables, this is noop"
452
alter table t1 modify a int;
453
show indexes from t1;
459
# Bug#11493 - Alter table rename to default database does not work without
462
create database mysqltest;
463
create table t1 (c1 int);
464
# Move table to other database.
465
alter table t1 rename mysqltest.t1;
466
# Assure that it has moved.
467
--error ER_BAD_TABLE_ERROR
470
alter table mysqltest.t1 rename t1;
471
# Assure that it is back.
473
# Now test for correct message if no database is selected.
474
# Create t1 in 'test'.
475
create table t1 (c1 int);
476
# Change to other db.
478
# Drop the current db. This de-selects any db.
479
drop database mysqltest;
480
# Now test for correct message.
481
--error ER_NO_DB_ERROR
482
alter table test.t1 rename t1;
483
# Check that explicit qualifying works even with no selected db.
484
alter table test.t1 rename test.t1;
485
# Go back to standard 'test' db.
490
# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the
493
CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;
494
CREATE INDEX i1 ON t1(a);
495
SHOW CREATE TABLE t1;
497
SHOW CREATE TABLE t1;
501
# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash
504
DROP TABLE IF EXISTS bug24219;
505
DROP TABLE IF EXISTS bug24219_2;
508
CREATE TABLE bug24219 (a INT, INDEX(a));
510
SHOW INDEX FROM bug24219;
512
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
514
SHOW INDEX FROM bug24219_2;
516
DROP TABLE bug24219_2;
519
# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
523
drop table if exists table_24562;
526
create table table_24562(
531
insert into table_24562 values
532
(1, 0, "Introduction"),
534
(1, 2, "Acknowledgements"),
539
(3, 0, "Intermediate"),
540
(3, 1, "Complex queries"),
541
(3, 2, "Stored Procedures"),
542
(3, 3, "Stored Functions"),
544
(4, 1, "Replication"),
545
(4, 2, "Load balancing"),
546
(4, 3, "High availability"),
547
(5, 0, "Conclusion");
549
select * from table_24562;
551
alter table table_24562 add column reviewer varchar(20),
554
select * from table_24562;
556
update table_24562 set reviewer="Me" where section=2;
557
update table_24562 set reviewer="You" where section=3;
559
alter table table_24562
560
order by section ASC, subsection DESC;
562
select * from table_24562;
564
alter table table_24562
565
order by table_24562.subsection ASC, table_24562.section DESC;
567
select * from table_24562;
569
--error ER_PARSE_ERROR
570
alter table table_24562 order by 12;
571
--error ER_PARSE_ERROR
572
alter table table_24562 order by (section + 12);
573
--error ER_PARSE_ERROR
574
alter table table_24562 order by length(title);
576
--error ER_BAD_FIELD_ERROR
577
alter table table_24562 order by no_such_col;
579
drop table table_24562;
584
# Bug #14693 (ALTER SET DEFAULT doesn't work)
587
create table t1 (mycol int(10) not null);
588
alter table t1 alter column mycol set default 0;
593
# Bug#25262 Auto Increment lost when changing Engine type
596
create table t1(id int(8) primary key auto_increment) engine=heap;
598
insert into t1 values (null);
599
insert into t1 values (null);
603
# Set auto increment to 50
604
alter table t1 auto_increment = 50;
607
alter table t1 engine = myisam;
609
# This insert should get id 50
610
insert into t1 values (null);
613
# Alter to heap again
614
alter table t1 engine = heap;
615
insert into t1 values (null);
621
## Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
622
## NO_ZERO_DATE mode.
624
#create table t1(f1 int);
625
#alter table t1 add column f2 datetime not null, add column f21 date not null;
626
#insert into t1 values(1,'2000-01-01','2000-01-01');
628
#alter table t1 add column f3 datetime not null;
630
#alter table t1 add column f3 date not null;
632
#alter table t1 add column f4 datetime not null default '2002-02-02',
633
# add column f41 date not null;
634
#alter table t1 add column f4 datetime not null default '2002-02-02',
635
# add column f41 date not null default '2002-02-02';
640
# Some additional tests for new, faster alter table. Note that most of the
641
# whole alter table code is being tested all around the test suite already.
644
create table t1 (v varchar(32));
645
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
647
# Fast alter, no copy performed
648
alter table t1 change v v2 varchar(32);
650
# Fast alter, no copy performed
651
alter table t1 change v2 v varchar(64);
653
update t1 set v = 'lmn' where v = 'hij';
655
# Regular alter table
656
alter table t1 add i int auto_increment not null primary key first;
658
update t1 set i=5 where i=3;
660
alter table t1 change i i bigint;
662
alter table t1 add unique key (i, v);
663
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
667
# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
668
# without # prefix is not allowed for TEXT columns, while index
669
# is defined with prefix.
671
create table t1 (t varchar(255) default null, key t (t(80)))
672
engine=myisam default charset=latin1;
673
alter table t1 change t t text;
677
# Bug#18038 MySQL server corrupts binary columns data
680
CREATE TABLE t1 (s CHAR(8) BINARY);
681
INSERT INTO t1 VALUES ('test');
682
SELECT LENGTH(s) FROM t1;
683
ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
684
SELECT LENGTH(s) FROM t1;
687
CREATE TABLE t1 (s BINARY(8));
688
INSERT INTO t1 VALUES ('test');
689
SELECT LENGTH(s) FROM t1;
690
SELECT HEX(s) FROM t1;
691
ALTER TABLE t1 MODIFY s BINARY(10);
692
SELECT HEX(s) FROM t1;
693
SELECT LENGTH(s) FROM t1;
697
# Bug#19386: Multiple alter causes crashed table
698
# The trailing column would get corrupted data, or server could not even read
702
CREATE TABLE t1 (v VARCHAR(3), b INT);
703
INSERT INTO t1 VALUES ('abc', 5);
705
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
709
--echo End of 5.0 tests
712
# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES
713
# It should be consistent across all platforms and for all engines
714
# (Before 5.1 this was not true as behavior was different between
715
# Unix/Windows and transactional/non-transactional tables).
716
# See also innodb_mysql.test
719
drop table if exists t1, t2, t3;
721
create table t1 (i int);
722
create table t3 (j int);
723
insert into t1 values ();
724
insert into t3 values ();
725
# Table which is altered under LOCK TABLES it should stay in list of locked
726
# tables and be available after alter takes place unless ALTER contains RENAME
727
# clause. We should see the new definition of table, of course.
728
lock table t1 write, t3 read;
729
# Example of so-called 'fast' ALTER TABLE
730
alter table t1 modify i int default 1;
731
insert into t1 values ();
733
# And now full-blown ALTER TABLE
734
alter table t1 change i c char(10) default "Two";
735
insert into t1 values ();
737
# If table is renamed then it should be removed from the list
738
# of locked tables. 'Fast' ALTER TABLE with RENAME clause:
739
alter table t1 modify c char(10) default "Three", rename to t2;
740
--error ER_TABLE_NOT_LOCKED
742
--error ER_TABLE_NOT_LOCKED
746
insert into t2 values ();
748
lock table t2 write, t3 read;
749
# Full ALTER TABLE with RENAME
750
alter table t2 change c vc varchar(100) default "Four", rename to t1;
751
--error ER_TABLE_NOT_LOCKED
753
--error ER_TABLE_NOT_LOCKED
757
insert into t1 values ();
763
# Bug#18775 - Temporary table from alter table visible to other threads
765
# Check if special characters work and duplicates are detected.
767
DROP TABLE IF EXISTS `t+1`, `t+2`;
769
CREATE TABLE `t+1` (c1 INT);
770
ALTER TABLE `t+1` RENAME `t+2`;
771
CREATE TABLE `t+1` (c1 INT);
772
--error ER_TABLE_EXISTS_ERROR
773
ALTER TABLE `t+1` RENAME `t+2`;
774
DROP TABLE `t+1`, `t+2`;
776
# Same for temporary tables though these names do not become file names.
777
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
778
ALTER TABLE `tt+1` RENAME `tt+2`;
779
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
780
--error ER_TABLE_EXISTS_ERROR
781
ALTER TABLE `tt+1` RENAME `tt+2`;
782
SHOW CREATE TABLE `tt+1`;
783
SHOW CREATE TABLE `tt+2`;
784
DROP TABLE `tt+1`, `tt+2`;
786
## Check if special characters as in tmp_file_prefix work.
787
#CREATE TABLE `#sql1` (c1 INT);
788
#CREATE TABLE `@0023sql2` (c1 INT);
790
#RENAME TABLE `#sql1` TO `@0023sql1`;
791
#RENAME TABLE `@0023sql2` TO `#sql2`;
793
#ALTER TABLE `@0023sql1` RENAME `#sql-1`;
794
#ALTER TABLE `#sql2` RENAME `@0023sql-2`;
796
#INSERT INTO `#sql-1` VALUES (1);
797
#INSERT INTO `@0023sql-2` VALUES (2);
798
#DROP TABLE `#sql-1`, `@0023sql-2`;
800
# Same for temporary tables though these names do not become file names.
801
CREATE TEMPORARY TABLE `#sql1` (c1 INT);
802
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
804
ALTER TABLE `#sql1` RENAME `@0023sql1`;
805
ALTER TABLE `@0023sql2` RENAME `#sql2`;
807
INSERT INTO `#sql2` VALUES (1);
808
INSERT INTO `@0023sql1` VALUES (2);
809
SHOW CREATE TABLE `#sql2`;
810
SHOW CREATE TABLE `@0023sql1`;
811
DROP TABLE `#sql2`, `@0023sql1`;
814
# Bug #22369: Alter table rename combined with other alterations causes lost tables
816
# This problem happens if the data change is compatible.
817
# Changing to the same type is compatible for example.
820
DROP TABLE IF EXISTS t1;
821
DROP TABLE IF EXISTS t2;
824
int_field INTEGER UNSIGNED NOT NULL,
831
SHOW INDEXES FROM t1;
833
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
834
--echo "Non-copy data change - new frm, but old data and index files"
836
CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,
839
--error ER_NO_SUCH_TABLE
840
SELECT * FROM t1 ORDER BY int_field;
841
SELECT * FROM t2 ORDER BY unsigned_int_field;
844
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;
850
# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
852
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
853
INSERT INTO t1 VALUES (1, 2, NULL);
855
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;
857
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;
862
# BUG#29957 - alter_table.test fails
864
create table t1 (c char(10) default "Two");
866
insert into t1 values ();
867
alter table t1 modify c char(10) default "Three";
added
removed
tests/t/alter_table.test
