How to resolve the error: SQL authentication method unknown in Laravel-MySql

Question

I using docker and I have a container of Laravel Framework 5.5.25 and other with mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL). in my configuration of docker compose I have this:

version: "2"
services:
    mysql:
    image: mysql
        ports:
            - "3307:3306"
        command: --sql_mode=""

So, when Laravel try to connect to MySql I have this error:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from

At the moment I had to go back to version 5.6 of MySql. but if someone knows how to solve the problem in version 8.0 welcome be. Thank you. — andreszam24, May 26 '18 at 23:38
Check my answer in https://stackoverflow.com/a/58615936/6312647 For Ubuntu — cookieCat, Oct 29 '19 at 21:57

score 21 · Answer 1 · answered Jun 27 '18 at 07:00

21

You have to use legacy style passwords for MySQL 8 and PHP7+:

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

answered Jun 27 '18 at 07:00

Esther

323
1
2
9

1

guys be careful, this command might damage your mysql login process and gets `Access denied for user 'username'@'ip_address' ` that have to stop mysql and use mysql in safe mode to reset your password – Hama Bohel Jul 08 '20 at 14:19

score 15 · Answer 2 · answered Apr 21 '20 at 03:42

15

For local development using Laravel Valet I was able to resolve this using TablePlus SQL Query editor by typing this

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

I hope it helps someone too.

answered Apr 21 '20 at 03:42

Jekayode

1,552
1
12
6

score 6 · Answer 3 · answered Oct 03 '19 at 07:01

6

I am using laravel 5.8 and having MAMP server got this error resolved by adding DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock in .env file like below


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

As I found in few of the blogs it generally comes because of the socket issue with latest MySQL version

answered Oct 03 '19 at 07:01

shivanisdev

687
6
16

For unix, this would be `DB_SOCKET=/var/run/mysqld/mysqld.sock`. This is definitely the cleanest solution. Thanks! – junkystu Mar 10 '21 at 10:11

score 0 · Answer 4 · answered May 04 '20 at 19:35

If you're looking for a Laravel way just simple edit config/database.php

and add modes array to MySQL section:

 'connections' => [

        'mysql' => [
            'driver'      => 'mysql',
            'host'        => env( 'DB_HOST', '127.0.0.1' ),
            'port'        => env( 'DB_PORT', '3306' ),
            'database'    => env( 'DB_DATABASE', 'forge' ),
            'username'    => env( 'DB_USERNAME', 'forge' ),
            'password'    => env( 'DB_PASSWORD', '' ),
            'unix_socket' => env( 'DB_SOCKET', '' ),
            'charset'     => 'utf8mb4',
            'collation'   => 'utf8mb4_unicode_ci',
            'prefix'      => '',
            'strict'      => true,
            'engine'      => null,
            'modes'       => [
                'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ],
        ],
    ],

Jaydeep Mor · Answer 5 · 2022-02-23T04:18:38.343

Reference : https://github.com/laradock/laradock/issues/1392#issuecomment-368308494

Add default authentication plugin to /etc/mysql/my.cnf

[mysqld]
default_authentication_plugin= mysql_native_password

Login to mysql for create new user as follow,

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 ;

Note : Remove old user, database and recreate again by above script.

How to resolve the error: SQL authentication method unknown in Laravel-MySql

5 Answers5

Linked

Related