Debian Codegraph @

mysql-test/suite/ndb/t/ndb_alter_table.test

raw
1 -- source include/have_multi_ndb.inc
2
3 connect (con1,localhost,root,,test);
4 connect (con2,localhost,root,,test);
5
6 connection con1;
7
8 #
9 # Basic test to show that the ALTER TABLE
10 # is working
11 #
12 CREATE TABLE t1 (
13 a INT NOT NULL,
14 b INT NOT NULL
15 ) ENGINE=ndbcluster;
16
17 INSERT INTO t1 VALUES (9410,9412);
18
19 ALTER TABLE t1 ADD COLUMN c int not null;
20 SELECT * FROM t1;
21
22 DROP TABLE t1;
23
24 #
25 # Verfify changing table names between databases
26 #
27 CREATE DATABASE mysqltest;
28 USE mysqltest;
29 CREATE TABLE t1 (
30 a INT NOT NULL,
31 b INT NOT NULL
32 ) ENGINE=ndbcluster;
33 RENAME TABLE t1 TO test.t1;
34 SHOW TABLES;
35 DROP DATABASE mysqltest;
36 USE test;
37 SHOW TABLES;
38 DROP TABLE t1;
39
40 #
41 # More advanced test
42 #
43 create table t1 (
44 col1 int not null auto_increment primary key,
45 col2 varchar(30) not null,
46 col3 varchar (20) not null,
47 col4 varchar(4) not null,
48 col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
49 col6 int not null, to_be_deleted int) ENGINE=ndbcluster charset=latin1;
50 --replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
51 show table status;
52 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
53 insert into t1 values
54 (0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7);
55 --replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
56 show table status;
57 select * from t1 order by col1;
58 alter table t1
59 add column col4_5 varchar(20) not null after col4,
60 add column col7 varchar(30) not null after col5,
61 add column col8 datetime not null, drop column to_be_deleted,
62 change column col2 fourth varchar(30) not null after col3,
63 modify column col6 int not null first;
64 --replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
65 show table status;
66 select * from t1 order by col1;
67 insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
68 --replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
69 show table status;
70 select * from t1 order by col1;
71 delete from t1;
72 insert into t1 values (0,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
73 SET SQL_MODE='';
74 insert into t1 values (1,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
75 select * from t1 order by col1;
76 alter table t1 drop column col4_5;
77 insert into t1 values (2,0,4,3,5,"PENDING","EXTRA",'2004-01-01 00:00:00');
78 select * from t1 order by col1;
79 drop table t1;
80
81
82 #
83 # Check that invalidating dictionary cache works
84 #
85
86 CREATE TABLE t1 (
87 a INT NOT NULL,
88 b INT NOT NULL
89 ) ENGINE=ndbcluster;
90
91 INSERT INTO t1 VALUES (9410,9412);
92
93 ALTER TABLE t1 ADD COLUMN c int not null;
94 select * from t1 order by a;
95
96 connection con2;
97 select * from t1 order by a;
98 alter table t1 drop c;
99
100 connection con1;
101 select * from t1 order by a;
102 drop table t1;
103
104 connection con2;
105 --error 1146
106 select * from t1 order by a;
107
108 CREATE TABLE t1 (
109 a INT NOT NULL PRIMARY KEY,
110 b INT NOT NULL
111 ) ENGINE=ndbcluster;
112
113 INSERT INTO t1 VALUES (0,1),(17,18);
114 select * from t1 order by a;
115 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
116 alter table t1 modify column a int not null auto_increment;
117 SET SQL_MODE='';
118 select * from t1 order by a;
119 INSERT INTO t1 VALUES (0,19),(20,21);
120 select * from t1 order by a;
121 drop table t1;
122
123 CREATE TABLE t1 (
124 a INT NOT NULL PRIMARY KEY,
125 b INT NOT NULL
126 ) ENGINE=ndbcluster;
127
128 INSERT INTO t1 VALUES (0,1),(17,18);
129 select * from t1 order by a;
130 alter table t1 add c int not null unique auto_increment;
131 select c from t1 order by c;
132 INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0);
133 select c from t1 order by c;
134 drop table t1;
135
136 ## Test moved to ndb_alter_table_row|stmt respectively as behaviour differs
137 #create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
138 #engine=ndb;
139 #insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
140 #create index c on t1(c);
141 #connection server2;
142 #select * from t1 where c = 'two';
143 #connection server1;
144 #alter table t1 drop index c;
145 #connection server2;
146 #select * from t1 where c = 'two';
147 #connection server1;
148 #drop table t1;
149
150 #--disable_warnings
151 #DROP TABLE IF EXISTS t2;
152 #--enable_warnings
153
154 #create table t2 (a int NOT NULL PRIMARY KEY) engine=myisam;
155 #let $1=12001;
156 #disable_query_log;
157 #while ($1)
158 #{
159 # eval insert into t2 values($1);
160 # dec $1;
161 #}
162 #enable_query_log;
163 #alter table t2 engine=ndbcluster;
164 #alter table t2 add c int;
165 #--error 1297
166 #delete from t2;
167 #to make sure we do a full table scan
168 #select count(*) from t2 where a+0 > 0;
169 #truncate table t2;
170 #select count(*) from t2;
171 #drop table t2;
172
173 ## Test moved to ndb_alter_table_row|stmt respectively as behaviour differs
174 #connection server1;
175 #create table t3 (a int primary key) engine=ndbcluster;
176
177 #connection server2;
178 #begin;
179 #insert into t3 values (1);
180
181 #connection server1;
182 #alter table t3 rename t4;
183
184 #connection server2;
185 ## with rbr the below will not work as the "alter" event
186 ## explicitly invalidates the dictionary cache.
187 ### This should work as transaction is ongoing...
188 ##delete from t3;
189 ##insert into t3 values (1);
190 #commit;
191
192 ## This should fail as its a new transaction
193 #--error 1146
194 #select * from t3;
195 #select * from t4;
196 #drop table t4;
197 #show tables;
198 #connection server1;
199
200 create table t1 (
201 ai bigint auto_increment,
202 c001 int(11) not null,
203 c002 int(11) not null,
204 c003 int(11) not null,
205 c004 int(11) not null,
206 c005 int(11) not null,
207 c006 int(11) not null,
208 c007 int(11) not null,
209 c008 int(11) not null,
210 c009 int(11) not null,
211 c010 int(11) not null,
212 c011 int(11) not null,
213 c012 int(11) not null,
214 c013 int(11) not null,
215 c014 int(11) not null,
216 c015 int(11) not null,
217 c016 int(11) not null,
218 c017 int(11) not null,
219 c018 int(11) not null,
220 c019 int(11) not null,
221 c020 int(11) not null,
222 c021 int(11) not null,
223 c022 int(11) not null,
224 c023 int(11) not null,
225 c024 int(11) not null,
226 c025 int(11) not null,
227 c026 int(11) not null,
228 c027 int(11) not null,
229 c028 int(11) not null,
230 c029 int(11) not null,
231 c030 int(11) not null,
232 c031 int(11) not null,
233 c032 int(11) not null,
234 c033 int(11) not null,
235 c034 int(11) not null,
236 c035 int(11) not null,
237 c036 int(11) not null,
238 c037 int(11) not null,
239 c038 int(11) not null,
240 c039 int(11) not null,
241 c040 int(11) not null,
242 c041 int(11) not null,
243 c042 int(11) not null,
244 c043 int(11) not null,
245 c044 int(11) not null,
246 c045 int(11) not null,
247 c046 int(11) not null,
248 c047 int(11) not null,
249 c048 int(11) not null,
250 c049 int(11) not null,
251 c050 int(11) not null,
252 c051 int(11) not null,
253 c052 int(11) not null,
254 c053 int(11) not null,
255 c054 int(11) not null,
256 c055 int(11) not null,
257 c056 int(11) not null,
258 c057 int(11) not null,
259 c058 int(11) not null,
260 c059 int(11) not null,
261 c060 int(11) not null,
262 c061 int(11) not null,
263 c062 int(11) not null,
264 c063 int(11) not null,
265 c064 int(11) not null,
266 c065 int(11) not null,
267 c066 int(11) not null,
268 c067 int(11) not null,
269 c068 int(11) not null,
270 c069 int(11) not null,
271 c070 int(11) not null,
272 c071 int(11) not null,
273 c072 int(11) not null,
274 c073 int(11) not null,
275 c074 int(11) not null,
276 c075 int(11) not null,
277 c076 int(11) not null,
278 c077 int(11) not null,
279 c078 int(11) not null,
280 c079 int(11) not null,
281 c080 int(11) not null,
282 c081 int(11) not null,
283 c082 int(11) not null,
284 c083 int(11) not null,
285 c084 int(11) not null,
286 c085 int(11) not null,
287 c086 int(11) not null,
288 c087 int(11) not null,
289 c088 int(11) not null,
290 c089 int(11) not null,
291 c090 int(11) not null,
292 c091 int(11) not null,
293 c092 int(11) not null,
294 c093 int(11) not null,
295 c094 int(11) not null,
296 c095 int(11) not null,
297 c096 int(11) not null,
298 c097 int(11) not null,
299 c098 int(11) not null,
300 c099 int(11) not null,
301 c100 int(11) not null,
302 c101 int(11) not null,
303 c102 int(11) not null,
304 c103 int(11) not null,
305 c104 int(11) not null,
306 c105 int(11) not null,
307 c106 int(11) not null,
308 c107 int(11) not null,
309 c108 int(11) not null,
310 c109 int(11) not null,
311 primary key (ai),
312 unique key tx1 (c002, c003, c004, c005)) engine=ndb;
313
314 create index tx2
315 on t1 (c010, c011, c012, c013);
316
317 drop table t1;
318
319 # simple test that auto incr is not lost at rename or alter
320 create table t1 (a int primary key auto_increment, b int) engine=ndb;
321 insert into t1 (b) values (101),(102),(103);
322 select * from t1 where a = 3;
323 alter table t1 rename t2;
324 insert into t2 (b) values (201),(202),(203);
325 select * from t2 where a = 6;
326 alter table t2 add c int;
327 insert into t2 (b) values (301),(302),(303);
328 select * from t2 where a = 9;
329 alter table t2 rename t1;
330 insert into t1 (b) values (401),(402),(403);
331 select * from t1 where a = 12;
332 drop table t1;
333
334 # some other ALTER combinations
335 # Check add/drop primary key (not supported on-line)
336 create table t1(a int not null) engine=ndb;
337 --source show_primary_keys.inc
338 insert into t1 values (1),(2),(3);
339 alter table t1 add primary key (a);
340 --source show_primary_keys.inc
341 update t1 set a = 17 where a = 1;
342 select * from t1 order by a;
343 alter table t1 drop primary key;
344 --source show_primary_keys.inc
345 update t1 set a = 1 where a = 17;
346 select * from t1 order by a;
347 drop table t1;
348
349 # bug#31233 mysql_alter_table() fails to drop UNIQUE KEY
350 create table t1(a int not null) engine=ndb;
351 --source show_primary_keys.inc
352 insert into t1 values (1),(2),(3);
353 create unique index pk on t1(a);
354 --source show_primary_keys.inc
355 update t1 set a = 17 where a = 1;
356 select * from t1 order by a;
357 alter table t1 drop index pk;
358 --source show_primary_keys.inc
359 update t1 set a = 1 where a = 17;
360 select * from t1 order by a;
361 drop table t1;
362
363 # alter .. alter
364 create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
365 --disable_warnings
366 show create table t1;
367 --enable_warnings
368 alter table t1 alter b set default 1;
369 --disable_warnings
370 show create table t1;
371 --enable_warnings
372 drop table t1;
373
374 # alter .. order by
375 create table t1 (a int not null, b int not null) engine=ndb;
376 insert into t1 values (1, 300), (2, 200), (3, 100);
377 select * from t1 order by a;
378 alter table t1 order by b;
379 select * from t1 order by b;
380 drop table t1;
381
382 #
383 # Alter main memory columns to disk columns (by copying the table)
384 #
385
386 CREATE LOGFILE GROUP lg1
387 ADD UNDOFILE 'undofile.dat'
388 ENGINE = ndb;
389
390 CREATE TABLESPACE ts1
391 ADD DATAFILE 'datafile.dat'
392 USE LOGFILE GROUP lg1
393 ENGINE = ndb;
394
395 CREATE TABLE t1
396 (a INT NOT NULL PRIMARY KEY, b VARCHAR(20), c VARCHAR(20))
397 ENGINE = ndb;
398
399 ALTER TABLE t1 TABLESPACE ts1, MODIFY c CHAR(20) STORAGE DISK;
400
401 DROP TABLE t1;
402
403 ALTER TABLESPACE ts1
404 DROP DATAFILE 'datafile.dat';
405
406 DROP TABLESPACE ts1;
407
408 DROP LOGFILE GROUP lg1
409 ENGINE = ndb;
410
411 #
412 # BUG19667566 : PARTITION BY WITH COMMENT LEADS TO MYSQL SERVER REBOOT IN MYSQL CLUSTER
413 #
414 --echo #test alter table add column with partion by and comment clause
415 CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
416 ALTER TABLE t1 ADD value INT NULL, COMMENT='comment1' PARTITION BY KEY(id);
417 --disable_warnings
418 SHOW CREATE TABLE t1;
419 --enable_warnings
420 DROP TABLE t1;
421
422 --echo #test alter table with and without ENGINE clause
423 CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
424 ALTER TABLE t1 ENGINE=NDB PARTITION BY KEY(id);
425 --disable_warnings
426 SHOW CREATE TABLE t1;
427 --enable_warnings
428 DROP TABLE t1;
429
430 CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
431 ALTER TABLE t1 PARTITION BY KEY(id);
432 --disable_warnings
433 SHOW CREATE TABLE t1;
434 --enable_warnings
435 DROP TABLE t1;
436
437 --echo bug#24577931 table comment is case sensitive
438 create table tbasic(
439 id int not null,
440 name varchar(20), age int
441 ) engine=ndb charset=latin1;
442
443 SELECT read_backup, fully_replicated from ndbinfo.dictionary_tables
444 WHERE table_name = 'tbasic';
445
446 alter table tbasic algorithm=copy, comment="NDB_TABLE=FULLY_REPLICATEd=1";
447
448 SELECT read_backup, fully_replicated from ndbinfo.dictionary_tables
449 WHERE table_name = 'tbasic';
450
451 drop table tbasic;