PHP 7.4 and MySQL Connections

Question

I had posted this on another forum thinking that it was Ubuntu-related and MySQL wasn't playing nicely with PHP but then I realized that mysqli is indeed loading and communicating with it as phpinfo() shows that mysqli is running so the problem I've been having seems to relate to a change in PHP version 7.4 in the way it connects to the database.

It is apparently related to the password so what changes to it would have to be made for it to work even if not backwards compatible? My local development databases use a simple password and I'm not sure what I need to do get it working again while still maintaining compatibility with the older MySQL 5.X on the live server but my sites use this function to connect.

I did review another posting and several others here and elsewhere about it and tried the test code provided in the example linked above but still could not connect.

function dbConn($DBname) {
    global $DBhost;
    global $DBusername;
    global $DBpass;
    $dbconn = new mysqli($DBhost, $DBusername, $DBpass, $DBname);
    mysqli_set_charset($dbconn,"UTF8");
    return $dbconn;
}

I am running Ubuntu 19.10 with Apache2 2.4.41 and MySQL 8.0.18 and even with mysql_native_password enabled, it is giving errors.

Warning: mysqli::__construct(): Unexpected server response while doing caching_sha2 auth: 109 in /var/www/html/testsite.loc/db_test.php on line 32

Warning: mysqli::__construct(): (HY000/2006): MySQL server has gone away in /var/www/html/testsite.loc/db_test.php on line 32

Warning: mysqli::query(): Couldn't fetch mysqli in /var/www/html/testsite.loc/db_test.php on line 33

Fatal error: Uncaught Error: Call to a member function fetch_row() on bool in /var/www/html/testsite.loc/db_test.php:34 Stack trace: #0 {main} thrown in /var/www/html/testsite.loc/db_test.php on line 34

Solution 1

The solution was to simply change the database password to something it can use by logging into mysql in terminal with:

sudo mysql -p -u root

Then running each of these at the mysql> prompt:

ALTER USER 'USERNAME'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YOUR_PASSWORD';

FLUSH PRIVILEGES;

Since my code is unchanged and still using the same password as before, it should work on both the local PHP 7.4 and the live 5.X. I am not sure how I will do that on the live server, though, as I do not have access to the mysql table.

Solution 2

The underlying issue is having an unsupported authentication plugin assigned to the user, which causes the password to not match. You could configure your development MySQL server differently, in order to establish a similar environment. In order to do so, you have to edit /etc/mysql/my.cnf:

[mysqld]
...
default-authentication-plugin = mysql_native_password

However, I'd rather suggest to upgrade the production environment to PHP 7.4 and MySQL 8.0, in order to use caching_sha2_password instead, which is the default authentication plugin since MySQL 8.0. One could also update the passwords during deployment through the MySQL CLI - but running identical configurations is the most hassle free - and the most reliable for testing it.

Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations explains it in detail.

Comments

[Martin Zeitler]

Is there any good reason to run the production system on PHP5?

[Barmar]

The code you posted works the same in PHP 5 and PHP 7. There's no change in how you connect to MySQL.

[Barmar]

The only incompatibility is the old mysql extension, which was removed in PHP 7. But PDO and mysqli are essentially the same.

[DonP]

This code was working in PHP 7.3 but does not work with 7.4 and it is my understanding that it is something to do with the password. As for why PHP 5 is on the production system, I have no control over that as it's a hosted system. Apparently I am able to update it but I still have one extensive legacy site that won't run on 7.X versions and the rewrite is not yet ready to go live.

[DonP]

I don't have a choice on the production server as it's shared through a hosting company. I believe I can switch, if I want, to one of the 7.X versions but not 7.4. I know about mysql_native_password and tried it but still cannot connect although it's unclear if the problem now is the same: Warning: mysqli::__construct(): Unexpected server response while doing caching_sha2 auth: 109 in /var/www/html/winelist.loc/db_test.php on line 32

[Martin Zeitler]

@DonP Alternatively you could downgrade your development environment to the production version... it also matters, which plugin is being assigned to the user in MySQL's internal users table - in combination with the server configuration. These have to match each other. Running different versions just introduces useless complexity and in some cases, difficult to reproduce issues. Most shared-hosting ISP even forcefully upgraded to PHP 7.x ...which resulted in demand for programmers.

[DonP]

Ubuntu itself upgraded PHP but I also prefer it to be up to date, especially if at any moment my hosting company might decide to make 7.X mandatory as I expect they will soon even if it's to 7.2 or 7.3. All sites have already been updated and ready for that eventuality until the 7.4 update which broke something.

[Martin Zeitler]

@DonP It is far more a MySQL 5.0 vs. 8.0 & PHP mysqli connector incompatibility, than it would be a PHP 5.x vs. 7.4 version incompatibility. You simply cannot use caching_sha2_password, when only mysql_native_password is being supported there. The update to MySQL 8.0 involves more than just changing the default authentication plugin, but also needs the assigned authentication plugin per user and also the password's value updated, so that it all will work together (half-updated, this will be broken).

[DonP]

Thank you and yes, I understand that and must have posted my answer that mentions those things at the same time you posted your comment. Also, it turns out that my hosting company allows me to change the PHP version on a site-by-site basis and has versions up to 7.3 available. Unfortunately I've not yet been able to enable it as apparently mysqli is missing along with other needed modules so I'm waiting for them to add those things.