Debian Codegraph @

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

raw
1 #
2 # WL#6205 - A series of tests to show the correct behavior for
3 # CREATE TABLESPACE and associated SQL statements for 16k page size.
4 #
5
6 --echo #
7 --echo # CREATE TABLESPACE related tests for 16k 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 CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
28 --error ER_ILLEGAL_HA_CREATE_OPTION
29 CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
30 SHOW WARNINGS;
31 --error ER_ILLEGAL_HA_CREATE_OPTION
32 CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
33 SHOW WARNINGS;
34 --source suite/innodb/include/show_i_s_tablespaces.inc
35
36 --echo #
37 --echo # Try to put a temporary table into a non-temporary compressed tablespace
38 --echo #
39 --error ER_ILLEGAL_HA_CREATE_OPTION
40 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
41 SHOW WARNINGS;
42
43 --echo #
44 --echo # Add tables to the 1K tablespace.
45 --echo #
46 CREATE TABLE t_zip1k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
47 --error ER_ILLEGAL_HA_CREATE_OPTION
48 CREATE TABLE t_zip2k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_1k;
49 SHOW WARNINGS;
50 --error ER_ILLEGAL_HA_CREATE_OPTION
51 CREATE TABLE t_zip4k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_1k;
52 SHOW WARNINGS;
53 --error ER_ILLEGAL_HA_CREATE_OPTION
54 CREATE TABLE t_zip8k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_1k;
55 SHOW WARNINGS;
56 --error ER_ILLEGAL_HA_CREATE_OPTION
57 CREATE TABLE t_zip16k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_1k;
58 SHOW WARNINGS;
59 --error ER_ILLEGAL_HA_CREATE_OPTION
60 CREATE TABLE t_zip32k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_1k;
61 SHOW WARNINGS;
62 --error ER_ILLEGAL_HA_CREATE_OPTION
63 CREATE TABLE t_red_in_1k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_1k;
64 SHOW WARNINGS;
65 --error ER_ILLEGAL_HA_CREATE_OPTION
66 CREATE TABLE t_cmp_in_1k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_1k;
67 SHOW WARNINGS;
68 --error ER_ILLEGAL_HA_CREATE_OPTION
69 CREATE TABLE t_dyn_in_1k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_1k;
70 SHOW WARNINGS;
71
72 --echo #
73 --echo # Add tables to the 2K tablespace.
74 --echo #
75 --error ER_ILLEGAL_HA_CREATE_OPTION
76 CREATE TABLE t_zip1k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_2k;
77 SHOW WARNINGS;
78 CREATE TABLE t_zip2k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_2k;
79 --error ER_ILLEGAL_HA_CREATE_OPTION
80 CREATE TABLE t_zip4k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_2k;
81 SHOW WARNINGS;
82 --error ER_ILLEGAL_HA_CREATE_OPTION
83 CREATE TABLE t_zip8k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_2k;
84 SHOW WARNINGS;
85 --error ER_ILLEGAL_HA_CREATE_OPTION
86 CREATE TABLE t_zip16k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_2k;
87 SHOW WARNINGS;
88 --error ER_ILLEGAL_HA_CREATE_OPTION
89 CREATE TABLE t_zip32k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_2k;
90 SHOW WARNINGS;
91 --error ER_ILLEGAL_HA_CREATE_OPTION
92 CREATE TABLE t_red_in_2k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_2k;
93 SHOW WARNINGS;
94 --error ER_ILLEGAL_HA_CREATE_OPTION
95 CREATE TABLE t_cmp_in_2k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_2k;
96 SHOW WARNINGS;
97 --error ER_ILLEGAL_HA_CREATE_OPTION
98 CREATE TABLE t_dyn_in_2k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_2k;
99 SHOW WARNINGS;
100
101 --echo #
102 --echo # Add tables to the 4K tablespace.
103 --echo #
104 --error ER_ILLEGAL_HA_CREATE_OPTION
105 CREATE TABLE t_zip1k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_4k;
106 SHOW WARNINGS;
107 --error ER_ILLEGAL_HA_CREATE_OPTION
108 CREATE TABLE t_zip2k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_4k;
109 SHOW WARNINGS;
110 CREATE TABLE t_zip4k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_4k;
111 --error ER_ILLEGAL_HA_CREATE_OPTION
112 CREATE TABLE t_zip8k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_4k;
113 SHOW WARNINGS;
114 --error ER_ILLEGAL_HA_CREATE_OPTION
115 CREATE TABLE t_zip16k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_4k;
116 SHOW WARNINGS;
117 --error ER_ILLEGAL_HA_CREATE_OPTION
118 CREATE TABLE t_zip32k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_4k;
119 SHOW WARNINGS;
120 --error ER_ILLEGAL_HA_CREATE_OPTION
121 CREATE TABLE t_red_in_4k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_4k;
122 SHOW WARNINGS;
123 --error ER_ILLEGAL_HA_CREATE_OPTION
124 CREATE TABLE t_cmp_in_4k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_4k;
125 SHOW WARNINGS;
126 --error ER_ILLEGAL_HA_CREATE_OPTION
127 CREATE TABLE t_dyn_in_4k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_4k;
128 SHOW WARNINGS;
129
130 --echo #
131 --echo # Add tables to the 8K tablespace.
132 --echo #
133 --error ER_ILLEGAL_HA_CREATE_OPTION
134 CREATE TABLE t_zip1k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_8k;
135 SHOW WARNINGS;
136 --error ER_ILLEGAL_HA_CREATE_OPTION
137 CREATE TABLE t_zip2k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_8k;
138 SHOW WARNINGS;
139 --error ER_ILLEGAL_HA_CREATE_OPTION
140 CREATE TABLE t_zip4k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_8k;
141 SHOW WARNINGS;
142 CREATE TABLE t_zip8k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_8k;
143 --error ER_ILLEGAL_HA_CREATE_OPTION
144 CREATE TABLE t_zip16k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_8k;
145 SHOW WARNINGS;
146 --error ER_ILLEGAL_HA_CREATE_OPTION
147 CREATE TABLE t_zip32k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_8k;
148 SHOW WARNINGS;
149 --error ER_ILLEGAL_HA_CREATE_OPTION
150 CREATE TABLE t_red_in_8k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_8k;
151 SHOW WARNINGS;
152 --error ER_ILLEGAL_HA_CREATE_OPTION
153 CREATE TABLE t_com_in_8k (a int, b text) ROW_FORMAT=compact TABLESPACE s_8k;
154 SHOW WARNINGS;
155 --error ER_ILLEGAL_HA_CREATE_OPTION
156 CREATE TABLE t_dyn_in_8k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_8k;
157 SHOW WARNINGS;
158
159 --echo #
160 --echo # Add tables to the 16K tablespace.
161 --echo #
162 --error ER_ILLEGAL_HA_CREATE_OPTION
163 CREATE TABLE t_zip1k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_16k;
164 SHOW WARNINGS;
165 --error ER_ILLEGAL_HA_CREATE_OPTION
166 CREATE TABLE t_zip2k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_16k;
167 SHOW WARNINGS;
168 --error ER_ILLEGAL_HA_CREATE_OPTION
169 CREATE TABLE t_zip4k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_16k;
170 SHOW WARNINGS;
171 --error ER_ILLEGAL_HA_CREATE_OPTION
172 CREATE TABLE t_zip8k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_16k;
173 SHOW WARNINGS;
174 --error ER_ILLEGAL_HA_CREATE_OPTION
175 CREATE TABLE t_zip16k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_16k;
176 SHOW WARNINGS;
177 --error ER_ILLEGAL_HA_CREATE_OPTION
178 CREATE TABLE t_zip32k_in_16k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_16k;
179 SHOW WARNINGS;
180 CREATE TABLE t_red_in_16k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_16k;
181 CREATE TABLE t_com_in_16k (a int, b text) ROW_FORMAT=compact TABLESPACE s_16k;
182 CREATE TABLE t_dyn_in_16k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_16k;
183
184 --echo # Add data to the existing Tables
185 INSERT INTO t_zip1k_in_1k VALUES (1,'a'),(2,'b'),(3,'c');
186 INSERT INTO t_zip2k_in_2k VALUES (1,'a'),(2,'b'),(3,'c');
187 INSERT INTO t_zip4k_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
188 INSERT INTO t_zip8k_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
189 INSERT INTO t_red_in_16k VALUES (1,'a'),(2,'b'),(3,'c');
190 INSERT INTO t_com_in_16k VALUES (1,'a'),(2,'b'),(3,'c');
191 INSERT INTO t_dyn_in_16k VALUES (1,'a'),(2,'b'),(3,'c');
192
193 --echo # Restart mysqld
194 --source include/restart_mysqld.inc
195
196 --echo #
197 --echo # Try to drop these tablespaces which are not empty
198 --echo #
199 --error ER_TABLESPACE_IS_NOT_EMPTY
200 DROP TABLESPACE s_1k;
201 --error ER_TABLESPACE_IS_NOT_EMPTY
202 DROP TABLESPACE s_2k;
203 --error ER_TABLESPACE_IS_NOT_EMPTY
204 DROP TABLESPACE s_4k;
205 --error ER_TABLESPACE_IS_NOT_EMPTY
206 DROP TABLESPACE s_8k;
207 --error ER_TABLESPACE_IS_NOT_EMPTY
208 DROP TABLESPACE s_16k;
209
210 --echo #
211 --echo # Add more data to the existing Tables
212 --echo #
213 INSERT INTO t_zip1k_in_1k VALUES (4,'d');
214 INSERT INTO t_zip2k_in_2k VALUES (4,'d');
215 INSERT INTO t_zip4k_in_4k VALUES (4,'d');
216 INSERT INTO t_zip8k_in_8k VALUES (4,'d');
217 INSERT INTO t_red_in_16k VALUES (4,'d');
218 INSERT INTO t_com_in_16k VALUES (4,'d');
219 INSERT INTO t_dyn_in_16k VALUES (4,'d');
220
221 --echo #
222 --echo # Create an empty copy of each table using LIKE
223 --echo #
224 CREATE TABLE t_zip1k_in_1k_like LIKE t_zip1k_in_1k;
225 CREATE TABLE t_zip2k_in_2k_like LIKE t_zip2k_in_2k;
226 CREATE TABLE t_zip4k_in_4k_like LIKE t_zip4k_in_4k;
227 CREATE TABLE t_zip8k_in_8k_like LIKE t_zip8k_in_8k;
228 CREATE TABLE t_red_in_16k_like LIKE t_red_in_16k;
229 CREATE TABLE t_com_in_16k_like LIKE t_com_in_16k;
230 CREATE TABLE t_dyn_in_16k_like LIKE t_dyn_in_16k;
231
232 --echo #
233 --echo # Create a full copy of each table using AS
234 --echo #
235 CREATE TABLE t_zip1k_in_1k_as TABLESPACE=s_1k KEY_BLOCK_SIZE=1 AS (SELECT * FROM t_zip1k_in_1k);
236 CREATE TABLE t_zip2k_in_2k_as TABLESPACE=s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip2k_in_2k);
237 CREATE TABLE t_zip4k_in_4k_as TABLESPACE=s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip4k_in_4k);
238 CREATE TABLE t_zip8k_in_8k_as TABLESPACE=s_8k KEY_BLOCK_SIZE=8 AS (SELECT * FROM t_zip8k_in_8k);
239 CREATE TABLE t_red_in_16k_as TABLESPACE=s_16k ROW_FORMAT=redundant AS (SELECT * FROM t_red_in_16k);
240 CREATE TABLE t_com_in_16k_as TABLESPACE=s_16k ROW_FORMAT=compact AS (SELECT * FROM t_com_in_16k);
241 CREATE TABLE t_dyn_in_16k_as TABLESPACE=s_16k ROW_FORMAT=dynamic AS (SELECT * FROM t_dyn_in_16k);
242
243 --echo #
244 --echo # Create a copy of the 1k table, moving it to another tablespace with a different
245 --echo # FILE_BLOCK_SIZE, while changing the KEY_BLOCK_SIZE.
246 --echo #
247 CREATE TABLE t_zip2k_in_2k_from_1k TABLESPACE s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip1k_in_1k);
248 CREATE TABLE t_zip4k_in_4k_from_1k TABLESPACE s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip1k_in_1k);
249 CREATE TABLE t_zip8k_in_8k_from_1k TABLESPACE s_8k KEY_BLOCK_SIZE=8 AS (SELECT * FROM t_zip1k_in_1k);
250 CREATE TABLE t_red_in_16k_from_1k TABLESPACE s_16k ROW_FORMAT=redundant AS (SELECT * FROM t_zip1k_in_1k);
251 CREATE TABLE t_com_in_16k_from_1k TABLESPACE s_16k ROW_FORMAT=compact AS (SELECT * FROM t_zip1k_in_1k);
252 CREATE TABLE t_dyn_in_16k_from_1k TABLESPACE s_16k ROW_FORMAT=dynamic AS (SELECT * FROM t_zip1k_in_1k);
253
254 --echo #
255 --echo # Create a tablespace that will be deleted while the engine is not
256 --echo # running to show that it can still be dropped.
257 --echo #
258 CREATE TABLESPACE s_missing ADD DATAFILE 'delete_me.ibd';
259 CREATE TABLE t_missing (a int) TABLESPACE=s_missing;
260
261 --source suite/innodb/include/show_i_s_tablespaces.inc
262 --source suite/innodb/include/show_i_s_tables.inc
263 --echo # Directory of MYSQLD_DATADIR/
264 --list_files $MYSQLD_DATADIR/ *.ibd
265 --echo # Directory of MYSQLD_DATADIR/test/
266 --list_files $MYSQLD_DATADIR/test/ *.ibd
267
268 --echo #
269 --echo # Stop the server.
270 --source include/shutdown_mysqld.inc
271
272 --echo # Delete a general tablespace datafile.
273 --remove_file $MYSQLD_DATADIR/delete_me.ibd
274 --source include/start_mysqld.inc
275
276 --echo # Make sure that the missing tablespace can be dropped and
277 --echo # everything else is OK.
278 --echo #
279
280 --source suite/innodb/include/show_i_s_tablespaces.inc
281 --source suite/innodb/include/show_i_s_tables.inc
282 --echo # Directory of MYSQLD_DATADIR/
283 --list_files $MYSQLD_DATADIR/ *.ibd
284 --echo # Directory of MYSQLD_DATADIR/test/
285 --list_files $MYSQLD_DATADIR/test/ *.ibd
286
287 --echo #
288 --echo # Clean-up tables we no longer need
289 --echo #
290
291 --error ER_TABLESPACE_IS_NOT_EMPTY
292 DROP TABLESPACE s_missing;
293 DROP TABLE t_missing;
294 DROP TABLESPACE s_missing;
295
296 DROP TABLE t_zip1k_in_1k_like;
297 DROP TABLE t_zip2k_in_2k_like;
298 DROP TABLE t_zip4k_in_4k_like;
299 DROP TABLE t_zip8k_in_8k_like;
300 DROP TABLE t_red_in_16k_like;
301 DROP TABLE t_com_in_16k_like;
302 DROP TABLE t_dyn_in_16k_like;
303 DROP TABLE t_zip1k_in_1k_as;
304 DROP TABLE t_zip2k_in_2k_as;
305 DROP TABLE t_zip4k_in_4k_as;
306 DROP TABLE t_zip8k_in_8k_as;
307 DROP TABLE t_red_in_16k_as;
308 DROP TABLE t_com_in_16k_as;
309 DROP TABLE t_dyn_in_16k_as;
310 DROP TABLE t_zip2k_in_2k_from_1k;
311 DROP TABLE t_zip4k_in_4k_from_1k;
312 DROP TABLE t_zip8k_in_8k_from_1k;
313 DROP TABLE t_red_in_16k_from_1k;
314 DROP TABLE t_com_in_16k_from_1k;
315 DROP TABLE t_dyn_in_16k_from_1k;
316
317 CHECK TABLE t_zip1k_in_1k;
318 CHECK TABLE t_zip2k_in_2k;
319 CHECK TABLE t_zip4k_in_4k;
320 CHECK TABLE t_zip8k_in_8k;
321 CHECK TABLE t_red_in_16k;
322 CHECK TABLE t_com_in_16k;
323 CHECK TABLE t_dyn_in_16k;
324
325 DROP TABLE t_zip1k_in_1k;
326 DROP TABLE t_zip2k_in_2k;
327 DROP TABLE t_zip4k_in_4k;
328 DROP TABLE t_zip8k_in_8k;
329 DROP TABLE t_red_in_16k;
330 DROP TABLE t_com_in_16k;
331 DROP TABLE t_dyn_in_16k;
332
333 --echo #
334 --echo # Create compressed tables explicitly as file_per_table tablespaces.
335 --echo #
336 CREATE TABLE t_zip1k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=1;
337 CREATE TABLE t_zip2k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=2;
338 CREATE TABLE t_zip4k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=4;
339 CREATE TABLE t_zip8k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=8;
340 CREATE TABLE t_zip16k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=16;
341
342 --echo #
343 --echo # Create compressed tables in remote locations using file-per-table.
344 --echo #
345 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
346 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';
347 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
348 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';
349 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
350 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';
351 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
352 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';
353 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
354 eval CREATE TABLE t_zip16k_as_remote (a int, b text) KEY_BLOCK_SIZE=16 TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
355
356 --source suite/innodb/include/show_i_s_tablespaces.inc
357 --source suite/innodb/include/show_i_s_tables.inc
358 --echo # MYSQLD_DATADIR/
359 --list_files $MYSQLD_DATADIR/ *.ibd
360 --echo # MYSQLD_DATADIR/test/
361 --list_files $MYSQLD_DATADIR/test/ *.ibd
362
363 --echo #
364 --echo # These file_per_table tables cannot be moved to a general tablespace if the
365 --echo # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
366 --echo # not compressed.
367 --echo #
368 --error ER_ILLEGAL_HA_CREATE_OPTION
369 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_1k`;
370 SHOW WARNINGS;
371 --error ER_ILLEGAL_HA_CREATE_OPTION
372 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_1k`;
373 SHOW WARNINGS;
374 --error ER_ILLEGAL_HA_CREATE_OPTION
375 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_1k`;
376 SHOW WARNINGS;
377 --error ER_ILLEGAL_HA_CREATE_OPTION
378 ALTER TABLE `t_zip16k_as_file_per_table` TABLESPACE=`s_1k`;
379 SHOW WARNINGS;
380 --error ER_ILLEGAL_HA_CREATE_OPTION
381 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_1k`;
382 SHOW WARNINGS;
383 --error ER_ILLEGAL_HA_CREATE_OPTION
384 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_1k`;
385 SHOW WARNINGS;
386 --error ER_ILLEGAL_HA_CREATE_OPTION
387 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_1k`;
388 SHOW WARNINGS;
389 --error ER_ILLEGAL_HA_CREATE_OPTION
390 ALTER TABLE `t_zip16k_as_remote` TABLESPACE=`s_1k`;
391 SHOW WARNINGS;
392
393 --error ER_ILLEGAL_HA_CREATE_OPTION
394 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_2k`;
395 SHOW WARNINGS;
396 --error ER_ILLEGAL_HA_CREATE_OPTION
397 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_2k`;
398 SHOW WARNINGS;
399 --error ER_ILLEGAL_HA_CREATE_OPTION
400 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_2k`;
401 SHOW WARNINGS;
402 --error ER_ILLEGAL_HA_CREATE_OPTION
403 ALTER TABLE `t_zip16k_as_file_per_table` TABLESPACE=`s_2k`;
404 SHOW WARNINGS;
405 --error ER_ILLEGAL_HA_CREATE_OPTION
406 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_2k`;
407 SHOW WARNINGS;
408 --error ER_ILLEGAL_HA_CREATE_OPTION
409 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_2k`;
410 SHOW WARNINGS;
411 --error ER_ILLEGAL_HA_CREATE_OPTION
412 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_2k`;
413 SHOW WARNINGS;
414 --error ER_ILLEGAL_HA_CREATE_OPTION
415 ALTER TABLE `t_zip16k_as_remote` TABLESPACE=`s_2k`;
416 SHOW WARNINGS;
417
418 --error ER_ILLEGAL_HA_CREATE_OPTION
419 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_4k`;
420 SHOW WARNINGS;
421 --error ER_ILLEGAL_HA_CREATE_OPTION
422 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_4k`;
423 SHOW WARNINGS;
424 --error ER_ILLEGAL_HA_CREATE_OPTION
425 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_4k`;
426 SHOW WARNINGS;
427 --error ER_ILLEGAL_HA_CREATE_OPTION
428 ALTER TABLE `t_zip16k_as_file_per_table` TABLESPACE=`s_4k`;
429 SHOW WARNINGS;
430 --error ER_ILLEGAL_HA_CREATE_OPTION
431 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_4k`;
432 SHOW WARNINGS;
433 --error ER_ILLEGAL_HA_CREATE_OPTION
434 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_4k`;
435 SHOW WARNINGS;
436 --error ER_ILLEGAL_HA_CREATE_OPTION
437 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_4k`;
438 SHOW WARNINGS;
439 --error ER_ILLEGAL_HA_CREATE_OPTION
440 ALTER TABLE `t_zip16k_as_remote` TABLESPACE=`s_4k`;
441 SHOW WARNINGS;
442
443 --error ER_ILLEGAL_HA_CREATE_OPTION
444 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_8k`;
445 SHOW WARNINGS;
446 --error ER_ILLEGAL_HA_CREATE_OPTION
447 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_8k`;
448 SHOW WARNINGS;
449 --error ER_ILLEGAL_HA_CREATE_OPTION
450 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_8k`;
451 SHOW WARNINGS;
452 --error ER_ILLEGAL_HA_CREATE_OPTION
453 ALTER TABLE `t_zip16k_as_file_per_table` TABLESPACE=`s_8k`;
454 SHOW WARNINGS;
455 --error ER_ILLEGAL_HA_CREATE_OPTION
456 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_8k`;
457 SHOW WARNINGS;
458 --error ER_ILLEGAL_HA_CREATE_OPTION
459 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_8k`;
460 SHOW WARNINGS;
461 --error ER_ILLEGAL_HA_CREATE_OPTION
462 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_8k`;
463 SHOW WARNINGS;
464 --error ER_ILLEGAL_HA_CREATE_OPTION
465 ALTER TABLE `t_zip16k_as_remote` TABLESPACE=`s_8k`;
466 SHOW WARNINGS;
467
468 --error ER_ILLEGAL_HA_CREATE_OPTION
469 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_16k`;
470 SHOW WARNINGS;
471 --error ER_ILLEGAL_HA_CREATE_OPTION
472 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_16k`;
473 SHOW WARNINGS;
474 --error ER_ILLEGAL_HA_CREATE_OPTION
475 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_16k`;
476 SHOW WARNINGS;
477 --error ER_ILLEGAL_HA_CREATE_OPTION
478 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_16k`;
479 SHOW WARNINGS;
480 --error ER_ILLEGAL_HA_CREATE_OPTION
481 ALTER TABLE `t_zip16k_as_file_per_table` TABLESPACE=`s_16k`;
482 SHOW WARNINGS;
483 --error ER_ILLEGAL_HA_CREATE_OPTION
484 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_16k`;
485 SHOW WARNINGS;
486 --error ER_ILLEGAL_HA_CREATE_OPTION
487 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_16k`;
488 SHOW WARNINGS;
489 --error ER_ILLEGAL_HA_CREATE_OPTION
490 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_16k`;
491 SHOW WARNINGS;
492 --error ER_ILLEGAL_HA_CREATE_OPTION
493 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_16k`;
494 SHOW WARNINGS;
495 --error ER_ILLEGAL_HA_CREATE_OPTION
496 ALTER TABLE `t_zip16k_as_remote` TABLESPACE=`s_16k`;
497 SHOW WARNINGS;
498
499 DROP TABLE `t_zip16k_as_remote`;
500
501 --echo #
502 --echo # These explicit file_per_table tables can be moved to a general tablespace
503 --echo # if FILE_BLOCK_SIZE matches KEY_BLOCK_SIZE and the tablespace is compressed.
504 --echo #
505 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_in_s_1k`;
506 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_in_s_2k`;
507 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_in_s_4k`;
508 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_8k`, RENAME TO `t_zip8k_in_s_8k`;
509 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_remote_in_s_1k`;
510 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_remote_in_s_2k`;
511 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_remote_in_s_4k`;
512 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_8k`, RENAME TO `t_zip8k_remote_in_s_8k`;
513
514 --source suite/innodb/include/show_i_s_tablespaces.inc
515 --source suite/innodb/include/show_i_s_tables.inc
516 --echo # MYSQLD_DATADIR/
517 --list_files $MYSQLD_DATADIR/ *.ibd
518 --echo # MYSQLD_DATADIR/test/
519 --list_files $MYSQLD_DATADIR/test/ *.ibd
520
521 CHECK TABLE `t_zip1k_in_s_1k`;
522 CHECK TABLE `t_zip2k_in_s_2k`;
523 CHECK TABLE `t_zip4k_in_s_4k`;
524 CHECK TABLE `t_zip8k_in_s_8k`;
525 CHECK TABLE `t_zip1k_remote_in_s_1k`;
526 CHECK TABLE `t_zip2k_remote_in_s_2k`;
527 CHECK TABLE `t_zip4k_remote_in_s_4k`;
528 CHECK TABLE `t_zip8k_remote_in_s_8k`;
529
530 --echo #
531 --echo # Tables in general tablespaces cannot be moved to another general tablespace if the
532 --echo # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
533 --echo # not compressed.
534 --echo #
535 --error ER_ILLEGAL_HA_CREATE_OPTION
536 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_1k`;
537 SHOW WARNINGS;
538 --error ER_ILLEGAL_HA_CREATE_OPTION
539 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_1k`;
540 SHOW WARNINGS;
541 --error ER_ILLEGAL_HA_CREATE_OPTION
542 ALTER TABLE `t_zip8k_in_s_8k` TABLESPACE=`s_1k`;
543 SHOW WARNINGS;
544
545 --error ER_ILLEGAL_HA_CREATE_OPTION
546 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_2k`;
547 SHOW WARNINGS;
548 --error ER_ILLEGAL_HA_CREATE_OPTION
549 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_2k`;
550 SHOW WARNINGS;
551 --error ER_ILLEGAL_HA_CREATE_OPTION
552 ALTER TABLE `t_zip8k_in_s_8k` TABLESPACE=`s_2k`;
553 SHOW WARNINGS;
554
555 --error ER_ILLEGAL_HA_CREATE_OPTION
556 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_4k`;
557 SHOW WARNINGS;
558 --error ER_ILLEGAL_HA_CREATE_OPTION
559 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_4k`;
560 SHOW WARNINGS;
561 --error ER_ILLEGAL_HA_CREATE_OPTION
562 ALTER TABLE `t_zip8k_in_s_8k` TABLESPACE=`s_4k`;
563 SHOW WARNINGS;
564
565 --error ER_ILLEGAL_HA_CREATE_OPTION
566 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_8k`;
567 SHOW WARNINGS;
568 --error ER_ILLEGAL_HA_CREATE_OPTION
569 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_8k`;
570 SHOW WARNINGS;
571 --error ER_ILLEGAL_HA_CREATE_OPTION
572 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_8k`;
573 SHOW WARNINGS;
574
575 --error ER_ILLEGAL_HA_CREATE_OPTION
576 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_16k`;
577 SHOW WARNINGS;
578 --error ER_ILLEGAL_HA_CREATE_OPTION
579 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_16k`;
580 SHOW WARNINGS;
581 --error ER_ILLEGAL_HA_CREATE_OPTION
582 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_16k`;
583 SHOW WARNINGS;
584 --error ER_ILLEGAL_HA_CREATE_OPTION
585 ALTER TABLE `t_zip8k_in_s_8k` TABLESPACE=`s_16k`;
586 SHOW WARNINGS;
587
588 --echo #
589 --echo # Tables in a general tablespace can be moved to file_per_table locations.
590 --echo #
591 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip1k_to_file_per_table`;
592 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip2k_to_file_per_table`;
593 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip4k_to_file_per_table`;
594 ALTER TABLE `t_zip8k_in_s_8k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip8k_to_file_per_table`;
595 --source suite/innodb/include/show_i_s_tablespaces.inc
596 --source suite/innodb/include/show_i_s_tables.inc
597
598 CHECK TABLE `t_zip1k_to_file_per_table`;
599 CHECK TABLE `t_zip2k_to_file_per_table`;
600 CHECK TABLE `t_zip4k_to_file_per_table`;
601 CHECK TABLE `t_zip8k_to_file_per_table`;
602 CHECK TABLE `t_zip16k_as_file_per_table`;
603
604 DROP TABLE `t_zip1k_to_file_per_table`;
605 DROP TABLE `t_zip2k_to_file_per_table`;
606 DROP TABLE `t_zip4k_to_file_per_table`;
607 DROP TABLE `t_zip8k_to_file_per_table`;
608 DROP TABLE `t_zip16k_as_file_per_table`;
609
610 --echo #
611 --echo # Tables in a general tablespace cannot be moved to remote file_per_table locations
612 --echo # because MySQL ignores DATA DIRECTORY in ALTER TABLE.
613 --echo #
614 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`;
615 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`;
616 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`;
617 ALTER TABLE `t_zip8k_remote_in_s_8k` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_zip8k_to_file_per_table`;
618 --source suite/innodb/include/show_i_s_tablespaces.inc
619 --source suite/innodb/include/show_i_s_tables.inc
620
621 DROP TABLE `t_zip1k_to_file_per_table`;
622 DROP TABLE `t_zip2k_to_file_per_table`;
623 DROP TABLE `t_zip4k_to_file_per_table`;
624 DROP TABLE `t_zip8k_to_file_per_table`;
625
626 --echo #
627 --echo # Clean-up.
628 --echo #
629
630 DROP TABLESPACE s_1k;
631 DROP TABLESPACE s_2k;
632 DROP TABLESPACE s_4k;
633 DROP TABLESPACE s_8k;
634 DROP TABLESPACE s_16k;
635 --rmdir $MYSQL_TMP_DIR/test
636
637 --disable_query_log
638 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number .* in a file operation");
639 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified");
640 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only");
641 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot delete tablespace .* because it is not found in the tablespace memory cache");
642 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Failed to find tablespace for table `test`.`t_missing` in the cache. Attempting to load the tablespace with space id .*");
643 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace .*, name 's_missing', file 'delete_me.ibd' is missing!");
644 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Ignoring tablespace `s_missing` because it could not be opened");
645 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Unable to open tablespace .* \\(flags=.*, filename=delete_me.ibd\\);");
646 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Trying to access missing tablespace .*");
647 call mtr.add_suppression("\\[ERROR\\].* '.*s_.k.ibd' read failed! - attempted to read 4 bytes, read only 0 bytes at offset 16418");
648 call mtr.add_suppression("\\[Warning\\].* Ignoring '.*s_.k.ibd' invalid tablespace ID in the header");
649 --enable_query_log