Debian Codegraph @

mysql-test/suite/innodb/t/create_tablespace_8k.test

raw
1 #
2 # WL#6205 - A series of tests to show the correct behavior for
3 # CREATE TABLESPACE and associated SQL statements for 8k page size.
4 #
5
6 --echo #
7 --echo # CREATE TABLESPACE related tests for 8k page sizes.
8 --echo #
9
10 SET DEFAULT_STORAGE_ENGINE=InnoDB;
11 LET $MYSQLD_DATADIR = `select @@datadir`;
12 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
13
14 --echo # Strict-mode has no effect on CREATE TABLESPACE. But it does affect
15 --echo # whether an invalid KEY_BLOCK_SIZE is rejected or adjusted.
16 SHOW VARIABLES LIKE 'innodb_strict_mode';
17 SHOW VARIABLES LIKE 'innodb_file_per_table';
18
19 --echo #
20 --echo # Create a tablespace with compressed page sizes that can match
21 --echo # innodb-page-size.
22 --echo #
23 CREATE TABLESPACE s_1k ADD DATAFILE 's_1k.ibd' FILE_BLOCK_SIZE=1k;
24 CREATE TABLESPACE s_2k ADD DATAFILE 's_2k.ibd' FILE_BLOCK_SIZE=2k;
25 CREATE TABLESPACE s_4k ADD DATAFILE 's_4k.ibd' FILE_BLOCK_SIZE=4k;
26 CREATE TABLESPACE s_8k ADD DATAFILE 's_8k.ibd' FILE_BLOCK_SIZE=8k;
27 --error ER_ILLEGAL_HA_CREATE_OPTION
28 CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
29 SHOW WARNINGS;
30 --error ER_ILLEGAL_HA_CREATE_OPTION
31 CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
32 SHOW WARNINGS;
33 --error ER_ILLEGAL_HA_CREATE_OPTION
34 CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
35 SHOW WARNINGS;
36 --source suite/innodb/include/show_i_s_tablespaces.inc
37
38 --echo #
39 --echo # Try to put a temporary table into a non-temporary compressed tablespace
40 --echo #
41 --error ER_ILLEGAL_HA_CREATE_OPTION
42 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
43 SHOW WARNINGS;
44
45 --echo #
46 --echo # Add tables to the 1K tablespace.
47 --echo #
48 CREATE TABLE t_zip1k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
49 --error ER_ILLEGAL_HA_CREATE_OPTION
50 CREATE TABLE t_zip2k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_1k;
51 SHOW WARNINGS;
52 --error ER_ILLEGAL_HA_CREATE_OPTION
53 CREATE TABLE t_zip4k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_1k;
54 SHOW WARNINGS;
55 --error ER_ILLEGAL_HA_CREATE_OPTION
56 CREATE TABLE t_zip8k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_1k;
57 SHOW WARNINGS;
58 --error ER_ILLEGAL_HA_CREATE_OPTION
59 CREATE TABLE t_zip16k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_1k;
60 SHOW WARNINGS;
61 --error ER_ILLEGAL_HA_CREATE_OPTION
62 CREATE TABLE t_zip32k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_1k;
63 SHOW WARNINGS;
64 --error ER_ILLEGAL_HA_CREATE_OPTION
65 CREATE TABLE t_red_in_1k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_1k;
66 SHOW WARNINGS;
67 --error ER_ILLEGAL_HA_CREATE_OPTION
68 CREATE TABLE t_cmp_in_1k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_1k;
69 SHOW WARNINGS;
70 --error ER_ILLEGAL_HA_CREATE_OPTION
71 CREATE TABLE t_dyn_in_1k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_1k;
72 SHOW WARNINGS;
73
74 --echo #
75 --echo # Add tables to the 2K tablespace.
76 --echo #
77 --error ER_ILLEGAL_HA_CREATE_OPTION
78 CREATE TABLE t_zip1k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_2k;
79 SHOW WARNINGS;
80 CREATE TABLE t_zip2k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_2k;
81 --error ER_ILLEGAL_HA_CREATE_OPTION
82 CREATE TABLE t_zip4k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_2k;
83 SHOW WARNINGS;
84 --error ER_ILLEGAL_HA_CREATE_OPTION
85 CREATE TABLE t_zip8k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_2k;
86 SHOW WARNINGS;
87 --error ER_ILLEGAL_HA_CREATE_OPTION
88 CREATE TABLE t_zip16k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_2k;
89 SHOW WARNINGS;
90 --error ER_ILLEGAL_HA_CREATE_OPTION
91 CREATE TABLE t_zip32k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_2k;
92 SHOW WARNINGS;
93 --error ER_ILLEGAL_HA_CREATE_OPTION
94 CREATE TABLE t_red_in_2k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_2k;
95 SHOW WARNINGS;
96 --error ER_ILLEGAL_HA_CREATE_OPTION
97 CREATE TABLE t_cmp_in_2k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_2k;
98 SHOW WARNINGS;
99 --error ER_ILLEGAL_HA_CREATE_OPTION
100 CREATE TABLE t_dyn_in_2k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_2k;
101 SHOW WARNINGS;
102
103 --echo #
104 --echo # Add tables to the 4K tablespace.
105 --echo #
106 --error ER_ILLEGAL_HA_CREATE_OPTION
107 CREATE TABLE t_zip1k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_4k;
108 SHOW WARNINGS;
109 --error ER_ILLEGAL_HA_CREATE_OPTION
110 CREATE TABLE t_zip2k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_4k;
111 SHOW WARNINGS;
112 CREATE TABLE t_zip4k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_4k;
113 --error ER_ILLEGAL_HA_CREATE_OPTION
114 CREATE TABLE t_zip8k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_4k;
115 SHOW WARNINGS;
116 --error ER_ILLEGAL_HA_CREATE_OPTION
117 CREATE TABLE t_zip16k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_4k;
118 SHOW WARNINGS;
119 --error ER_ILLEGAL_HA_CREATE_OPTION
120 CREATE TABLE t_zip32k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_4k;
121 SHOW WARNINGS;
122 --error ER_ILLEGAL_HA_CREATE_OPTION
123 CREATE TABLE t_red_in_4k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_4k;
124 SHOW WARNINGS;
125 --error ER_ILLEGAL_HA_CREATE_OPTION
126 CREATE TABLE t_cmp_in_4k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_4k;
127 SHOW WARNINGS;
128 --error ER_ILLEGAL_HA_CREATE_OPTION
129 CREATE TABLE t_dyn_in_4k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_4k;
130 SHOW WARNINGS;
131
132 --echo #
133 --echo # Add tables to the 8K tablespace.
134 --echo #
135 --error ER_ILLEGAL_HA_CREATE_OPTION
136 CREATE TABLE t_zip1k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_8k;
137 SHOW WARNINGS;
138 --error ER_ILLEGAL_HA_CREATE_OPTION
139 CREATE TABLE t_zip2k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_8k;
140 SHOW WARNINGS;
141 --error ER_ILLEGAL_HA_CREATE_OPTION
142 CREATE TABLE t_zip4k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_8k;
143 SHOW WARNINGS;
144 --error ER_ILLEGAL_HA_CREATE_OPTION
145 CREATE TABLE t_zip8k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_8k;
146 SHOW WARNINGS;
147 --error ER_ILLEGAL_HA_CREATE_OPTION
148 CREATE TABLE t_zip16k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_8k;
149 SHOW WARNINGS;
150 --error ER_ILLEGAL_HA_CREATE_OPTION
151 CREATE TABLE t_zip32k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_8k;
152 SHOW WARNINGS;
153
154 CREATE TABLE t_red_in_8k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_8k;
155 CREATE TABLE t_com_in_8k (a int, b text) ROW_FORMAT=compact TABLESPACE s_8k;
156 CREATE TABLE t_dyn_in_8k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_8k;
157
158 --echo # Add data to the existing Tables
159 INSERT INTO t_zip1k_in_1k VALUES (1,'a'),(2,'b'),(3,'c');
160 INSERT INTO t_zip2k_in_2k VALUES (1,'a'),(2,'b'),(3,'c');
161 INSERT INTO t_zip4k_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
162 INSERT INTO t_red_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
163 INSERT INTO t_com_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
164 INSERT INTO t_dyn_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
165
166 --echo # Restart mysqld
167 --source include/restart_mysqld.inc
168
169 --echo #
170 --echo # Try to drop a tablespace which is not empty
171 --echo #
172 --error ER_TABLESPACE_IS_NOT_EMPTY
173 DROP TABLESPACE s_8k;
174
175 --echo #
176 --echo # Add more data to the existing Tables
177 --echo #
178 INSERT INTO t_zip1k_in_1k VALUES (4,'d');
179 INSERT INTO t_zip2k_in_2k VALUES (4,'d');
180 INSERT INTO t_zip4k_in_4k VALUES (4,'d');
181 INSERT INTO t_red_in_8k VALUES (4,'d');
182 INSERT INTO t_com_in_8k VALUES (4,'d');
183 INSERT INTO t_dyn_in_8k VALUES (4,'d');
184
185 --echo #
186 --echo # Create an empty copy of each table using LIKE
187 --echo #
188 CREATE TABLE t_zip1k_in_1k_like LIKE t_zip1k_in_1k;
189 CREATE TABLE t_zip2k_in_2k_like LIKE t_zip2k_in_2k;
190 CREATE TABLE t_zip4k_in_4k_like LIKE t_zip4k_in_4k;
191 CREATE TABLE t_red_in_8k_like LIKE t_red_in_8k;
192 CREATE TABLE t_com_in_8k_like LIKE t_com_in_8k;
193 CREATE TABLE t_dyn_in_8k_like LIKE t_dyn_in_8k;
194
195 --echo #
196 --echo # Create a full copy of each table using AS
197 --echo #
198 CREATE TABLE t_zip1k_in_1k_as TABLESPACE=s_1k KEY_BLOCK_SIZE=1 AS (SELECT * FROM t_zip1k_in_1k);
199 CREATE TABLE t_zip2k_in_2k_as TABLESPACE=s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip2k_in_2k);
200 CREATE TABLE t_zip4k_in_4k_as TABLESPACE=s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip4k_in_4k);
201 CREATE TABLE t_red_in_8k_as TABLESPACE=s_8k ROW_FORMAT=redundant AS (SELECT * FROM t_red_in_8k);
202 CREATE TABLE t_com_in_8k_as TABLESPACE=s_8k ROW_FORMAT=compact AS (SELECT * FROM t_com_in_8k);
203 CREATE TABLE t_dyn_in_8k_as TABLESPACE=s_8k ROW_FORMAT=dynamic AS (SELECT * FROM t_dyn_in_8k);
204
205 --echo #
206 --echo # Create a copy of the 1k table, moving it to another tablespace with a different
207 --echo # FILE_BLOCK_SIZE, while changing the KEY_BLOCK_SIZE.
208 --echo #
209 CREATE TABLE t_zip2k_in_2k_from_1k TABLESPACE s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip1k_in_1k);
210 CREATE TABLE t_zip4k_in_4k_from_1k TABLESPACE s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip1k_in_1k);
211 CREATE TABLE t_red_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=redundant AS (SELECT * FROM t_zip1k_in_1k);
212 CREATE TABLE t_com_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=compact AS (SELECT * FROM t_zip1k_in_1k);
213 CREATE TABLE t_dyn_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=dynamic AS (SELECT * FROM t_zip1k_in_1k);
214
215 --source suite/innodb/include/show_i_s_tablespaces.inc
216 --source suite/innodb/include/show_i_s_tables.inc
217 --echo # Directory of MYSQLD_DATADIR/
218 --list_files $MYSQLD_DATADIR/ *.ibd
219 --echo # Directory of MYSQLD_DATADIR/test/
220 --list_files $MYSQLD_DATADIR/test/ *.ibd
221
222 --echo #
223 --echo # Restart the server and make sure that everything is OK.
224 --echo #
225 --source include/restart_mysqld.inc
226
227 --source suite/innodb/include/show_i_s_tablespaces.inc
228 --source suite/innodb/include/show_i_s_tables.inc
229 --echo # Directory of MYSQLD_DATADIR/
230 --list_files $MYSQLD_DATADIR/ *.ibd
231 --echo # Directory of MYSQLD_DATADIR/test/
232 --list_files $MYSQLD_DATADIR/test/ *.ibd
233
234 --echo #
235 --echo # Clean-up tables we no longer need
236 --echo #
237
238 DROP TABLE t_zip1k_in_1k_like;
239 DROP TABLE t_zip2k_in_2k_like;
240 DROP TABLE t_zip4k_in_4k_like;
241 DROP TABLE t_red_in_8k_like;
242 DROP TABLE t_com_in_8k_like;
243 DROP TABLE t_dyn_in_8k_like;
244 DROP TABLE t_zip1k_in_1k_as;
245 DROP TABLE t_zip2k_in_2k_as;
246 DROP TABLE t_zip4k_in_4k_as;
247 DROP TABLE t_red_in_8k_as;
248 DROP TABLE t_com_in_8k_as;
249 DROP TABLE t_dyn_in_8k_as;
250 DROP TABLE t_zip2k_in_2k_from_1k;
251 DROP TABLE t_zip4k_in_4k_from_1k;
252 DROP TABLE t_red_in_8k_from_1k;
253 DROP TABLE t_com_in_8k_from_1k;
254 DROP TABLE t_dyn_in_8k_from_1k;
255
256 CHECK TABLE t_zip1k_in_1k;
257 CHECK TABLE t_zip2k_in_2k;
258 CHECK TABLE t_zip4k_in_4k;
259 CHECK TABLE t_red_in_8k;
260 CHECK TABLE t_com_in_8k;
261 CHECK TABLE t_dyn_in_8k;
262
263 DROP TABLE t_zip1k_in_1k;
264 DROP TABLE t_zip2k_in_2k;
265 DROP TABLE t_zip4k_in_4k;
266 DROP TABLE t_red_in_8k;
267 DROP TABLE t_com_in_8k;
268 DROP TABLE t_dyn_in_8k;
269
270 SET SESSION innodb_strict_mode = ON;
271 SET GLOBAL innodb_file_per_table = ON;
272
273 --echo #
274 --echo # Create compressed tables explicitly as file_per_table tablespaces.
275 --echo #
276 CREATE TABLE t_zip1k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=1;
277 CREATE TABLE t_zip2k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=2;
278 CREATE TABLE t_zip4k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=4;
279 CREATE TABLE t_zip8k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=8;
280
281 --echo #
282 --echo # Create compressed tables in remote locations using file-per-table.
283 --echo #
284 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
285 eval CREATE TABLE t_zip1k_as_remote (a int, b text) KEY_BLOCK_SIZE=1 TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
286 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
287 eval CREATE TABLE t_zip2k_as_remote (a int, b text) KEY_BLOCK_SIZE=2 TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
288 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
289 eval CREATE TABLE t_zip4k_as_remote (a int, b text) KEY_BLOCK_SIZE=4 TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
290 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
291 eval CREATE TABLE t_zip8k_as_remote (a int, b text) KEY_BLOCK_SIZE=8 TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
292
293 --source suite/innodb/include/show_i_s_tablespaces.inc
294 --source suite/innodb/include/show_i_s_tables.inc
295 --echo # MYSQLD_DATADIR/
296 --list_files $MYSQLD_DATADIR/ *.ibd
297 --echo # MYSQLD_DATADIR/test/
298 --list_files $MYSQLD_DATADIR/test/ *.ibd
299
300 --echo #
301 --echo # These file_per_table tables cannot be moved to a general tablespace if the
302 --echo # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
303 --echo # not compressed.
304 --echo #
305 --error ER_ILLEGAL_HA_CREATE_OPTION
306 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_1k`;
307 SHOW WARNINGS;
308 --error ER_ILLEGAL_HA_CREATE_OPTION
309 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_1k`;
310 SHOW WARNINGS;
311 --error ER_ILLEGAL_HA_CREATE_OPTION
312 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_1k`;
313 SHOW WARNINGS;
314 --error ER_ILLEGAL_HA_CREATE_OPTION
315 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_1k`;
316 SHOW WARNINGS;
317 --error ER_ILLEGAL_HA_CREATE_OPTION
318 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_1k`;
319 SHOW WARNINGS;
320 --error ER_ILLEGAL_HA_CREATE_OPTION
321 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_1k`;
322 SHOW WARNINGS;
323
324 --error ER_ILLEGAL_HA_CREATE_OPTION
325 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_2k`;
326 SHOW WARNINGS;
327 --error ER_ILLEGAL_HA_CREATE_OPTION
328 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_2k`;
329 SHOW WARNINGS;
330 --error ER_ILLEGAL_HA_CREATE_OPTION
331 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_2k`;
332 SHOW WARNINGS;
333 --error ER_ILLEGAL_HA_CREATE_OPTION
334 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_2k`;
335 SHOW WARNINGS;
336 --error ER_ILLEGAL_HA_CREATE_OPTION
337 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_2k`;
338 SHOW WARNINGS;
339 --error ER_ILLEGAL_HA_CREATE_OPTION
340 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_2k`;
341 SHOW WARNINGS;
342
343 --error ER_ILLEGAL_HA_CREATE_OPTION
344 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_4k`;
345 SHOW WARNINGS;
346 --error ER_ILLEGAL_HA_CREATE_OPTION
347 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_4k`;
348 SHOW WARNINGS;
349 --error ER_ILLEGAL_HA_CREATE_OPTION
350 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_4k`;
351 SHOW WARNINGS;
352 --error ER_ILLEGAL_HA_CREATE_OPTION
353 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_4k`;
354 SHOW WARNINGS;
355 --error ER_ILLEGAL_HA_CREATE_OPTION
356 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_4k`;
357 SHOW WARNINGS;
358 --error ER_ILLEGAL_HA_CREATE_OPTION
359 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_4k`;
360 SHOW WARNINGS;
361
362 --error ER_ILLEGAL_HA_CREATE_OPTION
363 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_8k`;
364 SHOW WARNINGS;
365 --error ER_ILLEGAL_HA_CREATE_OPTION
366 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_8k`;
367 SHOW WARNINGS;
368 --error ER_ILLEGAL_HA_CREATE_OPTION
369 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_8k`;
370 SHOW WARNINGS;
371 --error ER_ILLEGAL_HA_CREATE_OPTION
372 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_8k`;
373 SHOW WARNINGS;
374 --error ER_ILLEGAL_HA_CREATE_OPTION
375 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_8k`;
376 SHOW WARNINGS;
377 --error ER_ILLEGAL_HA_CREATE_OPTION
378 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_8k`;
379 SHOW WARNINGS;
380
381 DROP TABLE `t_zip8k_as_remote`;
382
383 --echo #
384 --echo # These explicit file_per_table tables can be moved to a general tablespace
385 --echo # if FILE_BLOCK_SIZE matches KEY_BLOCK_SIZE and the tablespace is compressed.
386 --echo #
387 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_in_s_1k`;
388 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_in_s_2k`;
389 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_in_s_4k`;
390 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_remote_in_s_1k`;
391 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_remote_in_s_2k`;
392 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_remote_in_s_4k`;
393
394 --source suite/innodb/include/show_i_s_tablespaces.inc
395 --source suite/innodb/include/show_i_s_tables.inc
396 --echo # MYSQLD_DATADIR/
397 --list_files $MYSQLD_DATADIR/ *.ibd
398 --echo # MYSQLD_DATADIR/test/
399 --list_files $MYSQLD_DATADIR/test/ *.ibd
400
401 CHECK TABLE `t_zip1k_in_s_1k`;
402 CHECK TABLE `t_zip2k_in_s_2k`;
403 CHECK TABLE `t_zip4k_in_s_4k`;
404 CHECK TABLE `t_zip1k_remote_in_s_1k`;
405 CHECK TABLE `t_zip2k_remote_in_s_2k`;
406 CHECK TABLE `t_zip4k_remote_in_s_4k`;
407
408 --echo #
409 --echo # Tables in general tablespaces cannot be moved to another general tablespace if the
410 --echo # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
411 --echo # not compressed.
412 --echo #
413 --error ER_ILLEGAL_HA_CREATE_OPTION
414 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_1k`;
415 SHOW WARNINGS;
416 --error ER_ILLEGAL_HA_CREATE_OPTION
417 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_1k`;
418 SHOW WARNINGS;
419
420 --error ER_ILLEGAL_HA_CREATE_OPTION
421 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_2k`;
422 SHOW WARNINGS;
423 --error ER_ILLEGAL_HA_CREATE_OPTION
424 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_2k`;
425 SHOW WARNINGS;
426
427 --error ER_ILLEGAL_HA_CREATE_OPTION
428 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_4k`;
429 SHOW WARNINGS;
430 --error ER_ILLEGAL_HA_CREATE_OPTION
431 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_4k`;
432 SHOW WARNINGS;
433
434 --error ER_ILLEGAL_HA_CREATE_OPTION
435 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_8k`;
436 SHOW WARNINGS;
437 --error ER_ILLEGAL_HA_CREATE_OPTION
438 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_8k`;
439 SHOW WARNINGS;
440 --error ER_ILLEGAL_HA_CREATE_OPTION
441 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_8k`;
442 SHOW WARNINGS;
443
444
445 --echo #
446 --echo # Tables in a general tablespace can be moved to file_per_table locations.
447 --echo #
448 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip1k_to_file_per_table`;
449 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip2k_to_file_per_table`;
450 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip4k_to_file_per_table`;
451 --source suite/innodb/include/show_i_s_tablespaces.inc
452 --source suite/innodb/include/show_i_s_tables.inc
453
454 CHECK TABLE `t_zip1k_to_file_per_table`;
455 CHECK TABLE `t_zip2k_to_file_per_table`;
456 CHECK TABLE `t_zip4k_to_file_per_table`;
457 CHECK TABLE `t_zip8k_as_file_per_table`;
458
459 DROP TABLE `t_zip1k_to_file_per_table`;
460 DROP TABLE `t_zip2k_to_file_per_table`;
461 DROP TABLE `t_zip4k_to_file_per_table`;
462 DROP TABLE `t_zip8k_as_file_per_table`;
463
464 --echo #
465 --echo # Tables in a general tablespace cannot be moved to remote file_per_table locations
466 --echo # because MySQL ignores DATA DIRECTORY in ALTER TABLE.
467 --echo #
468 ALTER TABLE `t_zip1k_remote_in_s_1k` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_zip1k_to_file_per_table`;
469 ALTER TABLE `t_zip2k_remote_in_s_2k` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_zip2k_to_file_per_table`;
470 ALTER TABLE `t_zip4k_remote_in_s_4k` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_zip4k_to_file_per_table`;
471 --source suite/innodb/include/show_i_s_tablespaces.inc
472 --source suite/innodb/include/show_i_s_tables.inc
473
474 DROP TABLE `t_zip1k_to_file_per_table`;
475 DROP TABLE `t_zip2k_to_file_per_table`;
476 DROP TABLE `t_zip4k_to_file_per_table`;
477
478 call mtr.add_suppression("\\[ERROR\\].* '.*s_.k.ibd' read failed! - attempted to read 4 bytes, read only 0 bytes at offset 8226");
479 call mtr.add_suppression("\\[Warning\\].* Ignoring '.*s_.k.ibd' invalid tablespace ID in the header");
480
481 --echo #
482 --echo # Clean-up.
483 --echo #
484
485 DROP TABLESPACE s_1k;
486 DROP TABLESPACE s_2k;
487 DROP TABLESPACE s_4k;
488 DROP TABLESPACE s_8k;
489 --rmdir $MYSQL_TMP_DIR/test