Bug #72935 Unknown variable sha256_password_public_key_path ...
Submitted: 10 Jun 2014 12:13 Modified: 12 Jun 2014 14:18
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.19 OS:Linux
Assigned to: Paul Dubois CPU Architecture:Any
Tags: regression, sha256_password, sha256_password_public_key_path, sha256_private_key_path

[10 Jun 2014 12:13] Valeriy Kravchuk
Description:
Manual (at http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sha256_password... and on other pages) clearly says that sha256_password_public_key_path variable is supported and sha256_password plugin is "there" by default. But this is what I see when I try to use it as manual explains:

140610 14:48:44 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-06-10 14:48:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-06-10 14:48:46 2502 [Note] Plugin 'FEDERATED' is disabled.
2014-06-10 14:48:46 2502 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-06-10 14:48:46 2502 [Note] InnoDB: The InnoDB memory heap is disabled
2014-06-10 14:48:46 2502 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-06-10 14:48:46 2502 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-06-10 14:48:46 2502 [Note] InnoDB: Using Linux native AIO
2014-06-10 14:48:46 2502 [Note] InnoDB: Not using CPU crc32 instructions
2014-06-10 14:48:46 2502 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-06-10 14:48:47 2502 [Note] InnoDB: Completed initialization of buffer pool
2014-06-10 14:48:47 2502 [Note] InnoDB: Highest supported file format is Barracuda.
2014-06-10 14:48:47 2502 [Note] InnoDB: 128 rollback segment(s) are active.
2014-06-10 14:48:47 2502 [Note] InnoDB: Waiting for purge to start
2014-06-10 14:48:47 2502 [Note] InnoDB: 5.6.19 started; log sequence number 1627466
2014-06-10 14:48:47 2502 [ERROR] /home/openxs/mysql-5.6.19-linux-glibc2.5-x86_64/bin/mysqld: unknown variable 'sha256_password_public_key_path=/var/lib/mysql/public_key.pem'
2014-06-10 14:48:47 2502 [ERROR] Aborting
...
2014-06-10 14:48:49 2502 [Note] /home/openxs/mysql-5.6.19-linux-glibc2.5-x86_64/bin/mysqld: Shutdown complete

This is what I have in the /etc/my.cnf:

[root@centos mysql-5.6.19-linux-glibc2.5-x86_64]# cat /etc/my.cnf | grep pem    #ssl-ca=/tmp/ca-crt.pem
ssl-ca                              = ca-cert.pem
ssl-cert                            = server-cert.pem
ssl-key                             = server-key.pem
sha256_password_public_key_path     = /var/lib/mysql/public_key.pem
sha256_password_private_key_path    = /var/lib/mysql/private_key.pem

Moreover, it seems Percona Server 5.6.17 (and probably MySQL 5.6.17 it is based on) just started without any problems with the same my.cnf. So, I assume this is either a regression that happened after 5.6.17 or may be caused by yassl vs openssl difference (see no link to openssl libraries below):

[root@centos mysql-5.6.19-linux-glibc2.5-x86_64]# ldd bin/mysqld
        linux-vdso.so.1 =>  (0x00007fffa05ff000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ff04ac1a000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007ff04aa19000)
        librt.so.1 => /lib64/librt.so.1 (0x00007ff04a810000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007ff04a5d9000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007ff04a3d5000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007ff04a0ce000)
        libm.so.6 => /lib64/libm.so.6 (0x00007ff049e4a000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ff049c34000)
        libc.so.6 => /lib64/libc.so.6 (0x00007ff04989f000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ff04ae3e000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007ff049628000)

How to repeat:
Configure sha256_password authentication plugin and create keys etc by the manual, http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html. Use explicit settings in [mysqld] as step 3 in the manual explains:

"In the server option file, configure the appropriate system variables with the names of the key files. If you place the files in the server data directory, you need not specify their full path names:

[mysqld]
sha256_password_private_key_path=mykey.pem
sha256_password_public_key_path=mykey.pub

If the files are not in the data directory, or to make their locations explicit in the option values, use full path names:

[mysqld]
sha256_password_private_key_path=/usr/local/mysql/mykey.pem
sha256_password_public_key_path=/usr/local/mysql/mykey.pub
"

Then try to start MySQL 5.6.19 (.tar.gz binaries in my case) on CentOS 6.5, for example. Note errors in the error log and server not started.

Suggested fix:
Add variables back? Alternatively, change the manual to explain that plugin no longer works, as when I comment out settings (with .pem files having their default names by the manual and located in the datadir) we still can not use this plugin it seems:

[root@centos mysql-5.6.19-linux-glibc2.5-x86_64]# mysql -uroot test             Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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> show variables like '%ssl%';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| have_openssl  | YES                                      |
| have_ssl      | YES                                      |
| ssl_ca        | ca-cert.pem                              |
| ssl_capath    |                                          |
| ssl_cert      | server-cert.pem                          |
| ssl_cipher    | AES256-SHA:DHE-RSA_AES256-SHA:AES256-SHA |
| ssl_crl       |                                          |
| ssl_crlpath   |                                          |
| ssl_key       | server-key.pem                           |
+---------------+------------------------------------------+
9 rows in set (0.00 sec)

mysql> show variables like 'sha256%';
Empty set (0.00 sec)

mysql> show status like 'Rsa%'\G
Empty set (0.00 sec)
[10 Jun 2014 17:13] Valeriy Kravchuk
This is probably a result of Community version being built with YaSSL. Manual explains:

" If MySQL is built using OpenSSL instead, RSA encryption is available and sha256_password implements the additional capabilities in the following list. (To enable these capabilities, you must also follow the RSA configuration procedure given later in this section.)

    It is possible for the client to transmit passwords to the server using RSA encryption during the client connection process, as described later.

    The server exposes two additional system variables, sha256_password_private_key_path and sha256_password_public_key_path. It is intended that the database administrator will set these to the names of the RSA private and public key files at server startup.

    The server exposes a status variable, Rsa_public_key, that displays the RSA public key value.

... "

This explains why sha256* variables are NOT available. The remaining problem is to highlight that MySQL 5.6.x Community Edition is built using YaSSL (still).
[11 Jun 2014 13:02] Umesh Shastry
Hello Valeriy,

Thank you for the report.
As you noted "This variable is available only if MySQL was built using OpenSSL" and the community edition is built using YaSSL and hence it complained.
Also, there is no proper way to figure out whether it is built using openssl or yassl , I would consider this as doc request to highlight about "Community Edition is built using YaSSL" and thus avoid further confusion over this.

Thanks,
Umesh
[11 Jun 2014 13:03] Umesh Shastry
Changing category to Doc request.
[12 Jun 2014 14:18] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Added "(MySQL Community Edition is built using yaSSL.)" to the affected system and status variable descriptions.