MDEV-7593: Default encryption key does not work correctly for page

encrypted tables

Introduced a new innodb_default_page_encryption_key configuration
variable to allow user to set the default key identifier.

5 files changed, 195 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-page_encryption.result b/mysql-test/suite/innodb/r/innodb-page_encryption.result
index 77ca7a9205b..dfc93508b90 100644
--- a/mysql-test/suite/innodb/r/innodb-page_encryption.result
+++ b/mysql-test/suite/innodb/r/innodb-page_encryption.result
@@ -5,6 +5,14 @@ create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_f
 create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed page_encryption=1 page_encryption_key=2;
 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic page_encryption=1  page_encryption_key=3;
 create table innodb_redundant(c1 bigint not null, b char(200))  engine=innodb row_format=redundant page_encryption=1  page_encryption_key=4;
+SET GLOBAL innodb_default_page_encryption_key = 5;
+create table innodb_defkey(c1  bigint not null, b char(200))  engine=innodb page_encryption=1;
+show create table innodb_defkey;
+Table	Create Table
+innodb_defkey	CREATE TABLE `innodb_defkey` (
+  `c1` bigint(20) NOT NULL,
+  `b` char(200) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_encryption`=1
 show create table innodb_compact;
 Table	Create Table
 innodb_compact	CREATE TABLE `innodb_compact` (
@@ -47,11 +55,13 @@ insert into innodb_compact select * from innodb_normal;
 insert into innodb_compressed select * from innodb_normal;
 insert into innodb_dynamic select * from innodb_normal;
 insert into innodb_redundant select * from innodb_normal;
+insert into innodb_defkey select * from innodb_normal;
 update innodb_normal set c1 = c1 +1;
 update innodb_compact set c1 = c1 + 1;
 update innodb_compressed set c1 = c1 + 1;
 update innodb_dynamic set c1 = c1 + 1;
 update innodb_redundant set c1 = c1 + 1;
+update innodb_defkey set c1 = c1 + 1;
 select count(*) from innodb_compact where c1 < 1500000;
 count(*)
 2000
@@ -64,6 +74,9 @@ count(*)
 select count(*) from innodb_redundant where c1 < 1500000;
 count(*)
 2000
+select count(*) from innodb_defkey where c1 < 1500000;
+count(*)
+2000
 select count(*) from innodb_compact t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 count(*)
@@ -80,6 +93,10 @@ select count(*) from innodb_redundant t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 count(*)
 2000
+select count(*) from innodb_defkey t1, innodb_normal t2 where
+t1.c1 = t2.c1 and t1.b = t2.b;
+count(*)
+2000
 SELECT variable_value >= 0 FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_num_pages_page_encrypted';
 variable_value >= 0
 1
@@ -96,6 +113,7 @@ update innodb_compact set c1 = c1 + 1;
 update innodb_compressed set c1 = c1 + 1;
 update innodb_dynamic set c1 = c1 + 1;
 update innodb_redundant set c1 = c1 + 1;
+update innodb_defkey set c1 = c1 + 1;
 select count(*) from innodb_compact where c1 < 1500000;
 count(*)
 2000
@@ -108,6 +126,9 @@ count(*)
 select count(*) from innodb_redundant where c1 < 1500000;
 count(*)
 2000
+select count(*) from innodb_defkey where c1 < 1500000;
+count(*)
+2000
 select count(*) from innodb_compact t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 count(*)
@@ -124,6 +145,10 @@ select count(*) from innodb_redundant t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 count(*)
 2000
+select count(*) from innodb_defkey t1, innodb_normal t2 where
+t1.c1 = t2.c1 and t1.b = t2.b;
+count(*)
+2000
 SELECT variable_value >= 0 FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_num_pages_page_encrypted';
 variable_value >= 0
 1
@@ -187,6 +212,12 @@ innodb_redundant	CREATE TABLE `innodb_redundant` (
   `c1` bigint(20) NOT NULL,
   `b` char(200) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
+show create table innodb_defkey;
+Table	Create Table
+innodb_defkey	CREATE TABLE `innodb_defkey` (
+  `c1` bigint(20) NOT NULL,
+  `b` char(200) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_encryption`=1
 update innodb_normal set c1 = c1 +1;
 update innodb_compact set c1 = c1 + 1;
 update innodb_compressed set c1 = c1 + 1;
@@ -235,3 +266,4 @@ drop table innodb_compact;
 drop table innodb_compressed;
 drop table innodb_dynamic;
 drop table innodb_redundant;
+drop table innodb_defkey;
diff --git a/mysql-test/suite/innodb/t/innodb-page_encryption.test b/mysql-test/suite/innodb/t/innodb-page_encryption.test
index 3664d8d0f75..66927d1905f 100644
--- a/mysql-test/suite/innodb/t/innodb-page_encryption.test
+++ b/mysql-test/suite/innodb/t/innodb-page_encryption.test
@@ -4,6 +4,7 @@
 --disable_query_log
 let $innodb_file_format_orig = `SELECT @@innodb_file_format`;
 let $innodb_file_per_table_orig = `SELECT @@innodb_file_per_table`;
+let $default_page_encryption_key = `SELECT @@innodb_default_page_encryption_key`;
 --enable_query_log
 
 SET GLOBAL innodb_file_format = `Barracuda`;
@@ -15,6 +16,10 @@ create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb ro
 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic page_encryption=1  page_encryption_key=3;
 create table innodb_redundant(c1 bigint not null, b char(200))  engine=innodb row_format=redundant page_encryption=1  page_encryption_key=4;
 
+SET GLOBAL innodb_default_page_encryption_key = 5;
+create table innodb_defkey(c1  bigint not null, b char(200))  engine=innodb page_encryption=1;
+show create table innodb_defkey;
+
 show create table innodb_compact;
 show create table innodb_compressed;
 show create table innodb_dynamic;
@@ -42,16 +47,20 @@ insert into innodb_compact select * from innodb_normal;
 insert into innodb_compressed select * from innodb_normal;
 insert into innodb_dynamic select * from innodb_normal;
 insert into innodb_redundant select * from innodb_normal;
+insert into innodb_defkey select * from innodb_normal;
 
 update innodb_normal set c1 = c1 +1;
 update innodb_compact set c1 = c1 + 1;
 update innodb_compressed set c1 = c1 + 1;
 update innodb_dynamic set c1 = c1 + 1;
 update innodb_redundant set c1 = c1 + 1;
+update innodb_defkey set c1 = c1 + 1;
+
 select count(*) from innodb_compact where c1 < 1500000;
 select count(*) from innodb_compressed where c1 < 1500000;
 select count(*) from innodb_dynamic where c1 < 1500000;
 select count(*) from innodb_redundant where c1 < 1500000;
+select count(*) from innodb_defkey where c1 < 1500000;
 select count(*) from innodb_compact t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 select count(*) from innodb_dynamic t1, innodb_normal t2 where
@@ -60,6 +69,8 @@ select count(*) from innodb_compressed t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 select count(*) from innodb_redundant t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
+select count(*) from innodb_defkey t1, innodb_normal t2 where
+t1.c1 = t2.c1 and t1.b = t2.b;
 
 # Note there that these variables are updated only when real I/O is done, thus they are not reliable
 SELECT variable_value >= 0 FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_num_pages_page_encrypted';
@@ -76,10 +87,12 @@ update innodb_compact set c1 = c1 + 1;
 update innodb_compressed set c1 = c1 + 1;
 update innodb_dynamic set c1 = c1 + 1;
 update innodb_redundant set c1 = c1 + 1;
+update innodb_defkey set c1 = c1 + 1;
 select count(*) from innodb_compact where c1 < 1500000;
 select count(*) from innodb_compressed where c1 < 1500000;
 select count(*) from innodb_dynamic where c1 < 1500000;
 select count(*) from innodb_redundant where c1 < 1500000;
+select count(*) from innodb_defkey where c1 < 1500000;
 select count(*) from innodb_compact t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 select count(*) from innodb_dynamic t1, innodb_normal t2 where
@@ -88,6 +101,8 @@ select count(*) from innodb_compressed t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
 select count(*) from innodb_redundant t1, innodb_normal t2 where
 t1.c1 = t2.c1 and t1.b = t2.b;
+select count(*) from innodb_defkey t1, innodb_normal t2 where
+t1.c1 = t2.c1 and t1.b = t2.b;
 
 SELECT variable_value >= 0 FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_num_pages_page_encrypted';
 SELECT variable_value >= 0 FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_num_pages_page_decrypted';
@@ -111,6 +126,7 @@ show create table innodb_compact;
 show create table innodb_compressed;
 show create table innodb_dynamic;
 show create table innodb_redundant;
+show create table innodb_defkey;
 
 update innodb_normal set c1 = c1 +1;
 update innodb_compact set c1 = c1 + 1;
@@ -141,9 +157,11 @@ drop table innodb_compact;
 drop table innodb_compressed;
 drop table innodb_dynamic;
 drop table innodb_redundant;
+drop table innodb_defkey;
 
 # reset system
 --disable_query_log
 EVAL SET GLOBAL innodb_file_per_table = $innodb_file_per_table_orig;
 EVAL SET GLOBAL innodb_file_format = $innodb_file_format_orig;
+EVAL SET GLOBAL innodb_default_page_encryption_key = $default_page_encryption_key;
 --enable_query_log
diff --git a/mysql-test/suite/sys_vars/r/innodb_default_page_encryption_key_basic.result b/mysql-test/suite/sys_vars/r/innodb_default_page_encryption_key_basic.result
new file mode 100644
index 00000000000..1e779d7c4f6
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/innodb_default_page_encryption_key_basic.result
@@ -0,0 +1,71 @@
+SET @start_global_value = @@global.innodb_default_page_encryption_key;
+SELECT @start_global_value;
+@start_global_value
+1
+Valid value 0-9
+select @@global.innodb_default_page_encryption_key <= 9;
+@@global.innodb_default_page_encryption_key <= 9
+1
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+1
+select @@session.innodb_default_page_encryption_key;
+ERROR HY000: Variable 'innodb_default_page_encryption_key' is a GLOBAL variable
+show global variables like 'innodb_default_page_encryption_key';
+Variable_name	Value
+innodb_default_page_encryption_key	1
+show session variables like 'innodb_default_page_encryption_key';
+Variable_name	Value
+innodb_default_page_encryption_key	1
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	1
+select * from information_schema.session_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	1
+set global innodb_default_page_encryption_key=2;
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+2
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	2
+select * from information_schema.session_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	2
+set session innodb_default_page_encryption_key=4;
+ERROR HY000: Variable 'innodb_default_page_encryption_key' is a GLOBAL variable and should be set with SET GLOBAL
+set global innodb_default_page_encryption_key=1.1;
+ERROR 42000: Incorrect argument type to variable 'innodb_default_page_encryption_key'
+set global innodb_default_page_encryption_key=1e1;
+ERROR 42000: Incorrect argument type to variable 'innodb_default_page_encryption_key'
+set global innodb_default_page_encryption_key="foo";
+ERROR 42000: Incorrect argument type to variable 'innodb_default_page_encryption_key'
+set global innodb_default_page_encryption_key=10;
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+10
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	10
+set global innodb_default_page_encryption_key=-7;
+Warnings:
+Warning	1292	Truncated incorrect innodb_default_page_encryption_k value: '-7'
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+1
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_DEFAULT_PAGE_ENCRYPTION_KEY	1
+set global innodb_default_page_encryption_key=1;
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+1
+set global innodb_default_page_encryption_key=255;
+select @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+255
+SET @@global.innodb_default_page_encryption_key = @start_global_value;
+SELECT @@global.innodb_default_page_encryption_key;
+@@global.innodb_default_page_encryption_key
+1
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 966187a61ab..553ca6f4042 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -565,6 +565,20 @@ NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	NULL
 READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	INNODB_DEFAULT_PAGE_ENCRYPTION_KEY
+SESSION_VALUE	NULL
+GLOBAL_VALUE	1
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	1
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	Encryption key used for page encryption.
+NUMERIC_MIN_VALUE	1
+NUMERIC_MAX_VALUE	255
+NUMERIC_BLOCK_SIZE	0
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_DEFRAGMENT
 SESSION_VALUE	NULL
 GLOBAL_VALUE	OFF
diff --git a/mysql-test/suite/sys_vars/t/innodb_default_page_encryption_key_basic.test b/mysql-test/suite/sys_vars/t/innodb_default_page_encryption_key_basic.test
new file mode 100644
index 00000000000..c8a02cb8c74
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/innodb_default_page_encryption_key_basic.test
@@ -0,0 +1,60 @@
+
+--source include/have_innodb.inc
+
+SET @start_global_value = @@global.innodb_default_page_encryption_key;
+SELECT @start_global_value;
+
+#
+# exists as global only
+#
+--echo Valid value 0-9
+select @@global.innodb_default_page_encryption_key <= 9;
+select @@global.innodb_default_page_encryption_key;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.innodb_default_page_encryption_key;
+show global variables like 'innodb_default_page_encryption_key';
+show session variables like 'innodb_default_page_encryption_key';
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+select * from information_schema.session_variables where variable_name='innodb_default_page_encryption_key';
+
+#
+# show that it's writable
+#
+set global innodb_default_page_encryption_key=2;
+select @@global.innodb_default_page_encryption_key;
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+select * from information_schema.session_variables where variable_name='innodb_default_page_encryption_key';
+--error ER_GLOBAL_VARIABLE
+set session innodb_default_page_encryption_key=4;
+
+#
+# incorrect types
+#
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_default_page_encryption_key=1.1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_default_page_encryption_key=1e1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_default_page_encryption_key="foo";
+
+set global innodb_default_page_encryption_key=10;
+select @@global.innodb_default_page_encryption_key;
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+set global innodb_default_page_encryption_key=-7;
+select @@global.innodb_default_page_encryption_key;
+select * from information_schema.global_variables where variable_name='innodb_default_page_encryption_key';
+
+#
+# min/max values
+#
+set global innodb_default_page_encryption_key=1;
+select @@global.innodb_default_page_encryption_key;
+set global innodb_default_page_encryption_key=255;
+select @@global.innodb_default_page_encryption_key;
+
+#
+# cleanup
+#
+
+SET @@global.innodb_default_page_encryption_key = @start_global_value;
+SELECT @@global.innodb_default_page_encryption_key;