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

Question

after installing a new laravel app 5.7 and trying to migrate I get this error:

Illuminate\Database\QueryException : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = xxx_db and table_name = migrations)

at C:\xampp\htdocs\xxxxx\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664 660| // If an exception occurs when attempting to run a query, we'll format the error 661| // message to include the bindings with SQL, which will make this exception a 662| // lot more helpful to the developer instead of just the database's errors. 663| catch (Exception $e) {

664| throw new QueryException( 665| $query, $this->prepareBindings($bindings), $e 666| ); 667| } 668|

Exception trace:

1 PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]") C:\xampp\htdocs\xxxxx\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

2 PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=xxx_db ", "root", "**********", []) C:\xampp\htdocs\xxxxx\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

Please use the argument -v to see more details.

already tried that solution but didn't solve my issue.

medo ampir
– medo ampir

2018-12-04 07:38:55 +00:00
Commented Dec 4, 2018 at 7:38 — medo ampir
– medo ampir, Commented Dec 4, 2018 at 7:38

Shabeer K · Accepted Answer · 2021-05-20 14:14:07Z

47

This query solved my problem.

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root@123';

edited May 20, 2021 at 14:14

answered Jan 22, 2020 at 13:19

Shabeer K

1,64718 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gucu112 Over a year ago

Worked for me on Ubuntu 18.04.4 :)

alvaropgl Over a year ago

better solution than reinstall mysql. **pay attention to the command. The last "root" (... by 'root';) is the password, not the username

skcin7 Over a year ago

Worked for me on macOS!

Rohan Kamble Over a year ago

worked for me on macOS for Server version: 8.0.21 - Homebrew. just want to mention 'some_password' can be your current working password as well, no need to set new password.

saber tabatabaee yazdi Over a year ago

worked for me on Windows Server with laravel 8

|

Gass · Accepted Answer · 2021-07-11 16:43:30Z

Enter to your mysql as root and run this query

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

You can change 1234 to be your password

Run these commands

php artisan config:clear

php artisan migrate

Note: this worked for me.

Amir Hosseinzadeh · Accepted Answer · 2022-03-25 14:13:47Z

Solution is here with 2 step:

Step 1. You have to edit /etc/mysql/my.cnf file and append this setting in mysqld section:

[mysqld] default_authentication_plugin= mysql_native_password

Step 2. Then run following mysql command:

CREATE USER 'new_root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123'; GRANT ALL PRIVILEGES ON *.* TO 'new_root'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;

It is better if you restart your mysql service.

 sudo systemctl restart mysql

This problem happens when you have install mysql8 and your code compatible with mysql5

Samuel Philipp · Accepted Answer · 2019-08-27 16:05:49Z

Re installed MySQL choosing Legacy Authentication Method as shown in iamge attached SQL Authentication method

Database parameters in .env as follows

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=database_name DB_USERNAME=database_username DB_PASSWORD=database_password(if any)

Database parameters in config/database.php

'mysql' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'database_name'), 'username' => env('DB_USERNAME', 'database_username'), 'password' => env('DB_PASSWORD', 'database_password'), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'prefix_indexes' => true, 'strict' => true, 'engine' => null, 'options' => extension_loaded('pdo_mysql') ? array_filter([ PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'), ]) : [], ],

Run php artisan migrate from the projects terminal

CiganoSA · Accepted Answer · 2022-07-22 17:12:36Z

If you are getting through this kind of issue like me, avoid losing your time

My scenario: I had to start an existing Laravel5.1 application on PHP5.6. Docker didn't helped me, so I had to create a virtual machine for that.

This issue happens because of the authentication driver for MySQL. On version 8, it will use caching_sha2_password (and Laravel 5 doesn't know what to do with this.

One way to correct this is to downgrade your MySQL version back to 5.7. I've tried a lot, but my apt crashed after "forcing" it to downloading 5.7. If you want to try, heres a discussion for it.

95% of other discussions on StackOverflow and other forums will say to you just ALTER your MySQL user for the mysql_native_password. For some reason, it DOES NOT work. Even after restarting mysql services and flushing it's privileges, doesn't work. I've tried this kind of solutions for hours.

Luckily we have a hero in THIS discussion and is @Amir Hosseinzadeh. You have to create a new user AFTER changing the MySQL driver configuration. Just follow his/her solution on top and it will work.

shivanisdev · Accepted Answer · 2019-10-03 07:35:15Z

By Providing DB_SOCKET in .env file this issue can be resolved like this :

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=8889 DB_DATABASE=my_db DB_USERNAME=root DB_PASSWORD= DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock

Not related to my case but good to know that socket configuration is that easy in Laravel! :O

prafull manyar · Accepted Answer · 2021-05-06 14:53:24Z

Please Check Server localhost Port on phpmyadmin and .env file like (3306,3307,8889)

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3307 DB_DATABASE=mydb DB_USERNAME=root DB_PASSWORD=masterpass

Sobhan Zarinpour · Accepted Answer · 2020-11-10 06:21:22Z

You can run Mysql installer - Community (if you are in windows) and then reconfigure mysql server to use legacy authentication method. it should be solve your problem without pain.

CodeWithRonny · Accepted Answer · 2021-10-15 12:44:49Z

Unfortunetly you changes your mysql user or password. If you user/password changed then go to .env file and change your phpmyadmin user and password. Give your database name

DB_DATABASE=hrms DB_USERNAME=username DB_PASSWORD=password

Jayjay · Accepted Answer · 2018-12-04 07:04:42Z

-2

Go to your .env file and make sure DB_CONNECTION=mysqland DB connections are correct.

answered Dec 4, 2018 at 7:04

Jayjay

1581 gold badge1 silver badge10 bronze badges

Comments

SagarPPanchal · Accepted Answer · 2023-10-10 11:52:29Z

In my case, I have follow below steps, which is the combination of above multiple answers :

NOTICE : For this laravel 5.1 version, php version should be <= 7.2

Step 1 : You have to edit /etc/mysql/my.cnf file and append this setting in mysqld section:

[mysqld] default_authentication_plugin= mysql_native_password

Step 2 :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

Step 3 :

 sudo systemctl restart mysql

Step 4 :

php artisan config:clear && php artisan migrate

Please don't duplicate existing answers - this is a copy of stackoverflow.com/a/71618362/1116230

Collectives™ on Stack Overflow

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

11 Answers 11

8 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

8 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Linked

Related