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
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.7.11
• OS: Any
• 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] MySQL Verification Team

Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh

[21 Mar 2016 9:49] MySQL Verification Team

// 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.