Cms: Can't install Craft on MySQL 8

[angrybrad]

Description

Environment is latest Valet. Fresh install of Craft 3 and Valet (MySQL wasn't upgraded from previous version).

Get this error in both the CLI and browser:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

Additional info

• Craft version: 3.0.x
• PHP version: 7.x
• Database driver & version: MySQL 8.0

[angrybrad]

Have confirmed this is an issue, but still investigating if there is anything we can do about it at the application layer. Seems like solutions to this are all more server side changes.

Context: https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

[jonlivingston]

Setting up local server with valet and ran into this same issue:

Jons-iMac:lisasellspontevedra jon$ ./craft setup
Which database driver are you using? [mysql,pgsql,?]: mysql
Database server name or IP address: [localhost]
Database port: [3306]
Database username: [root]
Database password:
Database name: lisasellspontevedra
Database table prefix:
Testing database credentials ... failed: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

Any solution for this? I'm running mysql Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

[angrybrad]

MySQL 8.0.4 changed the default password authentication to a new format that's incompatible with previous formats.

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

From PHP's PDO docs:

From: https://secure.php.net/manual/en/ref.pdo-mysql.php

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

So you'll either need to:

1. Be running PHP 7.1.16+, 7.2.4+, (or presumably 7.3+) so you can use the newer PHP PDO that recognizes that format.

2. Change MySQL's my.cnf file to use the older password method:

   default-authentication-plugin=mysql_native_password

3. Change the individual user account that Craft is connecting with to use the older method via:

   alter user 'username'@'localhost' identified with mysql_native_password by 'password';

You'd need to swap out username and password with yours.

Leaving this for reference as well: https://github.com/laradock/laradock/issues/1392

[doifeellucky]

Hi angrybrad

Apologies, I'm a github and command line novice. Could you please clarify what you mean for #3 above. I'm using PHP 7.3 so I assume #1 is OK. I've found two instances of my.cnf in /usr/, to which I've added the line in #2 to the bottom. I get to the the point mentioned by jonlivingston above, but don't know what you mean by 'yours' for username and password, as my understanding is these are not needed, well root as username but the password is blank, as part of the setup.

Please note that this is a fresh install of Mojave, with the disk wiped.

I've tried changing the server to 127.0.0.1, the port to 8889, all the 'fixes' I could find, with no luck.

Thanks

[doifeellucky]

It's OK, I figured this out. I had to first log in to mysql using

mysql -u root -p

Then create a database user with a password using

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

And finally assign all privileges to that user using

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

I was now able to complete the Craft setup.

[michaeldietiker]

Just alter your mysql user and you're good to go:

ALTER USER '<user>'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';