MDEV-18601: Can't create table with ENCRYPTED=DEFAULT when innodb_default_encryption_key_id!=1bb-10.1-MDEV-18601

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.

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 472fb86288f..51dbf0c0b13 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -11955,30 +11955,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 c9db941b4bf..4ec4754a5c4 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -12522,30 +12522,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 */