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>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.