4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/innodb/r/tablespace_encrypt_4.result

611 lines
27 KiB
Plaintext
Raw Permalink Blame History

#########################################################################
# START : WITHOUT KEYRING PLUGIN
#########################################################################
#########
# SETUP #
#########
#########################################################################
# RESTART 1 : WITH KEYRING PLUGIN
#########################################################################
#########################################################################
# Non Partitioned Table #
#########################################################################
DROP TABLE IF EXISTS t1;
# Create an Encrypted and an Unencrypted tablespace
CREATE TABLESPACE encrypt_ts add datafile 'encrypt_ts.ibd' ENCRYPTION='Y';
CREATE TABLESPACE unencrypt_ts add datafile 'unencrypt_ts.ibd' ENCRYPTION='N';
# Create table with "encryption" option in general tablespace
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='y';
SHOW WARNINGS;
Level Code Message
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='n';
ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
SHOW WARNINGS;
Level Code Message
Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
CREATE TABLE t1 (c int) TABLESPACE=innodb_system ENCRYPTION='y';
ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
SHOW WARNINGS;
Level Code Message
Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
CREATE TABLE t1 (c int) TABLESPACE=innodb_system ENCRYPTION='n';
SHOW WARNINGS;
Level Code Message
DROP TABLE t1;
#-------------------------------------------------------
# general [encrypted] => general [unencrypted]
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
# general [unencrypted] => general [encrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
# Changing encryption type without explicit ENCRYPTION clause fails.
ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=COPY;
ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
#-------------------------------------------------------
# general [encrypted] => file-per-table [unencrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
# general [encrypted] => file-per-table [encrypted]
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION= 'Y';
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=INPLACE;
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
#-------------------------------------------------------
# general [unencrypted] => file-per-table [unencrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# general [unencrypted] => file-per-table [encrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
# general [unencrypted] => file-per-table [unencrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#-------------------------------------------------------
# file-per-table [unencrypted] => general [encrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='n';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='n';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
# file-per-table [encrypted] => general [encrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='y';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='y';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
# file-per-table [encrypted] => general [unencrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='y';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
# file-per-table [unencrypted] => general [unencrypted]
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='n';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=unencrypt_ts, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (c int) ENCRYPTION='n';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 TABLESPACE=unencrypt_ts, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
#-------------------------------------------------------
# ALTER TABLE ADD COLUMN/INDEX WITH MOVING TO DIFFERENT TABLESPACE
CREATE TABLESPACE encrypt_ts1 add datafile 'encrypt_ts1.ibd' ENCRYPTION='Y';
CREATE TABLESPACE unencrypt_ts1 add datafile 'unencrypt_ts1.ibd' ENCRYPTION='N';
# ALGORITHM=DEFAULT
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N';
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y';
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`),
KEY `c2` (`c2`)
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
DROP TABLE t1;
# ALGORITHM=INPLACE
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1 ENCRYPTION='Y', ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
# ALGORITHM=COPY
CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
ERROR HY000: Source tablespace is encrypted but target tablespace is not.
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
DROP TABLE t1;
CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL
) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts, ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`)
) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `c` (`c`),
KEY `c2` (`c2`)
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
DROP TABLE t1;
#########################################################################
# Partitioned Table #
#########################################################################
DROP TABLE IF EXISTS t1;
# Create table without explicit tablespace name
CREATE TABLE t1 (id INT, name VARCHAR(50))
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
# Try to ALTER TABLE to have general tablespace at table level
ALTER TABLE t1 TABLESPACE=unencrypt_ts;
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
# Alter encryption option
ALTER TABLE t1 ENCRYPTION='Y';
DROP TABLE t1;
# Create table with general tablespace at table level
CREATE TABLE t1 (id INT, name VARCHAR(50))
TABLESPACE=encrypt_ts ENCRYPTION='Y'
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Create table with system tablespace at table level
CREATE TABLE t1 (id INT, name VARCHAR(50))
TABLESPACE=innodb_system
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Create table with innodb_file_per_table tablespace at table level
CREATE TABLE t1 (id INT, name VARCHAR(50))
TABLESPACE=innodb_file_per_table
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
DROP TABLE t1;
# Create table with general tablespace at partition level
CREATE TABLE t1 (id INT, name VARCHAR(50)) ENCRYPTION='Y'
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Create table with system tablespace at partition level
CREATE TABLE t1 (id INT, name VARCHAR(50))
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30) TABLESPACE=innodb_system);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Create table with innodb_file_per_table tablespace at partition level
CREATE TABLE t1 (id INT, name VARCHAR(50))
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);
DROP TABLE t1;
# Create table with ENCRYPTION='y' option
CREATE TABLE t1 (id INT, name VARCHAR(50))
ENCRYPTION='Y'
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
INSERT INTO t1 VALUES (5,'test');
INSERT INTO t1 VALUES (12,'test1');
INSERT INTO t1 VALUES (22,'test2');
# Truncate specific partition of encrypted table
ALTER TABLE t1 TRUNCATE PARTITION p0;
# Alter encryption option
ALTER TABLE t1 ENCRYPTION='N';
DROP TABLE t1;
# Create table with ENCRYPTION='y' option and innodb_file_per_table
# tablespace at table level
CREATE TABLE t1 (id INT, name VARCHAR(50))
ENCRYPTION='Y' TABLESPACE=innodb_file_per_table
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
# Alter encryption option
ALTER TABLE t1 ENCRYPTION='N';
# Alter table to move a partition to general tablespace.
ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
PARTITION P2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
SHOW WARNINGS;
Level Code Message
Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
PARTITION P2 VALUES LESS THAN (30) TABLESPACE=unencrypt_ts);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Alter table to move a partition to system tablespace.
ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
PARTITION P2 VALUES LESS THAN (30) TABLESPACE=innodb_system);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Alter table to move a partition to file_per_table tablespace.
ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
PARTITION P2 VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION P2 VALUES LESS THAN (30) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
# Alter table to add a new partition in general tablespace
ALTER TABLE t1 ADD PARTITION (
PARTITION p3 VALUES LESS THAN (40) tablespace=encrypt_ts);
ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
SHOW WARNINGS;
Level Code Message
Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
ALTER TABLE t1 ADD PARTITION (
PARTITION p3 VALUES LESS THAN (40) tablespace=unencrypt_ts);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Alter table to add a new partition in innodb_system tablespace
ALTER TABLE t1 ADD PARTITION (
PARTITION p3 VALUES LESS THAN (40) tablespace=innodb_system);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Alter table to add a new partition in innodb_file_per_table tablespace
ALTER TABLE t1 ADD PARTITION (
PARTITION p3 VALUES LESS THAN (40) tablespace=innodb_file_per_table);
# Alter table to add a new partition without giving tablespace
ALTER TABLE t1 ADD PARTITION (
PARTITION p4 VALUES LESS THAN (50));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION P2 VALUES LESS THAN (30) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (40) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (50) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
# Alter table to add a new partition with encryption option at partition
# level.
ALTER TABLE t1 ADD PARTITION (
PARTITION p5 VALUES LESS THAN (60) encryption='Y');
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'encryption='Y')' at line 2
DROP TABLE t1;
# Create table with ENCRYPTION='y' option and general tablespace
# at table level
CREATE TABLE t1 (id INT, name VARCHAR(50))
ENCRYPTION='Y' TABLESPACE=encrypt_ts
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
# Create table with ENCRYPTION='y' option and general tablespace
# at partition level
CREATE TABLE t1 (id INT, name VARCHAR(50))
ENCRYPTION='Y'
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
SHOW WARNINGS;
Level Code Message
Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
###########
# Cleanup #
###########
DROP TABLE IF EXISTS t1;
DROP TABLESPACE encrypt_ts;
DROP TABLESPACE encrypt_ts1;
DROP TABLESPACE unencrypt_ts;
DROP TABLESPACE unencrypt_ts1;
# Restarting server without keyring to restore server state
# restart:
Reference in New Issue View Git Blame Copy Permalink