Bug #93044 Save roundtrip in client by respecting the auth plugin in the server greeting
Submitted: 1 Nov 2018 14:48 Modified: 10 Feb 12:33
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S4 (Feature request)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2018 14:48] Daniël van Eeden
Description:
The MySQL 8.0 client always tries to authenticate with the caching_sha2 plugin even on MySQL 5.7 servers. This causes an extra roundtrip which hurts performance.

How to repeat:
MySQL 8.0.13 client to MySQL 5.7.23:
←: Server Greeting: Authentication Plugin: mysql_native_password
→: Login Request: Client Auth Plugin: caching_sha2_password
←: Auth Switch Request: Auth Method Name: mysql_native_password
→: Auth Switch Response
←: OK

This also breaks connecting to some non-MySQL servers (Vitess vtgate):
MySQL 8.0.13 client to vtgate:
←: Server Greeting: Authentication Plugin: mysql_native_password
→: Login Request: Client Auth Plugin: caching_sha2_password
←: MySQL Error 2012 (HY000): Client asked for auth caching_sha2_password, but server wants auth mysql_native_password

Suggested fix:
The client should use the auth plugin as send by the server and only use Auth Switch Request/Response if the account doesn't match the default.
[1 Nov 2018 14:49] Daniël van Eeden
Related: https://github.com/vitessio/vitess/issues/4332
[1 Nov 2018 15:07] Georgi Kodinov
Proposed fix is for libmysql to take the server method if it knows about it or otherwise take the client default.
Note that the server sends the server method name and not the corresponding client method. So a client might need to keep track of several server methods it knows about and do its own translation. This is not very optimal, but is a start of sorts.
[8 Feb 9:38] Georgi Kodinov
Posted by developer:
 
Daniel,

Have you looked at using mysql_options(MYSQL_DEFAULT_AUTH) ?
and the corresponding https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_default-au... client side option ?
[10 Feb 12:33] Daniël van Eeden
The MYSQL_DEFAULT_AUTH option and --default-auth work. However that just moves the problem.

CLIENT     OPTIONS     SERVER    EXTRA_ROUNDTRIP
8.0        default     8.0       No
8.0        default     5.7       Yes
8.0        def=native  8.0       Yes
8.0        def=native  5.7       No

If the client would respect the default auth as send by the server none of these would have an extra roundtrip (assuming all accounts use the default authentication method).