MySQL Bugs: #89977: Impossible to change storage engine of encrypted table after decrypting

Bug #89977	Impossible to change storage engine of encrypted table after decrypting
Submitted:	9 Mar 2018 9:03	Modified:	15 Apr 2018 14:12
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.7.20	OS:	CentOS (7)
Assigned to:		CPU Architecture:	Any

Description:
The weird thing happened when you try to change engine of encrypted table:

create table t1(id int not null) encryption='Y';
alter table t1 engine=myisam;
ERROR 1031 (HY000): Table storage engine for '#sql-3250_3' doesn't have this option

Now trying with encryption='N':

alter table t1 encryption='N';
Query OK, 0 rows affected (0,73 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table t1 engine=myisam;
ERROR 1031 (HY000): Table storage engine for '#sql-3250_3' doesn't have this option

As a result it is impossible to change storage engine of encrypted table, even after decrypting.

Further testing:

create table t1(id int not null);
alter table t1 engine=myisam;

Possible to alter myisam table:

alter table t1 encryption='Y';
Query OK, 0 rows affected (0,04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Converting back to innodb:

alter table t1 engine=innodb;
Query OK, 0 rows affected (0,38 sec)
Records: 0  Duplicates: 0  Warnings: 0

Trying to convert to myisam:

alter table t1 engine=myisam;
ERROR 1031 (HY000): Table storage engine for '#sql-3250_3' doesn't have this option

How to repeat:
See description

Hi!

I have got the same results as you have. 

However, this is not a bug in the InnoDB storage engine, but a behaviour that is not documented.

Verified as a documentation request.

Posted by developer:
 
The limitation was added to the Tablespace Encryption documentation:
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

Thank you for the bug report.