Debian Codegraph @

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

raw
1 ##############################################################
2 # Author: JBM
3 # Date: 2006-01-12
4 # Purpose: To test using ndb memory and disk tables together.
5 ##############################################################
6
7 ##############################################################
8 # Author: Nikolay
9 # Date: 2006-05-12
10 # Purpose: To test using ndb memory and disk tables together.
11 #
12 # Select from disk into memory table
13 # Select from disk into memory table
14 # Create test that loads data, use mysql dump to dump data, drop table,
15 # create table and load from mysql dump.
16 # Use group by asc and dec; Use having; Use order by
17 # ALTER Tests (Meta data testing):
18 # ALTER from InnoDB to Cluster Disk Data
19 # ALTER from MyISAM to Cluster Disk Data
20 # ALTER from Cluster Disk Data to InnoDB
21 # ALTER from Cluster Disk Data to MyISAM
22 # ALTER DD Tables and add columns
23 # ALTER DD Tables and add Indexes
24 # ALTER DD Tables and drop columns
25 #
26 ##############################################################
27 # Author: Jonathan
28 # Date 2006-08-28
29 # Purpose: To take out some of the test that are already
30 # Covered by other tests. Per Jonas
31 # The new purpose of this test is testing "Alter"
32 # Statements. Therefore the name is changed to
33 # ndb_dd_alter.test
34 # Removed tests include:
35 # Select from disk into memory table
36 # Select from disk into memory table
37 # Create test that loads data, use mysql dump to dump data, drop table,
38 # create table and load from mysql dump.
39 # Use group by asc and dec; Use having; Use order by
40 ##############################################################
41
42 -- source include/have_ndb.inc
43
44 ############ Test Setup Section #############
45 -- echo **** Test Setup Section ****
46 ################## ALTER Tests (Meta data testing) ####################
47
48 CREATE LOGFILE GROUP lg
49 ADD UNDOFILE './lg_group/undofile.dat'
50 INITIAL_SIZE 16M
51 UNDO_BUFFER_SIZE = 1M
52 ENGINE=NDB;
53
54 CREATE TABLESPACE ts
55 ADD DATAFILE './table_space/datafile.dat'
56 USE LOGFILE GROUP lg
57 INITIAL_SIZE 36M
58 ENGINE NDB;
59
60 #### Try to ALTER from InnoDB to Cluster Disk Data
61
62 CREATE TABLE test.t1 (
63 a1 smallint NOT NULL,
64 a2 int NOT NULL,
65 a3 bigint NOT NULL,
66 a4 char(10),
67 a5 decimal(5,1),
68 a6 time,
69 a7 date,
70 a8 datetime,
71 a9 VARCHAR(255),
72 a10 blob,
73 PRIMARY KEY(a1)
74 ) ENGINE=InnoDB;
75
76 let $1=20;
77 disable_query_log;
78 while ($1)
79 {
80 eval insert into test.t1 values($1, $1+1, $1+2000000000, "aaa$1", 34.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
81 dec $1;
82 }
83 enable_query_log;
84
85 --disable_warnings
86 SHOW CREATE TABLE test.t1;
87 --enable_warnings
88 SELECT * FROM test.t1 ORDER BY a1;
89 --error ER_ALTER_OPERATION_NOT_SUPPORTED
90 ALTER TABLE test.t1 ALGORITHM=INPLACE, TABLESPACE ts STORAGE DISK ENGINE=NDB;
91 ALTER TABLE test.t1 ALGORITHM=COPY, TABLESPACE ts STORAGE DISK ENGINE=NDB;
92 --disable_warnings
93 SHOW CREATE TABLE test.t1;
94 --enable_warnings
95 # Check column storage
96 source show_attributes.inc;
97 SELECT * FROM test.t1 ORDER BY a1;
98 DROP TABLE test.t1;
99
100 #### Try to ALTER from MyISAM to Cluster Disk Data
101
102 CREATE TABLE test.t1 (
103 a1 smallint NOT NULL,
104 a2 int NOT NULL,
105 a3 bigint NOT NULL,
106 a4 char(10),
107 a5 decimal(5,1),
108 a6 time,
109 a7 date,
110 a8 datetime,
111 a9 VARCHAR(255),
112 a10 blob,
113 PRIMARY KEY(a1)
114 ) ENGINE=MyISAM;
115
116 let $1=20;
117 disable_query_log;
118 while ($1)
119 {
120 eval insert into test.t1 values($1, $1+1, $1+2000000000, "aaa$1", 34.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
121 dec $1;
122 }
123 enable_query_log;
124
125 --disable_warnings
126 SHOW CREATE TABLE test.t1;
127 --enable_warnings
128 SELECT * FROM test.t1 ORDER BY a1;
129 ALTER TABLE test.t1 ALGORITHM=COPY, TABLESPACE ts STORAGE DISK ENGINE=NDB;
130 --disable_warnings
131 SHOW CREATE TABLE test.t1;
132 --enable_warnings
133 # Check column storage
134 source show_attributes.inc;
135 SELECT * FROM test.t1 ORDER BY a1;
136
137 #### Try to ALTER from Cluster Disk Data to InnoDB
138 # Create tablespace in Innodb with same name as in NDB
139 # since the ALTER from NDB to Innodb will try to ALTER the table
140 # into a tablespace with same name in InnoDB
141 CREATE TABLESPACE ts_innodb ADD DATAFILE 'ts_innodb.ibd' ENGINE=Innodb;
142
143 --error ER_ALTER_OPERATION_NOT_SUPPORTED
144 ALTER TABLE test.t1 ALGORITHM=INPLACE, ENGINE=InnoDB TABLESPACE ts_innodb;
145 ALTER TABLE test.t1 ALGORITHM=COPY, ENGINE=InnoDB TABLESPACE ts_innodb;
146 --disable_warnings
147 SHOW CREATE TABLE test.t1;
148 --enable_warnings
149 SELECT * FROM test.t1 ORDER BY a1;
150 ALTER TABLE test.t1 TABLESPACE ts STORAGE DISK ENGINE=NDB;
151 --disable_warnings
152 SHOW CREATE TABLE test.t1;
153 --enable_warnings
154 # Check column storage
155 source show_attributes.inc;
156 # Drop the tablespace
157 DROP TABLESPACE ts_innodb;
158
159 #### Try to ALTER from Cluster Disk Data to MyISAM
160
161 --error ER_ALTER_OPERATION_NOT_SUPPORTED
162 ALTER TABLE test.t1 ALGORITHM=INPLACE, ENGINE=MyISAM;
163 ALTER TABLE test.t1 ALGORITHM=COPY, ENGINE=MyISAM;
164 --disable_warnings
165 SHOW CREATE TABLE test.t1;
166 --enable_warnings
167 DROP TABLE test.t1;
168
169 #### Try to ALTER DD Tables and add columns
170
171 CREATE TABLE test.t1 (a1 INT PRIMARY KEY) TABLESPACE ts STORAGE DISK ENGINE=NDB;
172
173 let $1=20;
174 disable_query_log;
175 while ($1)
176 {
177 eval insert into test.t1 values($1);
178 dec $1;
179 }
180 enable_query_log;
181
182 SELECT * FROM test.t1 ORDER BY a1;
183
184 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
185 PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLESPACE_NAME
186 FROM information_schema.partitions
187 WHERE table_name= 't1' AND partition_name = 'p0';
188
189 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
190 ALTER TABLE test.t1 ALGORITHM=INPLACE, ADD a2 FLOAT, ADD a3 DOUBLE;
191 ALTER TABLE test.t1 ALGORITHM=COPY, ADD a2 FLOAT, ADD a3 DOUBLE;
192
193 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
194 PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLESPACE_NAME
195 FROM information_schema.partitions
196 WHERE table_name= 't1' AND partition_name = 'p0';
197
198 let $1=20;
199 disable_query_log;
200 while ($1)
201 {
202 eval update test.t1 set a2 = $1+1.2345, a3 = $1+20000000.00 where a1 = $1;
203 dec $1;
204 }
205 enable_query_log;
206
207 SELECT * FROM test.t1 ORDER BY a1;
208
209 ALTER TABLE test.t1 ALGORITHM=COPY, ADD a4 BIT, ADD a5 TINYINT, ADD a6 BIGINT, ADD a7 DATE, ADD a8 TIME;
210
211 let $1=20;
212 disable_query_log;
213 while ($1)
214 {
215 eval update test.t1 set a4 = 0, a5 = 1, a6 = $1+23456, a7 = '2006-1-1',
216 a8 = '07:04:00' where a1 = $1;
217 dec $1;
218 }
219 enable_query_log;
220
221 SELECT a1,a2,a3,hex(a4), a5,a6,a7,a8 FROM test.t1 ORDER BY a1;
222
223 ALTER TABLE test.t1 ADD a9 DATETIME, ADD a10 TINYTEXT, ADD a11 MEDIUMTEXT, ADD a12 LONGTEXT, ADD a13 TEXT, ADD a14 BLOB;
224 --disable_warnings
225 SHOW CREATE TABLE test.t1;
226 --enable_warnings
227 # Check column storage
228 source show_attributes.inc;
229
230 disable_query_log;
231 set @d2 = 'dd2';
232 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
233 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
234 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
235 set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
236 enable_query_log;
237
238 let $1=20;
239 disable_query_log;
240 while ($1)
241 {
242 eval update test.t1 set a9 = '1971-5-28 16:55:03', a10 = 'abc', a11 = 'abcdefg',
243 a12 = 'LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL', a13 = 'Text Field',
244 a14 = @d2 where a1 = $1;
245 dec $1;
246 }
247 enable_query_log;
248
249 SELECT a1, a2,a3,hex(a4),a5,a6,a7,a8,a9,a10,a11,a12,a13 FROM test.t1 ORDER BY a1;
250
251 #### Try to move t1 into too-small tablespace ####
252 # Bug #45768
253
254 CREATE TABLESPACE ts2
255 ADD DATAFILE './table_space/datafile2.dat'
256 USE LOGFILE GROUP lg
257 INITIAL_SIZE 1M
258 ENGINE NDB;
259
260 --echo Expecting tablespace is full error
261 # We mention the same error twice here to suppress printing of the error
262 # This is required as the error contains the temp table name which
263 # varies per-run
264 -- disable_query_log
265 call mtr.add_suppression("The table '#.*' is full");
266 call mtr.add_suppression("The table 't1' is full");
267 -- enable_query_log
268 --error 1114,1114
269 ALTER TABLE test.t1 TABLESPACE ts2 STORAGE DISK ENGINE NDB;
270
271 ALTER TABLESPACE ts2
272 DROP DATAFILE './table_space/datafile2.dat';
273 DROP TABLESPACE ts2;
274
275 #### Try to ALTER DD Tables and add Indexes
276
277 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
278 ALTER TABLE test.t1 ALGORITHM=INPLACE, ADD INDEX a2_i (a2), ADD INDEX a3_i (a3);
279 ALTER TABLE test.t1 ALGORITHM=COPY, ADD INDEX a2_i (a2), ADD INDEX a3_i (a3);
280
281 --disable_warnings
282 SHOW CREATE TABLE test.t1;
283 --enable_warnings
284
285 # Check column storage
286 source show_attributes.inc;
287
288 ALTER TABLE test.t1 ALGORITHM=INPLACE, DROP INDEX a2_i;
289
290 --disable_warnings
291 SHOW CREATE TABLE test.t1;
292 --enable_warnings
293
294 # Check column storage
295 source show_attributes.inc;
296
297 TRUNCATE TABLE test.t1;
298
299 --disable_warnings
300 SHOW CREATE TABLE test.t1;
301 --enable_warnings
302
303 # Check column storage
304 source show_attributes.inc;
305
306 #### Try to ALTER DD Tables and drop columns
307
308
309 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
310 ALTER TABLE test.t1 ALGORITHM=INPLACE, DROP a14;
311 ALTER TABLE test.t1 ALGORITHM=COPY, DROP a14;
312 ALTER TABLE test.t1 DROP a13;
313 ALTER TABLE test.t1 DROP a12;
314 ALTER TABLE test.t1 DROP a11;
315 ALTER TABLE test.t1 DROP a10;
316 ALTER TABLE test.t1 DROP a9;
317 ALTER TABLE test.t1 DROP a8;
318 ALTER TABLE test.t1 DROP a7;
319 ALTER TABLE test.t1 DROP a6;
320 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
321 ALTER TABLE test.t1 ALGORITHM=INPLACE, DROP PRIMARY KEY;
322 ALTER TABLE test.t1 ALGORITHM=COPY, DROP PRIMARY KEY;
323
324 --disable_warnings
325 SHOW CREATE TABLE test.t1;
326 --enable_warnings
327
328 # Check column storage
329 source show_attributes.inc;
330
331 DROP TABLE test.t1;
332
333 # Bug#55618
334 create table t1 (a int primary key, b int, c int, index bc (b,c))
335 storage disk tablespace ts engine = ndb;
336 alter table t1 algorithm=inplace, drop index bc;
337 alter table t1 algorithm=inplace, add index b (b);
338 alter table t1 algorithm=inplace, drop index b;
339 drop table t1;
340
341
342 ALTER TABLESPACE ts
343 DROP DATAFILE './table_space/datafile.dat';
344 DROP TABLESPACE ts;
345 DROP LOGFILE GROUP lg ENGINE=NDB;