Bug #103216 create table failed with ENCRYPTION set to Y after ALTER MASTER KEY crash

• Submitted: 6 Apr 2021 8:30
• Modified: 7 Apr 2021 4:43
• Reporter: Xlong Li (OCA)
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.22,8.0.23
• OS: Any
• CPU Architecture: Any

[6 Apr 2021 8:30] Xlong Li

Description:

First, initialize the data with none-file_per_table mode
Then, start up database 
run SQL like this :

SET GLOBAL innodb_file_per_table = 1;
ALTER TABLESPACE mysql ENCRYPTION='Y';
CREATE TABLE tde_db.t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
INSERT INTO tde_db.t1 VALUES(0, "aaaaa");
INSERT INTO tde_db.t1 VALUES(1, "bbbbb");
INSERT INTO tde_db.t1 VALUES(2, "ccccc");
INSERT INTO tde_db.t1 VALUES(3, "ddddd");

SET SESSION DEBUG="+d,ib_encryption_rotate_crash";
ALTER INSTANCE ROTATE INNODB MASTER KEY;

#crash recovery then start db with none-file_per_table mode

SET SESSION DEBUG="-d,ib_encryption_rotate_crash";
INSERT INTO tde_db.t1 VALUES(4, "eeeee");

CREATE TABLE tde_db.t2(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;

create table  tde_db.t2 failed with ERROR:
ERROR 3825 (HY000): Request to create 'encrypted' table while using an 'unencrypted' tablespace.

but all tablespace is ENCRYPTION, when running "SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE OPTIONS LIKE '%encryption=Y%';" with result: 
"NAME    OPTIONS
mysql   encryption=Y;
tde_db/t1       autoextend_size=0;encryption=Y;"

How to repeat:
Repeat as described above

[6 Apr 2021 9:58] MySQL Verification Team

Hello Xlong Li,

Thank you for the report and test case.

regards,
Umesh

[7 Apr 2021 4:43] MySQL Verification Team

Hello Xlong Li,

Discussed internally with the developer(who handle this part of the code) and concluded that it is not a bug. You are trying to create an encrypted table while innodb-file-per-table=OFF. Which causes table to be created in innodb_system_tablesapce (ibdata1) which is not encrypted. 

Closing this as not a bug.

regards,
Umesh