Debian Codegraph @

mysql-test/suite/innodb/r/create_tablespace_8k.result

raw
1 #
2 # CREATE TABLESPACE related tests for 8k page sizes.
3 #
4 SET DEFAULT_STORAGE_ENGINE=InnoDB;
5 # Strict-mode has no effect on CREATE TABLESPACE. But it does affect
6 # whether an invalid KEY_BLOCK_SIZE is rejected or adjusted.
7 SHOW VARIABLES LIKE 'innodb_strict_mode';
8 Variable_name Value
9 innodb_strict_mode ON
10 SHOW VARIABLES LIKE 'innodb_file_per_table';
11 Variable_name Value
12 innodb_file_per_table ON
13 #
14 # Create a tablespace with compressed page sizes that can match
15 # innodb-page-size.
16 #
17 CREATE TABLESPACE s_1k ADD DATAFILE 's_1k.ibd' FILE_BLOCK_SIZE=1k;
18 CREATE TABLESPACE s_2k ADD DATAFILE 's_2k.ibd' FILE_BLOCK_SIZE=2k;
19 CREATE TABLESPACE s_4k ADD DATAFILE 's_4k.ibd' FILE_BLOCK_SIZE=4k;
20 CREATE TABLESPACE s_8k ADD DATAFILE 's_8k.ibd' FILE_BLOCK_SIZE=8k;
21 CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
22 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=8192.
23 SHOW WARNINGS;
24 Level Code Message
25 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=8192.
26 Error 1528 Failed to create TABLESPACE s_16k
27 Error 1031 Table storage engine for 's_16k' doesn't have this option
28 CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
29 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=8192.
30 SHOW WARNINGS;
31 Level Code Message
32 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=8192.
33 Error 1528 Failed to create TABLESPACE s_32k
34 Error 1031 Table storage engine for 's_32k' doesn't have this option
35 CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
36 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=8192.
37 SHOW WARNINGS;
38 Level Code Message
39 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=8192.
40 Error 1528 Failed to create TABLESPACE s_64k
41 Error 1031 Table storage engine for 's_64k' doesn't have this option
42 === information_schema.innodb_tablespaces and innodb_datafiles ===
43 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
44 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
45 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
46 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
47 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
48 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
49 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
50 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
51 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
52 === information_schema.files ===
53 Space_Name File_Type Engine Status Tablespace_Name Path
54 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
55 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
56 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
57 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
58 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
59 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
60 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
61 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
62 #
63 # Try to put a temporary table into a non-temporary compressed tablespace
64 #
65 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
66 ERROR HY000: InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
67 SHOW WARNINGS;
68 Level Code Message
69 Error 1478 InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
70 Error 1031 Table storage engine for 't_temp_zip' doesn't have this option
71 #
72 # Add tables to the 1K tablespace.
73 #
74 CREATE TABLE t_zip1k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
75 CREATE TABLE t_zip2k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_1k;
76 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
77 SHOW WARNINGS;
78 Level Code Message
79 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
80 Error 1031 Table storage engine for 't_zip2k_in_1k' doesn't have this option
81 CREATE TABLE t_zip4k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_1k;
82 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
83 SHOW WARNINGS;
84 Level Code Message
85 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
86 Error 1031 Table storage engine for 't_zip4k_in_1k' doesn't have this option
87 CREATE TABLE t_zip8k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_1k;
88 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
89 SHOW WARNINGS;
90 Level Code Message
91 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
92 Error 1031 Table storage engine for 't_zip8k_in_1k' doesn't have this option
93 CREATE TABLE t_zip16k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_1k;
94 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
95 SHOW WARNINGS;
96 Level Code Message
97 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
98 Error 1031 Table storage engine for 't_zip16k_in_1k' doesn't have this option
99 CREATE TABLE t_zip32k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_1k;
100 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
101 SHOW WARNINGS;
102 Level Code Message
103 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
104 Error 1031 Table storage engine for 't_zip32k_in_1k' doesn't have this option
105 CREATE TABLE t_red_in_1k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_1k;
106 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
107 SHOW WARNINGS;
108 Level Code Message
109 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
110 Error 1031 Table storage engine for 't_red_in_1k' doesn't have this option
111 CREATE TABLE t_cmp_in_1k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_1k;
112 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
113 SHOW WARNINGS;
114 Level Code Message
115 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
116 Error 1031 Table storage engine for 't_cmp_in_1k' doesn't have this option
117 CREATE TABLE t_dyn_in_1k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_1k;
118 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
119 SHOW WARNINGS;
120 Level Code Message
121 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
122 Error 1031 Table storage engine for 't_dyn_in_1k' doesn't have this option
123 #
124 # Add tables to the 2K tablespace.
125 #
126 CREATE TABLE t_zip1k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_2k;
127 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
128 SHOW WARNINGS;
129 Level Code Message
130 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
131 Error 1031 Table storage engine for 't_zip1k_in_2k' doesn't have this option
132 CREATE TABLE t_zip2k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_2k;
133 CREATE TABLE t_zip4k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_2k;
134 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
135 SHOW WARNINGS;
136 Level Code Message
137 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
138 Error 1031 Table storage engine for 't_zip4k_in_2k' doesn't have this option
139 CREATE TABLE t_zip8k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_2k;
140 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
141 SHOW WARNINGS;
142 Level Code Message
143 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
144 Error 1031 Table storage engine for 't_zip8k_in_2k' doesn't have this option
145 CREATE TABLE t_zip16k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_2k;
146 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
147 SHOW WARNINGS;
148 Level Code Message
149 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
150 Error 1031 Table storage engine for 't_zip16k_in_2k' doesn't have this option
151 CREATE TABLE t_zip32k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_2k;
152 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
153 SHOW WARNINGS;
154 Level Code Message
155 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
156 Error 1031 Table storage engine for 't_zip32k_in_2k' doesn't have this option
157 CREATE TABLE t_red_in_2k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_2k;
158 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
159 SHOW WARNINGS;
160 Level Code Message
161 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
162 Error 1031 Table storage engine for 't_red_in_2k' doesn't have this option
163 CREATE TABLE t_cmp_in_2k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_2k;
164 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
165 SHOW WARNINGS;
166 Level Code Message
167 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
168 Error 1031 Table storage engine for 't_cmp_in_2k' doesn't have this option
169 CREATE TABLE t_dyn_in_2k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_2k;
170 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
171 SHOW WARNINGS;
172 Level Code Message
173 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
174 Error 1031 Table storage engine for 't_dyn_in_2k' doesn't have this option
175 #
176 # Add tables to the 4K tablespace.
177 #
178 CREATE TABLE t_zip1k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_4k;
179 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
180 SHOW WARNINGS;
181 Level Code Message
182 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
183 Error 1031 Table storage engine for 't_zip1k_in_4k' doesn't have this option
184 CREATE TABLE t_zip2k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_4k;
185 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
186 SHOW WARNINGS;
187 Level Code Message
188 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
189 Error 1031 Table storage engine for 't_zip2k_in_4k' doesn't have this option
190 CREATE TABLE t_zip4k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_4k;
191 CREATE TABLE t_zip8k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_4k;
192 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
193 SHOW WARNINGS;
194 Level Code Message
195 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
196 Error 1031 Table storage engine for 't_zip8k_in_4k' doesn't have this option
197 CREATE TABLE t_zip16k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_4k;
198 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 16384
199 SHOW WARNINGS;
200 Level Code Message
201 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 16384
202 Error 1031 Table storage engine for 't_zip16k_in_4k' doesn't have this option
203 CREATE TABLE t_zip32k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_4k;
204 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 32768
205 SHOW WARNINGS;
206 Level Code Message
207 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 32768
208 Error 1031 Table storage engine for 't_zip32k_in_4k' doesn't have this option
209 CREATE TABLE t_red_in_4k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_4k;
210 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
211 SHOW WARNINGS;
212 Level Code Message
213 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
214 Error 1031 Table storage engine for 't_red_in_4k' doesn't have this option
215 CREATE TABLE t_cmp_in_4k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_4k;
216 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
217 SHOW WARNINGS;
218 Level Code Message
219 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
220 Error 1031 Table storage engine for 't_cmp_in_4k' doesn't have this option
221 CREATE TABLE t_dyn_in_4k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_4k;
222 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
223 SHOW WARNINGS;
224 Level Code Message
225 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
226 Error 1031 Table storage engine for 't_dyn_in_4k' doesn't have this option
227 #
228 # Add tables to the 8K tablespace.
229 #
230 CREATE TABLE t_zip1k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_8k;
231 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
232 SHOW WARNINGS;
233 Level Code Message
234 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
235 Error 1031 Table storage engine for 't_zip1k_in_8k' doesn't have this option
236 CREATE TABLE t_zip2k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_8k;
237 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
238 SHOW WARNINGS;
239 Level Code Message
240 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
241 Error 1031 Table storage engine for 't_zip2k_in_8k' doesn't have this option
242 CREATE TABLE t_zip4k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_8k;
243 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
244 SHOW WARNINGS;
245 Level Code Message
246 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
247 Error 1031 Table storage engine for 't_zip4k_in_8k' doesn't have this option
248 CREATE TABLE t_zip8k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_8k;
249 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
250 SHOW WARNINGS;
251 Level Code Message
252 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
253 Error 1031 Table storage engine for 't_zip8k_in_8k' doesn't have this option
254 CREATE TABLE t_zip16k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_8k;
255 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
256 SHOW WARNINGS;
257 Level Code Message
258 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
259 Error 1031 Table storage engine for 't_zip16k_in_8k' doesn't have this option
260 CREATE TABLE t_zip32k_in_8k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_8k;
261 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
262 SHOW WARNINGS;
263 Level Code Message
264 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
265 Error 1031 Table storage engine for 't_zip32k_in_8k' doesn't have this option
266 CREATE TABLE t_red_in_8k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_8k;
267 CREATE TABLE t_com_in_8k (a int, b text) ROW_FORMAT=compact TABLESPACE s_8k;
268 CREATE TABLE t_dyn_in_8k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_8k;
269 # Add data to the existing Tables
270 INSERT INTO t_zip1k_in_1k VALUES (1,'a'),(2,'b'),(3,'c');
271 INSERT INTO t_zip2k_in_2k VALUES (1,'a'),(2,'b'),(3,'c');
272 INSERT INTO t_zip4k_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
273 INSERT INTO t_red_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
274 INSERT INTO t_com_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
275 INSERT INTO t_dyn_in_8k VALUES (1,'a'),(2,'b'),(3,'c');
276 # Restart mysqld
277 # restart
278 #
279 # Try to drop a tablespace which is not empty
280 #
281 DROP TABLESPACE s_8k;
282 ERROR HY000: Tablespace `s_8k` is not empty.
283 #
284 # Add more data to the existing Tables
285 #
286 INSERT INTO t_zip1k_in_1k VALUES (4,'d');
287 INSERT INTO t_zip2k_in_2k VALUES (4,'d');
288 INSERT INTO t_zip4k_in_4k VALUES (4,'d');
289 INSERT INTO t_red_in_8k VALUES (4,'d');
290 INSERT INTO t_com_in_8k VALUES (4,'d');
291 INSERT INTO t_dyn_in_8k VALUES (4,'d');
292 #
293 # Create an empty copy of each table using LIKE
294 #
295 CREATE TABLE t_zip1k_in_1k_like LIKE t_zip1k_in_1k;
296 CREATE TABLE t_zip2k_in_2k_like LIKE t_zip2k_in_2k;
297 CREATE TABLE t_zip4k_in_4k_like LIKE t_zip4k_in_4k;
298 CREATE TABLE t_red_in_8k_like LIKE t_red_in_8k;
299 CREATE TABLE t_com_in_8k_like LIKE t_com_in_8k;
300 CREATE TABLE t_dyn_in_8k_like LIKE t_dyn_in_8k;
301 #
302 # Create a full copy of each table using AS
303 #
304 CREATE TABLE t_zip1k_in_1k_as TABLESPACE=s_1k KEY_BLOCK_SIZE=1 AS (SELECT * FROM t_zip1k_in_1k);
305 CREATE TABLE t_zip2k_in_2k_as TABLESPACE=s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip2k_in_2k);
306 CREATE TABLE t_zip4k_in_4k_as TABLESPACE=s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip4k_in_4k);
307 CREATE TABLE t_red_in_8k_as TABLESPACE=s_8k ROW_FORMAT=redundant AS (SELECT * FROM t_red_in_8k);
308 CREATE TABLE t_com_in_8k_as TABLESPACE=s_8k ROW_FORMAT=compact AS (SELECT * FROM t_com_in_8k);
309 CREATE TABLE t_dyn_in_8k_as TABLESPACE=s_8k ROW_FORMAT=dynamic AS (SELECT * FROM t_dyn_in_8k);
310 #
311 # Create a copy of the 1k table, moving it to another tablespace with a different
312 # FILE_BLOCK_SIZE, while changing the KEY_BLOCK_SIZE.
313 #
314 CREATE TABLE t_zip2k_in_2k_from_1k TABLESPACE s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip1k_in_1k);
315 CREATE TABLE t_zip4k_in_4k_from_1k TABLESPACE s_4k KEY_BLOCK_SIZE=4 AS (SELECT * FROM t_zip1k_in_1k);
316 CREATE TABLE t_red_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=redundant AS (SELECT * FROM t_zip1k_in_1k);
317 CREATE TABLE t_com_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=compact AS (SELECT * FROM t_zip1k_in_1k);
318 CREATE TABLE t_dyn_in_8k_from_1k TABLESPACE s_8k ROW_FORMAT=dynamic AS (SELECT * FROM t_zip1k_in_1k);
319 === information_schema.innodb_tablespaces and innodb_datafiles ===
320 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
321 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
322 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
323 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
324 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
325 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
326 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
327 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
328 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
329 === information_schema.files ===
330 Space_Name File_Type Engine Status Tablespace_Name Path
331 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
332 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
333 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
334 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
335 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
336 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
337 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
338 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
339 === information_schema.innodb_tables and innodb_tablespaces ===
340 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
341 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
342 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
343 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
344 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
345 test/t_com_in_8k s_8k 129 5 Compact 0 General
346 test/t_com_in_8k_as s_8k 129 5 Compact 0 General
347 test/t_com_in_8k_from_1k s_8k 129 5 Compact 0 General
348 test/t_com_in_8k_like s_8k 129 5 Compact 0 General
349 test/t_dyn_in_8k s_8k 161 5 Dynamic 0 General
350 test/t_dyn_in_8k_as s_8k 161 5 Dynamic 0 General
351 test/t_dyn_in_8k_from_1k s_8k 161 5 Dynamic 0 General
352 test/t_dyn_in_8k_like s_8k 161 5 Dynamic 0 General
353 test/t_red_in_8k s_8k 128 5 Redundant 0 General
354 test/t_red_in_8k_as s_8k 128 5 Redundant 0 General
355 test/t_red_in_8k_from_1k s_8k 128 5 Redundant 0 General
356 test/t_red_in_8k_like s_8k 128 5 Redundant 0 General
357 test/t_zip1k_in_1k s_1k 163 5 Compressed 1024 General
358 test/t_zip1k_in_1k_as s_1k 163 5 Compressed 1024 General
359 test/t_zip1k_in_1k_like s_1k 163 5 Compressed 1024 General
360 test/t_zip2k_in_2k s_2k 165 5 Compressed 2048 General
361 test/t_zip2k_in_2k_as s_2k 165 5 Compressed 2048 General
362 test/t_zip2k_in_2k_from_1k s_2k 165 5 Compressed 2048 General
363 test/t_zip2k_in_2k_like s_2k 165 5 Compressed 2048 General
364 test/t_zip4k_in_4k s_4k 167 5 Compressed 4096 General
365 test/t_zip4k_in_4k_as s_4k 167 5 Compressed 4096 General
366 test/t_zip4k_in_4k_from_1k s_4k 167 5 Compressed 4096 General
367 test/t_zip4k_in_4k_like s_4k 167 5 Compressed 4096 General
368 # Directory of MYSQLD_DATADIR/
369 mysql.ibd
370 s_1k.ibd
371 s_2k.ibd
372 s_4k.ibd
373 s_8k.ibd
374 # Directory of MYSQLD_DATADIR/test/
375 #
376 # Restart the server and make sure that everything is OK.
377 #
378 # restart
379 === information_schema.innodb_tablespaces and innodb_datafiles ===
380 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
381 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
382 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
383 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
384 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
385 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
386 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
387 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
388 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
389 === information_schema.files ===
390 Space_Name File_Type Engine Status Tablespace_Name Path
391 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
392 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
393 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
394 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
395 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
396 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
397 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
398 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
399 === information_schema.innodb_tables and innodb_tablespaces ===
400 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
401 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
402 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
403 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
404 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
405 test/t_com_in_8k s_8k 129 5 Compact 0 General
406 test/t_com_in_8k_as s_8k 129 5 Compact 0 General
407 test/t_com_in_8k_from_1k s_8k 129 5 Compact 0 General
408 test/t_com_in_8k_like s_8k 129 5 Compact 0 General
409 test/t_dyn_in_8k s_8k 161 5 Dynamic 0 General
410 test/t_dyn_in_8k_as s_8k 161 5 Dynamic 0 General
411 test/t_dyn_in_8k_from_1k s_8k 161 5 Dynamic 0 General
412 test/t_dyn_in_8k_like s_8k 161 5 Dynamic 0 General
413 test/t_red_in_8k s_8k 128 5 Redundant 0 General
414 test/t_red_in_8k_as s_8k 128 5 Redundant 0 General
415 test/t_red_in_8k_from_1k s_8k 128 5 Redundant 0 General
416 test/t_red_in_8k_like s_8k 128 5 Redundant 0 General
417 test/t_zip1k_in_1k s_1k 163 5 Compressed 1024 General
418 test/t_zip1k_in_1k_as s_1k 163 5 Compressed 1024 General
419 test/t_zip1k_in_1k_like s_1k 163 5 Compressed 1024 General
420 test/t_zip2k_in_2k s_2k 165 5 Compressed 2048 General
421 test/t_zip2k_in_2k_as s_2k 165 5 Compressed 2048 General
422 test/t_zip2k_in_2k_from_1k s_2k 165 5 Compressed 2048 General
423 test/t_zip2k_in_2k_like s_2k 165 5 Compressed 2048 General
424 test/t_zip4k_in_4k s_4k 167 5 Compressed 4096 General
425 test/t_zip4k_in_4k_as s_4k 167 5 Compressed 4096 General
426 test/t_zip4k_in_4k_from_1k s_4k 167 5 Compressed 4096 General
427 test/t_zip4k_in_4k_like s_4k 167 5 Compressed 4096 General
428 # Directory of MYSQLD_DATADIR/
429 mysql.ibd
430 s_1k.ibd
431 s_2k.ibd
432 s_4k.ibd
433 s_8k.ibd
434 # Directory of MYSQLD_DATADIR/test/
435 #
436 # Clean-up tables we no longer need
437 #
438 DROP TABLE t_zip1k_in_1k_like;
439 DROP TABLE t_zip2k_in_2k_like;
440 DROP TABLE t_zip4k_in_4k_like;
441 DROP TABLE t_red_in_8k_like;
442 DROP TABLE t_com_in_8k_like;
443 DROP TABLE t_dyn_in_8k_like;
444 DROP TABLE t_zip1k_in_1k_as;
445 DROP TABLE t_zip2k_in_2k_as;
446 DROP TABLE t_zip4k_in_4k_as;
447 DROP TABLE t_red_in_8k_as;
448 DROP TABLE t_com_in_8k_as;
449 DROP TABLE t_dyn_in_8k_as;
450 DROP TABLE t_zip2k_in_2k_from_1k;
451 DROP TABLE t_zip4k_in_4k_from_1k;
452 DROP TABLE t_red_in_8k_from_1k;
453 DROP TABLE t_com_in_8k_from_1k;
454 DROP TABLE t_dyn_in_8k_from_1k;
455 CHECK TABLE t_zip1k_in_1k;
456 Table Op Msg_type Msg_text
457 test.t_zip1k_in_1k check status OK
458 CHECK TABLE t_zip2k_in_2k;
459 Table Op Msg_type Msg_text
460 test.t_zip2k_in_2k check status OK
461 CHECK TABLE t_zip4k_in_4k;
462 Table Op Msg_type Msg_text
463 test.t_zip4k_in_4k check status OK
464 CHECK TABLE t_red_in_8k;
465 Table Op Msg_type Msg_text
466 test.t_red_in_8k check status OK
467 CHECK TABLE t_com_in_8k;
468 Table Op Msg_type Msg_text
469 test.t_com_in_8k check status OK
470 CHECK TABLE t_dyn_in_8k;
471 Table Op Msg_type Msg_text
472 test.t_dyn_in_8k check status OK
473 DROP TABLE t_zip1k_in_1k;
474 DROP TABLE t_zip2k_in_2k;
475 DROP TABLE t_zip4k_in_4k;
476 DROP TABLE t_red_in_8k;
477 DROP TABLE t_com_in_8k;
478 DROP TABLE t_dyn_in_8k;
479 SET SESSION innodb_strict_mode = ON;
480 SET GLOBAL innodb_file_per_table = ON;
481 #
482 # Create compressed tables explicitly as file_per_table tablespaces.
483 #
484 CREATE TABLE t_zip1k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=1;
485 CREATE TABLE t_zip2k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=2;
486 CREATE TABLE t_zip4k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=4;
487 CREATE TABLE t_zip8k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=8;
488 #
489 # Create compressed tables in remote locations using file-per-table.
490 #
491 CREATE TABLE t_zip1k_as_remote (a int, b text) KEY_BLOCK_SIZE=1 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
492 CREATE TABLE t_zip2k_as_remote (a int, b text) KEY_BLOCK_SIZE=2 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
493 CREATE TABLE t_zip4k_as_remote (a int, b text) KEY_BLOCK_SIZE=4 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
494 CREATE TABLE t_zip8k_as_remote (a int, b text) KEY_BLOCK_SIZE=8 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
495 === information_schema.innodb_tablespaces and innodb_datafiles ===
496 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
497 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
498 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
499 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
500 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
501 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
502 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
503 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
504 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
505 test/t_zip1k_as_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_as_file_per_table.ibd
506 test/t_zip1k_as_remote Single DEFAULT 1024 1 1 Compressed MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
507 test/t_zip2k_as_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_as_file_per_table.ibd
508 test/t_zip2k_as_remote Single DEFAULT 2048 1 1 Compressed MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
509 test/t_zip4k_as_file_per_table Single DEFAULT 4096 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
510 test/t_zip4k_as_remote Single DEFAULT 4096 1 1 Compressed MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
511 test/t_zip8k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
512 test/t_zip8k_as_remote Single DEFAULT DEFAULT 1 1 Compressed MYSQL_TMP_DIR/test/t_zip8k_as_remote.ibd
513 === information_schema.files ===
514 Space_Name File_Type Engine Status Tablespace_Name Path
515 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
516 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
517 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
518 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
519 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
520 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
521 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
522 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
523 test/t_zip1k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip1k_as_file_per_table MYSQLD_DATADIR/test/t_zip1k_as_file_per_table.ibd
524 test/t_zip1k_as_remote TABLESPACE InnoDB NORMAL test/t_zip1k_as_remote MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
525 test/t_zip2k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip2k_as_file_per_table MYSQLD_DATADIR/test/t_zip2k_as_file_per_table.ibd
526 test/t_zip2k_as_remote TABLESPACE InnoDB NORMAL test/t_zip2k_as_remote MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
527 test/t_zip4k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip4k_as_file_per_table MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
528 test/t_zip4k_as_remote TABLESPACE InnoDB NORMAL test/t_zip4k_as_remote MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
529 test/t_zip8k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip8k_as_file_per_table MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
530 test/t_zip8k_as_remote TABLESPACE InnoDB NORMAL test/t_zip8k_as_remote MYSQL_TMP_DIR/test/t_zip8k_as_remote.ibd
531 === information_schema.innodb_tables and innodb_tablespaces ===
532 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
533 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
534 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
535 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
536 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
537 test/t_zip1k_as_file_per_table test/t_zip1k_as_file_per_table 35 5 Compressed 1024 Single
538 test/t_zip1k_as_remote test/t_zip1k_as_remote 99 5 Compressed 1024 Single
539 test/t_zip2k_as_file_per_table test/t_zip2k_as_file_per_table 37 5 Compressed 2048 Single
540 test/t_zip2k_as_remote test/t_zip2k_as_remote 101 5 Compressed 2048 Single
541 test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 5 Compressed 4096 Single
542 test/t_zip4k_as_remote test/t_zip4k_as_remote 103 5 Compressed 4096 Single
543 test/t_zip8k_as_file_per_table test/t_zip8k_as_file_per_table 41 5 Compressed 8192 Single
544 test/t_zip8k_as_remote test/t_zip8k_as_remote 105 5 Compressed 8192 Single
545 # MYSQLD_DATADIR/
546 mysql.ibd
547 s_1k.ibd
548 s_2k.ibd
549 s_4k.ibd
550 s_8k.ibd
551 # MYSQLD_DATADIR/test/
552 t_zip1k_as_file_per_table.ibd
553 t_zip2k_as_file_per_table.ibd
554 t_zip4k_as_file_per_table.ibd
555 t_zip8k_as_file_per_table.ibd
556 #
557 # These file_per_table tables cannot be moved to a general tablespace if the
558 # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
559 # not compressed.
560 #
561 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_1k`;
562 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
563 SHOW WARNINGS;
564 Level Code Message
565 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
566 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
567 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_1k`;
568 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
569 SHOW WARNINGS;
570 Level Code Message
571 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
572 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
573 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_1k`;
574 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
575 SHOW WARNINGS;
576 Level Code Message
577 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
578 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
579 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_1k`;
580 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
581 SHOW WARNINGS;
582 Level Code Message
583 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
584 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
585 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_1k`;
586 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
587 SHOW WARNINGS;
588 Level Code Message
589 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
590 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
591 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_1k`;
592 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
593 SHOW WARNINGS;
594 Level Code Message
595 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
596 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
597 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_2k`;
598 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
599 SHOW WARNINGS;
600 Level Code Message
601 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
602 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
603 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_2k`;
604 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
605 SHOW WARNINGS;
606 Level Code Message
607 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
608 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
609 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_2k`;
610 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
611 SHOW WARNINGS;
612 Level Code Message
613 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
614 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
615 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_2k`;
616 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
617 SHOW WARNINGS;
618 Level Code Message
619 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
620 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
621 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_2k`;
622 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
623 SHOW WARNINGS;
624 Level Code Message
625 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
626 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
627 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_2k`;
628 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
629 SHOW WARNINGS;
630 Level Code Message
631 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
632 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
633 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_4k`;
634 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
635 SHOW WARNINGS;
636 Level Code Message
637 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
638 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
639 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_4k`;
640 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
641 SHOW WARNINGS;
642 Level Code Message
643 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
644 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
645 ALTER TABLE `t_zip8k_as_file_per_table` TABLESPACE=`s_4k`;
646 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
647 SHOW WARNINGS;
648 Level Code Message
649 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
650 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
651 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_4k`;
652 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
653 SHOW WARNINGS;
654 Level Code Message
655 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
656 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
657 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_4k`;
658 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
659 SHOW WARNINGS;
660 Level Code Message
661 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
662 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
663 ALTER TABLE `t_zip8k_as_remote` TABLESPACE=`s_4k`;
664 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
665 SHOW WARNINGS;
666 Level Code Message
667 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 8192
668 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
669 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_8k`;
670 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
671 SHOW WARNINGS;
672 Level Code Message
673 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
674 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
675 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_8k`;
676 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
677 SHOW WARNINGS;
678 Level Code Message
679 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
680 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
681 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_8k`;
682 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
683 SHOW WARNINGS;
684 Level Code Message
685 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
686 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
687 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_8k`;
688 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
689 SHOW WARNINGS;
690 Level Code Message
691 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
692 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
693 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_8k`;
694 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
695 SHOW WARNINGS;
696 Level Code Message
697 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
698 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
699 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_8k`;
700 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
701 SHOW WARNINGS;
702 Level Code Message
703 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
704 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
705 DROP TABLE `t_zip8k_as_remote`;
706 #
707 # These explicit file_per_table tables can be moved to a general tablespace
708 # if FILE_BLOCK_SIZE matches KEY_BLOCK_SIZE and the tablespace is compressed.
709 #
710 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_in_s_1k`;
711 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_in_s_2k`;
712 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_in_s_4k`;
713 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_remote_in_s_1k`;
714 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_remote_in_s_2k`;
715 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_4k`, RENAME TO `t_zip4k_remote_in_s_4k`;
716 === information_schema.innodb_tablespaces and innodb_datafiles ===
717 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
718 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
719 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
720 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
721 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
722 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
723 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
724 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
725 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
726 test/t_zip8k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
727 === information_schema.files ===
728 Space_Name File_Type Engine Status Tablespace_Name Path
729 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
730 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
731 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
732 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
733 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
734 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
735 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
736 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
737 test/t_zip8k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip8k_as_file_per_table MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
738 === information_schema.innodb_tables and innodb_tablespaces ===
739 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
740 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
741 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
742 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
743 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
744 test/t_zip1k_in_s_1k s_1k 163 5 Compressed 1024 General
745 test/t_zip1k_remote_in_s_1k s_1k 163 5 Compressed 1024 General
746 test/t_zip2k_in_s_2k s_2k 165 5 Compressed 2048 General
747 test/t_zip2k_remote_in_s_2k s_2k 165 5 Compressed 2048 General
748 test/t_zip4k_in_s_4k s_4k 167 5 Compressed 4096 General
749 test/t_zip4k_remote_in_s_4k s_4k 167 5 Compressed 4096 General
750 test/t_zip8k_as_file_per_table test/t_zip8k_as_file_per_table 41 5 Compressed 8192 Single
751 # MYSQLD_DATADIR/
752 mysql.ibd
753 s_1k.ibd
754 s_2k.ibd
755 s_4k.ibd
756 s_8k.ibd
757 # MYSQLD_DATADIR/test/
758 t_zip8k_as_file_per_table.ibd
759 CHECK TABLE `t_zip1k_in_s_1k`;
760 Table Op Msg_type Msg_text
761 test.t_zip1k_in_s_1k check status OK
762 CHECK TABLE `t_zip2k_in_s_2k`;
763 Table Op Msg_type Msg_text
764 test.t_zip2k_in_s_2k check status OK
765 CHECK TABLE `t_zip4k_in_s_4k`;
766 Table Op Msg_type Msg_text
767 test.t_zip4k_in_s_4k check status OK
768 CHECK TABLE `t_zip1k_remote_in_s_1k`;
769 Table Op Msg_type Msg_text
770 test.t_zip1k_remote_in_s_1k check status OK
771 CHECK TABLE `t_zip2k_remote_in_s_2k`;
772 Table Op Msg_type Msg_text
773 test.t_zip2k_remote_in_s_2k check status OK
774 CHECK TABLE `t_zip4k_remote_in_s_4k`;
775 Table Op Msg_type Msg_text
776 test.t_zip4k_remote_in_s_4k check status OK
777 #
778 # Tables in general tablespaces cannot be moved to another general tablespace if the
779 # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
780 # not compressed.
781 #
782 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_1k`;
783 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
784 SHOW WARNINGS;
785 Level Code Message
786 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
787 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
788 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_1k`;
789 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
790 SHOW WARNINGS;
791 Level Code Message
792 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
793 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
794 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_2k`;
795 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
796 SHOW WARNINGS;
797 Level Code Message
798 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
799 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
800 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_2k`;
801 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
802 SHOW WARNINGS;
803 Level Code Message
804 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
805 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
806 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_4k`;
807 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
808 SHOW WARNINGS;
809 Level Code Message
810 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 1024
811 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
812 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_4k`;
813 ERROR HY000: InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
814 SHOW WARNINGS;
815 Level Code Message
816 Error 1478 InnoDB: Tablespace `s_4k` uses block size 4096 and cannot contain a table with physical page size 2048
817 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
818 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_8k`;
819 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
820 SHOW WARNINGS;
821 Level Code Message
822 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
823 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
824 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_8k`;
825 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
826 SHOW WARNINGS;
827 Level Code Message
828 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
829 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
830 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`s_8k`;
831 ERROR HY000: InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
832 SHOW WARNINGS;
833 Level Code Message
834 Error 1478 InnoDB: Tablespace `s_8k` cannot contain a COMPRESSED table
835 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
836 #
837 # Tables in a general tablespace can be moved to file_per_table locations.
838 #
839 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip1k_to_file_per_table`;
840 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip2k_to_file_per_table`;
841 ALTER TABLE `t_zip4k_in_s_4k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip4k_to_file_per_table`;
842 === information_schema.innodb_tablespaces and innodb_datafiles ===
843 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
844 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
845 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
846 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
847 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
848 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
849 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
850 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
851 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
852 test/t_zip1k_to_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
853 test/t_zip2k_to_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
854 test/t_zip4k_to_file_per_table Single DEFAULT 4096 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_to_file_per_table.ibd
855 test/t_zip8k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
856 === information_schema.files ===
857 Space_Name File_Type Engine Status Tablespace_Name Path
858 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
859 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
860 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
861 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
862 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
863 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
864 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
865 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
866 test/t_zip1k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip1k_to_file_per_table MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
867 test/t_zip2k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip2k_to_file_per_table MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
868 test/t_zip4k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip4k_to_file_per_table MYSQLD_DATADIR/test/t_zip4k_to_file_per_table.ibd
869 test/t_zip8k_as_file_per_table TABLESPACE InnoDB NORMAL test/t_zip8k_as_file_per_table MYSQLD_DATADIR/test/t_zip8k_as_file_per_table.ibd
870 === information_schema.innodb_tables and innodb_tablespaces ===
871 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
872 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
873 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
874 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
875 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
876 test/t_zip1k_remote_in_s_1k s_1k 163 5 Compressed 1024 General
877 test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 5 Compressed 1024 Single
878 test/t_zip2k_remote_in_s_2k s_2k 165 5 Compressed 2048 General
879 test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 5 Compressed 2048 Single
880 test/t_zip4k_remote_in_s_4k s_4k 167 5 Compressed 4096 General
881 test/t_zip4k_to_file_per_table test/t_zip4k_to_file_per_table 39 5 Compressed 4096 Single
882 test/t_zip8k_as_file_per_table test/t_zip8k_as_file_per_table 41 5 Compressed 8192 Single
883 CHECK TABLE `t_zip1k_to_file_per_table`;
884 Table Op Msg_type Msg_text
885 test.t_zip1k_to_file_per_table check status OK
886 CHECK TABLE `t_zip2k_to_file_per_table`;
887 Table Op Msg_type Msg_text
888 test.t_zip2k_to_file_per_table check status OK
889 CHECK TABLE `t_zip4k_to_file_per_table`;
890 Table Op Msg_type Msg_text
891 test.t_zip4k_to_file_per_table check status OK
892 CHECK TABLE `t_zip8k_as_file_per_table`;
893 Table Op Msg_type Msg_text
894 test.t_zip8k_as_file_per_table check status OK
895 DROP TABLE `t_zip1k_to_file_per_table`;
896 DROP TABLE `t_zip2k_to_file_per_table`;
897 DROP TABLE `t_zip4k_to_file_per_table`;
898 DROP TABLE `t_zip8k_as_file_per_table`;
899 #
900 # Tables in a general tablespace cannot be moved to remote file_per_table locations
901 # because MySQL ignores DATA DIRECTORY in ALTER TABLE.
902 #
903 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`;
904 Warnings:
905 Warning 1618 <DATA DIRECTORY> option ignored
906 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`;
907 Warnings:
908 Warning 1618 <DATA DIRECTORY> option ignored
909 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`;
910 Warnings:
911 Warning 1618 <DATA DIRECTORY> option ignored
912 === information_schema.innodb_tablespaces and innodb_datafiles ===
913 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
914 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
915 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
916 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
917 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
918 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
919 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
920 s_4k General DEFAULT 4096 1 1 Compressed s_4k.ibd
921 s_8k General DEFAULT 0 1 1 Any s_8k.ibd
922 test/t_zip1k_to_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
923 test/t_zip2k_to_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
924 test/t_zip4k_to_file_per_table Single DEFAULT 4096 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_to_file_per_table.ibd
925 === information_schema.files ===
926 Space_Name File_Type Engine Status Tablespace_Name Path
927 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
928 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
929 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
930 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
931 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
932 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
933 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
934 s_8k TABLESPACE InnoDB NORMAL s_8k MYSQLD_DATADIR/s_8k.ibd
935 test/t_zip1k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip1k_to_file_per_table MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
936 test/t_zip2k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip2k_to_file_per_table MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
937 test/t_zip4k_to_file_per_table TABLESPACE InnoDB NORMAL test/t_zip4k_to_file_per_table MYSQLD_DATADIR/test/t_zip4k_to_file_per_table.ibd
938 === information_schema.innodb_tables and innodb_tablespaces ===
939 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
940 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
941 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
942 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
943 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
944 test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 5 Compressed 1024 Single
945 test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 5 Compressed 2048 Single
946 test/t_zip4k_to_file_per_table test/t_zip4k_to_file_per_table 39 5 Compressed 4096 Single
947 DROP TABLE `t_zip1k_to_file_per_table`;
948 DROP TABLE `t_zip2k_to_file_per_table`;
949 DROP TABLE `t_zip4k_to_file_per_table`;
950 call mtr.add_suppression("\\[ERROR\\].* '.*s_.k.ibd' read failed! - attempted to read 4 bytes, read only 0 bytes at offset 8226");
951 call mtr.add_suppression("\\[Warning\\].* Ignoring '.*s_.k.ibd' invalid tablespace ID in the header");
952 #
953 # Clean-up.
954 #
955 DROP TABLESPACE s_1k;
956 DROP TABLESPACE s_2k;
957 DROP TABLESPACE s_4k;
958 DROP TABLESPACE s_8k;