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 MEDIUMINT 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, order by payoutid,bandid;
30
alter table t1 order by bandid,payoutid;
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) );
120
alter table t1 disable keys;
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;
206
# Test disable keys with locking
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, disable keys;
374
show indexes from t1;
376
alter table t1 enable keys;
377
show indexes from t1;
379
alter table t1 modify a bigint, disable keys;
380
show indexes from t1;
382
alter table t1 enable keys;
383
show indexes from t1;
385
alter table t1 add b char(10), disable keys;
386
show indexes from t1;
388
alter table t1 add c decimal(10,2), enable keys;
389
show indexes from t1;
391
--echo "this however did"
392
alter table t1 disable keys;
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;
406
alter table t1 disable keys;
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, disable keys;
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, disable keys;
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;
431
alter table t1 disable keys;
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, disable keys;
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, disable keys;
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);
575
--error ER_PARSE_ERROR
576
alter table table_24562 order by (select 12 from dual);
578
--error ER_BAD_FIELD_ERROR
579
alter table table_24562 order by no_such_col;
581
drop table table_24562;
586
# Bug #14693 (ALTER SET DEFAULT doesn't work)
589
create table t1 (mycol int(10) not null);
590
alter table t1 alter column mycol set default 0;
595
# Bug#25262 Auto Increment lost when changing Engine type
598
create table t1(id int(8) primary key auto_increment) engine=heap;
600
insert into t1 values (null);
601
insert into t1 values (null);
605
# Set auto increment to 50
606
alter table t1 auto_increment = 50;
609
alter table t1 engine = myisam;
611
# This insert should get id 50
612
insert into t1 values (null);
615
# Alter to heap again
616
alter table t1 engine = heap;
617
insert into t1 values (null);
623
# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
626
create table t1(f1 int);
627
alter table t1 add column f2 datetime not null, add column f21 date not null;
628
insert into t1 values(1,'2000-01-01','2000-01-01');
630
alter table t1 add column f3 datetime not null;
632
alter table t1 add column f3 date not null;
634
alter table t1 add column f4 datetime not null default '2002-02-02',
635
add column f41 date not null;
636
alter table t1 add column f4 datetime not null default '2002-02-02',
637
add column f41 date not null default '2002-02-02';
640
set sql_mode= @orig_sql_mode;
643
# Some additional tests for new, faster alter table. Note that most of the
644
# whole alter table code is being tested all around the test suite already.
647
create table t1 (v varchar(32));
648
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
650
# Fast alter, no copy performed
651
alter table t1 change v v2 varchar(32);
653
# Fast alter, no copy performed
654
alter table t1 change v2 v varchar(64);
656
update t1 set v = 'lmn' where v = 'hij';
658
# Regular alter table
659
alter table t1 add i int auto_increment not null primary key first;
661
update t1 set i=5 where i=3;
663
alter table t1 change i i bigint;
665
alter table t1 add unique key (i, v);
666
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
670
# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
671
# without # prefix is not allowed for TEXT columns, while index
672
# is defined with prefix.
674
create table t1 (t varchar(255) default null, key t (t(80)))
675
engine=myisam default charset=latin1;
676
alter table t1 change t t text;
680
# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER
683
CREATE TABLE t1 (a varchar(500));
685
ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);
686
SHOW CREATE TABLE t1;
687
ALTER TABLE t1 ADD KEY(b(50));
688
SHOW CREATE TABLE t1;
690
ALTER TABLE t1 ADD c POINT;
691
SHOW CREATE TABLE t1;
693
--error ER_WRONG_SUB_KEY
694
CREATE TABLE t2 (a INT, KEY (a(20)));
696
ALTER TABLE t1 ADD d INT;
697
--error ER_WRONG_SUB_KEY
698
ALTER TABLE t1 ADD KEY (d(20));
700
# the 5.1 part of the test
701
--error ER_WRONG_SUB_KEY
702
ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));
707
# Bug#18038 MySQL server corrupts binary columns data
710
CREATE TABLE t1 (s CHAR(8) BINARY);
711
INSERT INTO t1 VALUES ('test');
712
SELECT LENGTH(s) FROM t1;
713
ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
714
SELECT LENGTH(s) FROM t1;
717
CREATE TABLE t1 (s BINARY(8));
718
INSERT INTO t1 VALUES ('test');
719
SELECT LENGTH(s) FROM t1;
720
SELECT HEX(s) FROM t1;
721
ALTER TABLE t1 MODIFY s BINARY(10);
722
SELECT HEX(s) FROM t1;
723
SELECT LENGTH(s) FROM t1;
727
# Bug#19386: Multiple alter causes crashed table
728
# The trailing column would get corrupted data, or server could not even read
732
CREATE TABLE t1 (v VARCHAR(3), b INT);
733
INSERT INTO t1 VALUES ('abc', 5);
735
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
739
--echo End of 5.0 tests
742
# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES
743
# It should be consistent across all platforms and for all engines
744
# (Before 5.1 this was not true as behavior was different between
745
# Unix/Windows and transactional/non-transactional tables).
746
# See also innodb_mysql.test
749
drop table if exists t1, t2, t3;
751
create table t1 (i int);
752
create table t3 (j int);
753
insert into t1 values ();
754
insert into t3 values ();
755
# Table which is altered under LOCK TABLES it should stay in list of locked
756
# tables and be available after alter takes place unless ALTER contains RENAME
757
# clause. We should see the new definition of table, of course.
758
lock table t1 write, t3 read;
759
# Example of so-called 'fast' ALTER TABLE
760
alter table t1 modify i int default 1;
761
insert into t1 values ();
763
# And now full-blown ALTER TABLE
764
alter table t1 change i c char(10) default "Two";
765
insert into t1 values ();
767
# If table is renamed then it should be removed from the list
768
# of locked tables. 'Fast' ALTER TABLE with RENAME clause:
769
alter table t1 modify c char(10) default "Three", rename to t2;
770
--error ER_TABLE_NOT_LOCKED
772
--error ER_TABLE_NOT_LOCKED
776
insert into t2 values ();
778
lock table t2 write, t3 read;
779
# Full ALTER TABLE with RENAME
780
alter table t2 change c vc varchar(100) default "Four", rename to t1;
781
--error ER_TABLE_NOT_LOCKED
783
--error ER_TABLE_NOT_LOCKED
787
insert into t1 values ();
793
# Bug#18775 - Temporary table from alter table visible to other threads
795
# Check if special characters work and duplicates are detected.
797
DROP TABLE IF EXISTS `t+1`, `t+2`;
799
CREATE TABLE `t+1` (c1 INT);
800
ALTER TABLE `t+1` RENAME `t+2`;
801
CREATE TABLE `t+1` (c1 INT);
802
--error ER_TABLE_EXISTS_ERROR
803
ALTER TABLE `t+1` RENAME `t+2`;
804
DROP TABLE `t+1`, `t+2`;
806
# Same for temporary tables though these names do not become file names.
807
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
808
ALTER TABLE `tt+1` RENAME `tt+2`;
809
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
810
--error ER_TABLE_EXISTS_ERROR
811
ALTER TABLE `tt+1` RENAME `tt+2`;
812
SHOW CREATE TABLE `tt+1`;
813
SHOW CREATE TABLE `tt+2`;
814
DROP TABLE `tt+1`, `tt+2`;
816
# Check if special characters as in tmp_file_prefix work.
817
CREATE TABLE `#sql1` (c1 INT);
818
CREATE TABLE `@0023sql2` (c1 INT);
820
RENAME TABLE `#sql1` TO `@0023sql1`;
821
RENAME TABLE `@0023sql2` TO `#sql2`;
823
ALTER TABLE `@0023sql1` RENAME `#sql-1`;
824
ALTER TABLE `#sql2` RENAME `@0023sql-2`;
826
INSERT INTO `#sql-1` VALUES (1);
827
INSERT INTO `@0023sql-2` VALUES (2);
828
DROP TABLE `#sql-1`, `@0023sql-2`;
830
# Same for temporary tables though these names do not become file names.
831
CREATE TEMPORARY TABLE `#sql1` (c1 INT);
832
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
834
ALTER TABLE `#sql1` RENAME `@0023sql1`;
835
ALTER TABLE `@0023sql2` RENAME `#sql2`;
837
INSERT INTO `#sql2` VALUES (1);
838
INSERT INTO `@0023sql1` VALUES (2);
839
SHOW CREATE TABLE `#sql2`;
840
SHOW CREATE TABLE `@0023sql1`;
841
DROP TABLE `#sql2`, `@0023sql1`;
844
# Bug #22369: Alter table rename combined with other alterations causes lost tables
846
# This problem happens if the data change is compatible.
847
# Changing to the same type is compatible for example.
850
DROP TABLE IF EXISTS t1;
851
DROP TABLE IF EXISTS t2;
854
int_field INTEGER UNSIGNED NOT NULL,
861
SHOW INDEXES FROM t1;
863
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");
864
--echo "Non-copy data change - new frm, but old data and index files"
866
CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,
869
--error ER_NO_SUCH_TABLE
870
SELECT * FROM t1 ORDER BY int_field;
871
SELECT * FROM t2 ORDER BY unsigned_int_field;
874
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;
880
# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
882
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
883
INSERT INTO t1 VALUES (1, 2, NULL);
885
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;
887
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;
892
# BUG#29957 - alter_table.test fails
894
create table t1 (c char(10) default "Two");
896
insert into t1 values ();
897
alter table t1 modify c char(10) default "Three";
added
removed
mysql-test/t/alter_table.test
