Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't install Craft on MySQL 8 #3021

Closed
angrybrad opened this issue Jun 22, 2018 · 6 comments
Closed

Can't install Craft on MySQL 8 #3021

angrybrad opened this issue Jun 22, 2018 · 6 comments
Assignees

Comments

@angrybrad
Copy link
Member

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 angrybrad self-assigned this Jun 22, 2018
@angrybrad
Copy link
Member Author

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
Copy link

jonlivingston commented Feb 8, 2019

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
Copy link
Member Author

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: laradock/laradock#1392

@doifeellucky
Copy link

doifeellucky commented Feb 12, 2019

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
Copy link

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
Copy link

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants