Can't connect to DB on the MySql version 8.0

[henrypham299]

• Laravel Version: 5.6.16
• PHP Version: 7.2
• Database Driver & Version: MySql version 8.0.11

Description:

https://hub.docker.com/_/mysql/
When I build the new project using Docker, I pull the image mysql:latest and use it to build the container MySql. After that, I config and install my project and got the bug below.

Steps To Reproduce:

composer install

cp .env.example .env

php artisan key:generate
Application key [base64:XVjUfcTiFFVT7SNICMgWoZ8AcnBAN9WjPaCt7224Bmc=] set successfully.

php artisan migrate

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

  at /var/www/localhost/htdocs/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]")
      /var/www/localhost/htdocs/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  2   PDO::__construct("mysql:host=db;port=3306;dbname=wbc_wallet", "root", "", [])
      /var/www/localhost/htdocs/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  Please use the argument -v to see more details.

[BrandonSurowiec]

There is a PR open to fix this. You'll find a workaround for your config there. #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',
            ],
        ],
    ],

[henrypham299]

@BrandonSurowiec

Thank you very much.

[BrandonSurowiec]

You're welcome.

[adiachenko]

@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.

[chilio]

@adiachenko how about running image mysql:8.0 via docker in gitlab runner in Laravel 5.6.
Any idea, cause this breaks the whole testing workflow (which was working fine from 5.5 to 5,7)?

[chilio]

@adiachenko thanks I got it working finally, although some mods needed...
So for anybody looking for answer to this question please check here

[briangonzalezmia]

@adiachenko Thank you! Finally got it after several hours bangin' head on wall :(

[mfgabriel92]

Didn't work for me.

[edwardkarlsson]

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).

[nomad-software]

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.

[shimaashamia]

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

[chilio]

@shimaashamia mysql 8.0 version brings significant changes to auth, therefore you need to make sure your app uses new logging authorization with DB.
You can configure your mysql 8 server to follow old auth directives as mentioned in comments above....

[autaut03]

@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.

[chilio]

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

[chaevnicher]

`In Connection.php line 664:

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

In Connector.php line 70:

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

In Connector.php line 70:

PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]
`
wwwut? table_name = ???

[nomad-software]

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

[95tuanle]

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!

[staudenmeir]

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

[simplymichael]

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.

[napsterrahul1]

hi iam using webmin in vm ware and it is not working at all please suggest i am usnig webmin for that

[swrshah1]

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.

[ThilinaM]

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

[sajidali2444]

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

save my life

[jpruiz114]

How come all the solutions suggested imply changing the behavior of the database?

mysql_native_password is the traditional method to authenticate, it is not very secure (it uses just a hash of the password), but it is compatible with older drivers.

Is there a pull request to support this in Laravel? Is it a PHP limitation?

Thanks.

I just found the answer to my comment. Please disregard.

This is because of this error:

php artisan migrate

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

https://www.php.net/manual/en/mysqli.requirements.php

https://stackoverflow.com/questions/55876981/php-7-3-4-does-not-support-caching-sha2-password

The PHP mysqli requirements page still says caching_sha2_password plugin will be supported in a future PHP release, so no PHP version seems to support caching_sha2_password. Obviously, short of providing an appropriate patch to PHP, you are not the only one to eventually switch back to 'mysql_native_password' auth strategy on your DB server.

[connecteev]

This was painful, but in case others are stuck, this is how I debugged and fixed the problem
https://laracasts.com/discuss/channels/laravel/error-running-cron-commands-to-connect-with-mysql8-database?page=1#reply=551836

[ThilinaM]

I had the same issue when I changed my password on my SQL

I tried the following things and it worked for me because of it has stored cache of .env file

1.first of the turn off artisan server and make changes to .env file and run these commands

php artisan cache:clear
php artisan config:clear
php artisan route:clear

then run the

php artisan serve

Then it will work Happy cording
Thilina Dharmasena

[oleynikd]

Don't forget to update to PHP 7.4

[wowremywang]

@adiachenko
I tried running this command
mysqld --default-authentication-plugin=mysql_native_password

020-03-11T22:25:40.586329Z 0 [System] [MY-010116] [Server] /usr/local/mysql-8.0.19-macos10.15-x86_64/bin/mysqld (mysqld 8.0.19) starting as process 18261
2020-03-11T22:25:40.606916Z 0 [Warning] [MY-010091] [Server] Can't create test file /usr/local/mysql-8.0.19-macos10.15-x86_64/data/mysqld_tmp_file_case_insensitive_test.lower-test
2020-03-11T22:25:40.606933Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.19-macos10.15-x86_64/data/ is case insensitive
2020-03-11T22:25:40.607132Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to '/usr/local/mysql-8.0.19-macos10.15-x86_64/data/' (OS errno: 13 - Permission denied)
2020-03-11T22:25:40.607199Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-11T22:25:40.607416Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.19-macos10.15-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

I also tried. sudo mysqld --default-authentication-plugin=mysql_native_password

2020-03-11T22:26:01.033046Z 0 [System] [MY-010116] [Server] /usr/local/mysql-8.0.19-macos10.15-x86_64/bin/mysqld (mysqld 8.0.19) starting as process 18264
2020-03-11T22:26:01.039044Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.19-macos10.15-x86_64/data/ is case insensitive
2020-03-11T22:26:01.039147Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2020-03-11T22:26:01.039254Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-11T22:26:01.039540Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.19-macos10.15-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

Any idea?

[nickescobedo]

One of my sites was still on PHP 7.3 and having issues connecting but my other sites that were on 7.4 were connection to MySQL just fine. Thank you @oleynikd for the tip!

[beng970804]

image:

Hi, may I know where should I placed this line of code in details? I didnt find anything similar in my docker file

[iOShuyang]

sudo mysqld --default-authentication-plugin=mysql_native_password

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

[beng970804]

sudo mysqld --default-authentication-plugin=mysql_native_password

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

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

[iOShuyang]

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';

已经解决了哈

[driesvints]

Hey everyone,

I'm locking this issue because it either has gone off-topic, become a dumping ground for things which shouldn't be in an issue tracker or is just too old. Please try to discuss things further on one of the below channels:

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC