Friday, April 20, 2018

Fun with caching_sha2_password in MySQL 8.0.11


I want to get benchmark numbers with MySQL 8.0.11. This is my first impression. The default auth method was changed to caching_sha2_password. See this post for more details. There will be some confusion with this change. By confusion I mean the difference between "error" and "OK because cached" below. I am not alone. See the experience that an expert had with replication.

Fun with caching_sha2_password occurs even with clients compiled as part of 8.0.11:

  1. install MySQL 8.0.11, disable SSL but use mostly default my.cnf
  2. bin/mysql -u... -p... -h127.0.0.1 -e ... -> error
  3. bin/mysql -u... -p... -e ... -> OK
  4. bin/mysql -u... -p... -h127.0.0.1 -e ... -> OK because cached

The error in step 2 is: ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

From show global variables I see the default is caching_sha2_password:

default_authentication_plugin   caching_sha2_password
Setting this in my.cnf after I created the user doesn't fix the problem. Setting this before creating the user is one fix. I did not test whether changing the value of user.plugin to "mysql_native_password" is another workaround.
default_authentication_plugin=mysql_native_password
The error when using an old mysql client will also be a source of confusion:
$ ~/b/orig5635/bin/mysql -u... -p.. -h127.0.0.1
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mdcallag/b/orig5635/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
 

3 comments:

  1. I have an opinion on the security of the new plugin with default settings:
    http://databaseblog.myname.nl/2018/02/how-cachingsha2password-leaks-passwords.html

    For performance I would expect good results with server, client and account all using the new plugin. However if the account uses the old plugin you'll need an extra network roundtrip unless the default is changed for the server.

    ReplyDelete
    Replies
    1. Nice post. The bug you filed is pink (hidden) - https://bugs.mysql.com/bug.php?id=79944

      Delete
  2. I've asked Oracle to make that bug public and I don't see any reason why it should remain private. It has most of the same info as the blog post.

    ReplyDelete