Bug #80801 Change query for "Identifying Tables that Use InnoDB Tablespace Encryption"
Submitted: 21 Mar 2016 9:16 Modified: 4 Apr 2016 14:11
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[21 Mar 2016 9:16] Shahriyar Rzayev
Description:
As per documentation: 
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

"Identifying Tables that Use InnoDB Tablespace Encryption" section is using:

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
    -> WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';

But it should be something like:

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
    -> WHERE CREATE_OPTIONS like '%ENCRYPTION="Y"%';

Result:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';
Empty set (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                  |
+--------------+------------+---------------------------------+
| dbtest       | sbtest1    | max_rows=1000000 ENCRYPTION="Y" |
+--------------+------------+---------------------------------+
1 row in set (0.02 sec)

How to repeat:
See description

Suggested fix:
Update like section as described :)
[21 Mar 2016 9:46] Umesh Shastry
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[21 Mar 2016 9:49] Umesh Shastry
// 5.7.11

mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 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         | t1         | ENCRYPTION="Y" |
+--------------+------------+----------------+
1 row in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 INT) ENGINE=innodb ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 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         | t1         | ENCRYPTION="Y" |
+--------------+------------+----------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 max_rows=5000;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=5000 ENCRYPTION='Y'
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';
Empty set (0.00 sec)

- with %%

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         | t1         | max_rows=5000 ENCRYPTION="Y" |
+--------------+------------+------------------------------+
1 row in set (0.00 sec)
[4 Apr 2016 14:11] Daniel Price
Posted by developer:
 
The query has been modified as suggested. The change should appear online within 24 hours. 
Thank you for the bug report.