MDS, PHP and authentication

Recently I blogged about how to easily deploy a LAMP application to MDS.

Using the Terraform module (OCI Resource Manager’s Stack) you also have the possibility to choose the PHP version to install:

But as you should already know, not all versions support the latest default authentication method in MySQL 8.0: caching_sha2_password [1], [2], [3].

Most of the PHP applications are using mysqli and mysqlnd as connector.

Let’s have a look at a summary of which combinations are available and compatible with MySQL Database Service:

We can see that the default settings of MDS are compatible with PHP 7.4 and 8.x. But in case you are using an older version of PHP (not older than 7.1), your application will be perfectly able to use MDS but the MySQL user used by the application will have to use the mysql_native_password authentication plugin.

It’s almost the same if you are using PDO with mysqlnd.

If you are using mysql_xdevapi as connector, this one works with all versions from 7.1.

This is an example on how to create a user with this previous authentication plugin:

SQL > create user test_php  
      identified with 'mysql_native_password'   
      by 'TestPassw0rd!';  

If you don’t do it, this is the error you will get in your PHP application using PHP <7.4 with MySQL 8.0 and MDS using the default authentication settings:

The server requested authentication method unknown to the client [caching_sha2_password] in test.php on line 2  
mysqli::__construct(\): \(HY000/2054): The server requested authentication method unknown to the client in test.php on line 2  

In MDS, you can also change this default authentication method to the previous native plugin. To achieve that you will have to create a new MDS configuration and set the user variable default_authentication_plugin to mysql_native_password as shown below:

And of course use that new configuration with your MDS instance.

Happy PHP development with MySQL !