framework: Can't connect to DB on the MySql version 8.0

@BrandonSurowiec @Sotatek-HenryPham

This and that are unrelated issues. The error happens because MySQL 8.0.4 changed default authentication mechanism from mysql_native_password to caching_sha2_password. One of the ways to fix the issue is to run mysqld with additional option:

mysqld --default-authentication-plugin=mysql_native_password

Alternatively, you can specify authentication plugin on a per user basis. In short, for now MySQL 8 isn’t really usable out of the box with most existing client implementations.

There is a PR open to fix this. You’ll find a workaround for your config there. https://github.com/laravel/framework/pull/23948

// database.php

    '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',
            ],
        ],
    ],

If you’re using docker you should add the following command to fix this issue:

services:
    mysql:
        image: mysql:latest
        command:
            - "--default-authentication-plugin=mysql_native_password"
...

Doing it this way means you don’t need the modify the database.php config with modes.

I personally think this issue should be reopened or resubmitted because the ‘fixes’ above are all workarounds.

If you aren’t connecting through docker and just locally on your machine, e.g, trying to run migrations through your laravel app, then the following works:

1. open mysql in the terminal (just type mysql)
2. Use the following command ALTER USER 'username here'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password here';
3. Leave the password empty if you don’t want a password.
4. Use this user in your laravel env file for the MYSQL database.

Don’t forget to create a new user or update existing user to utilise the older way of authenticating. CREATE USER root@localhost IDENTIFIED WITH mysql_native_password BY 'asdf'; And then grant all access to the user (if it is a new user).

I also face the same issue in Mysql80 Add entry and give the permission then it will be ok

This is a PHP issue, Laravel can’t do anything to fix it: https://bugs.php.net/bug.php?id=76243

Don’t forget to create a new user or update existing user to utilise the older way of authenticating. CREATE USER root@localhost IDENTIFIED WITH mysql_native_password BY 'asdf'; And then grant all access to the user (if it is a new user).

It works for me. Thanks!

sudo mysqld --default-authentication-plugin=mysql_native_password

你是怎么解决的？请问一下

如果沒必要用到8.0 建議把你docker mysql 降去5.8版本吧 我也是這樣就解決了這問題

1. Log in as root to mysql
2. Run this sql command:

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

已经解决了哈

Don’t forget to update to PHP 7.4

Don’t forget to create a new user or update existing user to utilise the older way of authenticating. CREATE USER root@localhost IDENTIFIED WITH mysql_native_password BY 'asdf'; And then grant all access to the user (if it is a new user).

This worked for me. Thanks.

@autaut03 it is a workaround for now, and I also hope that Laravel maintainers will cover this scenario.

@chilio That’s a solution for now, but I’m hoping that the framework will eventually have native support for new authentication method, so that it’s following latest standarts and security measures.

Hello

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

at C:\MAMP\htdocs\bloger\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:\MAMP\htdocs\bloger\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

2 PDO::__construct(“mysql:host=127.0.0.1;port=3306;dbname=homestead”, “homestead”, “secret”, []) C:\MAMP\htdocs\bloger\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

Please use the argument -v to see more details.

use MAMP