alter_table.test 5.91 KB
Newer Older
1 2 3
#
# Test of alter table
#
4
--disable_warnings
5
drop table if exists t1,t2;
6 7
--enable_warnings

8 9 10 11 12 13 14
create table t1 (
col1 int not null auto_increment primary key,
col2 varchar(30) not null,
col3 varchar (20) not null,
col4 varchar(4) not null,
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
col6 int not null, to_be_deleted int);
15
insert into t1 values (2,4,3,5,"PENDING",1,7);
16 17
alter table t1
add column col4_5 varchar(20) not null after col4,
18 19 20 21 22
add column col7 varchar(30) not null after col5,
add column col8 datetime not null, drop column to_be_deleted,
change column col2 fourth varchar(30) not null after col3,
modify column col6 int not null first;
select * from t1;
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
drop table t1;

create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
alter table t1 add column new_col int, order by payoutid,bandid;
select * from t1;
alter table t1 order by bandid,payoutid;
select * from t1;
drop table t1;

# Check that pack_keys and dynamic length rows are not forced. 

CREATE TABLE t1 (
GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL,
LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL,
NAME varchar(80) DEFAULT '' NOT NULL,
PRIMARY KEY (GROUP_ID,LANG_ID),
KEY NAME (NAME));
#show table status like "t1";
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
monty@donna.mysql.com's avatar
monty@donna.mysql.com committed
43
SHOW FULL COLUMNS FROM t1;
44 45 46 47 48 49 50 51 52 53 54
DROP TABLE t1;

#
# Test of ALTER TABLE ... ORDER BY
#

create table t1 (n int);
insert into t1 values(9),(3),(12),(10);
alter table t1 order by n;
select * from t1;
drop table t1;
monty@donna.mysql.fi's avatar
monty@donna.mysql.fi committed
55 56 57 58 59 60 61 62 63 64 65

CREATE TABLE t1 (
  id int(11) unsigned NOT NULL default '0',
  category_id tinyint(4) unsigned NOT NULL default '0',
  type_id tinyint(4) unsigned NOT NULL default '0',
  body text NOT NULL,
  user_id int(11) unsigned NOT NULL default '0',
  status enum('new','old') NOT NULL default 'new',
  PRIMARY KEY (id)
) TYPE=MyISAM;

66 67
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;
DROP TABLE t1;
monty@donna.mysql.fi's avatar
monty@donna.mysql.fi committed
68

69 70 71 72 73 74 75 76 77 78 79
#
# The following combination found a hang-bug in MyISAM
#

CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) type=myisam;
insert into t1 values (null,"hello");
LOCK TABLES t1 WRITE;
ALTER TABLE t1 ADD Column new_col int not null;
UNLOCK TABLES;
OPTIMIZE TABLE t1;
DROP TABLE t1;
80 81 82 83 84 85 86 87 88 89 90

#
# ALTER TABLE ... ENABLE/DISABLE KEYS

create table t1 (n1 int not null, n2 int, n3 int, n4 float,
                unique(n1),
                key (n1, n2, n3, n4),
                key (n2, n3, n4, n1),
                key (n3, n4, n1, n2),
                key (n4, n1, n2, n3) );
alter table t1 disable keys;
91
show keys from t1;
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
92 93
#let $1=10000;
let $1=10;
94 95 96 97 98 99
while ($1)
{
 eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND());
 dec $1;
}
alter table t1 enable keys;
100
show keys from t1;
101
drop table t1;
monty@work.mysql.com's avatar
merge  
monty@work.mysql.com committed
102

103 104 105 106 107 108 109 110 111
#
# Drop and add an auto_increment column
#

create table t1 (i int unsigned not null auto_increment primary key);
insert into t1 values (null),(null),(null),(null);
alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i);
select * from t1;
drop table t1;
112 113 114 115 116 117 118 119 120 121

#
# Alter table and rename
#

create table t1 (i int unsigned not null auto_increment primary key);
alter table t1 rename t2;
alter table t2 rename t1, add c char(10) comment "no comment";
show columns from t1;
drop table t1;
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137

# implicit analyze

create table t1 (a int, b int);
let $1=100;
while ($1)
{
 eval insert into t1 values(1,$1), (2,$1), (3, $1);
 dec $1;
}
alter table t1 add unique (a,b), add key (b);
show keys from t1;
analyze table t1;
show keys from t1;
drop table t1;

138 139 140 141 142 143 144 145 146
#
# Test of ALTER TABLE DELAYED
#

CREATE TABLE t1 (i int(10), index(i) );
ALTER TABLE t1 DISABLE KEYS;
INSERT DELAYED INTO t1 VALUES(1),(2),(3);
ALTER TABLE t1 ENABLE KEYS;
drop table t1;
147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

#
# Test that data get converted when character set is changed
# Test that data doesn't get converted when src or dst is BINARY/BLOB
#
set names koi8r;
create table t1 (a char(10) character set koi8r);
insert into t1 values ('');
select a,hex(a) from t1;
alter table t1 change a a char(10) character set cp1251;
select a,hex(a) from t1;
alter table t1 change a a char(10) binary;
select a,hex(a) from t1;
alter table t1 change a a char(10) character set cp1251;
select a,hex(a) from t1;
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;
alter table t1 change a a varchar(10) character set cp1251;
select a,hex(a) from t1;
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;
alter table t1 change a a text character set cp1251;
select a,hex(a) from t1;
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;

drop table t1;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
174

175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235
#
# Test ALTER TABLE ENABLE/DISABLE keys when things are locked
#

CREATE TABLE t1 (
  Host varchar(16) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  PRIMARY KEY  (Host,User)
) TYPE=MyISAM;

ALTER TABLE t1 DISABLE KEYS;
LOCK TABLES t1 WRITE;
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
SHOW INDEX FROM t1;
ALTER TABLE t1 ENABLE KEYS;
UNLOCK TABLES;
CHECK TABLES t1;
DROP TABLE t1;

#
# Test with two keys
#

CREATE TABLE t1 (
  Host varchar(16) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  PRIMARY KEY  (Host,User),
  KEY  (Host)
) TYPE=MyISAM;

ALTER TABLE t1 DISABLE KEYS;
SHOW INDEX FROM t1;
LOCK TABLES t1 WRITE;
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
SHOW INDEX FROM t1;
ALTER TABLE t1 ENABLE KEYS;
SHOW INDEX FROM t1;
UNLOCK TABLES;
CHECK TABLES t1;

# Test RENAME with LOCK TABLES
LOCK TABLES t1 WRITE;
ALTER TABLE t1 RENAME t2;
UNLOCK TABLES;
select * from t2;
DROP TABLE t2;

#
# Test disable keys with locking
#
CREATE TABLE t1 (
  Host varchar(16) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  PRIMARY KEY  (Host,User),
  KEY  (Host)
) TYPE=MyISAM;

LOCK TABLES t1 WRITE;
ALTER TABLE t1 DISABLE KEYS;
SHOW INDEX FROM t1;
DROP TABLE t1;