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

10

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.

edited Jul 11, 2021 at 16:43

Gass

9,6405 gold badges47 silver badges53 bronze badges

answered Jul 11, 2021 at 7:03

Amiruddin Saddam

1571 silver badge6 bronze badges

1 Comment

Ferna Stereo Over a year ago

That worked for me on Ubuntu 22.04.2

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

7

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

answered Mar 25, 2022 at 14:13

Amir Hosseinzadeh

8,5984 gold badges24 silver badges36 bronze badges

Comments

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

1

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

answered Oct 3, 2019 at 7:35

shivanisdev

6858 silver badges17 bronze badges

1 Comment

Gucu112 Over a year ago

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

1

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

answered May 6, 2021 at 14:53

prafull manyar

212 bronze badges

Comments

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

0

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.

answered Nov 10, 2020 at 6:21

Sobhan Zarinpour

322 bronze badges

Comments

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

0

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

edited Oct 15, 2021 at 12:44

answered Oct 15, 2021 at 11:09

CodeWithRonny

3403 silver badges14 bronze badges

Comments

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

-2

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

answered Oct 10, 2023 at 11:52

SagarPPanchal

10.2k6 gold badges36 silver badges66 bronze badges

1 Comment

Nico Haase Over a year ago

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

Your Answer

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

Your Answer

Sign up or log in

Post as a guest

Linked

Related