Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35025

innodb_encrypt_tables=ON does not show tables are encrypted when created without specification

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • None
    • N/A
    • Encryption
    • None

    Description

      Hey,

      On an instance where `innodb_encrypt_tables` is set to ON, tables are not encrypted when it is not specified:

      MariaDB [dba]> set wsrep_on=0;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      MariaDB [dba]> create table hello_test2 (id serial);
      		 
      Query OK, 0 rows affected (0.023 sec)
      		 
       
      		 
      MariaDB [dba]> show create table hello_test2\G
      		 
      *************************** 1. row ***************************
      		 
             Table: hello_test2
      		 
      Create Table: CREATE TABLE `hello_test2` (
      		 
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      		 
        UNIQUE KEY `id` (`id`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [dba]> show session variables like 'innodb_encrypt_tables';
      		 
      +-----------------------+-------+
      		 
      | Variable_name         | Value |
      		 
      +-----------------------+-------+
      		 
      | innodb_encrypt_tables | ON    |
      		 
      +-----------------------+-------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [dba]> create table hello_test3 (id serial) encrypted=yes;
      		 
      Query OK, 0 rows affected (0.080 sec)
      		 
       
      		 
      MariaDB [dba]> show create table hello_test3\G
      		 
      *************************** 1. row ***************************
      		 
             Table: hello_test3
      		 
      Create Table: CREATE TABLE `hello_test3` (
      		 
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      		 
        UNIQUE KEY `id` (`id`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `encrypted`=yes
      		 
      1 row in set (0.001 sec)

      Inspecting the table on the disk shows that both tables are actually encrypted.
      Executing ALTER TABLE hello_test2 ENCRYPTED=yes rebuilds the table while it is actually already encrypted.

      I would expect that the table shows up as encrypted=no if encryption is innodb_encrypt_tables is ON or FORCE.
      And, next to that, I would expect that a table is not rebuilt if it is already encrypted by default, and then encryption is explicitly added to it by ALTER TABLE ... encrypted=Yes;

      Thank you,
      Michael

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          marko Marko Mäkelä added a comment -

          Thank you for the report.

          As far as I understand it, the problem is that DDL operations do not give storage engines any chance to modify the contents of the TABLE_SHARE that will be serialized to the .frm file. That file would contain the table options.

          A similar problem exists with regard to ROW_FORMAT and innodb_default_row_format.

          marko Marko Mäkelä added a comment - Thank you for the report. As far as I understand it, the problem is that DDL operations do not give storage engines any chance to modify the contents of the TABLE_SHARE that will be serialized to the .frm file. That file would contain the table options. A similar problem exists with regard to ROW_FORMAT and innodb_default_row_format .
          michaeldg Michaël de groot added a comment -

          In the meantime I found that the table is actually encrypted, according to Information Schema INNODB_TABLESPACES_ENCRYPTION Table

          michaeldg Michaël de groot added a comment - In the meantime I found that the table is actually encrypted, according to Information Schema INNODB_TABLESPACES_ENCRYPTION Table
          marko Marko Mäkelä added a comment -

          Right, also the true ROW_FORMAT would be visible via SHOW TABLE STATUS.

          marko Marko Mäkelä added a comment - Right, also the true ROW_FORMAT would be visible via SHOW TABLE STATUS .
          serg Sergei Golubchik added a comment -

          This is intentional, see https://mariadb.com/kb/en/innodb-enabling-encryption/

          ENCRYPTED=YES means the table will be encrypted even when innodb_encrypt_tables=OFF, and not simply "a table is encrypted"

          serg Sergei Golubchik added a comment - This is intentional, see https://mariadb.com/kb/en/innodb-enabling-encryption/ ENCRYPTED=YES means the table will be encrypted even when innodb_encrypt_tables=OFF , and not simply "a table is encrypted"

          People

            sanja Oleksandr Byelkin
            michaeldg Michaël de groot
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.