Old app, new database… am I screwed?

Who has never encountered a customer who, for all sorts of reasons (valid or not), was unable to update an application and therefore could no longer connect to the latest versions of MySQL?

Or worse still, data that is shared between two applications, one of which absolutely must use the latest version of MySQL and the other, which, once again, cannot be updated and does not support the new authentication method.

Some context

In July 2023, with MySQL 8.0.34, the mysql_native_password was marked as deprecated. The new default authentication method in MySQL 8.0 is caching_sha2_password.

In April 2024, with MySQL 8.4 LTS, mysql_native_password was no longer loaded by default. It can be manually reactivated at MySQL start.

Finally, in July 2024, with MySQL 9.0, the plugin was removed, making caching_sha2_password the mandatory authentication plugin.

I previously wrote about that: https://lefred.be/content/mysql-9-0-its-time-to-abandon-the-weak-authentication-method/

The Problem

The problem is that if you can’t upgrade to a connector that supports caching_sha2_password, your application won’t be able to connect to any MySQL version >= 9.0.

Let’s use PHP 7.2, for example, if we try to connect to MySQL 9.x, we will get the following error:

$ php71 test.php 
PHP version: 7.1.33
PHP Warning:  mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /home/test/test.php on line 10
PHP Warning:  mysqli::__construct(): (HY000/2054): The server requested authentication method unknown to the client in /home/test/test.php on line 10
Connection failed: The server requested authentication method unknown to the client

If we check the user and its authentication plugin, we can see that indeed the system uses the new method (caching_sha2_password):

mysql> select User, Host, Plugin from mysql.user where user like 'fred%';
+------+------+-----------------------+
| User | Host | Plugin                |
+------+------+-----------------------+
| fred | %    | caching_sha2_password |
+------+------+-----------------------+
1 row in set (0.000 sec)

The Solution

The best solution, of course, would be to upgrade that PHP 7.2 application to a newer version that supports caching_sha2_password. PHP >=7.4 supports the new authentication method.

However, as I said, this is not always a possibility.

Of course, it is not recommended, as you will use a weak authentication method, but if you don’t have any other options and can secure your environment, you could use an authentication plugin compatible with the old method.

I’ve created a legacy plugin that allows clients to connect when they use mysql_native_password.

mysql_native_password_legacy

I’ve built the plugin for MySQL 9.5 on Fedora 42, OL/EL 8, and OL/EL 9:

This is how to use it after installation:

MySQL> INSTALL PLUGIN mysql_native_password_legacy 
       SONAME 'mysql_native_password_legacy.so';

MySQL> CREATE USER fred_legacy IDENTIFIED 
       WITH 'mysql_native_password_legacy' BY 'Passw0rd!';

MySQL> SELECT User, Host, Plugin FROM mysql.user WHERE user LIKE 'fred%';
+-------------+------+------------------------------+
| User        | Host | Plugin                       |
+-------------+------+------------------------------+
| fred        | %    | caching_sha2_password        |
| fred_legacy | %    | mysql_native_password_legacy |
+-------------+------+------------------------------+
2 rows in set (0.000 sec)

Let’s try our application (just a connection to MySQL and display its version):

<?php
$servername = "127.0.0.1";
$port = 3306;
$username = "fred_legacy";
$password = "Passw0rd!";

echo "PHP version: " . phpversion() . "\n";

$conn = new mysqli($servername, $username, $password, 'test',  $port );

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error . "\n");
}
echo "Connected successfully\n";

echo "MySQL version: " . $conn->server_info . "\n";

$conn->close();
?>

And when we run it:

$ php71 test.php 
PHP version: 7.1.33
Connected successfully
MySQL version: 9.5.0-ai

Conclusion

It’s really recommended to use the new authentication default plugin when using MySQL (caching_sha2_password); however, if you are really stuck in the past with your application and you want to benefit from the latest MySQL 9.x, there is a solution, maybe not the best, maybe not a supported one, but… a working one.

I’ve made those packages. Let me know what you think and if you use them. If this is useful, I might share the code on GitHub, too, in the future.

Happy connections to MySQL!