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

Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1

    XMLWordPrintable

Details

    Description

      A regression was introduced in 10.1.38. See the following behavior:

      MariaDB [db1]> SET GLOBAL innodb_encrypt_tables=OFF;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db1]> SET SESSION innodb_default_encryption_key_id=2;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db1]> CREATE TABLE unencrypted_tab (id int primary key);
      ERROR 1005 (HY000): Can't create table `db1`.`unencrypted_tab` (errno: 140 "Wrong create options")
      MariaDB [db1]> SHOW WARNINGS;
      +---------+------+--------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                        |
      +---------+------+--------------------------------------------------------------------------------+
      | Warning |  140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1              |
      | Error   | 1005 | Can't create table `db1`.`unencrypted_tab` (errno: 140 "Wrong create options") |
      | Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB                |
      +---------+------+--------------------------------------------------------------------------------+
      3 rows in set (0.00 sec)
      

      This can be worked around by setting innodb_default_encryption_key_id=1 or ENCRYPTED=NO. See the following:

      MariaDB [db1]> SET SESSION innodb_default_encryption_key_id=1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db1]> CREATE TABLE unencrypted_tab (id int primary key);
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [db1]> DROP TABLE unencrypted_tab;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [db1]> SET SESSION innodb_default_encryption_key_id=2;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db1]> CREATE TABLE unencrypted_tab (id int primary key) ENCRYPTED=NO;
      Query OK, 0 rows affected (0.01 sec)
      

      It seems that this was introduced by the following change:

      https://github.com/MariaDB/server/commit/ef40018535b71f0a4387fa6c1cc22e9991dfc0db#diff-48fb879ad9d11afdfac59268a011ba97

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.