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

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

Open
MaximilianCoutuer opened this Issue Feb 23, 2018 · 46 comments

Comments

Projects
None yet
@MaximilianCoutuer

MaximilianCoutuer commented Feb 23, 2018

Info:

  • Docker version ($ docker --version): Docker version 17.12.0-ce, build c97c6d6
  • Laradock commit ($ git rev-parse HEAD): 8323c659ed588ad2d2b02a13dfab651c051302e8
  • System info (Mac, PC, Linux): MacOS Sierra 10.12.6
  • System info disto/version:

Issue:

Using the mysql and nginx containers, running a migration yields the following error: The server requested authentication method unknown to the client [caching_sha2_password].

This seems to be due to mysql 8.x forcing a new password format. Attempting to force old_passwords=1 from inside the mysql container yields ERROR 1231 (42000): Variable 'old_passwords' can't be set to the value of '1'.

This does occur when trying to run a migration from inside the workspace - where both the version of php and mysql should be controlled by Laradock.


Expected behavior:

Laravel should be able to login to the database and do its thing.


Reproduce:

Pull clean copy of Laradock, set DB_HOST=127.0.0.1 in .env (for reasons unknown to me, DB_HOST=mysql yields SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: nodename nor servname provided, or not known), start the mysql and nginx containers, enter the workspace container and run php artisan migrate.


@adammparker

This comment has been minimized.

adammparker commented Feb 24, 2018

mysql works for me inside the .env file, but otherwise I'm experiencing the exact same problem.

Additionally, I'm using PHP_VERSION=56 in laradock/.env (have an older 5.1 Laravel project I need to stand up and don't want to troubleshoot Valet issues with older version(s) of PHP.

php artisan migrate returns two errors:

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

  [ErrorException]
  PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]
@Drakota

This comment has been minimized.

Drakota commented Feb 25, 2018

+1, I'm having the same problem here.

@mulodo-ngo-anh

This comment has been minimized.

mulodo-ngo-anh commented Feb 25, 2018

Me too, same problem .

i did resolved the problem following:

  1. add default authentication plugin to laradock/mysql/my.cnf
[mysqld]
default_authentication_plugin= mysql_native_password
  1. update content file mysql/docker-entrypoint-initdb.d/createdb.sql like:
CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
#
CREATE DATABASE IF NOT EXISTS `yourdb` COLLATE 'utf8_general_ci' ;
GRANT ALL ON `yourdb`.* TO 'admin'@'%' ;
FLUSH PRIVILEGES ;

important note: remove old user, database and recreate again by above script.
3. Remove mysql container and re-run it.
4. php artisan migrate is done :)

Reason bug:
Default authenticate of MySQL 8.0.4 changed.
Refer to detail information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-4.html
at Security Notes

hope it help you.
Thanks

@adammparker

This comment has been minimized.

adammparker commented Feb 25, 2018

Thanks @rockerbom for the thorough response. I didn't try your steps, but ended up with a different solution (for anyone that doesn't necessarily need MySQL 8.x):

  1. Changed MYSQL_VERSION to 5.7 in laradock/.env
  2. docker-compose build --no-cache mysql
@danaia

This comment has been minimized.

danaia commented Feb 26, 2018

@rockerbom This worked for me after a long time of forensics. Also solved how to create a DB. @adammparker I tried your option and bring everything back to 5.7 but I was not able to bash into the container for some reason.

@adammparker

This comment has been minimized.

adammparker commented Feb 26, 2018

@danaia I think I may have experienced something similar. Rather than debugging I just nuked all of my Docker settings, containers, and images and just started from scratch (with the older [and more stable] version of MySQL).

@mulodo-ngo-anh

This comment has been minimized.

mulodo-ngo-anh commented Feb 26, 2018

@danaia nice man

@MaximilianCoutuer

This comment has been minimized.

MaximilianCoutuer commented Feb 26, 2018

I ended up with a similar solution as @adammparker. Thanks @rockerbom for an alternative. 👍

@wmellema

This comment has been minimized.

wmellema commented Feb 27, 2018

Getting the same issue with php 7.1, any ideas on how to fix it for 7.1 without redoing all the Docker entrypoints?

@mulodo-ngo-anh

This comment has been minimized.

mulodo-ngo-anh commented Feb 27, 2018

@wmellema are you instaill php-cli?
could you share .env file?

@danaia

This comment has been minimized.

danaia commented Mar 1, 2018

Have a stable solution after a few days banging thy head. MySQL 8.0 is not stable at all for me. I am surprised it is a part of Laradock. Does anyone know why a dev version is included VS a production version? Once I downgraded to 5.7 all errors above are gone and my head is healing. Like to suggest to the developers of Laradock to please use a stable version of MySQL so others may not need to travel the road of misery.

@MattBidas

This comment has been minimized.

MattBidas commented Mar 2, 2018

Some erros
The server requested authentication method unknown to the client

And downgrade mysql version to 5.6 or 5.7 doesn't work

@bestlong

This comment has been minimized.

Contributor

bestlong commented Mar 2, 2018

Hi everyone, I think, always check config file new different after upgrade laradock is good practice.

$ diff .env env-example

not just copy paste.

@silsuer

This comment has been minimized.

silsuer commented Mar 7, 2018

@adammparker I changed Mysql verision to 5.7 in .env, and rebuild it. I can run it, but its exit 2 when I use docker-compose ps, could you help me?

@capripio

This comment has been minimized.

capripio commented Mar 7, 2018

@silsuer , agreed same issue occurred exit 6 to me thought!

@silsuer

This comment has been minimized.

silsuer commented Mar 7, 2018

@capripio I resolved it with https://github.com/laradock/laradock/issues/955

My 'solution' - don't use DATA_SAVE_PATH
(in docker-compose.yml)

@EngEryx

This comment has been minimized.

EngEryx commented Mar 7, 2018

@silsuer I had the same issue.

I set MYSQL_VERSION to 5.7,
Further removed DATA_SAVE_PATH
Then removed MYSQL_ROOT_USER since I realized an issue with Docker container for MySQL ...it already creates the root user as root. So when I removed it all worked fine.

I am also hosting multiple projects and all is working well without extra patches.

Would like to know who is still having an issue I can help...

@robindijkhof

This comment has been minimized.

robindijkhof commented Apr 23, 2018

I'm NOT getting the error with mysql 8.0.0. This version might also be working.

@MattBidas

This comment has been minimized.

MattBidas commented Apr 23, 2018

@robindijkhof Use this to solve problem :
#1407

**stop all service docker-compose down
Edit .env file set MYSQL_VERSION=5.7 or MYSQL_VERSION=latest

Delete mysql database

$ rm -rf ~/.laradock/data/mysql

rebuild mysql image

$ docker-compose build mysql

retry

$ docker-compose up -d nginx mysql phpmyadmin
It's working for me.**

@robindijkhof

This comment has been minimized.

robindijkhof commented Apr 23, 2018

@MattBidas I'm NOT getting the error with v8.0.0. Just posting it as another possible solution.

@MattBidas

This comment has been minimized.

MattBidas commented Apr 23, 2018

@robindijkhof Alles Gute ! Sorry ;-)

@kazirahmat

This comment has been minimized.

kazirahmat commented May 30, 2018

I have same problem. But I didn't find and change MYSQL_VERSION to 5.7 in laradock/.env . Actually Where .env file?
Please help me. @adamm or any one

@igorjacauna

This comment has been minimized.

Contributor

igorjacauna commented May 30, 2018

@kazirahmat there is a file named env-example in laradock directory. You have to create a copy of this file and rename to .env

@BackendATS

This comment has been minimized.

BackendATS commented Jun 21, 2018

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]
php 7
mysql 8
ubuntu 18.04
How can i resolve this? I can't make 'php artisan migrate:refresh --seed' my database

@themark147

This comment has been minimized.

themark147 commented Jun 30, 2018

yea if you want previous version of mysql ex. 5.7 is necessary to remove mysql data that solution works for me

@bye-webster

This comment has been minimized.

bye-webster commented Jul 28, 2018

you can check by "docker inspect name_of_mysql_1" and then you can use IP of Gateway which is "172.18.0.1"
"NetworkID": "05f4c08c374b61334709dc8ce4b5e5cad8b94e09c27b66b0ed64c89c8d585f1f",
"EndpointID": "4830e8ae5f13cc983252374e1955f8731490e561603996e6c8265ccf79dea73b",
"Gateway": "172.18.0.1",
"IPAddress": "172.18.0.3",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"MacAddress": "02:42:ac:12:00:03",
"DriverOpts": null

just info, im already done with it..

@francoism90

This comment has been minimized.

francoism90 commented Aug 1, 2018

This issue still exists, but I don't know if this is PMA or something else, anyway as workaround:

$ docker-compose exec mysql bash
$ mysql -u root -p 

You should be able to login as 'root' or your other password.

Execute the following commands:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';

This should give you access in PMA.

@ko1eda

This comment has been minimized.

ko1eda commented Aug 3, 2018

@francoism90 just started using laradock and was banging my head against the wall bc of this but your solution worked thank you :)

What is the cause of this exactly is it a bug with mysql for docker? I never ran into this when setting up my own mysql container

@tblancog

This comment has been minimized.

tblancog commented Aug 14, 2018

It also worked for my, thanks @francoism90

@nmardones

This comment has been minimized.

nmardones commented Sep 5, 2018

Gracias, @francoism90

@nadimtuhin

This comment has been minimized.

nadimtuhin commented Sep 16, 2018

It also worked for my, thanks @francoism90

@JeromeSaby

This comment has been minimized.

JeromeSaby commented Sep 25, 2018

@francoism90 Perfect. Thanks a lot !

@runforrestrun

This comment has been minimized.

runforrestrun commented Oct 1, 2018

@francoism90, Thanks for your workaround! This worked. But does anyone know what the permanent solution is?

@fgibaux

This comment has been minimized.

fgibaux commented Oct 5, 2018

According to the PHP documentation, we should wait for a new version of mysqlnd: http://php.net/manual/en/mysqli.requirements.php to be able to use the new authentication method.

@maheshWebkul721

This comment has been minimized.

maheshWebkul721 commented Oct 14, 2018

@francoism90 its working, thanks a lot.

@alaamebrahim

This comment has been minimized.

alaamebrahim commented Oct 18, 2018

francoism90
Thanks that woked!

@tabirkeland

This comment has been minimized.

tabirkeland commented Oct 21, 2018

@francoism90 thank you!

@aowie1

This comment has been minimized.

aowie1 commented Oct 24, 2018

If you already built your container using the default MYSQL_VERSION (at this time of writing the latest was v8) you'll need to remove the image, volume and data directory before updating to the lower version:
docker rmi laradock_mysql
docker volume rm laradock_mysql
rm -rf ~/.lardock/data/mysql

then update your laradock's .env to MYSQL_VERSION={your_desired_version}
then rebuild while busting the cache docker-compose build --no-cache mysql

@ndberg

This comment has been minimized.

ndberg commented Nov 13, 2018

Thanks @aowie1. There is a error in your second command, it should be:
docker volume rm laradock_mysql

@adriiiprieto

This comment has been minimized.

adriiiprieto commented Nov 13, 2018

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

@aowie1

This comment has been minimized.

aowie1 commented Nov 13, 2018

Thanks @aowie1. There is a error in your second command, it should be:
docker volume rm laradock_mysql

good catch, thanks. I updated it

@Crowbrammer

This comment has been minimized.

Crowbrammer commented Nov 19, 2018

Basic PDO user with a possible solution. You do not have to uninstall MySQL 8.x or even do the above (maybe... I'm not using Laradock). You just need to go to your MySQL installer, reconfigure your server, and select Legacy Authentication Method (which retains MySQL 5.x compatibility). I figured this out through trial and error after my dsn in my PDO didn't allow me to connect.

Finding the Installer in Windows
Click 'Reconfigure'
Select the bottom option

@haobird

This comment has been minimized.

haobird commented Nov 19, 2018

@robindijkhof Use this to solve problem :
#1407

**stop all service docker-compose down
Edit .env file set MYSQL_VERSION=5.7 or MYSQL_VERSION=latest

Delete mysql database

$ rm -rf ~/.laradock/data/mysql

rebuild mysql image

$ docker-compose build mysql

retry

$ docker-compose up -d nginx mysql phpmyadmin
It's working for me.**

I solve it with your method

@fabionofre

This comment has been minimized.

fabionofre commented Nov 20, 2018

I solve with Mysql Installer. Just select the legacy authentication method.

image

@Pareshoct7

This comment has been minimized.

Pareshoct7 commented Dec 6, 2018

@fabionofre Thanks for the crystal clear solution.

I applied solution from this video.
https://gist.github.com/nrollr/3f57fc15ded7dddddcc4e82fe137b58e#gistcomment-1959744

but that is not enough. So, I have to follow your answer also @fabionofre

#1392 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment