Debian Codegraph @

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

raw
1 #
2 # Test support for the COMPRESSION syntax on any system by
3 # making InnoDB ignore the actual Punch Hole and setting the
4 # file system punch hole size (block_size or compression unit)
5 # to half the UNIV_PAGE_SIZE so that punch hole seems to work
6 # even though no space on the FS is being released.
7 #
8 SET DEFAULT_STORAGE_ENGINE=InnoDB;
9 SET GLOBAL innodb_file_per_table = ON;
10 SELECT @@innodb_file_per_table;
11 @@innodb_file_per_table
12 1
13 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
14 #
15 # Try to create a table with ZLib compression with strict mode OFF.
16 # If there is an error about Punch Hole not supported, make InnoDB
17 # think that Punch Hole is working but actually ignore the calls.
18 #
19 SET SESSION innodb_strict_mode = OFF;
20 SELECT @@innodb_strict_mode;
21 @@innodb_strict_mode
22 0
23 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
24 DROP TABLE t1;
25 #
26 # Test acceptable DDL with innodb_strict_mode=ON.
27 #
28 SET SESSION innodb_strict_mode = ON;
29 SELECT @@innodb_strict_mode;
30 @@innodb_strict_mode
31 1
32 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
33 SHOW CREATE TABLE t1;
34 Table Create Table
35 t1 CREATE TABLE `t1` (
36 `c1` int NOT NULL,
37 PRIMARY KEY (`c1`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'
39 INSERT INTO t1 VALUES(1),(2),(3),(4);
40 FLUSH TABLES t1 WITH READ LOCK;
41 UNLOCK TABLES;
42 SELECT * FROM t1;
43 c1
44 1
45 2
46 3
47 4
48 CREATE TABLE t2(c1 INT PRIMARY KEY) COMPRESSION="LZ4";
49 SHOW CREATE TABLE t2;
50 Table Create Table
51 t2 CREATE TABLE `t2` (
52 `c1` int NOT NULL,
53 PRIMARY KEY (`c1`)
54 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='LZ4'
55 INSERT INTO t2 VALUES(1),(2),(3),(4);
56 FLUSH TABLES t2 WITH READ LOCK;
57 UNLOCK TABLES;
58 SELECT * FROM t2;
59 c1
60 1
61 2
62 3
63 4
64 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' OR TABLE_NAME = 't2' ORDER BY TABLE_NAME;
65 TABLE_NAME CREATE_OPTIONS
66 t1 COMPRESSION="ZLIB"
67 t2 COMPRESSION="LZ4"
68 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t%' ORDER BY NAME;
69 NAME ROW_FORMAT SPACE_TYPE
70 test/t1 Dynamic Single
71 test/t2 Dynamic Single
72 #
73 # Bug#21687636 COMPRESSION COLUMN IN INNODB_TABLESPACES IS NOT CORRECT
74 #
75 # restart
76 SET GLOBAL innodb_file_per_table = ON;
77 SET SESSION innodb_strict_mode = ON;
78 SHOW CREATE TABLE t1;
79 Table Create Table
80 t1 CREATE TABLE `t1` (
81 `c1` int NOT NULL,
82 PRIMARY KEY (`c1`)
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'
84 SHOW CREATE TABLE t2;
85 Table Create Table
86 t2 CREATE TABLE `t2` (
87 `c1` int NOT NULL,
88 PRIMARY KEY (`c1`)
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='LZ4'
90 #
91 # Bug#21557723 Create table from the SHOW CREATE TABLE output
92 #
93 DROP TABLE t1, t2;
94 CREATE TABLE `t1` (
95 `c1` int NOT NULL,
96 PRIMARY KEY (`c1`)
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB';
98 CREATE TABLE `t2` (
99 `c1` int NOT NULL,
100 PRIMARY KEY (`c1`)
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='LZ4';
102 SHOW CREATE TABLE t1;
103 Table Create Table
104 t1 CREATE TABLE `t1` (
105 `c1` int NOT NULL,
106 PRIMARY KEY (`c1`)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'
108 SHOW CREATE TABLE t2;
109 Table Create Table
110 t2 CREATE TABLE `t2` (
111 `c1` int NOT NULL,
112 PRIMARY KEY (`c1`)
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='LZ4'
114 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' OR TABLE_NAME = 't2' ORDER BY TABLE_NAME;
115 TABLE_NAME CREATE_OPTIONS
116 t1 COMPRESSION="ZLIB"
117 t2 COMPRESSION="LZ4"
118 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t%' ORDER BY NAME;
119 NAME ROW_FORMAT SPACE_TYPE
120 test/t1 Dynamic Single
121 test/t2 Dynamic Single
122 INSERT INTO t1 VALUES(1),(2),(3),(4);
123 FLUSH TABLES t1 WITH READ LOCK;
124 UNLOCK TABLES;
125 SELECT * FROM t1;
126 c1
127 1
128 2
129 3
130 4
131 INSERT INTO t2 VALUES(1),(2),(3),(4);
132 FLUSH TABLES t2 WITH READ LOCK;
133 UNLOCK TABLES;
134 SELECT * FROM t2;
135 c1
136 1
137 2
138 3
139 4
140 DROP TABLE t2;
141 #
142 # Create a second table using CREATE TABLE LIKE and CREATE TABLE AS SELECT *
143 # starting with a table that really has Page Compression
144 #
145 CREATE TABLE t2 LIKE t1;
146 INSERT INTO t2 SELECT * FROM t1;
147 CREATE TABLE t3 AS SELECT * FROM t1;
148 CREATE DATABASE test2;
149 CREATE TABLE test2.t2 LIKE t1;
150 INSERT INTO test2.t2 SELECT * FROM t1;
151 CREATE TABLE test2.t3 AS SELECT * FROM t1;
152 SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
153 WHERE TABLE_NAME = 't2' OR TABLE_NAME = 't3' ORDER BY TABLE_SCHEMA, TABLE_NAME;
154 TABLE_SCHEMA TABLE_NAME CREATE_OPTIONS
155 test t2 COMPRESSION="ZLIB"
156 test t3
157 test2 t2 COMPRESSION="ZLIB"
158 test2 t3
159 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
160 WHERE NAME LIKE 'test%/t%' ORDER BY NAME;
161 NAME ROW_FORMAT SPACE_TYPE
162 test/t1 Dynamic Single
163 test/t2 Dynamic Single
164 test/t3 Dynamic Single
165 test2/t2 Dynamic Single
166 test2/t3 Dynamic Single
167 SHOW CREATE TABLE t2;
168 Table Create Table
169 t2 CREATE TABLE `t2` (
170 `c1` int NOT NULL,
171 PRIMARY KEY (`c1`)
172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'
173 SHOW CREATE TABLE t3;
174 Table Create Table
175 t3 CREATE TABLE `t3` (
176 `c1` int NOT NULL
177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
178 SHOW CREATE TABLE test2.t2;
179 Table Create Table
180 t2 CREATE TABLE `t2` (
181 `c1` int NOT NULL,
182 PRIMARY KEY (`c1`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'
184 SHOW CREATE TABLE test2.t3;
185 Table Create Table
186 t3 CREATE TABLE `t3` (
187 `c1` int NOT NULL
188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
189 DROP TABLE t2;
190 DROP TABLE t3;
191 DROP TABLE test2.t2;
192 DROP TABLE test2.t3;
193 DROP DATABASE test2;
194 #
195 # Change compression with ALTER TABLE
196 #
197 ALTER TABLE t1 COMPRESSION='';
198 INSERT INTO t1 VALUES(5);
199 FLUSH TABLES t1 WITH READ LOCK;
200 UNLOCK TABLES;
201 SHOW CREATE TABLE t1;
202 Table Create Table
203 t1 CREATE TABLE `t1` (
204 `c1` int NOT NULL,
205 PRIMARY KEY (`c1`)
206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
207 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
208 TABLE_NAME CREATE_OPTIONS
209 t1
210 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
211 NAME ROW_FORMAT SPACE_TYPE
212 test/t1 Dynamic Single
213 ALTER TABLE t1 COMPRESSION='LZ4';
214 INSERT INTO t1 VALUES(6);
215 FLUSH TABLES t1 WITH READ LOCK;
216 UNLOCK TABLES;
217 SHOW CREATE TABLE t1;
218 Table Create Table
219 t1 CREATE TABLE `t1` (
220 `c1` int NOT NULL,
221 PRIMARY KEY (`c1`)
222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='LZ4'
223 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
224 TABLE_NAME CREATE_OPTIONS
225 t1 COMPRESSION="LZ4"
226 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
227 NAME ROW_FORMAT SPACE_TYPE
228 test/t1 Dynamic Single
229 ALTER TABLE t1 COMPRESSION='NONE';
230 INSERT INTO t1 VALUES(7);
231 FLUSH TABLES t1 WITH READ LOCK;
232 UNLOCK TABLES;
233 SHOW CREATE TABLE t1;
234 Table Create Table
235 t1 CREATE TABLE `t1` (
236 `c1` int NOT NULL,
237 PRIMARY KEY (`c1`)
238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='NONE'
239 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
240 TABLE_NAME CREATE_OPTIONS
241 t1 COMPRESSION="NONE"
242 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
243 NAME ROW_FORMAT SPACE_TYPE
244 test/t1 Dynamic Single
245 OPTIMIZE TABLE t1;
246 Table Op Msg_type Msg_text
247 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
248 test.t1 optimize status OK
249 SHOW CREATE TABLE t1;
250 Table Create Table
251 t1 CREATE TABLE `t1` (
252 `c1` int NOT NULL,
253 PRIMARY KEY (`c1`)
254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='NONE'
255 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
256 TABLE_NAME CREATE_OPTIONS
257 t1 COMPRESSION="NONE"
258 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
259 NAME ROW_FORMAT SPACE_TYPE
260 test/t1 Dynamic Single
261 DROP TABLE t1;
262 #
263 # Test various BAD DDL with innodb_strict_mode=ON.
264 #
265 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="zlibX";
266 ERROR HY000: Table storage engine for 't1' doesn't have this option
267 SHOW WARNINGS;
268 Level Code Message
269 Warning 1112 InnoDB: Unsupported compression algorithm 'zlibX'
270 Error 1031 Table storage engine for 't1' doesn't have this option
271 SHOW CREATE TABLE t1;
272 ERROR 42S02: Table 'test.t1' doesn't exist
273 #
274 # Check for too long string
275 #
276 SET @long_str = REPEAT('ZLIB', 32768);
277 SET @s = CONCAT('CREATE TABLE t1(c1 INT) ENGINE=InnoDB, COMPRESSION="', @long_str, '"');
278 PREPARE stmt from @s;
279 EXECUTE stmt;
280 ERROR HY000: String 'ZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZL' is too long for COMPRESSION (should be no longer than 2048)
281 SHOW WARNINGS;
282 Level Code Message
283 Error 1470 String 'ZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZL' is too long for COMPRESSION (should be no longer than 2048)
284 DEALLOCATE PREPARE stmt;
285 SHOW CREATE TABLE t1;
286 ERROR 42S02: Table 'test.t1' doesn't exist
287 #
288 # Compression cannot be used with ROW_FORMAT=COMPRESSED, TEMPORARY
289 # or Shared tablespaces, either general or system.
290 #
291 # CREATE TABLE: COMPRESSION + TEMPORARY
292 CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
293 ERROR HY000: Table storage engine for 't1' doesn't have this option
294 SHOW WARNINGS;
295 Level Code Message
296 Warning 138 InnoDB: Page Compression is not supported for temporary tables
297 Error 1031 Table storage engine for 't1' doesn't have this option
298 # CREATE TABLE: COMPRESSION + TABLESPACE
299 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=s1;
300 ERROR HY000: Table storage engine for 't1' doesn't have this option
301 SHOW WARNINGS;
302 Level Code Message
303 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
304 Error 1031 Table storage engine for 't1' doesn't have this option
305 # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_system
306 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_system;
307 ERROR HY000: Table storage engine for 't1' doesn't have this option
308 SHOW WARNINGS;
309 Level Code Message
310 Warning 138 InnoDB: Page Compression is not supported for the system tablespace
311 Error 1031 Table storage engine for 't1' doesn't have this option
312 # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_temporary
313 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
314 ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
315 SHOW WARNINGS;
316 Level Code Message
317 Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
318 Error 1031 Table storage engine for 't1' doesn't have this option
319 # CREATE TABLE: COMPRESSION + TEMPORARY + TABLESPACE=innodb_temporary
320 CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
321 ERROR HY000: Table storage engine for 't1' doesn't have this option
322 SHOW WARNINGS;
323 Level Code Message
324 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
325 Warning 138 InnoDB: Page Compression is not supported for temporary tables
326 Error 1031 Table storage engine for 't1' doesn't have this option
327 # CREATE TABLE: COMPRESSION + implicit System Tablespace
328 SET GLOBAL INNODB_FILE_PER_TABLE = OFF;
329 CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="zlib";
330 ERROR HY000: Table storage engine for 't1' doesn't have this option
331 SHOW WARNINGS;
332 Level Code Message
333 Warning 138 InnoDB: Page Compression is not supported for the system tablespace
334 Error 1031 Table storage engine for 't1' doesn't have this option
335 CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="blah";
336 ERROR HY000: Table storage engine for 't1' doesn't have this option
337 SHOW WARNINGS;
338 Level Code Message
339 Warning 1112 InnoDB: Unsupported compression algorithm 'blah'
340 Error 1031 Table storage engine for 't1' doesn't have this option
341 SET GLOBAL INNODB_FILE_PER_TABLE = ON;
342 # CREATE TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
343 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
344 ERROR HY000: Table storage engine for 't1' doesn't have this option
345 SHOW WARNINGS;
346 Level Code Message
347 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
348 Error 1031 Table storage engine for 't1' doesn't have this option
349 # CREATE TABLE: COMPRESSION + KEY_BLOCK_SIZE
350 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
351 ERROR HY000: Table storage engine for 't1' doesn't have this option
352 SHOW WARNINGS;
353 Level Code Message
354 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
355 Error 1031 Table storage engine for 't1' doesn't have this option
356 # ALTER TABLE: implicit COMPRESSION + TABLESPACE
357 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
358 ALTER TABLE t1 TABLESPACE=s1;
359 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
360 SHOW WARNINGS;
361 Level Code Message
362 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
363 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
364 # ALTER TABLE: COMPRESSION + TABLESPACE
365 ALTER TABLE t1 COMPRESSION="LZ4" TABLESPACE=s1;
366 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
367 SHOW WARNINGS;
368 Level Code Message
369 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
370 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
371 # ALTER TABLE: implicit COMPRESSION + ROW_FORMAT=COMPRESSED
372 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
373 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
374 SHOW WARNINGS;
375 Level Code Message
376 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
377 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
378 # ALTER TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
379 ALTER TABLE t1 COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
380 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
381 SHOW WARNINGS;
382 Level Code Message
383 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
384 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
385 # ALTER TABLE: COMPRESSION + KEY_BLOCK_SIZE
386 ALTER TABLE t1 COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
387 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
388 SHOW WARNINGS;
389 Level Code Message
390 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
391 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
392 # ALTER TABLE: COMPRESSION='abcdefghijklmnopqrstuvwxyz'
393 ALTER TABLE t1 COMPRESSION='abcdefghijklmnopqrstuvwxyz';
394 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
395 SHOW WARNINGS;
396 Level Code Message
397 Warning 1112 InnoDB: Unsupported compression algorithm 'abcdefghijklmnopqrstuvwxyz'
398 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
399 DROP TABLE t1;
400 #
401 # Test the bad combinations above with innodb_strict_mode=OFF.
402 #
403 SET SESSION innodb_strict_mode = OFF;
404 SELECT @@innodb_strict_mode;
405 @@innodb_strict_mode
406 0
407 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="zlibX";
408 Warnings:
409 Warning 1112 InnoDB: Unsupported compression algorithm 'zlibX'
410 SHOW WARNINGS;
411 Level Code Message
412 Warning 1112 InnoDB: Unsupported compression algorithm 'zlibX'
413 SHOW CREATE TABLE t1;
414 Table Create Table
415 t1 CREATE TABLE `t1` (
416 `c1` int NOT NULL,
417 PRIMARY KEY (`c1`)
418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='zlibX'
419 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
420 TABLE_NAME CREATE_OPTIONS
421 t1 COMPRESSION="zlibX"
422 SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
423 NAME ROW_FORMAT SPACE_TYPE
424 test/t1 Dynamic Single
425 DROP TABLE t1;
426 #
427 # Check for too long string
428 #
429 SET @long_str = REPEAT('ZLIB', 32768);
430 SET @s = CONCAT('CREATE TABLE t1(c1 INT) ENGINE=InnoDB, COMPRESSION="', @long_str, '"');
431 PREPARE stmt from @s;
432 EXECUTE stmt;
433 ERROR HY000: String 'ZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZL' is too long for COMPRESSION (should be no longer than 2048)
434 SHOW WARNINGS;
435 Level Code Message
436 Error 1470 String 'ZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZLIBZL' is too long for COMPRESSION (should be no longer than 2048)
437 DEALLOCATE PREPARE stmt;
438 SHOW CREATE TABLE t1;
439 ERROR 42S02: Table 'test.t1' doesn't exist
440 #
441 # Compression cannot be used with ROW_FORMAT=COMPRESSED, TEMPORARY
442 # or Shared tablespaces, either general or system.
443 #
444 # CREATE TABLE: COMPRESSION + TEMPORARY
445 CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
446 ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version
447 SHOW WARNINGS;
448 Level Code Message
449 Warning 138 InnoDB: Page Compression is not supported for temporary tables
450 Warning 138 InnoDB: Compression not supported for temporary tables
451 Error 1112 Table 't1' uses an extension that doesn't exist in this MySQL version
452 # CREATE TABLE: COMPRESSION + TABLESPACE
453 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=s1;
454 ERROR HY000: Table storage engine for 't1' doesn't have this option
455 SHOW WARNINGS;
456 Level Code Message
457 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
458 Error 1031 Table storage engine for 't1' doesn't have this option
459 # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_system
460 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_system;
461 ERROR HY000: Table storage engine for 't1' doesn't have this option
462 SHOW WARNINGS;
463 Level Code Message
464 Warning 138 InnoDB: Page Compression is not supported for the system tablespace
465 Error 1031 Table storage engine for 't1' doesn't have this option
466 # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_temporary
467 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
468 ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
469 SHOW WARNINGS;
470 Level Code Message
471 Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
472 Error 1031 Table storage engine for 't1' doesn't have this option
473 # CREATE TABLE: COMPRESSION + TEMPORARY + TABLESPACE=innodb_temporary
474 CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
475 ERROR HY000: Table storage engine for 't1' doesn't have this option
476 SHOW WARNINGS;
477 Level Code Message
478 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
479 Warning 138 InnoDB: Page Compression is not supported for temporary tables
480 Error 1031 Table storage engine for 't1' doesn't have this option
481 # CREATE TABLE: COMPRESSION + implicit System Tablespace
482 SET GLOBAL INNODB_FILE_PER_TABLE = OFF;
483 CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="zlib";
484 ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version
485 SHOW WARNINGS;
486 Level Code Message
487 Warning 138 InnoDB: Page Compression is not supported for the system tablespace
488 Warning 138 InnoDB: Compression not supported for shared tablespaces
489 Error 1112 Table 't1' uses an extension that doesn't exist in this MySQL version
490 CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="blah";
491 ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version
492 SHOW WARNINGS;
493 Level Code Message
494 Warning 1112 InnoDB: Unsupported compression algorithm 'blah'
495 Warning 138 InnoDB: Compression not supported for shared tablespaces
496 Error 1112 Table 't1' uses an extension that doesn't exist in this MySQL version
497 SET GLOBAL INNODB_FILE_PER_TABLE = ON;
498 # CREATE TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
499 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
500 Warnings:
501 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
502 SHOW WARNINGS;
503 Level Code Message
504 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
505 SHOW CREATE TABLE t1;
506 Table Create Table
507 t1 CREATE TABLE `t1` (
508 `c1` int NOT NULL,
509 PRIMARY KEY (`c1`)
510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED COMPRESSION='ZLIB'
511 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
512 TABLE_NAME CREATE_OPTIONS
513 t1 row_format=COMPRESSED COMPRESSION="ZLIB"
514 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
515 NAME ROW_FORMAT SPACE_TYPE
516 test/t1 Compressed Single
517 INSERT INTO t1 VALUES(1),(2),(3),(4);
518 FLUSH TABLES t1 WITH READ LOCK;
519 UNLOCK TABLES;
520 SELECT * FROM t1;
521 c1
522 1
523 2
524 3
525 4
526 DROP TABLE t1;
527 # CREATE TABLE: COMPRESSION + KEY_BLOCK_SIZE
528 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
529 Warnings:
530 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
531 SHOW WARNINGS;
532 Level Code Message
533 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
534 SHOW CREATE TABLE t1;
535 Table Create Table
536 t1 CREATE TABLE `t1` (
537 `c1` int NOT NULL,
538 PRIMARY KEY (`c1`)
539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci KEY_BLOCK_SIZE=2 COMPRESSION='ZLIB'
540 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
541 TABLE_NAME CREATE_OPTIONS
542 t1 KEY_BLOCK_SIZE=2 COMPRESSION="ZLIB"
543 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
544 NAME ROW_FORMAT SPACE_TYPE
545 test/t1 Compressed Single
546 INSERT INTO t1 VALUES(1),(2),(3),(4);
547 FLUSH TABLES t1 WITH READ LOCK;
548 UNLOCK TABLES;
549 SELECT * FROM t1;
550 c1
551 1
552 2
553 3
554 4
555 DROP TABLE t1;
556 # ALTER TABLE: implicit COMPRESSION + TABLESPACE
557 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
558 ALTER TABLE t1 TABLESPACE=s1;
559 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
560 SHOW WARNINGS;
561 Level Code Message
562 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
563 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
564 # ALTER TABLE: COMPRESSION + TABLESPACE
565 ALTER TABLE t1 COMPRESSION="LZ4" TABLESPACE=s1;
566 ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
567 SHOW WARNINGS;
568 Level Code Message
569 Warning 138 InnoDB: Page Compression is not supported for shared general tablespaces
570 Error 1478 Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'
571 # ALTER TABLE: implicit COMPRESSION + ROW_FORMAT=COMPRESSED
572 ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
573 Warnings:
574 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
575 SHOW WARNINGS;
576 Level Code Message
577 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
578 SHOW CREATE TABLE t1;
579 Table Create Table
580 t1 CREATE TABLE `t1` (
581 `c1` int NOT NULL,
582 PRIMARY KEY (`c1`)
583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED COMPRESSION='ZLIB'
584 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
585 TABLE_NAME CREATE_OPTIONS
586 t1 row_format=COMPRESSED COMPRESSION="ZLIB"
587 SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
588 NAME ROW_FORMAT SPACE_TYPE
589 test/t1 Compressed Single
590 # ALTER TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
591 ALTER TABLE t1 COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
592 Warnings:
593 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
594 SHOW WARNINGS;
595 Level Code Message
596 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
597 SHOW CREATE TABLE t1;
598 Table Create Table
599 t1 CREATE TABLE `t1` (
600 `c1` int NOT NULL,
601 PRIMARY KEY (`c1`)
602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED COMPRESSION='ZLIB'
603 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
604 TABLE_NAME CREATE_OPTIONS
605 t1 row_format=COMPRESSED COMPRESSION="ZLIB"
606 SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
607 NAME ROW_FORMAT SPACE_TYPE
608 test/t1 Compressed Single
609 # ALTER TABLE: COMPRESSION + KEY_BLOCK_SIZE
610 ALTER TABLE t1 COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
611 Warnings:
612 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
613 SHOW WARNINGS;
614 Level Code Message
615 Warning 1112 InnoDB: Page Compression is not supported with row_format=compressed or key_block_size > 0
616 SHOW CREATE TABLE t1;
617 Table Create Table
618 t1 CREATE TABLE `t1` (
619 `c1` int NOT NULL,
620 PRIMARY KEY (`c1`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 COMPRESSION='ZLIB'
622 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
623 TABLE_NAME CREATE_OPTIONS
624 t1 row_format=COMPRESSED KEY_BLOCK_SIZE=2 COMPRESSION="ZLIB"
625 SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
626 NAME ROW_FORMAT SPACE_TYPE
627 test/t1 Compressed Single
628 # ALTER TABLE: COMPRESSION='abcdefghijklmnopqrstuvwxyz'
629 ALTER TABLE t1 COMPRESSION='abcdefghijklmnopqrstuvwxyz';
630 Warnings:
631 Warning 1112 InnoDB: Unsupported compression algorithm 'abcdefghijklmnopqrstuvwxyz'
632 SHOW WARNINGS;
633 Level Code Message
634 Warning 1112 InnoDB: Unsupported compression algorithm 'abcdefghijklmnopqrstuvwxyz'
635 SHOW CREATE TABLE t1;
636 Table Create Table
637 t1 CREATE TABLE `t1` (
638 `c1` int NOT NULL,
639 PRIMARY KEY (`c1`)
640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 COMPRESSION='abcdefghijklmnopqrstuvwxyz'
641 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
642 TABLE_NAME CREATE_OPTIONS
643 t1 row_format=COMPRESSED KEY_BLOCK_SIZE=2 COMPRESSION="abcdefg"
644 SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
645 NAME ROW_FORMAT SPACE_TYPE
646 test/t1 Compressed Single
647 DROP TABLE t1;
648 #
649 # BUG#22916982 - ASSERT 0 IN FSP_SDI_GET_ROOT_PAGE_NUM(), FLUSH TABLES FOR EXPORT
650 #
651 CREATE TABLE t1 (a INT) ENGINE=INNODB ROW_FORMAT=DYNAMIC COMPRESSION='ZLIB';
652 FLUSH TABLE t1 FOR EXPORT;
653 # restart
654 FLUSH TABLE t1 FOR EXPORT;
655 UNLOCK TABLES;
656 DROP TABLE t1;
657 #
658 # Cleanup
659 #
660 DROP TABLESPACE s1;
661 SET GLOBAL INNODB_FILE_PER_TABLE=1;
662 SET GLOBAL INNODB_FILE_PER_TABLE=1;
663 SET DEBUG='-d,ignore_punch_hole';
664 SET DEBUG='-d,ignore_punch_hole_size';