Bug #78981 [Ver->Asn]: Can't connect to MySQL 8 if server's auth plugin does not match user's one

Edit report at https://bugs.php.net/bug.php?id=78981&edit=1 ID: 78981 Updated by: nikic@php.net Reported by: txigreman at hotmail dot com Summary: Can't connect to MySQL 8 if server's auth plugin does not match user's one -Status: Verified +Status: Assigned Type: Bug Package: MySQL related Operating System: Docker php:7.4-fpm image PHP Version: 7.4.0 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2019-12-27 10:14:25] nikic@php.net 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. ------------------------------------------------------------------------ [2019-12-23 22:28:42] tom at ctors dot net 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. ------------------------------------------------------------------------ [2019-12-19 15:38:24] txigreman at hotmail dot com 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. ------------------------------------------------------------------------ [2019-12-19 11:40:23] drtechno at mail dot com 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'; ------------------------------------------------------------------------ [2019-12-17 17:34:03] txigreman at hotmail dot com Description: ------------ I have a MySQL 8 server configured to use caching_sha2_password as default authentication plugin, but it's overrode for a specific user with mysql_native_password. When I try to open a connection to the database, using mysqli or pdo, I get this error: Unexpected server response while doing caching_sha2 auth: 109 It does work if I change the server and/or user configuration to make them match. I think PHP should query the user's authentication plugin instead of using the server's default one. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=78981&edit=1