PHP 7.4 and MySQL Connections

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

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.