Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1588

Issues with pt-mysql-summary encryption checks

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: 3.0.10, 3.0.11
    • Fix Version/s: None
    • Component/s: pt-mysql-summary
    • Labels:

      Description

      There are 2 functions in pt-mysql-summary that will not work quite as expected:

      collect_encrypted_tables() {
          $CMD_MYSQL $EXT_ARGV --table -ss -e "SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION=\"Y\"%';"
      }
      
      collect_encrypted_tablespaces() {
          $CMD_MYSQL $EXT_ARGV --table -ss -e "SELECT SPACE, NAME, SPACE_TYPE from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where FLAG&8192 = 8192;"
      }
      

      collect_encrypted_tables

      The first one will return tables that are potentially unencrypted (see bug #91791). From what I can tell, to correctly determine collect_encrypted_tables you need to use the something like the following to confirm:

      PS
      SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
                 SUBSTRING_INDEX(name, '/', -1) AS db_table,
                 (flag & 8192) != 0 AS encrypted
      FROM information_schema.INNODB_SYS_TABLESPACES
      
      MariaDB
      SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
             SUBSTRING_INDEX(name, '/', -1) AS db_table,
             COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted
      FROM information_schema.INNODB_SYS_TABLESPACES
          LEFT JOIN information_schema.INNODB_TABLESPACES_ENCRYPTION USING(NAME)
      

      For MariaDB, tables can be automatically encrypted and do not appear to have the identifier in the create options:

      MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPT%';
      +--------------+--------------------+--------------------------------------+
      | TABLE_SCHEMA | TABLE_NAME         | CREATE_OPTIONS                       |
      +--------------+--------------------+--------------------------------------+
      | mysql        | innodb_table_stats | stats_persistent=0 `encrypted`='YES' |
      +--------------+--------------------+--------------------------------------+
      1 row in set (0.005 sec)
      
      MariaDB [(none)]> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
          ->        SUBSTRING_INDEX(name, '/', -1) AS db_table,
          ->        COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted
          -> FROM information_schema.INNODB_SYS_TABLESPACES
          ->     LEFT JOIN information_schema.INNODB_TABLESPACES_ENCRYPTION USING(NAME) HAVING encrypted;
      +---------+--------------------+-----------+
      | db_name | db_table           | encrypted |
      +---------+--------------------+-----------+
      | sandbox | foo                |         1 |
      | mysql   | innodb_table_stats |         1 |
      +---------+--------------------+-----------+
      2 rows in set (0.001 sec)
      

      For MariaDB in particular, it should be noted that the comment (and alter) is different:

      encrypted vs encryption
      

      Sadly, Oracle doesn't seem to have a way other than by trying to inspect the data.

      collect_encrypted_tablespaces

      This will fail on MySQL 8.0 as the views have been renamed

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  ceri.williams Ceri Williams
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated: