1
by brian
clean slate |
1 |
#
|
2 |
# Test of alter table
|
|
3 |
#
|
|
4 |
--disable_warnings |
|
5 |
drop table if exists t1,t2; |
|
6 |
drop database if exists mysqltest; |
|
7 |
--enable_warnings |
|
8 |
||
9 |
create table t1 ( |
|
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); |
|
1976.6.1
by Brian Aker
This is a fix for bug lp:686197 |
17 |
SELECT * FROM t1; |
18 |
||
2107.1.1
by Brian Aker
Add in all of the error messages, also remove all cases of --error number |
19 |
--error ER_INVALID_ALTER_TABLE_FOR_NOT_NULL |
1
by brian
clean slate |
20 |
alter table t1 |
21 |
add column col4_5 varchar(20) not null after col4, |
|
22 |
add column col7 varchar(30) not null after col5, |
|
1606
by Brian Aker
datetime was still allowing an alter table with an invalid date. |
23 |
add column col8 datetime not null default '1000-01-01 00:00:00', drop column to_be_deleted, |
1
by brian
clean slate |
24 |
change column col2 fourth varchar(30) not null after col3, |
25 |
modify column col6 int not null first; |
|
1976.6.1
by Brian Aker
This is a fix for bug lp:686197 |
26 |
|
27 |
alter table t1 |
|
28 |
add column col4_5 varchar(20) DEFAULT "added" not null after col4, |
|
29 |
add column col7 varchar(30) DEFAULT "added" not null after col5, |
|
30 |
add column col8 datetime not null default '1000-01-01 00:00:00', |
|
31 |
drop column to_be_deleted, |
|
32 |
change column col2 fourth varchar(30) not null after col3, |
|
33 |
modify column col6 int not null first; |
|
34 |
||
1
by brian
clean slate |
35 |
select * from t1; |
36 |
drop table t1; |
|
37 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
38 |
create table t1 (bandID INT NOT NULL PRIMARY KEY, payoutID int NOT NULL); |
1
by brian
clean slate |
39 |
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); |
201
by Brian Aker
Convert default engine to Innodb |
40 |
alter table t1 add column new_col int; |
1
by brian
clean slate |
41 |
select * from t1; |
201
by Brian Aker
Convert default engine to Innodb |
42 |
alter table t1; |
1
by brian
clean slate |
43 |
select * from t1; |
44 |
drop table t1; |
|
45 |
||
46 |
# Check that pack_keys and dynamic length rows are not forced.
|
|
47 |
||
48 |
CREATE TABLE t1 ( |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
49 |
GROUP_ID int DEFAULT '0' NOT NULL, |
50 |
LANG_ID int DEFAULT '0' NOT NULL, |
|
1
by brian
clean slate |
51 |
NAME varchar(80) DEFAULT '' NOT NULL, |
52 |
PRIMARY KEY (GROUP_ID,LANG_ID), |
|
53 |
KEY NAME (NAME)); |
|
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
54 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
55 |
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; |
56 |
--replace_column 8 # |
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
57 |
show COLUMNS FROM t1; |
1
by brian
clean slate |
58 |
DROP TABLE t1; |
59 |
||
60 |
#
|
|
61 |
# Test of ALTER TABLE ... ORDER BY
|
|
62 |
#
|
|
63 |
||
64 |
create table t1 (n int); |
|
65 |
insert into t1 values(9),(3),(12),(10); |
|
66 |
alter table t1 order by n; |
|
67 |
select * from t1; |
|
68 |
drop table t1; |
|
69 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
70 |
CREATE TEMPORARY TABLE t1 ( |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
71 |
id int NOT NULL default '0', |
72 |
category_id int NOT NULL default '0', |
|
73 |
type_id int NOT NULL default '0', |
|
1
by brian
clean slate |
74 |
body text NOT NULL, |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
75 |
user_id int NOT NULL default '0', |
1
by brian
clean slate |
76 |
status enum('new','old') NOT NULL default 'new', |
77 |
PRIMARY KEY (id) |
|
78 |
) ENGINE=MyISAM; |
|
79 |
||
80 |
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; |
|
81 |
DROP TABLE t1; |
|
82 |
||
83 |
#
|
|
84 |
# Drop and add an auto_increment column
|
|
85 |
#
|
|
86 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
87 |
create table t1 (i int not null auto_increment primary key); |
1
by brian
clean slate |
88 |
insert into t1 values (null),(null),(null),(null); |
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
89 |
alter table t1 drop i,add i int not null auto_increment, drop primary key, add primary key (i); |
1
by brian
clean slate |
90 |
select * from t1; |
91 |
drop table t1; |
|
92 |
||
93 |
#
|
|
94 |
# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1
|
|
95 |
# if it exists
|
|
96 |
#
|
|
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"); |
|
102 |
select * from t1; |
|
103 |
select * from mysqltest.t1; |
|
104 |
--error ER_TABLE_EXISTS_ERROR |
|
105 |
alter table t1 rename mysqltest.t1; |
|
106 |
select * from t1; |
|
107 |
select * from mysqltest.t1; |
|
108 |
drop table t1; |
|
109 |
drop database mysqltest; |
|
110 |
||
111 |
#
|
|
112 |
# ALTER TABLE ... ENABLE/DISABLE KEYS
|
|
113 |
||
114 |
create table t1 (n1 int not null, n2 int, n3 int, n4 float, |
|
115 |
unique(n1), |
|
116 |
key (n1, n2, n3, n4), |
|
117 |
key (n2, n3, n4, n1), |
|
118 |
key (n3, n4, n1, n2), |
|
119 |
key (n4, n1, n2, n3) ); |
|
201
by Brian Aker
Convert default engine to Innodb |
120 |
alter table t1; |
1
by brian
clean slate |
121 |
show keys from t1; |
122 |
#let $1=10000;
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
123 |
set autocommit=0; |
124 |
begin; |
|
1
by brian
clean slate |
125 |
let $1=10; |
126 |
while ($1) |
|
127 |
{
|
|
128 |
eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); |
|
129 |
dec $1; |
|
130 |
}
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
131 |
commit; |
132 |
set autocommit=1; |
|
1
by brian
clean slate |
133 |
alter table t1 enable keys; |
134 |
show keys from t1; |
|
135 |
drop table t1; |
|
136 |
||
137 |
#
|
|
138 |
# Alter table and rename
|
|
139 |
#
|
|
140 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
141 |
create table t1 (i int not null auto_increment primary key); |
1
by brian
clean slate |
142 |
alter table t1 rename t2; |
143 |
alter table t2 rename t1, add c char(10) comment "no comment"; |
|
144 |
show columns from t1; |
|
145 |
drop table t1; |
|
146 |
||
147 |
# implicit analyze
|
|
148 |
||
149 |
create table t1 (a int, b int); |
|
150 |
let $1=100; |
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
151 |
set autocommit=0; |
152 |
begin; |
|
1
by brian
clean slate |
153 |
while ($1) |
154 |
{
|
|
155 |
eval insert into t1 values(1,$1), (2,$1), (3, $1); |
|
156 |
dec $1; |
|
157 |
}
|
|
206.1.1
by Stewart Smith
cut 'make test' time in half. |
158 |
commit; |
159 |
set autocommit=1; |
|
1
by brian
clean slate |
160 |
alter table t1 add unique (a,b), add key (b); |
161 |
show keys from t1; |
|
162 |
analyze table t1; |
|
163 |
show keys from t1; |
|
164 |
drop table t1; |
|
165 |
||
166 |
#
|
|
167 |
# Test with two keys
|
|
168 |
#
|
|
169 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
170 |
CREATE TEMPORARY TABLE t1 ( |
1217
by Brian Aker
Removed bits of charset support from the parser. |
171 |
Host varchar(16) NOT NULL default '', |
172 |
User varchar(16) NOT NULL default '', |
|
1
by brian
clean slate |
173 |
PRIMARY KEY (Host,User), |
174 |
KEY (Host) |
|
175 |
) ENGINE=MyISAM; |
|
176 |
||
177 |
ALTER TABLE t1 DISABLE KEYS; |
|
1273.19.2
by Brian Aker
First pass through show indexs. |
178 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
179 |
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); |
1273.19.2
by Brian Aker
First pass through show indexs. |
180 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
181 |
ALTER TABLE t1 ENABLE KEYS; |
1273.19.2
by Brian Aker
First pass through show indexs. |
182 |
#SHOW INDEX FROM t1;
|
1
by brian
clean slate |
183 |
CHECK TABLES t1; |
184 |
||
1054.1.1
by Brian Aker
Remove guts in parser for LOCK TABLE. |
185 |
# Test RENAME
|
1
by brian
clean slate |
186 |
ALTER TABLE t1 RENAME t2; |
187 |
select * from t2; |
|
188 |
DROP TABLE t2; |
|
189 |
||
190 |
#
|
|
191 |
# BUG#4717 - check for valid table names
|
|
192 |
#
|
|
193 |
create table t1 (a int); |
|
194 |
--error ER_WRONG_TABLE_NAME |
|
195 |
alter table t1 rename to ``; |
|
196 |
--error ER_WRONG_TABLE_NAME |
|
197 |
rename table t1 to ``; |
|
198 |
drop table t1; |
|
199 |
||
200 |
#
|
|
201 |
# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns
|
|
202 |
#
|
|
203 |
drop table if exists t1; |
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
204 |
create TEMPORARY table t1 ( a varchar(10) not null primary key ) engine=myisam; |
1
by brian
clean slate |
205 |
flush tables; |
206 |
alter table t1 modify a varchar(10); |
|
207 |
flush tables; |
|
208 |
alter table t1 modify a varchar(10) not null; |
|
209 |
drop table if exists t1; |
|
210 |
||
211 |
# The following is also part of bug #6236 (CREATE TABLE didn't properly count
|
|
212 |
# not null columns for primary keys)
|
|
213 |
||
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
214 |
create TEMPORARY 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; |
896.3.6
by Stewart Smith
Read Fields out of proto instead of FRM. |
215 |
insert into t1 (a,b,c,d,e,f,g,h,i) values(1,1,1,1,1,1,1,1,1); |
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
216 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
217 |
show table status like 't1'; |
218 |
alter table t1 modify a int; |
|
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
219 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
220 |
show table status like 't1'; |
221 |
drop table t1; |
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
222 |
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; |
1
by brian
clean slate |
223 |
insert into t1 (a) values(1); |
1320.1.18
by Brian Aker
Overhaul of SHOW TABLE STATUS. |
224 |
--replace_column 1 # 6 # 7 # 8 # 9 # 10 # |
1
by brian
clean slate |
225 |
show table status like 't1'; |
226 |
drop table t1; |
|
227 |
||
228 |
#
|
|
229 |
# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)
|
|
230 |
#
|
|
231 |
||
232 |
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); |
|
233 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
234 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
235 |
SHOW CREATE TABLE t1; |
236 |
--error ER_CANT_DROP_FIELD_OR_KEY |
|
237 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
238 |
DROP TABLE t1; |
|
239 |
||
240 |
# BUG#3899
|
|
241 |
create table t1 (a int, b int, key(a)); |
|
242 |
insert into t1 values (1,1), (2,2); |
|
243 |
--error ER_CANT_DROP_FIELD_OR_KEY |
|
244 |
alter table t1 drop key no_such_key; |
|
245 |
alter table t1 drop key a; |
|
246 |
drop table t1; |
|
247 |
||
248 |
#
|
|
249 |
# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)
|
|
250 |
#
|
|
251 |
# Some platforms (Mac OS X, Windows) will send the error message using small letters.
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
252 |
CREATE TEMPORARY TABLE T12207(a int) ENGINE=MYISAM; |
1
by brian
clean slate |
253 |
--replace_result t12207 T12207 |
254 |
--error ER_ILLEGAL_HA |
|
255 |
ALTER TABLE T12207 DISCARD TABLESPACE; |
|
256 |
DROP TABLE T12207; |
|
257 |
||
258 |
#
|
|
259 |
# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"
|
|
260 |
# MySQL should not think that packed field with non-zero decimals is
|
|
261 |
# geometry field and allow to create prefix index which is
|
|
262 |
# shorter than packed field length.
|
|
263 |
#
|
|
264 |
create table t1 ( a timestamp ); |
|
265 |
--error ER_WRONG_SUB_KEY |
|
266 |
alter table t1 add unique ( a(1) ); |
|
267 |
drop table t1; |
|
268 |
||
269 |
#
|
|
270 |
# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table
|
|
271 |
#
|
|
272 |
# This problem happens if the data change is compatible.
|
|
273 |
# Changing to the same type is compatible for example.
|
|
274 |
#
|
|
275 |
--disable_warnings |
|
276 |
drop table if exists t1; |
|
277 |
--enable_warnings |
|
278 |
create table t1 (a int, key(a)); |
|
279 |
show indexes from t1; |
|
280 |
--echo "this used not to disable the index" |
|
201
by Brian Aker
Convert default engine to Innodb |
281 |
alter table t1 modify a int; |
282 |
show indexes from t1; |
|
283 |
||
284 |
alter table t1 enable keys; |
|
285 |
show indexes from t1; |
|
286 |
||
287 |
alter table t1 modify a bigint; |
|
288 |
show indexes from t1; |
|
289 |
||
290 |
alter table t1 enable keys; |
|
291 |
show indexes from t1; |
|
292 |
||
293 |
alter table t1 add b char(10); |
|
294 |
show indexes from t1; |
|
295 |
||
296 |
alter table t1 add c decimal(10,2); |
|
1
by brian
clean slate |
297 |
show indexes from t1; |
298 |
||
299 |
--echo "this however did" |
|
201
by Brian Aker
Convert default engine to Innodb |
300 |
alter table t1; |
1
by brian
clean slate |
301 |
show indexes from t1; |
302 |
||
303 |
desc t1; |
|
304 |
||
305 |
alter table t1 add d decimal(15,5); |
|
306 |
--echo "The key should still be disabled" |
|
307 |
show indexes from t1; |
|
308 |
||
309 |
drop table t1; |
|
310 |
||
311 |
--echo "Now will test with one unique index" |
|
312 |
create table t1(a int, b char(10), unique(a)); |
|
313 |
show indexes from t1; |
|
201
by Brian Aker
Convert default engine to Innodb |
314 |
alter table t1; |
1
by brian
clean slate |
315 |
show indexes from t1; |
316 |
alter table t1 enable keys; |
|
317 |
||
318 |
--echo "If no copy on noop change, this won't touch the data file" |
|
319 |
--echo "Unique index, no change" |
|
201
by Brian Aker
Convert default engine to Innodb |
320 |
alter table t1 modify a int; |
1
by brian
clean slate |
321 |
show indexes from t1; |
322 |
||
323 |
--echo "Change the type implying data copy" |
|
324 |
--echo "Unique index, no change" |
|
201
by Brian Aker
Convert default engine to Innodb |
325 |
alter table t1 modify a bigint; |
1
by brian
clean slate |
326 |
show indexes from t1; |
327 |
||
328 |
alter table t1 modify a bigint; |
|
329 |
show indexes from t1; |
|
330 |
||
331 |
alter table t1 modify a int; |
|
332 |
show indexes from t1; |
|
333 |
||
334 |
drop table t1; |
|
335 |
||
336 |
--echo "Now will test with one unique and one non-unique index" |
|
337 |
create table t1(a int, b char(10), unique(a), key(b)); |
|
338 |
show indexes from t1; |
|
201
by Brian Aker
Convert default engine to Innodb |
339 |
alter table t1; |
1
by brian
clean slate |
340 |
show indexes from t1; |
341 |
alter table t1 enable keys; |
|
342 |
||
343 |
||
344 |
--echo "If no copy on noop change, this won't touch the data file" |
|
345 |
--echo "The non-unique index will be disabled" |
|
201
by Brian Aker
Convert default engine to Innodb |
346 |
alter table t1 modify a int; |
1
by brian
clean slate |
347 |
show indexes from t1; |
348 |
alter table t1 enable keys; |
|
349 |
show indexes from t1; |
|
350 |
||
351 |
--echo "Change the type implying data copy" |
|
352 |
--echo "The non-unique index will be disabled" |
|
201
by Brian Aker
Convert default engine to Innodb |
353 |
alter table t1 modify a bigint; |
1
by brian
clean slate |
354 |
show indexes from t1; |
355 |
||
356 |
--echo "Change again the type, but leave the indexes as_is" |
|
357 |
alter table t1 modify a int; |
|
358 |
show indexes from t1; |
|
359 |
--echo "Try the same. When data is no copied on similar tables, this is noop" |
|
360 |
alter table t1 modify a int; |
|
361 |
show indexes from t1; |
|
362 |
||
363 |
drop table t1; |
|
364 |
||
365 |
||
366 |
#
|
|
367 |
# Bug#11493 - Alter table rename to default database does not work without
|
|
368 |
# db name qualifying
|
|
369 |
#
|
|
370 |
create database mysqltest; |
|
371 |
create table t1 (c1 int); |
|
372 |
# Move table to other database.
|
|
373 |
alter table t1 rename mysqltest.t1; |
|
374 |
# Assure that it has moved.
|
|
375 |
--error ER_BAD_TABLE_ERROR |
|
376 |
drop table t1; |
|
377 |
# Move table back.
|
|
378 |
alter table mysqltest.t1 rename t1; |
|
379 |
# Assure that it is back.
|
|
380 |
drop table t1; |
|
381 |
# Now test for correct message if no database is selected.
|
|
382 |
# Create t1 in 'test'.
|
|
383 |
create table t1 (c1 int); |
|
384 |
# Change to other db.
|
|
385 |
use mysqltest; |
|
386 |
# Drop the current db. This de-selects any db.
|
|
387 |
drop database mysqltest; |
|
388 |
# Now test for correct message.
|
|
389 |
--error ER_NO_DB_ERROR |
|
390 |
alter table test.t1 rename t1; |
|
391 |
# Check that explicit qualifying works even with no selected db.
|
|
392 |
alter table test.t1 rename test.t1; |
|
393 |
# Go back to standard 'test' db.
|
|
394 |
use test; |
|
395 |
drop table t1; |
|
396 |
||
397 |
#
|
|
1223.1.4
by Brian Aker
Merge Brian |
398 |
# BUG#23404 - ROW_FORMAT=COMPACT option is lost is an index is added to the
|
1
by brian
clean slate |
399 |
# table
|
400 |
#
|
|
1222.1.6
by Brian Aker
Fix engines to not rely on HA_CREATE_INFO. |
401 |
CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT; |
1
by brian
clean slate |
402 |
CREATE INDEX i1 ON t1(a); |
942.3.1
by Vladimir Kolesnikov
test generalizations |
403 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
404 |
SHOW CREATE TABLE t1; |
405 |
DROP INDEX i1 ON t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
406 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
407 |
SHOW CREATE TABLE t1; |
408 |
DROP TABLE t1; |
|
409 |
||
410 |
#
|
|
411 |
# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash
|
|
412 |
#
|
|
413 |
--disable_warnings |
|
414 |
DROP TABLE IF EXISTS bug24219; |
|
415 |
DROP TABLE IF EXISTS bug24219_2; |
|
416 |
--enable_warnings |
|
417 |
||
418 |
CREATE TABLE bug24219 (a INT, INDEX(a)); |
|
419 |
||
420 |
SHOW INDEX FROM bug24219; |
|
421 |
||
422 |
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; |
|
423 |
||
424 |
SHOW INDEX FROM bug24219_2; |
|
425 |
||
426 |
DROP TABLE bug24219_2; |
|
427 |
||
428 |
#
|
|
429 |
# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
|
|
430 |
#
|
|
431 |
||
432 |
--disable_warnings |
|
433 |
drop table if exists table_24562; |
|
434 |
--enable_warnings |
|
435 |
||
436 |
create table table_24562( |
|
437 |
section int, |
|
438 |
subsection int, |
|
439 |
title varchar(50)); |
|
440 |
||
441 |
insert into table_24562 values |
|
442 |
(1, 0, "Introduction"), |
|
443 |
(1, 1, "Authors"), |
|
444 |
(1, 2, "Acknowledgements"), |
|
445 |
(2, 0, "Basics"), |
|
446 |
(2, 1, "Syntax"), |
|
447 |
(2, 2, "Client"), |
|
448 |
(2, 3, "Server"), |
|
449 |
(3, 0, "Intermediate"), |
|
450 |
(3, 1, "Complex queries"), |
|
451 |
(3, 2, "Stored Procedures"), |
|
452 |
(3, 3, "Stored Functions"), |
|
453 |
(4, 0, "Advanced"), |
|
454 |
(4, 1, "Replication"), |
|
455 |
(4, 2, "Load balancing"), |
|
456 |
(4, 3, "High availability"), |
|
457 |
(5, 0, "Conclusion"); |
|
458 |
||
459 |
select * from table_24562; |
|
460 |
||
461 |
alter table table_24562 add column reviewer varchar(20), |
|
462 |
order by title; |
|
463 |
||
464 |
select * from table_24562; |
|
465 |
||
466 |
update table_24562 set reviewer="Me" where section=2; |
|
467 |
update table_24562 set reviewer="You" where section=3; |
|
468 |
||
469 |
alter table table_24562 |
|
470 |
order by section ASC, subsection DESC; |
|
471 |
||
472 |
select * from table_24562; |
|
473 |
||
474 |
alter table table_24562 |
|
475 |
order by table_24562.subsection ASC, table_24562.section DESC; |
|
476 |
||
477 |
select * from table_24562; |
|
478 |
||
479 |
--error ER_PARSE_ERROR |
|
480 |
alter table table_24562 order by 12; |
|
481 |
--error ER_PARSE_ERROR |
|
482 |
alter table table_24562 order by (section + 12); |
|
483 |
--error ER_PARSE_ERROR |
|
484 |
alter table table_24562 order by length(title); |
|
485 |
||
486 |
--error ER_BAD_FIELD_ERROR |
|
487 |
alter table table_24562 order by no_such_col; |
|
488 |
||
489 |
drop table table_24562; |
|
490 |
||
491 |
# End of 4.1 tests
|
|
492 |
||
493 |
#
|
|
494 |
# Bug #14693 (ALTER SET DEFAULT doesn't work)
|
|
495 |
#
|
|
496 |
||
223
by Brian Aker
Cleanup int() work. |
497 |
create table t1 (mycol int not null); |
1
by brian
clean slate |
498 |
alter table t1 alter column mycol set default 0; |
499 |
desc t1; |
|
500 |
drop table t1; |
|
501 |
||
502 |
#
|
|
503 |
# Bug#25262 Auto Increment lost when changing Engine type
|
|
504 |
#
|
|
505 |
||
1233.2.1
by Monty Taylor
Renamed instances of HEAP engine to MEMORY. Removed the alias. |
506 |
create TEMPORARY table t1(id int primary key auto_increment) engine=MEMORY; |
1
by brian
clean slate |
507 |
|
508 |
insert into t1 values (null); |
|
509 |
insert into t1 values (null); |
|
510 |
||
511 |
select * from t1; |
|
512 |
||
513 |
# Set auto increment to 50
|
|
514 |
alter table t1 auto_increment = 50; |
|
515 |
||
516 |
# Alter to myisam
|
|
517 |
alter table t1 engine = myisam; |
|
518 |
||
519 |
# This insert should get id 50
|
|
520 |
insert into t1 values (null); |
|
521 |
select * from t1; |
|
522 |
||
1233.2.1
by Monty Taylor
Renamed instances of HEAP engine to MEMORY. Removed the alias. |
523 |
# Alter to MEMORY again
|
524 |
alter table t1 engine = MEMORY; |
|
1
by brian
clean slate |
525 |
insert into t1 values (null); |
526 |
select * from t1; |
|
527 |
||
528 |
drop table t1; |
|
529 |
||
186
by Brian Aker
Partial fix for alter table |
530 |
##
|
531 |
## Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
|
|
532 |
## NO_ZERO_DATE mode.
|
|
533 |
##
|
|
534 |
#create table t1(f1 int);
|
|
535 |
#alter table t1 add column f2 datetime not null, add column f21 date not null;
|
|
536 |
#insert into t1 values(1,'2000-01-01','2000-01-01');
|
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
537 |
#--error ER_TRUNCATED_WRONG_VALUE
|
186
by Brian Aker
Partial fix for alter table |
538 |
#alter table t1 add column f3 datetime not null;
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
539 |
#--error ER_TRUNCATED_WRONG_VALUE
|
186
by Brian Aker
Partial fix for alter table |
540 |
#alter table t1 add column f3 date not null;
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
541 |
#--error ER_TRUNCATED_WRONG_VALUE
|
186
by Brian Aker
Partial fix for alter table |
542 |
#alter table t1 add column f4 datetime not null default '2002-02-02',
|
543 |
# add column f41 date not null;
|
|
544 |
#alter table t1 add column f4 datetime not null default '2002-02-02',
|
|
545 |
# add column f41 date not null default '2002-02-02';
|
|
546 |
#select * from t1;
|
|
547 |
#drop table t1;
|
|
1
by brian
clean slate |
548 |
|
549 |
#
|
|
550 |
# Some additional tests for new, faster alter table. Note that most of the
|
|
551 |
# whole alter table code is being tested all around the test suite already.
|
|
552 |
#
|
|
553 |
||
554 |
create table t1 (v varchar(32)); |
|
555 |
insert into t1 values ('def'),('abc'),('hij'),('3r4f'); |
|
556 |
select * from t1; |
|
557 |
# Fast alter, no copy performed
|
|
558 |
alter table t1 change v v2 varchar(32); |
|
559 |
select * from t1; |
|
560 |
# Fast alter, no copy performed
|
|
561 |
alter table t1 change v2 v varchar(64); |
|
562 |
select * from t1; |
|
563 |
update t1 set v = 'lmn' where v = 'hij'; |
|
564 |
select * from t1; |
|
565 |
# Regular alter table
|
|
566 |
alter table t1 add i int auto_increment not null primary key first; |
|
567 |
select * from t1; |
|
568 |
update t1 set i=5 where i=3; |
|
569 |
select * from t1; |
|
570 |
alter table t1 change i i bigint; |
|
571 |
select * from t1; |
|
572 |
alter table t1 add unique key (i, v); |
|
573 |
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); |
|
574 |
drop table t1; |
|
575 |
||
576 |
#
|
|
577 |
# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
|
|
578 |
# without # prefix is not allowed for TEXT columns, while index
|
|
579 |
# is defined with prefix.
|
|
580 |
#
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
581 |
create TEMPORARY table t1 (t varchar(255) default null, key t (t(80))) engine=myisam; |
1
by brian
clean slate |
582 |
alter table t1 change t t text; |
583 |
drop table t1; |
|
584 |
||
585 |
#
|
|
586 |
# Bug#18038 MySQL server corrupts binary columns data
|
|
587 |
#
|
|
588 |
||
1217
by Brian Aker
Removed bits of charset support from the parser. |
589 |
CREATE TABLE t1 (s CHAR(8)); |
1
by brian
clean slate |
590 |
INSERT INTO t1 VALUES ('test'); |
591 |
SELECT LENGTH(s) FROM t1; |
|
1217
by Brian Aker
Removed bits of charset support from the parser. |
592 |
ALTER TABLE t1 MODIFY s CHAR(10); |
1
by brian
clean slate |
593 |
SELECT LENGTH(s) FROM t1; |
594 |
DROP TABLE t1; |
|
595 |
||
233
by Brian Aker
Fix to remove binary/nchar |
596 |
CREATE TABLE t1 (s varbinary(8)); |
1
by brian
clean slate |
597 |
INSERT INTO t1 VALUES ('test'); |
598 |
SELECT LENGTH(s) FROM t1; |
|
599 |
SELECT HEX(s) FROM t1; |
|
233
by Brian Aker
Fix to remove binary/nchar |
600 |
ALTER TABLE t1 MODIFY s varbinary(10); |
1
by brian
clean slate |
601 |
SELECT HEX(s) FROM t1; |
602 |
SELECT LENGTH(s) FROM t1; |
|
603 |
DROP TABLE t1; |
|
604 |
||
605 |
#
|
|
606 |
# Bug#19386: Multiple alter causes crashed table
|
|
607 |
# The trailing column would get corrupted data, or server could not even read
|
|
608 |
# it.
|
|
609 |
#
|
|
610 |
||
611 |
CREATE TABLE t1 (v VARCHAR(3), b INT); |
|
612 |
INSERT INTO t1 VALUES ('abc', 5); |
|
613 |
SELECT * FROM t1; |
|
614 |
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); |
|
615 |
SELECT * FROM t1; |
|
616 |
DROP TABLE t1; |
|
617 |
||
618 |
--echo End of 5.0 tests |
|
619 |
||
620 |
#
|
|
621 |
# Bug#18775 - Temporary table from alter table visible to other threads
|
|
622 |
#
|
|
623 |
# Check if special characters work and duplicates are detected.
|
|
624 |
--disable_warnings |
|
625 |
DROP TABLE IF EXISTS `t+1`, `t+2`; |
|
626 |
--enable_warnings |
|
627 |
CREATE TABLE `t+1` (c1 INT); |
|
628 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
629 |
CREATE TABLE `t+1` (c1 INT); |
|
630 |
--error ER_TABLE_EXISTS_ERROR |
|
631 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
632 |
DROP TABLE `t+1`, `t+2`; |
|
633 |
#
|
|
634 |
# Same for temporary tables though these names do not become file names.
|
|
635 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
636 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
637 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
638 |
--error ER_TABLE_EXISTS_ERROR |
|
639 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
640 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
641 |
SHOW CREATE TABLE `tt+1`; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
642 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
643 |
SHOW CREATE TABLE `tt+2`; |
644 |
DROP TABLE `tt+1`, `tt+2`; |
|
186
by Brian Aker
Partial fix for alter table |
645 |
##
|
646 |
## Check if special characters as in tmp_file_prefix work.
|
|
647 |
#CREATE TABLE `#sql1` (c1 INT);
|
|
648 |
#CREATE TABLE `@0023sql2` (c1 INT);
|
|
649 |
#SHOW TABLES;
|
|
650 |
#RENAME TABLE `#sql1` TO `@0023sql1`;
|
|
651 |
#RENAME TABLE `@0023sql2` TO `#sql2`;
|
|
652 |
#SHOW TABLES;
|
|
653 |
#ALTER TABLE `@0023sql1` RENAME `#sql-1`;
|
|
654 |
#ALTER TABLE `#sql2` RENAME `@0023sql-2`;
|
|
655 |
#SHOW TABLES;
|
|
656 |
#INSERT INTO `#sql-1` VALUES (1);
|
|
657 |
#INSERT INTO `@0023sql-2` VALUES (2);
|
|
658 |
#DROP TABLE `#sql-1`, `@0023sql-2`;
|
|
1
by brian
clean slate |
659 |
#
|
660 |
# Same for temporary tables though these names do not become file names.
|
|
661 |
CREATE TEMPORARY TABLE `#sql1` (c1 INT); |
|
662 |
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); |
|
663 |
SHOW TABLES; |
|
664 |
ALTER TABLE `#sql1` RENAME `@0023sql1`; |
|
665 |
ALTER TABLE `@0023sql2` RENAME `#sql2`; |
|
666 |
SHOW TABLES; |
|
667 |
INSERT INTO `#sql2` VALUES (1); |
|
668 |
INSERT INTO `@0023sql1` VALUES (2); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
669 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
670 |
SHOW CREATE TABLE `#sql2`; |
942.3.1
by Vladimir Kolesnikov
test generalizations |
671 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
672 |
SHOW CREATE TABLE `@0023sql1`; |
673 |
DROP TABLE `#sql2`, `@0023sql1`; |
|
674 |
||
675 |
#
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
676 |
#
|
1
by brian
clean slate |
677 |
# Bug #22369: Alter table rename combined with other alterations causes lost tables
|
678 |
#
|
|
679 |
# This problem happens if the data change is compatible.
|
|
680 |
# Changing to the same type is compatible for example.
|
|
681 |
#
|
|
682 |
--disable_warnings |
|
683 |
DROP TABLE IF EXISTS t1; |
|
684 |
DROP TABLE IF EXISTS t2; |
|
685 |
--enable_warnings |
|
686 |
CREATE TABLE t1 ( |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
687 |
int_field INTEGER NOT NULL, |
1
by brian
clean slate |
688 |
char_field CHAR(10), |
689 |
INDEX(`int_field`) |
|
690 |
);
|
|
691 |
||
692 |
DESCRIBE t1; |
|
693 |
||
694 |
SHOW INDEXES FROM t1; |
|
695 |
||
696 |
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); |
|
697 |
--echo "Non-copy data change - new frm, but old data and index files" |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
698 |
ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2; |
1
by brian
clean slate |
699 |
|
2140.1.2
by Brian Aker
Merge in decomplication of our error system when reading through the table |
700 |
--error ER_TABLE_UNKNOWN |
1
by brian
clean slate |
701 |
SELECT * FROM t1 ORDER BY int_field; |
702 |
SELECT * FROM t2 ORDER BY unsigned_int_field; |
|
703 |
DESCRIBE t2; |
|
704 |
DESCRIBE t2; |
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
705 |
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL; |
1
by brian
clean slate |
706 |
DESCRIBE t2; |
707 |
||
708 |
DROP TABLE t2; |
|
709 |
||
710 |
#
|
|
711 |
# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
|
|
712 |
#
|
|
713 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); |
|
714 |
INSERT INTO t1 VALUES (1, 2, NULL); |
|
715 |
SELECT * FROM t1; |
|
716 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; |
|
717 |
SELECT * FROM t1; |
|
718 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; |
|
719 |
SELECT * FROM t1; |
|
720 |
DROP TABLE t1; |