Bug #84356 General tablespace table encryption.
Submitted: 28 Dec 2016 11:05 Modified: 28 Dec 2016 13:44
Reporter: Yusif Yusifov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7, 5.7.17 OS:Ubuntu (16.04)
Assigned to: CPU Architecture:Any
Tags: encryption, General Tablespace, Partitons table

[28 Dec 2016 11:05] Yusif Yusifov
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.
[28 Dec 2016 12:22] Umesh Shastry
Hello Yusif Yusifov,

Thank you for the report and test case.
Observed this with 5.7.17 and with partitioned tables where as for non-partitioned table it complains.

Thanks,
Umesh
[28 Dec 2016 13:44] Yusif Yusifov
Severity changed Serious.