MySQL Bugs: #96109: Document how to disable undo_log encryption or add to limitations that it can't

Bug #96109	Document how to disable undo_log encryption or add to limitations that it can't
Submitted:	5 Jul 2019 13:28	Modified:	11 Jul 2019 13:08
Reporter:	Hrvoje Matijakovic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
I wasn't able to find in the docs how to disable the undo_log encryption. 

Currently once:
1) the server has been started with early-plugin-load=keyring_file.so and
2) innodb_undo_log_encrypt set to 1, 
3) setting the innodb_undo_log_encrypt to 0,
4) stopping the service, 
5)removing the keyring_file.so from the config file,
6) results in server error on startup

From the discussion with developers this is happening because once turned on even if the encryption is turned off in the future, the server can't guarantee that there aren't any previously encrypted records in it, so it keeps the encryption header.

How to repeat:
Installed packages:
root@stretch:~# dpkg -l | grep mysql
ii  libdbd-mysql-perl              4.041-2                        amd64        Perl5 database interface to the MariaDB/MySQL database
ii  mysql-apt-config               0.8.13-1                       all          Auto configuration for MySQL APT Repo.
ii  mysql-client                   8.0.16-2debian9                amd64        MySQL Client meta package depending on latest version
ii  mysql-common                   8.0.16-2debian9                amd64        Common files shared between packages
ii  mysql-community-client         8.0.16-2debian9                amd64        MySQL Client
ii  mysql-community-client-core    8.0.16-2debian9                amd64        MySQL Client Core Binaries
ii  mysql-community-server         8.0.16-2debian9                amd64        MySQL Server
ii  mysql-community-server-core    8.0.16-2debian9                amd64        MySQL Server Core Binaires

mysql> select @@innodb_undo_log_encrypt;
+---------------------------+
| @@innodb_undo_log_encrypt |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql>  set global innodb_undo_log_encrypt=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_undo_log_encrypt;
+---------------------------+
| @@innodb_undo_log_encrypt |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql> ^DBye
root@stretch:~# service mysql restart
root@stretch:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_undo_log_encrypt;
+---------------------------+
| @@innodb_undo_log_encrypt |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql> ^DBye
root@stretch:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
root@stretch:~# service mysql restart
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

Suggested fix:
If there is a way, pleaser document on how to disable the undo ts encryption if not, please add it to the limitations.

Zdravo Hrvoje,

Thank you for your bug report.

This is truly a bug in documentation.

Verified as reported.

Puno pozdrava !!!!!!

Posted by developer:
 
The following note was added:

"When undo log encryption is disabled, the server continues to require the
keyring plugin that was used to encrypt undo log data until the undo
tablespaces that contained the encrypted undo log data are truncated. 
(An encryption header is only removed from an undo tablespace when the
undo tablespace is truncated.) For information about truncating undo 
tablespaces, see Truncating Undo Tablespaces."

The following development bug was opened:
Bug #30034552: INNODB: NON-DETERMINATE TIME FOR UNDO TABLESPACE UN-ECRYPTION

Thank you, Daniel.

Posted by developer:
 
Bug #30034552 aims to provide monitoring of encryption use in undo tablespaces and provide a setting that causes immediate un-encryption of undo tablespaces.