revision-id: 531645545cf295c6564238fbc84a463b7919d844 (mariadb-10.1.37-82-g531645545cf) parent(s): 698df0a1ae0f72199afb71dc560552ce00c4dc35 author: Jan Lindström committer: Jan Lindström timestamp: 2019-02-19 10:28:13 +0200 message: MDEV-18601: Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1 Allow creating and altering table even when used key_id or default key_id is not system default 1. ha_innobase::check_table_options Ignore key_id table option when user has explicitly requested no encryption or if default encryption is used and encryption is disabled. Issue only a warning as used key_id value is not stored InnoDB data dictionary and encryption metadata is not created on page 0 of the table. --- .../encryption/r/innodb-encryption-alter.result | 51 +++++++++------------- .../encryption/t/innodb-encryption-alter.test | 23 +++++----- storage/innobase/handler/ha_innodb.cc | 31 +++++-------- storage/xtradb/handler/ha_innodb.cc | 31 +++++-------- 4 files changed, 55 insertions(+), 81 deletions(-) diff --git a/mysql-test/suite/encryption/r/innodb-encryption-alter.result b/mysql-test/suite/encryption/r/innodb-encryption-alter.result index 5245d1da7d0..8765a0f65cf 100644 --- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result +++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result @@ -4,7 +4,7 @@ SET GLOBAL innodb_encrypt_tables = ON; SET GLOBAL innodb_encryption_threads = 4; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4; Warnings: -Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 4 when encryption is disabled +Warning 140 InnoDB: ENCRYPTION_KEY_ID=4 ignored when ENCRYPTED=NO DROP TABLE t1; set innodb_default_encryption_key_id = 99; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; @@ -41,7 +41,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1; Warnings: -Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 1 when encryption is disabled +Warning 140 InnoDB: ENCRYPTION_KEY_ID=1 ignored when ENCRYPTED=NO ALTER TABLE t1 ENCRYPTION_KEY_ID=99; ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' SHOW WARNINGS; @@ -50,40 +50,29 @@ Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available Error 1478 Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' set innodb_default_encryption_key_id = 1; drop table t1,t2; +set innodb_default_encryption_key_id = 10; SET GLOBAL innodb_encrypt_tables=OFF; CREATE TABLE t1 (a int not null primary key) engine=innodb; ALTER TABLE t1 ENCRYPTION_KEY_ID=4; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' -SHOW WARNINGS; -Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +Warnings: +Warning 140 InnoDB: ENCRYPTION_KEY_ID=4 ignored when innodb_encrypt_tables=OFF +ALTER TABLE t1 ENCRYPTION_KEY_ID=8, ALGORITHM=COPY; +Warnings: +Warning 140 InnoDB: ENCRYPTION_KEY_ID=8 ignored when innodb_encrypt_tables=OFF DROP TABLE t1; -CREATE TABLE t2 (a int not null primary key) engine=innodb; -ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY; -ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") +CREATE TABLE t1 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; +Warnings: +Warning 140 InnoDB: ENCRYPTION_KEY_ID=4 ignored when innodb_encrypt_tables=OFF SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1005 Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `a` int(11) NOT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t2; -CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; -ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options") +Warning 140 InnoDB: ENCRYPTION_KEY_ID=4 ignored when innodb_encrypt_tables=OFF +ALTER TABLE t1 ENCRYPTION_KEY_ID=10; +DROP TABLE t1; +CREATE TABLE t1 (a int not null primary key) engine=innodb ENCRYPTED=NO ENCRYPTION_KEY_ID=10; SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1005 Can't create table `test`.`t3` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +ALTER TABLE t1 ENCRYPTION_KEY_ID=1; +Warnings: +Warning 140 InnoDB: ENCRYPTION_KEY_ID=1 ignored when ENCRYPTED=NO +DROP TABLE t1; +set innodb_default_encryption_key_id = 1; diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test index 9465226dd96..d1bcafa8767 100644 --- a/mysql-test/suite/encryption/t/innodb-encryption-alter.test +++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test @@ -89,27 +89,26 @@ drop table t1,t2; # # MDEV-17230: encryption_key_id from alter is ignored by encryption threads +# MDEV-18601: Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1 # +set innodb_default_encryption_key_id = 10; SET GLOBAL innodb_encrypt_tables=OFF; CREATE TABLE t1 (a int not null primary key) engine=innodb; ---error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ENCRYPTION_KEY_ID=4; -SHOW WARNINGS; -SHOW CREATE TABLE t1; +ALTER TABLE t1 ENCRYPTION_KEY_ID=8, ALGORITHM=COPY; DROP TABLE t1; -CREATE TABLE t2 (a int not null primary key) engine=innodb; ---replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY; ---replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ +CREATE TABLE t1 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; SHOW WARNINGS; -SHOW CREATE TABLE t2; -DROP TABLE t2; +ALTER TABLE t1 ENCRYPTION_KEY_ID=10; +DROP TABLE t1; ---error ER_CANT_CREATE_TABLE -CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; +CREATE TABLE t1 (a int not null primary key) engine=innodb ENCRYPTED=NO ENCRYPTION_KEY_ID=10; SHOW WARNINGS; +ALTER TABLE t1 ENCRYPTION_KEY_ID=1; +DROP TABLE t1; + +set innodb_default_encryption_key_id = 1; # reset system --disable_query_log diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index fc97b1dc104..ec6d7b7c5b9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -11882,30 +11882,23 @@ ha_innobase::check_table_options( } } - /* Ignore nondefault key_id if encryption is set off */ - if (encrypt == FIL_ENCRYPTION_OFF && - options->encryption_key_id != THDVAR(thd, default_encryption_key_id)) { + /* We should ignore key_id table option when user has + explicitly requested no encryption or if default encryption + is used and encryption is disabled. */ + const uint key_id = THDVAR(thd, default_encryption_key_id); + if ((encrypt == FIL_ENCRYPTION_OFF + || (encrypt == FIL_ENCRYPTION_DEFAULT && !srv_encrypt_tables)) + && options->encryption_key_id != key_id) { push_warning_printf( thd, Sql_condition::WARN_LEVEL_WARN, HA_WRONG_CREATE_OPTION, - "InnoDB: Ignored ENCRYPTION_KEY_ID %u when encryption is disabled", - (uint)options->encryption_key_id + "InnoDB: ENCRYPTION_KEY_ID=%u ignored when %s", + (uint)options->encryption_key_id, + encrypt == FIL_ENCRYPTION_OFF ? "ENCRYPTED=NO" + : "innodb_encrypt_tables=OFF" ); - options->encryption_key_id = FIL_DEFAULT_ENCRYPTION_KEY; - } - /* If default encryption is used and encryption is disabled, you may - not use nondefault encryption_key_id as it is not stored anywhere. */ - if (encrypt == FIL_ENCRYPTION_DEFAULT - && !srv_encrypt_tables - && options->encryption_key_id != FIL_DEFAULT_ENCRYPTION_KEY) { - compile_time_assert(FIL_DEFAULT_ENCRYPTION_KEY == 1); - push_warning_printf( - thd, Sql_condition::WARN_LEVEL_WARN, - HA_WRONG_CREATE_OPTION, - "InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1" - ); - return "ENCRYPTION_KEY_ID"; + options->encryption_key_id = key_id; } /* Check atomic writes requirements */ diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index f4e0420519e..02a5a8901ad 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -12451,30 +12451,23 @@ ha_innobase::check_table_options( } } - /* Ignore nondefault key_id if encryption is set off */ - if (encrypt == FIL_ENCRYPTION_OFF && - options->encryption_key_id != THDVAR(thd, default_encryption_key_id)) { + /* We should ignore key_id table option when user has + explicitly requested no encryption or if default encryption + is used and encryption is disabled. */ + const uint key_id = THDVAR(thd, default_encryption_key_id); + if ((encrypt == FIL_ENCRYPTION_OFF + || (encrypt == FIL_ENCRYPTION_DEFAULT && !srv_encrypt_tables)) + && options->encryption_key_id != key_id) { push_warning_printf( thd, Sql_condition::WARN_LEVEL_WARN, HA_WRONG_CREATE_OPTION, - "InnoDB: Ignored ENCRYPTION_KEY_ID %u when encryption is disabled", - (uint)options->encryption_key_id + "InnoDB: ENCRYPTION_KEY_ID=%u ignored when %s", + (uint)options->encryption_key_id, + encrypt == FIL_ENCRYPTION_OFF ? "ENCRYPTED=NO" + : "innodb_encrypt_tables=OFF" ); - options->encryption_key_id = FIL_DEFAULT_ENCRYPTION_KEY; - } - /* If default encryption is used and encryption is disabled, you may - not use nondefault encryption_key_id as it is not stored anywhere. */ - if (encrypt == FIL_ENCRYPTION_DEFAULT - && !srv_encrypt_tables - && options->encryption_key_id != FIL_DEFAULT_ENCRYPTION_KEY) { - compile_time_assert(FIL_DEFAULT_ENCRYPTION_KEY == 1); - push_warning_printf( - thd, Sql_condition::WARN_LEVEL_WARN, - HA_WRONG_CREATE_OPTION, - "InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1" - ); - return "ENCRYPTION_KEY_ID"; + options->encryption_key_id = key_id; } /* Check atomic writes requirements */