[Commits] ce29dfac72e: MDEV-18601: Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1
revision-id: ce29dfac72e301f83cc589e697ac77b13bbb3902 (mariadb-10.1.38-6-gce29dfac72e) parent(s): 98e185ee373310291825fe6ac87f45afe6a3ccf7 author: Jan Lindström committer: Jan Lindström timestamp: 2019-02-21 14:53:11 +0200 message: MDEV-18601: Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1 innodb_check_default_encryption_key_id Add a new function to check that provided innodb_default_encryption_key_id is found from key file if it is not same as system default. We need to accept system default here as it is used e.g. when no encryption configuration is set. ha_innobase::check_table_options Used encryption key_id must be found from key file even when encryption is disabled by table options or default encryption is used and encryption is disabled. This is because encryption can be enabled later and it is possible only if all existing tables use key_id that is found from key file. Again we need to accept system default as it is used on default table creation. innodb_var_default_encryption_key_id New test to test variable innodb_default_encryption_key_id --- .../encryption/r/innodb-encryption-alter.result | 69 +++++----- .../encryption/r/innodb-page_encryption.result | 2 +- .../r/innodb_var_default_encryption_key_id.result | 144 +++++++++++++++++++++ .../encryption/t/innodb-encryption-alter.test | 29 +++-- .../t/innodb_var_default_encryption_key_id.test | 101 +++++++++++++++ storage/innobase/handler/ha_innodb.cc | 97 ++++++++++---- storage/xtradb/handler/ha_innodb.cc | 97 ++++++++++---- 7 files changed, 439 insertions(+), 100 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..038e2a2fd47 100644 --- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result +++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result @@ -3,24 +3,18 @@ SET GLOBAL innodb_file_per_table = ON; 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 DROP TABLE t1; set innodb_default_encryption_key_id = 99; +Warnings: +Warning 1210 InnoDB: Ignored innodb_default_encryption_key_id=99 as it is not available in the key file. Using default=1. CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB +DROP TABLE t1; set innodb_default_encryption_key_id = 4; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; SHOW CREATE TABLE t1; @@ -40,50 +34,57 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`pk`) ) 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 ALTER TABLE t1 ENCRYPTION_KEY_ID=99; ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available +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 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 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; 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; +SHOW CREATE TABLE t1; Table Create Table -t2 CREATE TABLE `t2` ( +t1 CREATE TABLE `t1` ( `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") +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 +ALTER TABLE t1 ENCRYPTION_KEY_ID=10; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `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 CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `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; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`=NO `ENCRYPTION_KEY_ID`=1 +DROP TABLE t1; +set innodb_default_encryption_key_id = 1; diff --git a/mysql-test/suite/encryption/r/innodb-page_encryption.result b/mysql-test/suite/encryption/r/innodb-page_encryption.result index c4814983af4..37c4d6caab1 100644 --- a/mysql-test/suite/encryption/r/innodb-page_encryption.result +++ b/mysql-test/suite/encryption/r/innodb-page_encryption.result @@ -13,7 +13,7 @@ create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_f ERROR HY000: Can't create table `test`.`innodb_dynamic` (errno: 140 "Wrong create options") show warnings; Level Code Message -Warning 140 InnoDB: ENCRYPTION_KEY_ID 3 not available +Warning 140 InnoDB: ENCRYPTION_KEY_ID=3 not available Error 1005 Can't create table `test`.`innodb_dynamic` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33; diff --git a/mysql-test/suite/encryption/r/innodb_var_default_encryption_key_id.result b/mysql-test/suite/encryption/r/innodb_var_default_encryption_key_id.result new file mode 100644 index 00000000000..f2af1b86b75 --- /dev/null +++ b/mysql-test/suite/encryption/r/innodb_var_default_encryption_key_id.result @@ -0,0 +1,144 @@ +SET @global_start_value = @@global.innodb_default_encryption_key_id; +SELECT @global_start_value; +@global_start_value +1 +SET @@global.innodb_default_encryption_key_id = DEFAULT; +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +SELECT @@innodb_default_encryption_key_id; +@@innodb_default_encryption_key_id +1 +SET @@global.innodb_default_encryption_key_id = 4; +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +4 +SET @@global.innodb_default_encryption_key_id = 0; +ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '0' +SET @@global.innodb_default_encryption_key_id = -1; +ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '-1' +SET @@global.innodb_default_encryption_key_id = 4294967296; +ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '4294967296' +SET @@global.innodb_default_encryption_key_id = "t"; +ERROR 42000: Incorrect argument type to variable 'innodb_default_encryption_key_id' +SET @@global.innodb_default_encryption_key_id = NULL; +ERROR 42000: Incorrect argument type to variable 'innodb_default_encryption_key_id' +SET @@global.innodb_default_encryption_key_id = 99; +Warnings: +Warning 1210 InnoDB: Ignored innodb_default_encryption_key_id=99 as it is not available in the key file. Using default=1. +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +SET @@global.innodb_default_encryption_key_id = 4294967295; +Warnings: +Warning 1210 InnoDB: Ignored innodb_default_encryption_key_id=4294967295 as it is not available in the key file. Using default=1. +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +SELECT @@global.innodb_default_encryption_key_id = +VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='innodb_default_encryption_key_id'; +@@global.innodb_default_encryption_key_id = +VARIABLE_VALUE +1 +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='innodb_default_encryption_key_id'; +VARIABLE_VALUE +1 +SET @@global.innodb_default_encryption_key_id = @global_start_value; +SELECT @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +select @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +select @@session.innodb_default_encryption_key_id; +@@session.innodb_default_encryption_key_id +1 +create table t1(a int not null primary key) engine=innodb encrypted=yes; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes +create table t2(a int not null primary key) engine=innodb encrypted=no; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=no +create table t3(a int not null primary key) engine=innodb; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +set session innodb_default_encryption_key_id = 2; +select @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +select @@session.innodb_default_encryption_key_id; +@@session.innodb_default_encryption_key_id +2 +create table t11(a int not null primary key) engine=innodb encrypted=yes; +show create table t11; +Table Create Table +t11 CREATE TABLE `t11` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `ENCRYPTION_KEY_ID`=2 +create table t12(a int not null primary key) engine=innodb encrypted=no; +show create table t12; +Table Create Table +t12 CREATE TABLE `t12` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=no `ENCRYPTION_KEY_ID`=2 +create table t13(a int not null primary key) engine=innodb; +show create table t13; +Table Create Table +t13 CREATE TABLE `t13` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=2 +set session innodb_default_encryption_key_id = 4; +select @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +select @@session.innodb_default_encryption_key_id; +@@session.innodb_default_encryption_key_id +4 +create table t21(a int not null primary key) engine=innodb encrypted=yes; +show create table t21; +Table Create Table +t21 CREATE TABLE `t21` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `ENCRYPTION_KEY_ID`=4 +create table t22(a int not null primary key) engine=innodb encrypted=no; +show create table t22; +Table Create Table +t22 CREATE TABLE `t22` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=no `ENCRYPTION_KEY_ID`=4 +create table t23(a int not null primary key) engine=innodb; +show create table t23; +Table Create Table +t23 CREATE TABLE `t23` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 +select @@global.innodb_default_encryption_key_id; +@@global.innodb_default_encryption_key_id +1 +select @@session.innodb_default_encryption_key_id; +@@session.innodb_default_encryption_key_id +1 +drop table t1,t2,t3,t11,t12,t13,t21,t22,t23; diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test index 9465226dd96..d1a856a0011 100644 --- a/mysql-test/suite/encryption/t/innodb-encryption-alter.test +++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test @@ -20,12 +20,12 @@ 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; DROP TABLE t1; set innodb_default_encryption_key_id = 99; ---error 1005 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; SHOW WARNINGS; ---error 1005 +DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; SHOW WARNINGS; +DROP TABLE t1; set innodb_default_encryption_key_id = 4; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; SHOW CREATE TABLE t1; @@ -89,27 +89,30 @@ 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; 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; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ENCRYPTION_KEY_ID=10; +SHOW CREATE TABLE t1; +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 CREATE TABLE t1; SHOW WARNINGS; +ALTER TABLE t1 ENCRYPTION_KEY_ID=1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +set innodb_default_encryption_key_id = 1; # reset system --disable_query_log diff --git a/mysql-test/suite/encryption/t/innodb_var_default_encryption_key_id.test b/mysql-test/suite/encryption/t/innodb_var_default_encryption_key_id.test new file mode 100644 index 00000000000..779bcc5300b --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_var_default_encryption_key_id.test @@ -0,0 +1,101 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc + +--disable_query_log +SET @global_file_format = @@global.innodb_file_format; +SET @global_file_per_table = @@global.innodb_file_per_table; +--enable_query_log + +--disable_query_log +--disable_warnings +SET GLOBAL innodb_file_format = `Barracuda`; +SET GLOBAL innodb_file_per_table = ON; +--enable_warnings +--enable_query_log + +SET @global_start_value = @@global.innodb_default_encryption_key_id; +SELECT @global_start_value; + +SET @@global.innodb_default_encryption_key_id = DEFAULT; +SELECT @@global.innodb_default_encryption_key_id; +SELECT @@innodb_default_encryption_key_id; + +SET @@global.innodb_default_encryption_key_id = 4; +SELECT @@global.innodb_default_encryption_key_id; + +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.innodb_default_encryption_key_id = 0; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.innodb_default_encryption_key_id = -1; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.innodb_default_encryption_key_id = 4294967296; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_default_encryption_key_id = "t"; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_default_encryption_key_id = NULL; + +SET @@global.innodb_default_encryption_key_id = 99; +SELECT @@global.innodb_default_encryption_key_id; + +SET @@global.innodb_default_encryption_key_id = 4294967295; +SELECT @@global.innodb_default_encryption_key_id; + +SELECT @@global.innodb_default_encryption_key_id = + VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME='innodb_default_encryption_key_id'; +SELECT @@global.innodb_default_encryption_key_id; +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME='innodb_default_encryption_key_id'; + +SET @@global.innodb_default_encryption_key_id = @global_start_value; +SELECT @@global.innodb_default_encryption_key_id; + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +--connection default +select @@global.innodb_default_encryption_key_id; +select @@session.innodb_default_encryption_key_id; +create table t1(a int not null primary key) engine=innodb encrypted=yes; +show create table t1; +create table t2(a int not null primary key) engine=innodb encrypted=no; +show create table t2; +create table t3(a int not null primary key) engine=innodb; +show create table t3; + +--connection con1 +set session innodb_default_encryption_key_id = 2; +select @@global.innodb_default_encryption_key_id; +select @@session.innodb_default_encryption_key_id; +create table t11(a int not null primary key) engine=innodb encrypted=yes; +show create table t11; +create table t12(a int not null primary key) engine=innodb encrypted=no; +show create table t12; +create table t13(a int not null primary key) engine=innodb; +show create table t13; + +--connection con2 +set session innodb_default_encryption_key_id = 4; +select @@global.innodb_default_encryption_key_id; +select @@session.innodb_default_encryption_key_id; +create table t21(a int not null primary key) engine=innodb encrypted=yes; +show create table t21; +create table t22(a int not null primary key) engine=innodb encrypted=no; +show create table t22; +create table t23(a int not null primary key) engine=innodb; +show create table t23; + +--connection default +select @@global.innodb_default_encryption_key_id; +select @@session.innodb_default_encryption_key_id; +--disconnect con1 +--disconnect con2 +drop table t1,t2,t3,t11,t12,t13,t21,t22,t23; + +--disable_query_log +--disable_warnings +SET @@global.innodb_file_format = @global_file_format; +SET @@global.innodb_file_per_table = @global_file_per_table; +SET @@global.innodb_default_encryption_key_id = @global_start_value; +--disable_warnings +--enable_warnings diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 472fb86288f..58dbe497ed9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -4,7 +4,7 @@ Copyright (c) 2000, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, 2009 Google Inc. Copyright (c) 2009, Percona Inc. Copyright (c) 2012, Facebook Inc. -Copyright (c) 2013, 2018, MariaDB Corporation. +Copyright (c) 2013, 2019, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -686,9 +686,58 @@ static int mysql_tmpfile_path(const char *path, const char *prefix) static void innodb_remember_check_sysvar_funcs(); mysql_var_check_func check_sysvar_enum; +/*************************************************************//** +Check whether provided innodb_default_encryption_key_id is +found from key file if it is not same as system default. +This function is registered as a callback with MySQL. +@return 0 for valid key_id */ +static int innodb_check_default_encryption_key_id( + THD* thd, /*!< in: thread handle */ + struct st_mysql_sys_var* var, /*!< in: pointer to system + variable */ + void* save, /*!< out: immediate result + for update function */ + struct st_mysql_value* value) /*!< in: incoming value */ +{ + long long intbuf; + uint key_id; + + if (value->val_int(value, &intbuf)) { + /* The value is NULL. That is invalid. */ + return(1); + } + + if (intbuf < 1 || intbuf > UINT_MAX32) { + /* Invalid value */ + return(1); + } + + key_id = static_cast<uint>(intbuf); + + if (key_id != FIL_DEFAULT_ENCRYPTION_KEY) { + /* If user has requested key_id that is not same as + system default check that key is found from key file. + If it is not found fall back to default. */ + if (!encryption_key_id_exists(key_id)) { + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_WARN, + ER_WRONG_ARGUMENTS, + "InnoDB: Ignored innodb_default_encryption_key_id=%u as " + "it is not available in the key file. Using default=1.", + key_id + ); + + key_id = FIL_DEFAULT_ENCRYPTION_KEY; + } + } + + *reinterpret_cast<ulint*>(save) = key_id; + return (0); +} + static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG, "Default encryption key id used for table encryption.", - NULL, NULL, + innodb_check_default_encryption_key_id, NULL, FIL_DEFAULT_ENCRYPTION_KEY, 1, UINT_MAX32, 0); /** @@ -11944,41 +11993,37 @@ ha_innobase::check_table_options( /* If encryption is set up make sure that used key_id is found */ if (encrypt == FIL_ENCRYPTION_ON || (encrypt == FIL_ENCRYPTION_DEFAULT && srv_encrypt_tables)) { - if (!encryption_key_id_exists((unsigned int)options->encryption_key_id)) { + if (!encryption_key_id_exists(options->encryption_key_id)) { push_warning_printf( thd, Sql_condition::WARN_LEVEL_WARN, HA_WRONG_CREATE_OPTION, - "InnoDB: ENCRYPTION_KEY_ID %u not available", - (uint)options->encryption_key_id + "InnoDB: ENCRYPTION_KEY_ID=%u not available", + options->encryption_key_id ); return "ENCRYPTION_KEY_ID"; } } - /* Ignore nondefault key_id if encryption is set off */ - if (encrypt == FIL_ENCRYPTION_OFF && - options->encryption_key_id != THDVAR(thd, default_encryption_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 - ); - 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 + /* Used encryption key_id must be found from key file even + when encryption is disabled by table options or default + encryption is used and encryption is disabled. This is + because encryption can be enabled later and it is possible + only if all existing tables use key_id that is found + from key file. */ + if ((encrypt == FIL_ENCRYPTION_OFF + || (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" + if (!encryption_key_id_exists(options->encryption_key_id)) { + + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_WARN, + HA_WRONG_CREATE_OPTION, + "InnoDB: ENCRYPTION_KEY_ID=%u not available", + options->encryption_key_id ); - return "ENCRYPTION_KEY_ID"; + return "ENCRYPTION_KEY_ID"; + } } /* Check atomic writes requirements */ diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index c9db941b4bf..66ad3544030 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -5,7 +5,7 @@ Copyright (c) 2013, 2018, MariaDB Corporation. Copyright (c) 2008, 2009 Google Inc. Copyright (c) 2009, Percona Inc. Copyright (c) 2012, Facebook Inc. -Copyright (c) 2013, 2018, MariaDB Corporation. +Copyright (c) 2013, 2019, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -687,9 +687,58 @@ ib_cb_t innodb_api_cb[] = { static void innodb_remember_check_sysvar_funcs(); mysql_var_check_func check_sysvar_enum; +/*************************************************************//** +Check whether provided innodb_default_encryption_key_id is +found from key file if it is not same as system default. +This function is registered as a callback with MySQL. +@return 0 for valid key_id */ +static int innodb_check_default_encryption_key_id( + THD* thd, /*!< in: thread handle */ + struct st_mysql_sys_var* var, /*!< in: pointer to system + variable */ + void* save, /*!< out: immediate result + for update function */ + struct st_mysql_value* value) /*!< in: incoming value */ +{ + long long intbuf; + uint key_id; + + if (value->val_int(value, &intbuf)) { + /* The value is NULL. That is invalid. */ + return(1); + } + + if (intbuf < 1 || intbuf > UINT_MAX32) { + /* Invalid value */ + return(1); + } + + key_id = static_cast<uint>(intbuf); + + if (key_id != FIL_DEFAULT_ENCRYPTION_KEY) { + /* If user has requested key_id that is not same as + system default check that key is found from key file. + If it is not found fall back to default. */ + if (!encryption_key_id_exists(key_id)) { + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_WARN, + ER_WRONG_ARGUMENTS, + "InnoDB: Ignored innodb_default_encryption_key_id=%u as " + "it is not available in the key file. Using default=1.", + key_id + ); + + key_id = FIL_DEFAULT_ENCRYPTION_KEY; + } + } + + *reinterpret_cast<ulint*>(save) = key_id; + return (0); +} + static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG, "Default encryption key id used for table encryption.", - NULL, NULL, + innodb_check_default_encryption_key_id, NULL, FIL_DEFAULT_ENCRYPTION_KEY, 1, UINT_MAX32, 0); /** @@ -12510,42 +12559,38 @@ ha_innobase::check_table_options( /* If encryption is set up make sure that used key_id is found */ if (encrypt == FIL_ENCRYPTION_ON || (encrypt == FIL_ENCRYPTION_DEFAULT && srv_encrypt_tables)) { - if (!encryption_key_id_exists((unsigned int)options->encryption_key_id)) { + if (!encryption_key_id_exists(options->encryption_key_id)) { push_warning_printf( thd, Sql_condition::WARN_LEVEL_WARN, HA_WRONG_CREATE_OPTION, - "InnoDB: ENCRYPTION_KEY_ID %u not available", - (uint)options->encryption_key_id + "InnoDB: ENCRYPTION_KEY_ID=%u not available", + options->encryption_key_id ); return "ENCRYPTION_KEY_ID"; } } - /* Ignore nondefault key_id if encryption is set off */ - if (encrypt == FIL_ENCRYPTION_OFF && - options->encryption_key_id != THDVAR(thd, default_encryption_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 - ); - 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 + /* Used encryption key_id must be found from key file even + when encryption is disabled by table options or default + encryption is used and encryption is disabled. This is + because encryption can be enabled later and it is possible + only if all existing tables use key_id that is found + from key file. */ + if ((encrypt == FIL_ENCRYPTION_OFF + || (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" + if (!encryption_key_id_exists(options->encryption_key_id)) { + + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_WARN, + HA_WRONG_CREATE_OPTION, + "InnoDB: ENCRYPTION_KEY_ID=%u not available", + options->encryption_key_id ); - return "ENCRYPTION_KEY_ID"; + return "ENCRYPTION_KEY_ID"; + } } /* Check atomic writes requirements */
Greetings from MariaDB Openworks in New York City. I just discussed this with Monty. His idea is that "don’t care" attributes should be ignored, or at most a warning could be issued for them. This allows ALTER TABLE from one storage engine to another while preserving attributes that only matter for some storage engines. Based on that basic principle, we should ignore the encryption_key_id attribute (whether or not it was explicitly specified by the user, or inherited from the session variable innodb_default_encryption_key_id). We could issue a warning if innodb_default_encryption_key_id is assigned to an invalid value, or an invalid encryption_key_id is being implicitly or explicitly passed to CREATE TABLE or ALTER TABLE. On Thu, Feb 21, 2019 at 2:53 PM jan <jan.lindstrom@mariadb.com> wrote: [snip]
diff --git a/mysql-test/suite/encryption/r/innodb-encryption-alter.result b/mysql-test/suite/encryption/r/innodb-encryption-alter.result index 5245d1da7d0..038e2a2fd47 100644 --- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result +++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result @@ -3,24 +3,18 @@ SET GLOBAL innodb_file_per_table = ON; 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 DROP TABLE t1; set innodb_default_encryption_key_id = 99; +Warnings: +Warning 1210 InnoDB: Ignored innodb_default_encryption_key_id=99 as it is not available in the key file. Using default=1.
I think that the warning during CREATE TABLE was appropriate and should not be removed. A warning for the SET is appropriate, but I do not like the "Using default=1" part. I think that we should keep the invalid attribute, and throw an error or warning during CREATE TABLE as appropriate.
CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; -ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available -Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
I believe that this CREATE TABLE should continue to be disallowed, because innodb_encrypt_tables=ON and innodb_default_encryption_key_id=9 should imply ENCRYPTED=YES and ENCRYPTION_KEY_ID=99, and the key 99 is not available. Even if you think that it should be allowed (I find it hard to agree with that), I believe that we should at least be issuing warnings. [snip]
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'
Here, it is correct that we should not throw a warning, because innodb_encrypt_tables=OFF causes the encryption_key_id to not matter. But we could issue a warning if ENCRYPTION_KEY_ID is not available. I am going to push a revised patch to the bb-10.1-marko branch and ask for your review. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
participants (2)
-
jan -
Marko Mäkelä