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

    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

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                2 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: