If you develop on a Mac as I do, you’ll want to get up to date with the latest versions of the software!
So I installed a fresh new copy of MySql database server – now version 8, and made sure I was using PHP 7.2 for use with my Jetbrains PHPStorm IDE.
The trouble arrived when I tested an application I had written in PHP that logs into the MySql database – it failed, saying that the client did not understand the server:
error: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] A quick internet search revealed that my new MySql installation had changed the password authentication mechanism from ‘mysql_native_password‘ to ‘caching_sha2_password‘, which the PHP MySql client I use, mysqli, does not ‘understand’.
I thought the answer would be simple – login to the MySql server as root and change the authentication mechanism for my PHP user like this:
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret'; However, when I tried this, I received the following error message:
1827 SQLSTATE: HY000 The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
It sounded to me as if mysql_native_password has been disabled in some way, so I needed to create a new config file that added in a setting to enable this authentication mechanism.
So, using terminal, I created this config file with this name:
sudo nano -c /usr/local/mysql/myconfnativepassword.cnf (Sudo is needed because /usr/local/mysql has only root access). In the config file I wrote just this text (nothing else in the file):
[mysqld]
default_authentication_plugin=mysql_native_password ..then CTRL-X Y to save it (Nano command for save and exit).
I then started OSX Control Panel and selected the MySql option, clicked Configuration, then adjusted the blank Configuration File text box and checkbox to look like this, leaving all other entries untouched:
..then clicked Apply.
After that, I clicked on the Instances tab, stopped then restarted the MySql server, then returned to the terminal to log back in as root.
This time the ALTER USER SQL statement above worked fine, and after that my PHP application was able to login to the MySql server.
Conclusion: PHP’s mysqli library is using legacy native password authentication to MySql, so if you install MySql 8 you will, at this time, need to ensure that legacy password authentication is enabled on the server too..
Comments