Description:
InnoDB tablespace encryption only supports InnoDB tables that are stored in a file-per-table tablespaces, but I can use this option on creating CSV table without any error message.
How to repeat:
1. Start MySQL server
/*************************************************************************
2016-08-04T07:08:54.587985Z 0 [Note] mysqld: ready for connections.
Version: '5.7.12-enterprise-commercial-advanced' socket: '' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial)
*******************************************************************/
2. Start MySQL Client
/******************************************************************
C:\DEMO\mysql-advanced-5.7.12-winx64\bin>mysql -uroot -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
********************************************************************/
3. Create MyIsam and CSV Table with TDE
/*******************************************************************
mysql> USE TEST;
Database changed
mysql> CREATE TABLE t_myisam (c1 INT) engine=myisam ENCRYPTION='Y';
ERROR 1031 (HY000): Table storage engine for 't_myisam' doesn't have this option
mysql> CREATE TABLE t_csv (c1 INT) engine=csv ENCRYPTION='Y';
ERROR 1178 (42000): The storage engine for the table doesn't support nullable
mysql> CREATE TABLE t_csv (c1 INT NOT NULL) engine=csv ENCRYPTION='Y';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test | csvt1 | ENCRYPTION="Y" |
| test | t2 | ENCRYPTION="Y" |
| test | t_csv | ENCRYPTION="Y" |
+--------------+------------+----------------+
3 rows in set (1.93 sec)
********************************************************************/
4. Insert data into CSV Table
/*******************************************************************
mysql> INSERT INTO t_csv VALUES('1');
Query OK, 1 row affected (0.06 sec)
********************************************************************/
5. Display the CSV file
/*******************************************************************
C:\DEMO\mysql-advanced-5.7.12-winx64\data\test>type t_csv.csv
1
Suggested fix:
Should have error message "Table storage engine for 't_csv' doesn't have this option"
Description: InnoDB tablespace encryption only supports InnoDB tables that are stored in a file-per-table tablespaces, but I can use this option on creating CSV table without any error message. How to repeat: 1. Start MySQL server /************************************************************************* 2016-08-04T07:08:54.587985Z 0 [Note] mysqld: ready for connections. Version: '5.7.12-enterprise-commercial-advanced' socket: '' port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial) *******************************************************************/ 2. Start MySQL Client /****************************************************************** C:\DEMO\mysql-advanced-5.7.12-winx64\bin>mysql -uroot -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.12-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ********************************************************************/ 3. Create MyIsam and CSV Table with TDE /******************************************************************* mysql> USE TEST; Database changed mysql> CREATE TABLE t_myisam (c1 INT) engine=myisam ENCRYPTION='Y'; ERROR 1031 (HY000): Table storage engine for 't_myisam' doesn't have this option mysql> CREATE TABLE t_csv (c1 INT) engine=csv ENCRYPTION='Y'; ERROR 1178 (42000): The storage engine for the table doesn't support nullable mysql> CREATE TABLE t_csv (c1 INT NOT NULL) engine=csv ENCRYPTION='Y'; Query OK, 0 rows affected (0.03 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | test | csvt1 | ENCRYPTION="Y" | | test | t2 | ENCRYPTION="Y" | | test | t_csv | ENCRYPTION="Y" | +--------------+------------+----------------+ 3 rows in set (1.93 sec) ********************************************************************/ 4. Insert data into CSV Table /******************************************************************* mysql> INSERT INTO t_csv VALUES('1'); Query OK, 1 row affected (0.06 sec) ********************************************************************/ 5. Display the CSV file /******************************************************************* C:\DEMO\mysql-advanced-5.7.12-winx64\data\test>type t_csv.csv 1 Suggested fix: Should have error message "Table storage engine for 't_csv' doesn't have this option"