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
Answer
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.