Well, That entry is within Mysql to set what type.for your user, you would change ​”mysql_native_password” to “caching_sha2_password“

for example, to change the user , "username" to a caching sha2 password using  "YOUR_PASSWORD" as the new password you would pass this SQL statement:

ALTER USER username IDENTIFIED WITH caching_sha2_password BY 'YOUR_PASSWORD';

Yeah, as I stated in the description it does work if both the server and the user authentication plugin match. You could also make it work changing the server config the variable "default_authentication_plugin" from "caching_sha2_password" to "mysql_native_password" and restarting the server.

But it's just a workaround. PHP should be using the authentication plugin configured for the user instead of the server's default one.

Tested with "Server version: 8.0.17-8 Percona Server (GPL), Release '8', Revision '868a4ef'" on Debian 10. PHP version 7.4.1 (compiled).

The server uses the "caching_sha2_password" by default. I have a user that is configured with "mysql_native_password". This indeed fails with error:

"Unexpected server response while doing caching_sha2 auth: 109"

From tcpdump / wireshark:

I see the client sending a MySQL "Login Request" to the server.
The server responds with a MySQL "Auth Switch Request":
  - packet length: 44
  - packet number: 2
  - status: 0xfe
  - Auth Method Name: mysql_native_password
  - Auth Method Data: 466a617f61072a391b031d722669503e30140b5100
The client sends a TCP ACK.
After 10 seconds the server sends a TCP FIN,ACK.

This results on the PHP side in a "MySQL server has gone away".

I expect to be falling in this code:
https://github.com/php/php-src/blob/master/ext/mysqlnd/mysqlnd_wireprotocol.c#L682
and see this message
https://github.com/php/php-src/blob/master/ext/mysqlnd/mysqlnd_wireprotocol.c#L694

I enabled "mysqlnd.debug" to see the messages but the code gets stuck on reading the "OK" header.

| | | | | | | | >php_mysqlnd_auth_response_read
| | | | | | | | | >mysqlnd_read_packet_header_and_body
| | | | | | | | | | info : buf=0x7f5f64a54000 size=4095
| | | | | | | | | | | >mysqlnd_pfc::receive
| | | | | | | | | | | <mysqlnd_pfc::receive (total=1 own=1 in_calls=0)
| | | | | | | | | | | >mysqlnd_vio::get_stream
| | | | | | | | | | | | info : 0x7f5f64c7dc00
| | | | | | | | | | | <mysqlnd_vio::get_stream (total=5592 own=5592 in_calls=0)
| | | | | | | | | | | >mysqlnd_vio::network_read
| | | | | | | | | | | | info : count=4
| | | | | | | | | | | | error: Error while reading header from socket
| | | | | | | | | | | <mysqlnd_vio::network_read (total=9742815 own=9742815 in_calls=0)
| | | | | | | | | | >mysqlnd_connection_state::set
| | | | | | | | | | | info : New state=6
| | | | | | | | | | <mysqlnd_connection_state::set (total=7322 own=7322 in_calls=0)
| | | | | | | | | | >mysqlnd_error_info::set_client_error
| | | | | | | | | | | >_mysqlnd_pestrdup
| | | | | | | | | | | <_mysqlnd_pestrdup (total=7 own=7 in_calls=0)
| | | | | | | | | | | info : adding error [MySQL server has gone away] to the list
| | | | | | | | | | <mysqlnd_error_info::set_client_error (total=18787 own=18780 in_calls=7)
| | | | | | | | | | error: Can't read OK's header

The timing between the "mysqlnd_read_packet_header_and_body" and "Can't read OK's header" is exactly 10 seconds.

Other headers that are read in advance, without problems, are: "greeting" and "PROT_CACHED_SHA2_RESULT_PACKET".

I tried to follow the code to https://github.com/php/php-src/blob/master/ext/mysqlnd/mysqlnd_vio.c#L89 but am stuck here myself.

The basic problem here is that mysqlnd_caching_sha2_handle_server_response() doesn't deal with auth switch responses. But that probably also isn't the right place to handle them, as these are not specific to the auth plugin.

It also doesn't look like the code deals with a leading 1 prefix, which might account for other reported failure modes.

I have a patch for the auth switch -- but connections still fail after that because mysqlnd thinks it's using SSL while it's not.

This issue (and a number of other issues) should be fixed now (will be part of 7.4.2). Please report back if you still see issues with current 7.4 HEAD or the upcoming release.

I've tested it in 7.4.2 and it works as expected.
Thanks a lot, great job.