MySQL Bugs: #93044: Save roundtrip in client by respecting the auth plugin in the server greeting

Bug #93044	Save roundtrip in client by respecting the auth plugin in the server greeting
Submitted:	1 Nov 2018 14:48	Modified:	30 Sep 2020 12:59
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, 8.0.21	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	auth, authentication, Connection, network, plugin, roundtrip, tls

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.

Related: https://github.com/vitessio/vitess/issues/4332

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.

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 ?

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).

The problem with the client "respecting the default auth" is that, as evident from the code itself (https://github.com/mysql/mysql-server/blob/f8cdce86448a211511e8a039c62580ae16cb96f5/sql/au...) the server is sending the *server* authentication method name to the client. 

And all the client knows about is the client authentication method names. 

In some limited cases (e.g. mysql_native) the client and the server authentication plugins are using the same name. But this doesn't exclude the fact that other client authentication methods (plugins) could eventually work too: e.g.:

server -- [ server hello + mysql_native auth name and challenge ] --> client
server <-- [ client hello + unix socket plugin name and empty reply ] --- client
server --- [ OK, if the user account is defined to be taking a unix socket ] --> client

So even if the server is sending a valid mysql_native auth challenge the client can choose to ignore it.

And it's only the server that knows how to map a client auth method to a server one. 

Thus, aside from some special cases where the client and server authentication method names match there's not a lot that can be done here. 

Is this what you request ?

Hi,

The default plugins on 5.7 and 8.0 use the same name for server and client plugin.

With a simple mapping like the one below the client would be able to get the
client plugin name from the server one.

SERVER                      CLIENT
mysql_native_password       the same
sha256_password             the same
caching_sha2_password       the same
authentication_pam          mysql_clear_password
authentication_windows      authentication_windows_client
authentication_ldap_simple  mysql_clear_password
authentication_ldap_sasl    authentication_ldap_sasl_client
auth_socket                 none

This is only to reduce the chance of having the extra roundtrip. Instead of always
using caching_sha2 against a server that has mysql_native_password as default, and
likely the same for most accounts and then for every connection having the extra
roundtrip it should try to be a little bit smarter and try with mysql_native_password,
which is more likely to not need the extra roundtrip.

This is just one of the things that could be done to reduce connections setup time,
especially cross datacenter/availabily zone.

Other things to reduce roundtrips and setup time include:
* TLS Tickets
* TLSv1.3 zero-RTT
* Directly starting TLS instead of a plain-text Greeting/Login
* Use of a proxy/router locally with a pre-established connection
* Using a connection pool (Java style) instead of connection setup in the critical
  part of the application (as is often done with PHP, Perl, Python, etc)

Each of these has it's own issues and complications. To get the best result it may
be needed to combine multiple of these.

Hi Daniel,

All your expounded  ideas are now part of this feature request.

Fully verified.