Debian Codegraph @

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

raw
1 #
2 # CREATE TABLESPACE related tests for 4k 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 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=8192 because INNODB_PAGE_SIZE=4096.
22 SHOW WARNINGS;
23 Level Code Message
24 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=8192 because INNODB_PAGE_SIZE=4096.
25 Error 1528 Failed to create TABLESPACE s_8k
26 Error 1031 Table storage engine for 's_8k' doesn't have this option
27 CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
28 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=4096.
29 SHOW WARNINGS;
30 Level Code Message
31 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=4096.
32 Error 1528 Failed to create TABLESPACE s_16k
33 Error 1031 Table storage engine for 's_16k' doesn't have this option
34 CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
35 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=4096.
36 SHOW WARNINGS;
37 Level Code Message
38 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=4096.
39 Error 1528 Failed to create TABLESPACE s_32k
40 Error 1031 Table storage engine for 's_32k' doesn't have this option
41 CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
42 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=4096.
43 SHOW WARNINGS;
44 Level Code Message
45 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=4096.
46 Error 1528 Failed to create TABLESPACE s_64k
47 Error 1031 Table storage engine for 's_64k' doesn't have this option
48 === information_schema.innodb_tablespaces and innodb_datafiles ===
49 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
50 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
51 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
52 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
53 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
54 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
55 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
56 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
57 === information_schema.files ===
58 Space_Name File_Type Engine Status Tablespace_Name Path
59 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
60 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
61 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
62 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
63 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
64 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
65 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
66 #
67 # Try to put a temporary table into a non-temporary compressed tablespace
68 #
69 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
70 ERROR HY000: InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
71 SHOW WARNINGS;
72 Level Code Message
73 Error 1478 InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
74 Error 1031 Table storage engine for 't_temp_zip' doesn't have this option
75 #
76 # Add tables to the 1K tablespace.
77 #
78 CREATE TABLE t_zip1k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
79 CREATE TABLE t_zip2k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_1k;
80 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
81 SHOW WARNINGS;
82 Level Code Message
83 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
84 Error 1031 Table storage engine for 't_zip2k_in_1k' doesn't have this option
85 CREATE TABLE t_zip4k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_1k;
86 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
87 SHOW WARNINGS;
88 Level Code Message
89 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
90 Error 1031 Table storage engine for 't_zip4k_in_1k' doesn't have this option
91 CREATE TABLE t_zip8k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_1k;
92 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
93 SHOW WARNINGS;
94 Level Code Message
95 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
96 Error 1031 Table storage engine for 't_zip8k_in_1k' doesn't have this option
97 CREATE TABLE t_zip16k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_1k;
98 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
99 SHOW WARNINGS;
100 Level Code Message
101 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
102 Error 1031 Table storage engine for 't_zip16k_in_1k' doesn't have this option
103 CREATE TABLE t_zip32k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_1k;
104 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
105 SHOW WARNINGS;
106 Level Code Message
107 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
108 Error 1031 Table storage engine for 't_zip32k_in_1k' doesn't have this option
109 CREATE TABLE t_red_in_1k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_1k;
110 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
111 SHOW WARNINGS;
112 Level Code Message
113 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
114 Error 1031 Table storage engine for 't_red_in_1k' doesn't have this option
115 CREATE TABLE t_cmp_in_1k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_1k;
116 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
117 SHOW WARNINGS;
118 Level Code Message
119 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
120 Error 1031 Table storage engine for 't_cmp_in_1k' doesn't have this option
121 CREATE TABLE t_dyn_in_1k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_1k;
122 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
123 SHOW WARNINGS;
124 Level Code Message
125 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
126 Error 1031 Table storage engine for 't_dyn_in_1k' doesn't have this option
127 #
128 # Add tables to the 2K tablespace.
129 #
130 CREATE TABLE t_zip1k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_2k;
131 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
132 SHOW WARNINGS;
133 Level Code Message
134 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
135 Error 1031 Table storage engine for 't_zip1k_in_2k' doesn't have this option
136 CREATE TABLE t_zip2k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_2k;
137 CREATE TABLE t_zip4k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_2k;
138 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
139 SHOW WARNINGS;
140 Level Code Message
141 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
142 Error 1031 Table storage engine for 't_zip4k_in_2k' doesn't have this option
143 CREATE TABLE t_zip8k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_2k;
144 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
145 SHOW WARNINGS;
146 Level Code Message
147 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
148 Error 1031 Table storage engine for 't_zip8k_in_2k' doesn't have this option
149 CREATE TABLE t_zip16k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_2k;
150 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
151 SHOW WARNINGS;
152 Level Code Message
153 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
154 Error 1031 Table storage engine for 't_zip16k_in_2k' doesn't have this option
155 CREATE TABLE t_zip32k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_2k;
156 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
157 SHOW WARNINGS;
158 Level Code Message
159 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
160 Error 1031 Table storage engine for 't_zip32k_in_2k' doesn't have this option
161 CREATE TABLE t_red_in_2k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_2k;
162 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
163 SHOW WARNINGS;
164 Level Code Message
165 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
166 Error 1031 Table storage engine for 't_red_in_2k' doesn't have this option
167 CREATE TABLE t_cmp_in_2k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_2k;
168 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
169 SHOW WARNINGS;
170 Level Code Message
171 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
172 Error 1031 Table storage engine for 't_cmp_in_2k' doesn't have this option
173 CREATE TABLE t_dyn_in_2k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_2k;
174 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
175 SHOW WARNINGS;
176 Level Code Message
177 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
178 Error 1031 Table storage engine for 't_dyn_in_2k' doesn't have this option
179 #
180 # Add tables to the 4K tablespace.
181 #
182 CREATE TABLE t_zip1k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_4k;
183 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
184 SHOW WARNINGS;
185 Level Code Message
186 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
187 Error 1031 Table storage engine for 't_zip1k_in_4k' doesn't have this option
188 CREATE TABLE t_zip2k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_4k;
189 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
190 SHOW WARNINGS;
191 Level Code Message
192 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
193 Error 1031 Table storage engine for 't_zip2k_in_4k' doesn't have this option
194 CREATE TABLE t_zip4k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_4k;
195 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
196 SHOW WARNINGS;
197 Level Code Message
198 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
199 Error 1031 Table storage engine for 't_zip4k_in_4k' doesn't have this option
200 CREATE TABLE t_zip8k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_4k;
201 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
202 SHOW WARNINGS;
203 Level Code Message
204 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
205 Error 1031 Table storage engine for 't_zip8k_in_4k' doesn't have this option
206 CREATE TABLE t_zip16k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_4k;
207 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
208 SHOW WARNINGS;
209 Level Code Message
210 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
211 Error 1031 Table storage engine for 't_zip16k_in_4k' doesn't have this option
212 CREATE TABLE t_zip32k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_4k;
213 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
214 SHOW WARNINGS;
215 Level Code Message
216 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
217 Error 1031 Table storage engine for 't_zip32k_in_4k' doesn't have this option
218 CREATE TABLE t_red_in_4k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_4k;
219 CREATE TABLE t_com_in_4k (a int, b text) ROW_FORMAT=compact TABLESPACE s_4k;
220 CREATE TABLE t_dyn_in_4k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_4k;
221 # Add data to the existing Tables
222 INSERT INTO t_zip1k_in_1k VALUES (1,'a'),(2,'b'),(3,'c');
223 INSERT INTO t_zip2k_in_2k VALUES (1,'a'),(2,'b'),(3,'c');
224 INSERT INTO t_red_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
225 INSERT INTO t_com_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
226 INSERT INTO t_dyn_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
227 # Restart mysqld
228 # restart
229 #
230 # Try to drop a tablespace which is not empty
231 #
232 DROP TABLESPACE s_4k;
233 ERROR HY000: Tablespace `s_4k` is not empty.
234 #
235 # Add more data to the existing Tables
236 #
237 INSERT INTO t_zip1k_in_1k VALUES (4,'d');
238 INSERT INTO t_zip2k_in_2k VALUES (4,'d');
239 INSERT INTO t_red_in_4k VALUES (4,'d');
240 INSERT INTO t_com_in_4k VALUES (4,'d');
241 INSERT INTO t_dyn_in_4k VALUES (4,'d');
242 #
243 # Create an empty copy of each table using LIKE
244 #
245 CREATE TABLE t_zip1k_in_1k_like LIKE t_zip1k_in_1k;
246 CREATE TABLE t_zip2k_in_2k_like LIKE t_zip2k_in_2k;
247 CREATE TABLE t_red_in_4k_like LIKE t_red_in_4k;
248 CREATE TABLE t_com_in_4k_like LIKE t_com_in_4k;
249 CREATE TABLE t_dyn_in_4k_like LIKE t_dyn_in_4k;
250 #
251 # Create a full copy of each table using AS
252 #
253 CREATE TABLE t_zip1k_in_1k_as TABLESPACE=s_1k KEY_BLOCK_SIZE=1 AS (SELECT * FROM t_zip1k_in_1k);
254 CREATE TABLE t_zip2k_in_2k_as TABLESPACE=s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip2k_in_2k);
255 CREATE TABLE t_red_in_4k_as TABLESPACE=s_4k ROW_FORMAT=redundant AS (SELECT * FROM t_red_in_4k);
256 CREATE TABLE t_com_in_4k_as TABLESPACE=s_4k ROW_FORMAT=compact AS (SELECT * FROM t_com_in_4k);
257 CREATE TABLE t_dyn_in_4k_as TABLESPACE=s_4k ROW_FORMAT=dynamic AS (SELECT * FROM t_dyn_in_4k);
258 #
259 # Create a copy of the 1k table, moving it to another tablespace with a different
260 # FILE_BLOCK_SIZE, while changing the KEY_BLOCK_SIZE.
261 #
262 CREATE TABLE t_zip2k_in_2k_from_1k TABLESPACE s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip1k_in_1k);
263 CREATE TABLE t_red_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=redundant AS (SELECT * FROM t_zip1k_in_1k);
264 CREATE TABLE t_com_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=compact AS (SELECT * FROM t_zip1k_in_1k);
265 CREATE TABLE t_dyn_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=dynamic AS (SELECT * FROM t_zip1k_in_1k);
266 === information_schema.innodb_tablespaces and innodb_datafiles ===
267 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
268 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
269 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
270 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
271 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
272 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
273 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
274 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
275 === information_schema.files ===
276 Space_Name File_Type Engine Status Tablespace_Name Path
277 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
278 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
279 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
280 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
281 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
282 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
283 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
284 === information_schema.innodb_tables and innodb_tablespaces ===
285 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
286 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
287 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
288 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
289 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
290 test/t_com_in_4k s_4k 129 5 Compact 0 General
291 test/t_com_in_4k_as s_4k 129 5 Compact 0 General
292 test/t_com_in_4k_from_1k s_4k 129 5 Compact 0 General
293 test/t_com_in_4k_like s_4k 129 5 Compact 0 General
294 test/t_dyn_in_4k s_4k 161 5 Dynamic 0 General
295 test/t_dyn_in_4k_as s_4k 161 5 Dynamic 0 General
296 test/t_dyn_in_4k_from_1k s_4k 161 5 Dynamic 0 General
297 test/t_dyn_in_4k_like s_4k 161 5 Dynamic 0 General
298 test/t_red_in_4k s_4k 128 5 Redundant 0 General
299 test/t_red_in_4k_as s_4k 128 5 Redundant 0 General
300 test/t_red_in_4k_from_1k s_4k 128 5 Redundant 0 General
301 test/t_red_in_4k_like s_4k 128 5 Redundant 0 General
302 test/t_zip1k_in_1k s_1k 163 5 Compressed 1024 General
303 test/t_zip1k_in_1k_as s_1k 163 5 Compressed 1024 General
304 test/t_zip1k_in_1k_like s_1k 163 5 Compressed 1024 General
305 test/t_zip2k_in_2k s_2k 165 5 Compressed 2048 General
306 test/t_zip2k_in_2k_as s_2k 165 5 Compressed 2048 General
307 test/t_zip2k_in_2k_from_1k s_2k 165 5 Compressed 2048 General
308 test/t_zip2k_in_2k_like s_2k 165 5 Compressed 2048 General
309 # Directory of MYSQLD_DATADIR/
310 mysql.ibd
311 s_1k.ibd
312 s_2k.ibd
313 s_4k.ibd
314 # Directory of MYSQLD_DATADIR/test/
315 #
316 # Restart the server and make sure that everything is OK.
317 #
318 # restart
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 0 1 1 Any s_4k.ibd
328 === information_schema.files ===
329 Space_Name File_Type Engine Status Tablespace_Name Path
330 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
331 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
332 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
333 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
334 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
335 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
336 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
337 === information_schema.innodb_tables and innodb_tablespaces ===
338 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
339 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
340 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
341 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
342 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
343 test/t_com_in_4k s_4k 129 5 Compact 0 General
344 test/t_com_in_4k_as s_4k 129 5 Compact 0 General
345 test/t_com_in_4k_from_1k s_4k 129 5 Compact 0 General
346 test/t_com_in_4k_like s_4k 129 5 Compact 0 General
347 test/t_dyn_in_4k s_4k 161 5 Dynamic 0 General
348 test/t_dyn_in_4k_as s_4k 161 5 Dynamic 0 General
349 test/t_dyn_in_4k_from_1k s_4k 161 5 Dynamic 0 General
350 test/t_dyn_in_4k_like s_4k 161 5 Dynamic 0 General
351 test/t_red_in_4k s_4k 128 5 Redundant 0 General
352 test/t_red_in_4k_as s_4k 128 5 Redundant 0 General
353 test/t_red_in_4k_from_1k s_4k 128 5 Redundant 0 General
354 test/t_red_in_4k_like s_4k 128 5 Redundant 0 General
355 test/t_zip1k_in_1k s_1k 163 5 Compressed 1024 General
356 test/t_zip1k_in_1k_as s_1k 163 5 Compressed 1024 General
357 test/t_zip1k_in_1k_like s_1k 163 5 Compressed 1024 General
358 test/t_zip2k_in_2k s_2k 165 5 Compressed 2048 General
359 test/t_zip2k_in_2k_as s_2k 165 5 Compressed 2048 General
360 test/t_zip2k_in_2k_from_1k s_2k 165 5 Compressed 2048 General
361 test/t_zip2k_in_2k_like s_2k 165 5 Compressed 2048 General
362 # Directory of MYSQLD_DATADIR/
363 mysql.ibd
364 s_1k.ibd
365 s_2k.ibd
366 s_4k.ibd
367 # Directory of MYSQLD_DATADIR/test/
368 #
369 # Clean-up tables we no longer need
370 #
371 DROP TABLE t_zip1k_in_1k_like;
372 DROP TABLE t_zip2k_in_2k_like;
373 DROP TABLE t_red_in_4k_like;
374 DROP TABLE t_com_in_4k_like;
375 DROP TABLE t_dyn_in_4k_like;
376 DROP TABLE t_zip1k_in_1k_as;
377 DROP TABLE t_zip2k_in_2k_as;
378 DROP TABLE t_red_in_4k_as;
379 DROP TABLE t_com_in_4k_as;
380 DROP TABLE t_dyn_in_4k_as;
381 DROP TABLE t_zip2k_in_2k_from_1k;
382 DROP TABLE t_red_in_4k_from_1k;
383 DROP TABLE t_com_in_4k_from_1k;
384 DROP TABLE t_dyn_in_4k_from_1k;
385 CHECK TABLE t_zip1k_in_1k;
386 Table Op Msg_type Msg_text
387 test.t_zip1k_in_1k check status OK
388 CHECK TABLE t_zip2k_in_2k;
389 Table Op Msg_type Msg_text
390 test.t_zip2k_in_2k check status OK
391 CHECK TABLE t_red_in_4k;
392 Table Op Msg_type Msg_text
393 test.t_red_in_4k check status OK
394 CHECK TABLE t_com_in_4k;
395 Table Op Msg_type Msg_text
396 test.t_com_in_4k check status OK
397 CHECK TABLE t_dyn_in_4k;
398 Table Op Msg_type Msg_text
399 test.t_dyn_in_4k check status OK
400 DROP TABLE t_zip1k_in_1k;
401 DROP TABLE t_zip2k_in_2k;
402 DROP TABLE t_red_in_4k;
403 DROP TABLE t_com_in_4k;
404 DROP TABLE t_dyn_in_4k;
405 #
406 # Create compressed tables explicitly as file_per_table tablespaces.
407 #
408 CREATE TABLE t_zip1k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=1;
409 CREATE TABLE t_zip2k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=2;
410 CREATE TABLE t_zip4k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=4;
411 #
412 # Create compressed tables in remote locations using file-per-table.
413 #
414 CREATE TABLE t_zip1k_as_remote (a int, b text) KEY_BLOCK_SIZE=1 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
415 CREATE TABLE t_zip2k_as_remote (a int, b text) KEY_BLOCK_SIZE=2 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
416 CREATE TABLE t_zip4k_as_remote (a int, b text) KEY_BLOCK_SIZE=4 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
417 === information_schema.innodb_tablespaces and innodb_datafiles ===
418 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
419 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
420 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
421 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
422 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
423 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
424 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
425 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
426 test/t_zip1k_as_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_as_file_per_table.ibd
427 test/t_zip1k_as_remote Single DEFAULT 1024 1 1 Compressed MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
428 test/t_zip2k_as_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_as_file_per_table.ibd
429 test/t_zip2k_as_remote Single DEFAULT 2048 1 1 Compressed MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
430 test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
431 test/t_zip4k_as_remote Single DEFAULT DEFAULT 1 1 Compressed MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
432 === information_schema.files ===
433 Space_Name File_Type Engine Status Tablespace_Name Path
434 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
435 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
436 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
437 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
438 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
439 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
440 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
441 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
442 test/t_zip1k_as_remote TABLESPACE InnoDB NORMAL test/t_zip1k_as_remote MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
443 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
444 test/t_zip2k_as_remote TABLESPACE InnoDB NORMAL test/t_zip2k_as_remote MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
445 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
446 test/t_zip4k_as_remote TABLESPACE InnoDB NORMAL test/t_zip4k_as_remote MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
447 === information_schema.innodb_tables and innodb_tablespaces ===
448 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
449 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
450 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
451 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
452 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
453 test/t_zip1k_as_file_per_table test/t_zip1k_as_file_per_table 35 5 Compressed 1024 Single
454 test/t_zip1k_as_remote test/t_zip1k_as_remote 99 5 Compressed 1024 Single
455 test/t_zip2k_as_file_per_table test/t_zip2k_as_file_per_table 37 5 Compressed 2048 Single
456 test/t_zip2k_as_remote test/t_zip2k_as_remote 101 5 Compressed 2048 Single
457 test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 5 Compressed 4096 Single
458 test/t_zip4k_as_remote test/t_zip4k_as_remote 103 5 Compressed 4096 Single
459 # MYSQLD_DATADIR/
460 mysql.ibd
461 s_1k.ibd
462 s_2k.ibd
463 s_4k.ibd
464 # MYSQLD_DATADIR/test/
465 t_zip1k_as_file_per_table.ibd
466 t_zip2k_as_file_per_table.ibd
467 t_zip4k_as_file_per_table.ibd
468 #
469 # These file_per_table tables cannot be moved to a general tablespace if the
470 # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
471 # not compressed.
472 #
473 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_1k`;
474 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
475 SHOW WARNINGS;
476 Level Code Message
477 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
478 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
479 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_1k`;
480 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
481 SHOW WARNINGS;
482 Level Code Message
483 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
484 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
485 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_1k`;
486 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
487 SHOW WARNINGS;
488 Level Code Message
489 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
490 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
491 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_1k`;
492 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
493 SHOW WARNINGS;
494 Level Code Message
495 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
496 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
497 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_2k`;
498 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
499 SHOW WARNINGS;
500 Level Code Message
501 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
502 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
503 ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_2k`;
504 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
505 SHOW WARNINGS;
506 Level Code Message
507 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
508 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
509 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_2k`;
510 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
511 SHOW WARNINGS;
512 Level Code Message
513 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
514 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
515 ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_2k`;
516 ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
517 SHOW WARNINGS;
518 Level Code Message
519 Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
520 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
521 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_4k`;
522 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
523 SHOW WARNINGS;
524 Level Code Message
525 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
526 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
527 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_4k`;
528 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
529 SHOW WARNINGS;
530 Level Code Message
531 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
532 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
533 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_4k`;
534 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
535 SHOW WARNINGS;
536 Level Code Message
537 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
538 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
539 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_4k`;
540 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
541 SHOW WARNINGS;
542 Level Code Message
543 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
544 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
545 DROP TABLE `t_zip4k_as_remote`;
546 #
547 # These explicit file_per_table tables can be moved to a general tablespace
548 # if FILE_BLOCK_SIZE matches KEY_BLOCK_SIZE and the tablespace is compressed.
549 #
550 ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_in_s_1k`;
551 ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_in_s_2k`;
552 ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_remote_in_s_1k`;
553 ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_remote_in_s_2k`;
554 === information_schema.innodb_tablespaces and innodb_datafiles ===
555 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
556 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
557 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
558 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
559 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
560 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
561 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
562 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
563 test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
564 === information_schema.files ===
565 Space_Name File_Type Engine Status Tablespace_Name Path
566 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
567 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
568 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
569 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
570 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
571 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
572 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
573 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
574 === information_schema.innodb_tables and innodb_tablespaces ===
575 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
576 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
577 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
578 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
579 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
580 test/t_zip1k_in_s_1k s_1k 163 5 Compressed 1024 General
581 test/t_zip1k_remote_in_s_1k s_1k 163 5 Compressed 1024 General
582 test/t_zip2k_in_s_2k s_2k 165 5 Compressed 2048 General
583 test/t_zip2k_remote_in_s_2k s_2k 165 5 Compressed 2048 General
584 test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 5 Compressed 4096 Single
585 # MYSQLD_DATADIR/
586 mysql.ibd
587 s_1k.ibd
588 s_2k.ibd
589 s_4k.ibd
590 # MYSQLD_DATADIR/test/
591 t_zip4k_as_file_per_table.ibd
592 CHECK TABLE `t_zip1k_in_s_1k`;
593 Table Op Msg_type Msg_text
594 test.t_zip1k_in_s_1k check status OK
595 CHECK TABLE `t_zip2k_in_s_2k`;
596 Table Op Msg_type Msg_text
597 test.t_zip2k_in_s_2k check status OK
598 CHECK TABLE `t_zip1k_remote_in_s_1k`;
599 Table Op Msg_type Msg_text
600 test.t_zip1k_remote_in_s_1k check status OK
601 CHECK TABLE `t_zip2k_remote_in_s_2k`;
602 Table Op Msg_type Msg_text
603 test.t_zip2k_remote_in_s_2k check status OK
604 #
605 # Tables in general tablespaces cannot be moved to another general tablespace if the
606 # FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
607 # not compressed.
608 #
609 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_1k`;
610 ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
611 SHOW WARNINGS;
612 Level Code Message
613 Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
614 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
615 ALTER TABLE `t_zip1k_in_s_1k` 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_zip1k_in_s_1k` TABLESPACE=`s_4k`;
622 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
623 SHOW WARNINGS;
624 Level Code Message
625 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
626 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
627 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_4k`;
628 ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
629 SHOW WARNINGS;
630 Level Code Message
631 Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
632 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
633 #
634 # Tables in a general tablespace can be moved to file_per_table locations.
635 #
636 ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip1k_to_file_per_table`;
637 ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip2k_to_file_per_table`;
638 === information_schema.innodb_tablespaces and innodb_datafiles ===
639 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
640 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
641 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
642 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
643 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
644 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
645 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
646 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
647 test/t_zip1k_to_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
648 test/t_zip2k_to_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
649 test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT 1 1 Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
650 === information_schema.files ===
651 Space_Name File_Type Engine Status Tablespace_Name Path
652 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
653 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
654 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
655 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
656 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
657 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
658 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
659 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
660 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
661 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
662 === information_schema.innodb_tables and innodb_tablespaces ===
663 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
664 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
665 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
666 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
667 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
668 test/t_zip1k_remote_in_s_1k s_1k 163 5 Compressed 1024 General
669 test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 5 Compressed 1024 Single
670 test/t_zip2k_remote_in_s_2k s_2k 165 5 Compressed 2048 General
671 test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 5 Compressed 2048 Single
672 test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 5 Compressed 4096 Single
673 CHECK TABLE `t_zip1k_to_file_per_table`;
674 Table Op Msg_type Msg_text
675 test.t_zip1k_to_file_per_table check status OK
676 CHECK TABLE `t_zip2k_to_file_per_table`;
677 Table Op Msg_type Msg_text
678 test.t_zip2k_to_file_per_table check status OK
679 CHECK TABLE `t_zip4k_as_file_per_table`;
680 Table Op Msg_type Msg_text
681 test.t_zip4k_as_file_per_table check status OK
682 DROP TABLE `t_zip1k_to_file_per_table`;
683 DROP TABLE `t_zip2k_to_file_per_table`;
684 DROP TABLE `t_zip4k_as_file_per_table`;
685 #
686 # Tables in a general tablespace cannot be moved to remote file_per_table locations
687 # because MySQL ignores DATA DIRECTORY in ALTER TABLE.
688 #
689 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`;
690 Warnings:
691 Warning 1618 <DATA DIRECTORY> option ignored
692 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`;
693 Warnings:
694 Warning 1618 <DATA DIRECTORY> option ignored
695 === information_schema.innodb_tablespaces and innodb_datafiles ===
696 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
697 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
698 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
699 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
700 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
701 s_1k General DEFAULT 1024 1 1 Compressed s_1k.ibd
702 s_2k General DEFAULT 2048 1 1 Compressed s_2k.ibd
703 s_4k General DEFAULT 0 1 1 Any s_4k.ibd
704 test/t_zip1k_to_file_per_table Single DEFAULT 1024 1 1 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
705 test/t_zip2k_to_file_per_table Single DEFAULT 2048 1 1 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
706 === information_schema.files ===
707 Space_Name File_Type Engine Status Tablespace_Name Path
708 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
709 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
710 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
711 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
712 s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
713 s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
714 s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
715 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
716 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
717 === information_schema.innodb_tables and innodb_tablespaces ===
718 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
719 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
720 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
721 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
722 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
723 test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 5 Compressed 1024 Single
724 test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 5 Compressed 2048 Single
725 DROP TABLE `t_zip1k_to_file_per_table`;
726 DROP TABLE `t_zip2k_to_file_per_table`;
727 #
728 # Clean-up.
729 #
730 DROP TABLESPACE s_1k;
731 DROP TABLESPACE s_2k;
732 DROP TABLESPACE s_4k;