polardbxengine/mysql-test/suite/innodb/t/table_compress.test

--echo #
--echo # Test support for the COMPRESSION syntax on any system by
--echo # making InnoDB ignore the actual Punch Hole and setting the
--echo # file system punch hole size (block_size or compression unit)
--echo # to half the UNIV_PAGE_SIZE so that punch hole seems to work
--echo # even though no space on the FS is being released.
--echo #

--source include/have_innodb_max_16k.inc

SET DEFAULT_STORAGE_ENGINE=InnoDB;

LET MYSQLD_DATADIR = `SELECT @@datadir`;

LET $prev_innodb_file_per_table = `SELECT @@innodb_file_per_table`;
SET GLOBAL innodb_file_per_table = ON;
SELECT @@innodb_file_per_table;

LET $prev_innodb_strict_mode = `SELECT @@innodb_strict_mode`;

CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';

--echo #
--echo # Try to create a table with ZLib compression with strict mode OFF.
--echo # If there is an error about Punch Hole not supported, make InnoDB
--echo # think that Punch Hole is working but actually ignore the calls.
--echo #
SET SESSION innodb_strict_mode = OFF;
SELECT @@innodb_strict_mode;

--disable_warnings
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
let COMPR_ZIP_WARN= `SHOW WARNINGS`;
--enable_warnings

perl;
  use strict;
  my $no_holes = ($ENV{COMPR_ZIP_WARN} =~ /Punch hole not supported/)? 1 : 0;
### we do not expect any other warning
  printf("Unexpected warning: %s\n",$ENV{COMPR_ZIP_WARN})
    if (not $no_holes and $ENV{COMPR_ZIP_WARN} ne '');
  open(DHF,">$ENV{'MYSQLD_DATADIR'}/compr.inc");
  printf DHF "let \$no_holes= %s;\n",$no_holes;
  close(DHF);
EOF

--source $MYSQLD_DATADIR/compr.inc
--remove_file $MYSQLD_DATADIR/compr.inc
LET IGNORE_PUNCH_HOLE=1;
if ($no_holes)
{
  --source include/have_debug.inc
  --disable_query_log
  LET IGNORE_PUNCH_HOLE=1;
  SET DEBUG='+d,ignore_punch_hole';
  --enable_query_log
}
DROP TABLE t1;

--echo #
--echo # Test acceptable DDL with innodb_strict_mode=ON.
--echo #
SET SESSION innodb_strict_mode = ON;
SELECT @@innodb_strict_mode;

CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(1),(2),(3),(4);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t1;

CREATE TABLE t2(c1 INT PRIMARY KEY) COMPRESSION="LZ4";
SHOW CREATE TABLE t2;
INSERT INTO t2 VALUES(1),(2),(3),(4);
FLUSH TABLES t2 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t2;

SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' OR TABLE_NAME = 't2' ORDER BY TABLE_NAME;
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t%' ORDER BY NAME;

--echo #
--echo # Bug#21687636 COMPRESSION COLUMN IN INNODB_TABLESPACES IS NOT CORRECT
--echo #
--source include/restart_mysqld.inc
IF ($IGNORE_PUNCH_HOLE)
{
  --disable_query_log
  SET DEBUG='+d,ignore_punch_hole';
  --enable_query_log
}
SET GLOBAL innodb_file_per_table = ON;
SET SESSION innodb_strict_mode = ON;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

--echo #
--echo # Bug#21557723 Create table from the SHOW CREATE TABLE output
--echo #
let $create1 = query_get_value(SHOW CREATE TABLE t1, Create Table, 1);
let $create2 = query_get_value(SHOW CREATE TABLE t2, Create Table, 1);
DROP TABLE t1, t2;
eval $create1;
eval $create2;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' OR TABLE_NAME = 't2' ORDER BY TABLE_NAME;
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t%' ORDER BY NAME;
INSERT INTO t1 VALUES(1),(2),(3),(4);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t1;
INSERT INTO t2 VALUES(1),(2),(3),(4);
FLUSH TABLES t2 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t2;
DROP TABLE t2;

--echo #
--echo # Create a second table using CREATE TABLE LIKE and CREATE TABLE AS SELECT *
--echo # starting with a table that really has Page Compression
--echo #
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE DATABASE test2;
CREATE TABLE test2.t2 LIKE t1;
INSERT INTO test2.t2 SELECT * FROM t1;
CREATE TABLE test2.t3 AS SELECT * FROM t1;

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME = 't2' OR TABLE_NAME = 't3' ORDER BY TABLE_SCHEMA, TABLE_NAME;
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE NAME LIKE 'test%/t%' ORDER BY NAME;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SHOW CREATE TABLE test2.t2;
SHOW CREATE TABLE test2.t3;

DROP TABLE t2;
DROP TABLE t3;
DROP TABLE test2.t2;
DROP TABLE test2.t3;
DROP DATABASE test2;

--echo #
--echo # Change compression with ALTER TABLE
--echo #
ALTER TABLE t1 COMPRESSION='';
INSERT INTO t1 VALUES(5);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

ALTER TABLE t1 COMPRESSION='LZ4';
INSERT INTO t1 VALUES(6);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

ALTER TABLE t1 COMPRESSION='NONE';
INSERT INTO t1 VALUES(7);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

OPTIMIZE TABLE t1;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

DROP TABLE t1;

--echo #
--echo # Test various BAD DDL with innodb_strict_mode=ON.
--echo #

--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="zlibX";
SHOW WARNINGS;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t1;

--echo #
--echo # Check for too long string
--echo #
SET @long_str = REPEAT('ZLIB', 32768);
SET @s = CONCAT('CREATE TABLE t1(c1 INT) ENGINE=InnoDB, COMPRESSION="', @long_str, '"');
PREPARE stmt from @s;
--error ER_WRONG_STRING_LENGTH
EXECUTE stmt;
SHOW WARNINGS;
DEALLOCATE PREPARE stmt;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t1;

--echo #
--echo # Compression cannot be used with ROW_FORMAT=COMPRESSED, TEMPORARY
--echo # or Shared tablespaces, either general or system.
--echo #

--echo # CREATE TABLE: COMPRESSION + TEMPORARY
--error ER_ILLEGAL_HA
CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=s1;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_system
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_system;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_temporary
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TEMPORARY + TABLESPACE=innodb_temporary
--error ER_ILLEGAL_HA
CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + implicit System Tablespace
SET GLOBAL INNODB_FILE_PER_TABLE = OFF;
--error ER_ILLEGAL_HA
CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="zlib";
SHOW WARNINGS;
--error ER_ILLEGAL_HA
CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="blah";
SHOW WARNINGS;
SET GLOBAL INNODB_FILE_PER_TABLE = ON;

--echo # CREATE TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + KEY_BLOCK_SIZE
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
SHOW WARNINGS;

--echo # ALTER TABLE: implicit COMPRESSION + TABLESPACE
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 TABLESPACE=s1;
SHOW WARNINGS;

--echo # ALTER TABLE: COMPRESSION + TABLESPACE
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 COMPRESSION="LZ4" TABLESPACE=s1;
SHOW WARNINGS;

--echo # ALTER TABLE: implicit COMPRESSION + ROW_FORMAT=COMPRESSED
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;

--echo # ALTER TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;

--echo # ALTER TABLE: COMPRESSION + KEY_BLOCK_SIZE
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
SHOW WARNINGS;

--echo # ALTER TABLE: COMPRESSION='abcdefghijklmnopqrstuvwxyz'
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 COMPRESSION='abcdefghijklmnopqrstuvwxyz';
SHOW WARNINGS;
DROP TABLE t1;

--echo #
--echo # Test the bad combinations above with innodb_strict_mode=OFF.
--echo #
SET SESSION innodb_strict_mode = OFF;
SELECT @@innodb_strict_mode;

CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="zlibX";
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
DROP TABLE t1;

--echo #
--echo # Check for too long string
--echo #
SET @long_str = REPEAT('ZLIB', 32768);
SET @s = CONCAT('CREATE TABLE t1(c1 INT) ENGINE=InnoDB, COMPRESSION="', @long_str, '"');
PREPARE stmt from @s;
--error ER_WRONG_STRING_LENGTH
EXECUTE stmt;
SHOW WARNINGS;
DEALLOCATE PREPARE stmt;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t1;

--echo #
--echo # Compression cannot be used with ROW_FORMAT=COMPRESSED, TEMPORARY
--echo # or Shared tablespaces, either general or system.
--echo #

--echo # CREATE TABLE: COMPRESSION + TEMPORARY
--error ER_UNSUPPORTED_EXTENSION
CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=s1;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_system
--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_system;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TABLESPACE=innodb_temporary
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + TEMPORARY + TABLESPACE=innodb_temporary
--error ER_ILLEGAL_HA
CREATE TEMPORARY TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" TABLESPACE=innodb_temporary;
SHOW WARNINGS;

--echo # CREATE TABLE: COMPRESSION + implicit System Tablespace
SET GLOBAL INNODB_FILE_PER_TABLE = OFF;
--error ER_UNSUPPORTED_EXTENSION
CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="zlib";
SHOW WARNINGS;
--error ER_UNSUPPORTED_EXTENSION
CREATE TABLE t1(C1 INT) ENGINE=InnoDB COMPRESSION="blah";
SHOW WARNINGS;
SET GLOBAL INNODB_FILE_PER_TABLE = ON;

--echo # CREATE TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
INSERT INTO t1 VALUES(1),(2),(3),(4);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t1;
DROP TABLE t1;

--echo # CREATE TABLE: COMPRESSION + KEY_BLOCK_SIZE
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
INSERT INTO t1 VALUES(1),(2),(3),(4);
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
SELECT * FROM t1;
DROP TABLE t1;

--echo # ALTER TABLE: implicit COMPRESSION + TABLESPACE
CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION="ZLIB";
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 TABLESPACE=s1;
SHOW WARNINGS;

--echo # ALTER TABLE: COMPRESSION + TABLESPACE
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 COMPRESSION="LZ4" TABLESPACE=s1;
SHOW WARNINGS;

--echo # ALTER TABLE: implicit COMPRESSION + ROW_FORMAT=COMPRESSED
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

--echo # ALTER TABLE: COMPRESSION + ROW_FORMAT=COMPRESSED
ALTER TABLE t1 COMPRESSION="ZLIB" ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

--echo # ALTER TABLE: COMPRESSION + KEY_BLOCK_SIZE
ALTER TABLE t1 COMPRESSION="ZLIB" KEY_BLOCK_SIZE=2;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';

--echo # ALTER TABLE: COMPRESSION='abcdefghijklmnopqrstuvwxyz'
ALTER TABLE t1 COMPRESSION='abcdefghijklmnopqrstuvwxyz';
SHOW WARNINGS;
SHOW CREATE TABLE t1;
SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
SELECT NAME,ROW_FORMAT,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'test/t1';
DROP TABLE t1;

--echo #
--echo # BUG#22916982 - ASSERT 0 IN FSP_SDI_GET_ROOT_PAGE_NUM(), FLUSH TABLES FOR EXPORT
--echo #

CREATE TABLE t1 (a INT) ENGINE=INNODB ROW_FORMAT=DYNAMIC COMPRESSION='ZLIB';
FLUSH TABLE t1 FOR EXPORT;
--source include/restart_mysqld.inc
FLUSH TABLE t1 FOR EXPORT;
UNLOCK TABLES;
DROP TABLE t1;

--echo #
--echo # Cleanup
--echo #
DROP TABLESPACE s1;
eval SET GLOBAL INNODB_FILE_PER_TABLE=$prev_innodb_file_per_table;
eval SET GLOBAL INNODB_FILE_PER_TABLE=$prev_innodb_strict_mode;
SET DEBUG='-d,ignore_punch_hole';
SET DEBUG='-d,ignore_punch_hole_size';