697 lines
39 KiB
Plaintext
697 lines
39 KiB
Plaintext
#
|
|
# CREATE TABLESPACE related tests for 4k page sizes.
|
|
#
|
|
SET DEFAULT_STORAGE_ENGINE=InnoDB;
|
|
# Strict-mode has no effect on CREATE TABLESPACE. But it does affect
|
|
# whether an invalid KEY_BLOCK_SIZE is rejected or adjusted.
|
|
SHOW VARIABLES LIKE 'innodb_strict_mode';
|
|
Variable_name Value
|
|
innodb_strict_mode ON
|
|
SHOW VARIABLES LIKE 'innodb_file_per_table';
|
|
Variable_name Value
|
|
innodb_file_per_table ON
|
|
#
|
|
# Create a tablespace with compressed page sizes that can match
|
|
# innodb-page-size.
|
|
#
|
|
CREATE TABLESPACE s_1k ADD DATAFILE 's_1k.ibd' FILE_BLOCK_SIZE=1k;
|
|
CREATE TABLESPACE s_2k ADD DATAFILE 's_2k.ibd' FILE_BLOCK_SIZE=2k;
|
|
CREATE TABLESPACE s_4k ADD DATAFILE 's_4k.ibd' FILE_BLOCK_SIZE=4k;
|
|
CREATE TABLESPACE s_8k ADD DATAFILE 's_8k.ibd' FILE_BLOCK_SIZE=8k;
|
|
ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=8192 because INNODB_PAGE_SIZE=4096.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=8192 because INNODB_PAGE_SIZE=4096.
|
|
Error 1528 Failed to create TABLESPACE s_8k
|
|
Error 1031 Table storage engine for 's_8k' doesn't have this option
|
|
CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
|
|
ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=4096.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=16384 because INNODB_PAGE_SIZE=4096.
|
|
Error 1528 Failed to create TABLESPACE s_16k
|
|
Error 1031 Table storage engine for 's_16k' doesn't have this option
|
|
CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
|
|
ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=4096.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=4096.
|
|
Error 1528 Failed to create TABLESPACE s_32k
|
|
Error 1031 Table storage engine for 's_32k' doesn't have this option
|
|
CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
|
|
ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=4096.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=4096.
|
|
Error 1528 Failed to create TABLESPACE s_64k
|
|
Error 1031 Table storage engine for 's_64k' doesn't have this option
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
#
|
|
# Try to put a temporary table into a non-temporary compressed tablespace
|
|
#
|
|
CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` cannot contain TEMPORARY tables.
|
|
Error 1031 Table storage engine for 't_temp_zip' doesn't have this option
|
|
#
|
|
# Add tables to the 1K tablespace.
|
|
#
|
|
CREATE TABLE t_zip1k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_1k;
|
|
CREATE TABLE t_zip2k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
Error 1031 Table storage engine for 't_zip2k_in_1k' doesn't have this option
|
|
CREATE TABLE t_zip4k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_zip4k_in_1k' doesn't have this option
|
|
CREATE TABLE t_zip8k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 8192
|
|
Error 1031 Table storage engine for 't_zip8k_in_1k' doesn't have this option
|
|
CREATE TABLE t_zip16k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 16384
|
|
Error 1031 Table storage engine for 't_zip16k_in_1k' doesn't have this option
|
|
CREATE TABLE t_zip32k_in_1k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 32768
|
|
Error 1031 Table storage engine for 't_zip32k_in_1k' doesn't have this option
|
|
CREATE TABLE t_red_in_1k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_red_in_1k' doesn't have this option
|
|
CREATE TABLE t_cmp_in_1k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_cmp_in_1k' doesn't have this option
|
|
CREATE TABLE t_dyn_in_1k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_1k;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_dyn_in_1k' doesn't have this option
|
|
#
|
|
# Add tables to the 2K tablespace.
|
|
#
|
|
CREATE TABLE t_zip1k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
Error 1031 Table storage engine for 't_zip1k_in_2k' doesn't have this option
|
|
CREATE TABLE t_zip2k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_2k;
|
|
CREATE TABLE t_zip4k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_zip4k_in_2k' doesn't have this option
|
|
CREATE TABLE t_zip8k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 8192
|
|
Error 1031 Table storage engine for 't_zip8k_in_2k' doesn't have this option
|
|
CREATE TABLE t_zip16k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 16384
|
|
Error 1031 Table storage engine for 't_zip16k_in_2k' doesn't have this option
|
|
CREATE TABLE t_zip32k_in_2k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 32768
|
|
Error 1031 Table storage engine for 't_zip32k_in_2k' doesn't have this option
|
|
CREATE TABLE t_red_in_2k (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_red_in_2k' doesn't have this option
|
|
CREATE TABLE t_cmp_in_2k (a int, b text) ROW_FORMAT=Compact TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_cmp_in_2k' doesn't have this option
|
|
CREATE TABLE t_dyn_in_2k (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_2k;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1031 Table storage engine for 't_dyn_in_2k' doesn't have this option
|
|
#
|
|
# Add tables to the 4K tablespace.
|
|
#
|
|
CREATE TABLE t_zip1k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip1k_in_4k' doesn't have this option
|
|
CREATE TABLE t_zip2k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip2k_in_4k' doesn't have this option
|
|
CREATE TABLE t_zip4k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip4k_in_4k' doesn't have this option
|
|
CREATE TABLE t_zip8k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip8k_in_4k' doesn't have this option
|
|
CREATE TABLE t_zip16k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip16k_in_4k' doesn't have this option
|
|
CREATE TABLE t_zip32k_in_4k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_4k;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1031 Table storage engine for 't_zip32k_in_4k' doesn't have this option
|
|
CREATE TABLE t_red_in_4k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_4k;
|
|
CREATE TABLE t_com_in_4k (a int, b text) ROW_FORMAT=compact TABLESPACE s_4k;
|
|
CREATE TABLE t_dyn_in_4k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_4k;
|
|
# Add data to the existing Tables
|
|
INSERT INTO t_zip1k_in_1k VALUES (1,'a'),(2,'b'),(3,'c');
|
|
INSERT INTO t_zip2k_in_2k VALUES (1,'a'),(2,'b'),(3,'c');
|
|
INSERT INTO t_red_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
|
|
INSERT INTO t_com_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
|
|
INSERT INTO t_dyn_in_4k VALUES (1,'a'),(2,'b'),(3,'c');
|
|
# Restart mysqld
|
|
# restart
|
|
#
|
|
# Try to drop a tablespace which is not empty
|
|
#
|
|
DROP TABLESPACE s_4k;
|
|
ERROR HY000: Tablespace `s_4k` is not empty.
|
|
#
|
|
# Add more data to the existing Tables
|
|
#
|
|
INSERT INTO t_zip1k_in_1k VALUES (4,'d');
|
|
INSERT INTO t_zip2k_in_2k VALUES (4,'d');
|
|
INSERT INTO t_red_in_4k VALUES (4,'d');
|
|
INSERT INTO t_com_in_4k VALUES (4,'d');
|
|
INSERT INTO t_dyn_in_4k VALUES (4,'d');
|
|
#
|
|
# Create an empty copy of each table using LIKE
|
|
#
|
|
CREATE TABLE t_zip1k_in_1k_like LIKE t_zip1k_in_1k;
|
|
CREATE TABLE t_zip2k_in_2k_like LIKE t_zip2k_in_2k;
|
|
CREATE TABLE t_red_in_4k_like LIKE t_red_in_4k;
|
|
CREATE TABLE t_com_in_4k_like LIKE t_com_in_4k;
|
|
CREATE TABLE t_dyn_in_4k_like LIKE t_dyn_in_4k;
|
|
#
|
|
# Create a full copy of each table using AS
|
|
#
|
|
CREATE TABLE t_zip1k_in_1k_as TABLESPACE=s_1k KEY_BLOCK_SIZE=1 AS (SELECT * FROM t_zip1k_in_1k);
|
|
CREATE TABLE t_zip2k_in_2k_as TABLESPACE=s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip2k_in_2k);
|
|
CREATE TABLE t_red_in_4k_as TABLESPACE=s_4k ROW_FORMAT=redundant AS (SELECT * FROM t_red_in_4k);
|
|
CREATE TABLE t_com_in_4k_as TABLESPACE=s_4k ROW_FORMAT=compact AS (SELECT * FROM t_com_in_4k);
|
|
CREATE TABLE t_dyn_in_4k_as TABLESPACE=s_4k ROW_FORMAT=dynamic AS (SELECT * FROM t_dyn_in_4k);
|
|
#
|
|
# Create a copy of the 1k table, moving it to another tablespace with a different
|
|
# FILE_BLOCK_SIZE, while changing the KEY_BLOCK_SIZE.
|
|
#
|
|
CREATE TABLE t_zip2k_in_2k_from_1k TABLESPACE s_2k KEY_BLOCK_SIZE=2 AS (SELECT * FROM t_zip1k_in_1k);
|
|
CREATE TABLE t_red_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=redundant AS (SELECT * FROM t_zip1k_in_1k);
|
|
CREATE TABLE t_com_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=compact AS (SELECT * FROM t_zip1k_in_1k);
|
|
CREATE TABLE t_dyn_in_4k_from_1k TABLESPACE s_4k ROW_FORMAT=dynamic AS (SELECT * FROM t_zip1k_in_1k);
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_com_in_4k s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_as s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_from_1k s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_like s_4k 129 8 Compact 0 General
|
|
test/t_dyn_in_4k s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_as s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_from_1k s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_like s_4k 161 8 Dynamic 0 General
|
|
test/t_red_in_4k s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_as s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_from_1k s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_like s_4k 128 8 Redundant 0 General
|
|
test/t_zip1k_in_1k s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_in_1k_as s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_in_1k_like s_1k 163 8 Compressed 1024 General
|
|
test/t_zip2k_in_2k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_as s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_from_1k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_like s_2k 165 8 Compressed 2048 General
|
|
# Directory of MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
s_1k.ibd
|
|
s_2k.ibd
|
|
s_4k.ibd
|
|
# Directory of MYSQLD_DATADIR/test/
|
|
#
|
|
# Restart the server and make sure that everything is OK.
|
|
#
|
|
# restart
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_com_in_4k s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_as s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_from_1k s_4k 129 8 Compact 0 General
|
|
test/t_com_in_4k_like s_4k 129 8 Compact 0 General
|
|
test/t_dyn_in_4k s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_as s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_from_1k s_4k 161 8 Dynamic 0 General
|
|
test/t_dyn_in_4k_like s_4k 161 8 Dynamic 0 General
|
|
test/t_red_in_4k s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_as s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_from_1k s_4k 128 8 Redundant 0 General
|
|
test/t_red_in_4k_like s_4k 128 8 Redundant 0 General
|
|
test/t_zip1k_in_1k s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_in_1k_as s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_in_1k_like s_1k 163 8 Compressed 1024 General
|
|
test/t_zip2k_in_2k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_as s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_from_1k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_in_2k_like s_2k 165 8 Compressed 2048 General
|
|
# Directory of MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
s_1k.ibd
|
|
s_2k.ibd
|
|
s_4k.ibd
|
|
# Directory of MYSQLD_DATADIR/test/
|
|
#
|
|
# Clean-up tables we no longer need
|
|
#
|
|
DROP TABLE t_zip1k_in_1k_like;
|
|
DROP TABLE t_zip2k_in_2k_like;
|
|
DROP TABLE t_red_in_4k_like;
|
|
DROP TABLE t_com_in_4k_like;
|
|
DROP TABLE t_dyn_in_4k_like;
|
|
DROP TABLE t_zip1k_in_1k_as;
|
|
DROP TABLE t_zip2k_in_2k_as;
|
|
DROP TABLE t_red_in_4k_as;
|
|
DROP TABLE t_com_in_4k_as;
|
|
DROP TABLE t_dyn_in_4k_as;
|
|
DROP TABLE t_zip2k_in_2k_from_1k;
|
|
DROP TABLE t_red_in_4k_from_1k;
|
|
DROP TABLE t_com_in_4k_from_1k;
|
|
DROP TABLE t_dyn_in_4k_from_1k;
|
|
CHECK TABLE t_zip1k_in_1k;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip1k_in_1k check status OK
|
|
CHECK TABLE t_zip2k_in_2k;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip2k_in_2k check status OK
|
|
CHECK TABLE t_red_in_4k;
|
|
Table Op Msg_type Msg_text
|
|
test.t_red_in_4k check status OK
|
|
CHECK TABLE t_com_in_4k;
|
|
Table Op Msg_type Msg_text
|
|
test.t_com_in_4k check status OK
|
|
CHECK TABLE t_dyn_in_4k;
|
|
Table Op Msg_type Msg_text
|
|
test.t_dyn_in_4k check status OK
|
|
DROP TABLE t_zip1k_in_1k;
|
|
DROP TABLE t_zip2k_in_2k;
|
|
DROP TABLE t_red_in_4k;
|
|
DROP TABLE t_com_in_4k;
|
|
DROP TABLE t_dyn_in_4k;
|
|
#
|
|
# Create compressed tables explicitly as file_per_table tablespaces.
|
|
#
|
|
CREATE TABLE t_zip1k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=1;
|
|
CREATE TABLE t_zip2k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=2;
|
|
CREATE TABLE t_zip4k_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compressed KEY_BLOCK_SIZE=4;
|
|
#
|
|
# Create compressed tables in remote locations using file-per-table.
|
|
#
|
|
CREATE TABLE t_zip1k_as_remote (a int, b text) KEY_BLOCK_SIZE=1 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
|
|
CREATE TABLE t_zip2k_as_remote (a int, b text) KEY_BLOCK_SIZE=2 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
|
|
CREATE TABLE t_zip4k_as_remote (a int, b text) KEY_BLOCK_SIZE=4 TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
test/t_zip1k_as_file_per_table Single DEFAULT 1024 Compressed MYSQLD_DATADIR/test/t_zip1k_as_file_per_table.ibd
|
|
test/t_zip1k_as_remote Single DEFAULT 1024 Compressed MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
|
|
test/t_zip2k_as_file_per_table Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t_zip2k_as_file_per_table.ibd
|
|
test/t_zip2k_as_remote Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
|
|
test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
|
|
test/t_zip4k_as_remote Single DEFAULT DEFAULT Compressed MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
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
|
|
test/t_zip1k_as_remote TABLESPACE InnoDB NORMAL test/t_zip1k_as_remote MYSQL_TMP_DIR/test/t_zip1k_as_remote.ibd
|
|
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
|
|
test/t_zip2k_as_remote TABLESPACE InnoDB NORMAL test/t_zip2k_as_remote MYSQL_TMP_DIR/test/t_zip2k_as_remote.ibd
|
|
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
|
|
test/t_zip4k_as_remote TABLESPACE InnoDB NORMAL test/t_zip4k_as_remote MYSQL_TMP_DIR/test/t_zip4k_as_remote.ibd
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_zip1k_as_file_per_table test/t_zip1k_as_file_per_table 35 8 Compressed 1024 Single
|
|
test/t_zip1k_as_remote test/t_zip1k_as_remote 99 8 Compressed 1024 Single
|
|
test/t_zip2k_as_file_per_table test/t_zip2k_as_file_per_table 37 8 Compressed 2048 Single
|
|
test/t_zip2k_as_remote test/t_zip2k_as_remote 101 8 Compressed 2048 Single
|
|
test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 8 Compressed 4096 Single
|
|
test/t_zip4k_as_remote test/t_zip4k_as_remote 103 8 Compressed 4096 Single
|
|
# MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
s_1k.ibd
|
|
s_2k.ibd
|
|
s_4k.ibd
|
|
# MYSQLD_DATADIR/test/
|
|
t_zip1k_as_file_per_table.ibd
|
|
t_zip2k_as_file_per_table.ibd
|
|
t_zip4k_as_file_per_table.ibd
|
|
#
|
|
# These file_per_table tables cannot be moved to a general tablespace if the
|
|
# FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
|
|
# not compressed.
|
|
#
|
|
ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_1k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_1k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_1k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_1k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 4096
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_2k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip4k_as_file_per_table` TABLESPACE=`s_2k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_2k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip4k_as_remote` TABLESPACE=`s_2k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 4096
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
DROP TABLE `t_zip4k_as_remote`;
|
|
#
|
|
# These explicit file_per_table tables can be moved to a general tablespace
|
|
# if FILE_BLOCK_SIZE matches KEY_BLOCK_SIZE and the tablespace is compressed.
|
|
#
|
|
ALTER TABLE `t_zip1k_as_file_per_table` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_in_s_1k`;
|
|
ALTER TABLE `t_zip2k_as_file_per_table` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_in_s_2k`;
|
|
ALTER TABLE `t_zip1k_as_remote` TABLESPACE=`s_1k`, RENAME TO `t_zip1k_remote_in_s_1k`;
|
|
ALTER TABLE `t_zip2k_as_remote` TABLESPACE=`s_2k`, RENAME TO `t_zip2k_remote_in_s_2k`;
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
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
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_zip1k_in_s_1k s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_remote_in_s_1k s_1k 163 8 Compressed 1024 General
|
|
test/t_zip2k_in_s_2k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_remote_in_s_2k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 8 Compressed 4096 Single
|
|
# MYSQLD_DATADIR/
|
|
lizard.ibd
|
|
mysql.ibd
|
|
s_1k.ibd
|
|
s_2k.ibd
|
|
s_4k.ibd
|
|
# MYSQLD_DATADIR/test/
|
|
t_zip4k_as_file_per_table.ibd
|
|
CHECK TABLE `t_zip1k_in_s_1k`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip1k_in_s_1k check status OK
|
|
CHECK TABLE `t_zip2k_in_s_2k`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip2k_in_s_2k check status OK
|
|
CHECK TABLE `t_zip1k_remote_in_s_1k`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip1k_remote_in_s_1k check status OK
|
|
CHECK TABLE `t_zip2k_remote_in_s_2k`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip2k_remote_in_s_2k check status OK
|
|
#
|
|
# Tables in general tablespaces cannot be moved to another general tablespace if the
|
|
# FILE_BLOCK_SIZE does not match the KEY_BLOCK_SIZE or if the tablespace is
|
|
# not compressed.
|
|
#
|
|
ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_1k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_1k` uses block size 1024 and cannot contain a table with physical page size 2048
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_2k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_2k` uses block size 2048 and cannot contain a table with physical page size 1024
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`s_4k`;
|
|
ERROR HY000: InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1478 InnoDB: Tablespace `s_4k` cannot contain a COMPRESSED table
|
|
Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
|
|
#
|
|
# Tables in a general tablespace can be moved to file_per_table locations.
|
|
#
|
|
ALTER TABLE `t_zip1k_in_s_1k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip1k_to_file_per_table`;
|
|
ALTER TABLE `t_zip2k_in_s_2k` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_zip2k_to_file_per_table`;
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
test/t_zip1k_to_file_per_table Single DEFAULT 1024 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
|
|
test/t_zip2k_to_file_per_table Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
|
|
test/t_zip4k_as_file_per_table Single DEFAULT DEFAULT Compressed MYSQLD_DATADIR/test/t_zip4k_as_file_per_table.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
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
|
|
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
|
|
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
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_zip1k_remote_in_s_1k s_1k 163 8 Compressed 1024 General
|
|
test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 8 Compressed 1024 Single
|
|
test/t_zip2k_remote_in_s_2k s_2k 165 8 Compressed 2048 General
|
|
test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 8 Compressed 2048 Single
|
|
test/t_zip4k_as_file_per_table test/t_zip4k_as_file_per_table 39 8 Compressed 4096 Single
|
|
CHECK TABLE `t_zip1k_to_file_per_table`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip1k_to_file_per_table check status OK
|
|
CHECK TABLE `t_zip2k_to_file_per_table`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip2k_to_file_per_table check status OK
|
|
CHECK TABLE `t_zip4k_as_file_per_table`;
|
|
Table Op Msg_type Msg_text
|
|
test.t_zip4k_as_file_per_table check status OK
|
|
DROP TABLE `t_zip1k_to_file_per_table`;
|
|
DROP TABLE `t_zip2k_to_file_per_table`;
|
|
DROP TABLE `t_zip4k_as_file_per_table`;
|
|
#
|
|
# Tables in a general tablespace cannot be moved to remote file_per_table locations
|
|
# because MySQL ignores DATA DIRECTORY in ALTER TABLE.
|
|
#
|
|
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`;
|
|
Warnings:
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
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`;
|
|
Warnings:
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k General DEFAULT 1024 Compressed s_1k.ibd
|
|
s_2k General DEFAULT 2048 Compressed s_2k.ibd
|
|
s_4k General DEFAULT 0 Any s_4k.ibd
|
|
test/t_zip1k_to_file_per_table Single DEFAULT 1024 Compressed MYSQLD_DATADIR/test/t_zip1k_to_file_per_table.ibd
|
|
test/t_zip2k_to_file_per_table Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t_zip2k_to_file_per_table.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
s_1k TABLESPACE InnoDB NORMAL s_1k MYSQLD_DATADIR/s_1k.ibd
|
|
s_2k TABLESPACE InnoDB NORMAL s_2k MYSQLD_DATADIR/s_2k.ibd
|
|
s_4k TABLESPACE InnoDB NORMAL s_4k MYSQLD_DATADIR/s_4k.ibd
|
|
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
|
|
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
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t_zip1k_to_file_per_table test/t_zip1k_to_file_per_table 35 8 Compressed 1024 Single
|
|
test/t_zip2k_to_file_per_table test/t_zip2k_to_file_per_table 37 8 Compressed 2048 Single
|
|
DROP TABLE `t_zip1k_to_file_per_table`;
|
|
DROP TABLE `t_zip2k_to_file_per_table`;
|
|
#
|
|
# Clean-up.
|
|
#
|
|
DROP TABLESPACE s_1k;
|
|
DROP TABLESPACE s_2k;
|
|
DROP TABLESPACE s_4k;
|