php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #76651 Can't establish a connection to MySQL after upgrading PHP from 7.2.7 to 7.2.8
Submitted: 2018-07-21 18:26 UTC Modified: 2018-09-16 06:44 UTC
Votes:27
Avg. Score:4.8 ± 0.5
Reproduced:27 of 27 (100.0%)
Same Version:25 (92.6%)
Same OS:10 (37.0%)
From: itaylorswift365 at gmail dot com Assigned: mysql (profile)
Status: Closed Package: MySQLi related
PHP Version: 7.2.8 OS: Debian 9.5 x64
Private report: No CVE-ID: None
View Developer Edit
 [2018-07-21 18:26 UTC] itaylorswift365 at gmail dot com 
Description:
------------
From PHP 7.2.0 to 7.2.7, everytime I compile and build PHP from source with this opinions: ./configure --enable-mysqlnd --with-mysqli=mysqlnd --with-mysql-sock=/tmp/mysql.sock --with-pdo-mysql=mysqlnd {...} and everything looks fine. PHP can connect to MySQL successfully.

But this time I compiled PHP 7.2.8 with the same configure opinions, the compile and build process was succeeded but after restarting php-fpm, I got an error that "MySQL Servr is Gone". But MySQL process was still running out there, I was confused. So I wrote a PHP script to figure out where the problem was. And I got this error "Connect DATABASE Error TYPE: 2002: Permission denied". So I checked the socket file permission of both PHP and MySQL, I got this: php socket file permission was 0660 while MySQL was 0777. And I made another test that if I downgrade PHP from 7.2.8 to 7.2.7, after restarting php-fpm, it could establish a successful connection to MySQL. Running the same script got "Success: A proper connection to MySQL was made! The database is great. Host information: Localhost via UNIX socket"

So running PHP 7.2.7 looks fine but there is a MySQL connection problem if running PHP 7.2.8.

(I use the default php.ini)

Test script:
---------------
<?php
$link = mysqli_connect("localhost", "user", "pass", "dbname");

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

echo "Success: A proper connection to MySQL was made! The database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;

mysqli_close($link);
?>

Expected result:
----------------
Success: A proper connection to MySQL was made! The my_db database is great. Host information: Localhost via UNIX socket

Actual result:
--------------
Connect DATABASE Error TYPE: 2002: Permission denied

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-07-21 18:30 UTC] itaylorswift365 at gmail dot com
-Package: MySQL related +Package: MySQLi related
 [2018-07-21 18:30 UTC] itaylorswift365 at gmail dot com 
Everything is fine on PHP 7.2.7.
 [2018-07-22 01:16 UTC] no at mail dot here 
This bug also crops up on PHP 7.2.8 for Windows (x86 and x64). phpMyAdmin is affected as well.
 [2018-07-23 15:09 UTC] johannes@php.net
-Assigned To: +Assigned To: mysql
 [2018-07-23 15:09 UTC] johannes@php.net 
Which MySQL server version are you using? Whats the value of `SELECT @@default_authebticatiin_plugin`?
 [2018-07-24 12:59 UTC] cmb@php.net 
Related To: Bug #76660
 [2018-07-24 14:25 UTC] itaylorswift365 at gmail dot com 
MySQL Version: 8.0.11 
Value of `SELECT @@default_authebticatiin_plugin` is "caching_sha2_password"
 [2018-07-26 20:30 UTC] amenshchikov at gmail dot com 
I had the same problem and solved it by changing password for MySQL user without 'mysql_native_password' plugin:
  ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';

But I was forced to use password with max lenght of 16 chars, otherwise I was getting the connection error.
 [2018-07-27 15:44 UTC] brayamrivas at gmail dot com 
Hi guys,

You can try to add this line into /etc/my.cnf (Systems based on Linux OS):

default-authentication-plugin=mysql_native_password


Then, restart mysqld proccess. 

This works for me. I'm using Laravel 5.5.

Best regards,
BR
 [2018-07-28 07:00 UTC] no at mail dot here 
PHP 7.2.7 works just fine with MySQL 8.0.11 when using mysql_native_password for logins, but PHP 7.2.8 does not.
 [2018-08-05 19:41 UTC] theodorejb at outlook dot com 
I'm also unable to connect to MySQL 8.0.12 after upgrading to PHP 7.2.8 (running on Windows 10 x64). The error message I get is "Access denied for user 'root'@'localhost' (using password: YES)" and PHP also outputs a warning saying "mysqli::__construct(): PHP was built without openssl extension, can't send password encrypted". This doesn't make sense to me since I do have the openssl extension enabled.
 [2018-08-06 21:10 UTC] eugene at zhegan dot in 
Seems like something is terribly broiken in 7.2.8, because only downgrading to 7.2.7 helps.
I've even built 7.2.8 with this changeset: https://github.com/php/php-src/commit/d6e81f0bfd0cb90586dd83d4fd47a4302605261a , doesn't help at all.
 [2018-08-07 07:39 UTC] thaddee at bechtold dot me 
Hi, Change the default_authentication_plugin of MySQL 8
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
 [2018-08-09 15:06 UTC] eugene at zhegan dot in 
Those who keep proposing to revert the authentication ciphers back to 5.7 simply don't understand what's the point or this ticket. Newer cipher has worked from 7.2.4 til 7.2.7, then it was broken in 7.2.8.
Reverting to a pre-8.0 behavior isn't a long-term solution, it's almost the same as proposing to roll back to 5.1 (why the latter isn't a "solution" ?)
 [2018-08-17 08:19 UTC] itaylorswift365 at gmail dot com 
PHP 7.2.9 didn't fix this.
 [2018-08-25 05:47 UTC] first236108 at 163 dot com 
PHP 7.3 bate2 didn't fix this too. help, I'm tired
 [2018-08-25 06:54 UTC] first236108 at 163 dot com 
Oh, I think Eugene is right.
the problem is good for safety, mybe. 

but I use the command solve the problem:

ALTER USER `root`@`localhost` IDENTIFIED WITH caching_sha2_password BY 'password';

good luck.话说，你们能看中文? 英语水平不好啊啊啊。
 [2018-08-27 18:55 UTC] no at mail dot here 
first236108, root is already set out of the box to use the caching_sha2_password authentication provider. Anyway, messing with MySQL settings does not fix the PHP bug introduced in PHP 7.2.8. We just have to wait until the good folks from the PHP team figure what they messed up and fix.
 [2018-08-31 02:06 UTC] 104979058 at qq dot com 
7.3.0beta3 
mysqli Object 
( 
[affected_rows] => 0 
[client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: 401a40ebd5e281cf22215acdc170723a1519aaa9 $ 
[client_version] => 50012 
[connect_errno] => 0 
[connect_error] => 
[errno] => 0 
[error] => 
[error_list] => Array 
( 
) 

已经修正了的bug...
 [2018-09-03 01:19 UTC] 104979058 at qq dot com 
Sorry, 7.3.0 beta 3 connection mysql 8 has been tested in detail. 
Need to change the configuration file, my.ini : 

Default_authentication_plugin=mysql_native_password 

Can you connect successfully.. Maybe this can help you.
 [2018-09-04 04:05 UTC] nikic@php.net 
I've reverted the MySQL auth changes via https://github.com/php/php-src/commit/03740ef7dffcc80530a89ebde3ccf5464f7f18e6 in all branches, which will hopefully resolve this issue.
 [2018-09-04 09:33 UTC] johannes@php.net 
Reverting doesn't make a thing better.

Root cause: MySQL 8.0 added a new authentication scheme and made it default. PHP builds without this patch can't use that scheme at all and have to switch to a different older, less secure, mechanism.

The issues with this change are the dependencies. This new machanism requires OpenSSL and ext/hash to be available. Without those statically compiled in (we can't do proper runtime guessing for C symbols) we can't use them and have a compile-time decision to make.

The proper fix would be to enforce OpenSSL and ext/Hash similar to ext/date. It's 2018 and basic security routines should be a thing users can rely on to be there ...

Reverting this change means that nobody can use the new authentication mechanism.
 [2018-09-04 13:29 UTC] nikic@php.net 
@johannes: Maybe I misunderstood the issue. The way I'm reading this bug report is that PHP 7.2.8 *regressed* MySQL authentication for a case that was previously working correctly. That is, a MySQL setup that worked with PHP 7.2.7 no longer works with PHP 7.2.8. Is that wrong?
 [2018-09-06 08:06 UTC] no at mail dot her 
Niki, You're right.

Using phpMyAdmin and MySql 8.0.12 for testing, depending on the db user's auth type, the following error messages are returned:


PHP 7.2.7:

Auth type Standard:
Works, no errors.

Auth type caching_sha2_password:
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client


PHP 7.2.8:

Auth type Standard:
mysqli_real_connect(): Unexpected server respose while doing caching_sha2 auth: 109

mysqli_real_connect(): MySQL server has gone away

mysqli_real_connect(): (HY000/2006): MySQL server has gone away

Auth type caching_sha2_password:
mysqli_real_connect(): PHP was built without openssl extension, can't send password encrypted

mysqli_real_connect(): (HY000/1045): Access denied for user 'user123'@'localhost' (using password: YES)
 [2018-09-10 18:11 UTC] cmb@php.net
-Status: Assigned +Status: Closed
 [2018-09-10 18:11 UTC] cmb@php.net 
This bug should be fixed with commit 03740ef[1].

[1] <http://git.php.net/?p=php-src.git;a=commit;h=03740ef7dffcc80530a89ebde3ccf5464f7f18e6>
 [2018-09-14 18:53 UTC] no at mail dot here 
This bug is still in PHP 7.2.10 (cli) (built: Sep 13 2018 00:47:25) ( NTS MSVC15 (Visual C++ 2017) x64 ).
 [2018-09-16 06:44 UTC] nikic@php.net 
PHP 7.2.10 does not yet include the revert (the only released version that does is PHP 7.3.0 RC1). The fix will be part of the next set of releases.
 [2018-09-16 06:48 UTC] nikic@php.net 
Related To: Bug #76817
 [2018-09-16 09:23 UTC] no at mail dot here 
Thanks for clarifying!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 21 09:01:32 2024 UTC