php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #78981 Can't connect to MySQL 8 if server's auth plugin does not match user's one
Submitted: 2019-12-17 17:34 UTC Modified: 2019-12-27 16:35 UTC
From: txigreman at hotmail dot com Assigned: nikic (profile)
Status: Closed Package: MySQL related
PHP Version: 7.4.0 OS: Docker php:7.4-fpm image
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: txigreman at hotmail dot com
New email:
PHP Version: OS:

 

 [2019-12-17 17:34 UTC] 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.


Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-12-19 11:40 UTC] 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-19 15:38 UTC] 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-23 22:28 UTC] 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-27 09:24 UTC] nikic@php.net
-Status: Open +Status: Verified
 [2019-12-27 10:14 UTC] 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-27 12:14 UTC] nikic@php.net
-Status: Verified +Status: Assigned -Assigned To: +Assigned To: nikic
 [2019-12-27 12:14 UTC] nikic@php.net
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.
 [2019-12-27 13:05 UTC] nikic@php.net
The following pull request has been associated:

Patch Name: Fix caching_sha2_password auth
On GitHub:  https://github.com/php/php-src/pull/5034
Patch:      https://github.com/php/php-src/pull/5034.patch
 [2019-12-27 16:35 UTC] nikic@php.net
-Status: Assigned +Status: Closed
 [2019-12-27 16:35 UTC] nikic@php.net
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.
 [2020-02-03 22:33 UTC] txigreman at hotmail dot com
I've tested it in 7.4.2 and it works as expected.
Thanks a lot, great job.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Wed Aug 12 16:01:25 2020 UTC