Bug #96083 innodb_undo_log_encrypt gets enabled with negative values
Submitted: 3 Jul 2019 10:37 Modified: 5 Jul 2019 11:21
Reporter: Hrvoje Matijakovic Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Encryption Severity:S3 (Non-critical)
Version:8.0.16 OS:Linux
Assigned to: CPU Architecture:x86

[3 Jul 2019 10:37] Hrvoje Matijakovic
Description:
innodb_undo_log_encrypt gets enabled with negative values:

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> set global innodb_undo_log_encrypt=-21;
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

Suggested fix:
Add checks for negative values like there are for values > 1. 

mysql> set global innodb_undo_log_encrypt=21;
ERROR 1231 (42000): Variable 'innodb_undo_log_encrypt' can't be set to the value of '21'
[3 Jul 2019 13:38] MySQL Verification Team
- On binary tarball build - even though it allows to set negative for innodb_undo_log_encrypt but parameter is still disabled.
- Need to confirm with rpm/debian installation environment i.e it enables parameter with negative value

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
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> set global innodb_undo_log_encrypt=-21;
Query OK, 0 rows affected (0.03 sec)

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

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

mysql> set global innodb_undo_log_encrypt=21;
ERROR 1231 (42000): Variable 'innodb_undo_log_encrypt' can't be set to the value of '21'
mysql> set global innodb_undo_log_encrypt=-21;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_undo_log_encrypt;
+---------------------------+
| @@innodb_undo_log_encrypt |
+---------------------------+
|                         0 |
+---------------------------+
1 ro
[4 Jul 2019 7:16] MySQL Verification Team
Hello Hrvoje,

Thank you for the report.
Confirmed that negative value is accepted for innodb_undo_log_encrypt but didn't notice that it enabled afterwards(on couple of instance including reported version i.e Debian9 - Server installed using apt repo, CentOS7 - Server installed using yum repository). Could you please confirm if this is the case? Thank you.

regards,
Umesh
[4 Jul 2019 7:17] MySQL Verification Team
- Debian9
root@umshastr:/home/ushastry/Downloads# dpkg -l|grep -i mysql
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
ii  mysql-connector-python                8.0.13-1debian9                             all          MySQL database driver written in Python
ii  mysql-server                          8.0.16-2debian9                             amd64        MySQL Server meta package depending on latest version
root@umshastr:/home/ushastry/Downloads# 

mysql -uroot -p
Enter password: 
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>  set global innodb_undo_log_encrypt=-21;
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> select @@innodb_undo_log_encrypt;
+---------------------------+
| @@innodb_undo_log_encrypt |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
root@umshastr:/home/ushastry/Downloads# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
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> set global innodb_undo_log_encrypt=21;
ERROR 1231 (42000): Variable 'innodb_undo_log_encrypt' can't be set to the value of '21'
[4 Jul 2019 7:17] MySQL Verification Team
- CentOS7

root@BugCentOS7 ushastry]# rpm -qa|grep -i mysql
mysql80-community-release-el7-3.noarch
mysql-utilities-1.6.5-1.el7.noarch
mysql-connector-python-8.0.16-1.el7.x86_64
mysql-connector-c++-8.0.16-1.el7.x86_64
mysql-community-test-8.0.16-2.el7.x86_64
mysql-community-libs-8.0.16-2.el7.x86_64
mysql-workbench-community-8.0.12-1.el7.x86_64
mysql-connector-c++-jdbc-8.0.16-1.el7.x86_64
mysql-community-client-8.0.16-2.el7.x86_64
mysql-shell-8.0.12-1.el7.x86_64
mysql-connector-python-cext-8.0.16-1.el7.x86_64
mysql-community-common-8.0.16-2.el7.x86_64
mysql-connector-c++-devel-8.0.16-1.el7.x86_64
mysql-community-server-8.0.16-2.el7.x86_64

[root@BugCentOS7 ushastry]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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>  set global innodb_undo_log_encrypt=-21;
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> set global innodb_undo_log_encrypt=21;
ERROR 1231 (42000): Variable 'innodb_undo_log_encrypt' can't be set to the value of '21'
mysql>
[5 Jul 2019 10:56] Hrvoje Matijakovic
I'm sorry I've omitted the part that I've added the keyring_file to the config. I'm able to reproduce the same behaviour as yours when the keyring plugin isn't loaded, but once I load it it behaves as originally reported:

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

mysql>  set global innodb_undo_log_encrypt=-21;
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:~# echo "early-plugin-load=keyring_file.so" >> /etc/mysql/mysql.conf.d/mysqld.cnf
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>  set global innodb_undo_log_encrypt=-21;
Query OK, 0 rows affected (0.01 sec)

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

Reproduced on stretch with the same set of packages as reported above.
[5 Jul 2019 11:21] MySQL Verification Team
Thank you for the feedback.
I'm able to reproduce even on binary tarball build provided keyring is enabled.

- 8.0.16

rm -rf 96083/
rm -rf keyring/
mkdir keyring
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96083 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96083 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/96083/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --early-plugin-load='keyring_file.so' --keyring_file_data=$PWD/keyring/keyring 2>&1 &

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
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> set global innodb_undo_log_encrypt=-21;
Query OK, 0 rows affected (0.01 sec)

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=21;
ERROR 1231 (42000): Variable 'innodb_undo_log_encrypt' can't be set to the value of '21'
mysql>