Bug #78981 [Opn->Ver]: 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: Open +Status: Verified Type: Bug Package: MySQL related Operating System: Docker php:7.4-fpm image PHP Version: 7.4.0 Block user comment: N Private report: N Previous Comments: ------------------------------------------------------------------------ [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