Description:
Hi Dear All.
As per documentation
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
InnoDB tablespace encryption only supports InnoDB tables that are stored in a file-per-table tablespaces. Encryption is not supported for tables stored in other InnoDB tablespace types including general tablespaces, the system tablespace, undo log tablespaces, and the temporary tablespace.
But we can create partitions table, encrypt it and place in general tablespace.
How to repeat:
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
mysql> create table test_tab2(a int,b int) engine = innodb
-> partition by range (a)
-> (PARTITION p1 VALUES LESS THAN (100),
-> PARTITION p2 VALUES LESS THAN (1000),
-> PARTITION p3 VALUES LESS THAN (10000),
-> PARTITION p4 VALUES LESS THAN (10001)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> alter table test_tab2 encryption='Y';
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test_tab2;
+-----------+------------------------+
| Table | Create Table
-------------------------------------+
| test_tab2 | CREATE TABLE `test_tab2` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ENCRYPTION='Y'
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */ |
+-----------+-----------------------------------------------
1 row in set (0.01 sec)
mysql> CREATE TABLESPACE `azemug` ADD DATAFILE 'azemug`.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test_tab2 tablespace azemug;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test_tab2;
--------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------+
| test_tab2 | CREATE TABLE `test_tab2` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `azemug` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 ENCRYPTION='Y'
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */ |
1 row in set (0.00 sec)
Suggested fix:
No Idea.
