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 NOT NULL PRIMARY KEY, payoutID int 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 DEFAULT '0' NOT NULL,
38
LANG_ID int 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 NOT NULL default '0',
60
category_id int NOT NULL default '0',
61
type_id int NOT NULL default '0',
63
user_id int 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 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 not null auto_increment primary key);
88
insert into t1 values (null),(null),(null),(null);
89
alter table t1 drop i,add i int 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) );
128
eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND());
133
alter table t1 enable keys;
138
# Alter table and rename
141
create table t1 (i int not null auto_increment primary key);
142
alter table t1 rename t2;
143
alter table t2 rename t1, add c char(10) comment "no comment";
144
show columns from t1;
149
create table t1 (a int, b int);
155
eval insert into t1 values(1,$1), (2,$1), (3, $1);
160
alter table t1 add unique (a,b), add key (b);
167
# Test ALTER TABLE ENABLE/DISABLE keys when things are locked
171
Host varchar(16) binary NOT NULL default '',
172
User varchar(16) binary NOT NULL default '',
173
PRIMARY KEY (Host,User)
176
ALTER TABLE t1 DISABLE KEYS;
177
LOCK TABLES t1 WRITE;
178
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
180
ALTER TABLE t1 ENABLE KEYS;
190
Host varchar(16) binary NOT NULL default '',
191
User varchar(16) binary NOT NULL default '',
192
PRIMARY KEY (Host,User),
196
ALTER TABLE t1 DISABLE KEYS;
198
LOCK TABLES t1 WRITE;
199
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
201
ALTER TABLE t1 ENABLE KEYS;
206
# Test RENAME with LOCK TABLES
207
LOCK TABLES t1 WRITE;
208
ALTER TABLE t1 RENAME t2;
217
Host varchar(16) binary NOT NULL default '',
218
User varchar(16) binary NOT NULL default '',
219
PRIMARY KEY (Host,User),
223
LOCK TABLES t1 WRITE;
224
ALTER TABLE t1 DISABLE KEYS;
229
# BUG#4717 - check for valid table names
231
create table t1 (a int);
232
--error ER_WRONG_TABLE_NAME
233
alter table t1 rename to ``;
234
--error ER_WRONG_TABLE_NAME
235
rename table t1 to ``;
239
# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns
241
drop table if exists t1;
242
create table t1 ( a varchar(10) not null primary key ) engine=myisam;
244
alter table t1 modify a varchar(10);
246
alter table t1 modify a varchar(10) not null;
247
drop table if exists t1;
249
# The following is also part of bug #6236 (CREATE TABLE didn't properly count
250
# not null columns for primary keys)
252
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;
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';
256
alter table t1 modify a int;
257
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
258
show table status like 't1';
260
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;
261
insert into t1 (a) values(1);
262
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
263
show table status like 't1';
267
# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)
270
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
271
ALTER TABLE t1 DROP PRIMARY KEY;
272
SHOW CREATE TABLE t1;
273
--error ER_CANT_DROP_FIELD_OR_KEY
274
ALTER TABLE t1 DROP PRIMARY KEY;
278
create table t1 (a int, b int, key(a));
279
insert into t1 values (1,1), (2,2);
280
--error ER_CANT_DROP_FIELD_OR_KEY
281
alter table t1 drop key no_such_key;
282
alter table t1 drop key a;
286
# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)
288
# Some platforms (Mac OS X, Windows) will send the error message using small letters.
289
CREATE TABLE T12207(a int) ENGINE=MYISAM;
290
--replace_result t12207 T12207
291
--error ER_ILLEGAL_HA
292
ALTER TABLE T12207 DISCARD TABLESPACE;
296
# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"
297
# MySQL should not think that packed field with non-zero decimals is
298
# geometry field and allow to create prefix index which is
299
# shorter than packed field length.
301
create table t1 ( a timestamp );
302
--error ER_WRONG_SUB_KEY
303
alter table t1 add unique ( a(1) );
307
# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table
309
# This problem happens if the data change is compatible.
310
# Changing to the same type is compatible for example.
313
drop table if exists t1;
315
create table t1 (a int, key(a));
316
show indexes from t1;
317
--echo "this used not to disable the index"
318
alter table t1 modify a int;
319
show indexes from t1;
321
alter table t1 enable keys;
322
show indexes from t1;
324
alter table t1 modify a bigint;
325
show indexes from t1;
327
alter table t1 enable keys;
328
show indexes from t1;
330
alter table t1 add b char(10);
331
show indexes from t1;
333
alter table t1 add c decimal(10,2);
334
show indexes from t1;
336
--echo "this however did"
338
show indexes from t1;
342
alter table t1 add d decimal(15,5);
343
--echo "The key should still be disabled"
344
show indexes from t1;
348
--echo "Now will test with one unique index"
349
create table t1(a int, b char(10), unique(a));
350
show indexes from t1;
352
show indexes from t1;
353
alter table t1 enable keys;
355
--echo "If no copy on noop change, this won't touch the data file"
356
--echo "Unique index, no change"
357
alter table t1 modify a int;
358
show indexes from t1;
360
--echo "Change the type implying data copy"
361
--echo "Unique index, no change"
362
alter table t1 modify a bigint;
363
show indexes from t1;
365
alter table t1 modify a bigint;
366
show indexes from t1;
368
alter table t1 modify a int;
369
show indexes from t1;
373
--echo "Now will test with one unique and one non-unique index"
374
create table t1(a int, b char(10), unique(a), key(b));
375
show indexes from t1;
377
show indexes from t1;
378
alter table t1 enable keys;
381
--echo "If no copy on noop change, this won't touch the data file"
382
--echo "The non-unique index will be disabled"
383
alter table t1 modify a int;
384
show indexes from t1;
385
alter table t1 enable keys;
386
show indexes from t1;
388
--echo "Change the type implying data copy"
389
--echo "The non-unique index will be disabled"
390
alter table t1 modify a bigint;
391
show indexes from t1;
393
--echo "Change again the type, but leave the indexes as_is"
394
alter table t1 modify a int;
395
show indexes from t1;
396
--echo "Try the same. When data is no copied on similar tables, this is noop"
397
alter table t1 modify a int;
398
show indexes from t1;
404
# Bug#11493 - Alter table rename to default database does not work without
407
create database mysqltest;
408
create table t1 (c1 int);
409
# Move table to other database.
410
alter table t1 rename mysqltest.t1;
411
# Assure that it has moved.
412
--error ER_BAD_TABLE_ERROR
415
alter table mysqltest.t1 rename t1;
416
# Assure that it is back.
418
# Now test for correct message if no database is selected.
419
# Create t1 in 'test'.
420
create table t1 (c1 int);
421
# Change to other db.
423
# Drop the current db. This de-selects any db.
424
drop database mysqltest;
425
# Now test for correct message.
426
--error ER_NO_DB_ERROR
427
alter table test.t1 rename t1;
428
# Check that explicit qualifying works even with no selected db.
429
alter table test.t1 rename test.t1;
430
# Go back to standard 'test' db.
435
# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the
438
CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;
439
CREATE INDEX i1 ON t1(a);
440
SHOW CREATE TABLE t1;
442
SHOW CREATE TABLE t1;
446
# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash
449
DROP TABLE IF EXISTS bug24219;
450
DROP TABLE IF EXISTS bug24219_2;
453
CREATE TABLE bug24219 (a INT, INDEX(a));
455
SHOW INDEX FROM bug24219;
457
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
459
SHOW INDEX FROM bug24219_2;
461
DROP TABLE bug24219_2;
464
# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
468
drop table if exists table_24562;
471
create table table_24562(
476
insert into table_24562 values
477
(1, 0, "Introduction"),
479
(1, 2, "Acknowledgements"),
484
(3, 0, "Intermediate"),
485
(3, 1, "Complex queries"),
486
(3, 2, "Stored Procedures"),
487
(3, 3, "Stored Functions"),
489
(4, 1, "Replication"),
490
(4, 2, "Load balancing"),
491
(4, 3, "High availability"),
492
(5, 0, "Conclusion");
494
select * from table_24562;
496
alter table table_24562 add column reviewer varchar(20),
499
select * from table_24562;
501
update table_24562 set reviewer="Me" where section=2;
502
update table_24562 set reviewer="You" where section=3;
504
alter table table_24562
505
order by section ASC, subsection DESC;
507
select * from table_24562;
509
alter table table_24562
510
order by table_24562.subsection ASC, table_24562.section DESC;
512
select * from table_24562;
514
--error ER_PARSE_ERROR
515
alter table table_24562 order by 12;
516
--error ER_PARSE_ERROR
517
alter table table_24562 order by (section + 12);
518
--error ER_PARSE_ERROR
519
alter table table_24562 order by length(title);
521
--error ER_BAD_FIELD_ERROR
522
alter table table_24562 order by no_such_col;
524
drop table table_24562;
529
# Bug #14693 (ALTER SET DEFAULT doesn't work)
532
create table t1 (mycol int not null);
533
alter table t1 alter column mycol set default 0;
538
# Bug#25262 Auto Increment lost when changing Engine type
541
create table t1(id int primary key auto_increment) engine=heap;
543
insert into t1 values (null);
544
insert into t1 values (null);
548
# Set auto increment to 50
549
alter table t1 auto_increment = 50;
552
alter table t1 engine = myisam;
554
# This insert should get id 50
555
insert into t1 values (null);
558
# Alter to heap again
559
alter table t1 engine = heap;
560
insert into t1 values (null);
566
## Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
567
## NO_ZERO_DATE mode.
569
#create table t1(f1 int);
570
#alter table t1 add column f2 datetime not null, add column f21 date not null;
571
#insert into t1 values(1,'2000-01-01','2000-01-01');
573
#alter table t1 add column f3 datetime not null;
575
#alter table t1 add column f3 date not null;
577
#alter table t1 add column f4 datetime not null default '2002-02-02',
578
# add column f41 date not null;
579
#alter table t1 add column f4 datetime not null default '2002-02-02',
580
# add column f41 date not null default '2002-02-02';
585
# Some additional tests for new, faster alter table. Note that most of the
586
# whole alter table code is being tested all around the test suite already.
589
create table t1 (v varchar(32));
590
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
592
# Fast alter, no copy performed
593
alter table t1 change v v2 varchar(32);
595
# Fast alter, no copy performed
596
alter table t1 change v2 v varchar(64);
598
update t1 set v = 'lmn' where v = 'hij';
600
# Regular alter table
601
alter table t1 add i int auto_increment not null primary key first;
603
update t1 set i=5 where i=3;
605
alter table t1 change i i bigint;
607
alter table t1 add unique key (i, v);
608
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
612
# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
613
# without # prefix is not allowed for TEXT columns, while index
614
# is defined with prefix.
616
create table t1 (t varchar(255) default null, key t (t(80))) engine=myisam;
617
alter table t1 change t t text;
621
# Bug#18038 MySQL server corrupts binary columns data
624
CREATE TABLE t1 (s CHAR(8) BINARY);
625
INSERT INTO t1 VALUES ('test');
626
SELECT LENGTH(s) FROM t1;
627
ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
628
SELECT LENGTH(s) FROM t1;
631
CREATE TABLE t1 (s varbinary(8));
632
INSERT INTO t1 VALUES ('test');
633
SELECT LENGTH(s) FROM t1;
634
SELECT HEX(s) FROM t1;
635
ALTER TABLE t1 MODIFY s varbinary(10);
636
SELECT HEX(s) FROM t1;
637
SELECT LENGTH(s) FROM t1;
641
# Bug#19386: Multiple alter causes crashed table
642
# The trailing column would get corrupted data, or server could not even read
646
CREATE TABLE t1 (v VARCHAR(3), b INT);
647
INSERT INTO t1 VALUES ('abc', 5);
649
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
653
--echo End of 5.0 tests
656
# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES
657
# It should be consistent across all platforms and for all engines
658
# (Before 5.1 this was not true as behavior was different between
659
# Unix/Windows and transactional/non-transactional tables).
660
# See also innodb_mysql.test
663
drop table if exists t1, t2, t3;
665
create table t1 (i int);
666
create table t3 (j int);
667
insert into t1 values ();
668
insert into t3 values ();
669
# Table which is altered under LOCK TABLES it should stay in list of locked
670
# tables and be available after alter takes place unless ALTER contains RENAME
671
# clause. We should see the new definition of table, of course.
672
lock table t1 write, t3 read;
673
# Example of so-called 'fast' ALTER TABLE
674
alter table t1 modify i int default 1;
675
insert into t1 values ();
677
# And now full-blown ALTER TABLE
678
alter table t1 change i c char(10) default "Two";
679
insert into t1 values ();
681
# If table is renamed then it should be removed from the list
682
# of locked tables. 'Fast' ALTER TABLE with RENAME clause:
683
alter table t1 modify c char(10) default "Three", rename to t2;
684
--error ER_TABLE_NOT_LOCKED
686
--error ER_TABLE_NOT_LOCKED
690
insert into t2 values ();
692
lock table t2 write, t3 read;
693
# Full ALTER TABLE with RENAME
694
alter table t2 change c vc varchar(100) default "Four", rename to t1;
695
--error ER_TABLE_NOT_LOCKED
697
--error ER_TABLE_NOT_LOCKED
701
insert into t1 values ();
707
# Bug#18775 - Temporary table from alter table visible to other threads
709
# Check if special characters work and duplicates are detected.
711
DROP TABLE IF EXISTS `t+1`, `t+2`;
713
CREATE TABLE `t+1` (c1 INT);
714
ALTER TABLE `t+1` RENAME `t+2`;
715
CREATE TABLE `t+1` (c1 INT);
716
--error ER_TABLE_EXISTS_ERROR
717
ALTER TABLE `t+1` RENAME `t+2`;
718
DROP TABLE `t+1`, `t+2`;
720
# Same for temporary tables though these names do not become file names.
721
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
722
ALTER TABLE `tt+1` RENAME `tt+2`;
723
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
724
--error ER_TABLE_EXISTS_ERROR
725
ALTER TABLE `tt+1` RENAME `tt+2`;
726
SHOW CREATE TABLE `tt+1`;
727
SHOW CREATE TABLE `tt+2`;
728
DROP TABLE `tt+1`, `tt+2`;
730
## Check if special characters as in tmp_file_prefix work.
731
#CREATE TABLE `#sql1` (c1 INT);
732
#CREATE TABLE `@0023sql2` (c1 INT);
734
#RENAME TABLE `#sql1` TO `@0023sql1`;
735
#RENAME TABLE `@0023sql2` TO `#sql2`;
737
#ALTER TABLE `@0023sql1` RENAME `#sql-1`;
738
#ALTER TABLE `#sql2` RENAME `@0023sql-2`;
740
#INSERT INTO `#sql-1` VALUES (1);
741
#INSERT INTO `@0023sql-2` VALUES (2);
742
#DROP TABLE `#sql-1`, `@0023sql-2`;
744
# Same for temporary tables though these names do not become file names.
745
CREATE TEMPORARY TABLE `#sql1` (c1 INT);
746
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
748
ALTER TABLE `#sql1` RENAME `@0023sql1`;
749
ALTER TABLE `@0023sql2` RENAME `#sql2`;
751
INSERT INTO `#sql2` VALUES (1);
752
INSERT INTO `@0023sql1` VALUES (2);
753
SHOW CREATE TABLE `#sql2`;
754
SHOW CREATE TABLE `@0023sql1`;
755
DROP TABLE `#sql2`, `@0023sql1`;
758
# Bug #22369: Alter table rename combined with other alterations causes lost tables
760
# This problem happens if the data change is compatible.
761
# Changing to the same type is compatible for example.
764
DROP TABLE IF EXISTS t1;
765
DROP TABLE IF EXISTS t2;
768
int_field INTEGER NOT NULL,
775
SHOW INDEXES FROM t1;
777
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
778
--echo "Non-copy data change - new frm, but old data and index files"
779
ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2;
781
--error ER_NO_SUCH_TABLE
782
SELECT * FROM t1 ORDER BY int_field;
783
SELECT * FROM t2 ORDER BY unsigned_int_field;
786
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;
792
# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
794
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
795
INSERT INTO t1 VALUES (1, 2, NULL);
797
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;
799
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;
804
# BUG#29957 - alter_table.test fails
806
create table t1 (c char(10) default "Two");
808
insert into t1 values ();
809
alter table t1 modify c char(10) default "Three";
added
removed
tests/t/alter_table.test
