laravel / framework

The Laravel Framework.
https://laravel.com
MIT License
32.45k stars 11k forks source link

Can't connect to DB on the MySql version 8.0 #23961

Closed henrypham299 closed 6 years ago

henrypham299 commented 6 years ago

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 commented 6 years ago

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',
            ],
        ],
    ],
henrypham299 commented 6 years ago

@BrandonSurowiec

Thank you very much.

BrandonSurowiec commented 6 years ago

You're welcome.

adiachenko commented 6 years ago

@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 commented 6 years ago

@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 commented 6 years ago

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

briangonzalezmia commented 6 years ago

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

mfgabriel92 commented 5 years ago

Didn't work for me.

edwardkarlsson commented 5 years ago

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

nomad-software commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

@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 commented 5 years ago

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

chaevnicher commented 5 years ago

`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 commented 5 years ago

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

95tuanle commented 5 years ago

Don't forget to create a new user or update existing user to utilise the older way of authenticating. CREATE USERroot@localhostIDENTIFIED 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 commented 5 years ago

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

simplymichael commented 5 years ago

Don't forget to create a new user or update existing user to utilise the older way of authenticating. CREATE USERroot@localhostIDENTIFIED 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 commented 5 years ago

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

swrshah1 commented 5 years ago

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 commented 5 years ago

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

image

sajidali2444 commented 5 years ago

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

image

save my life

jpruiz114 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Don't forget to update to PHP 7.4

wowremywang commented 4 years ago

@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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

sudo mysqld --default-authentication-plugin=mysql_native_password

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

beng970804 commented 4 years ago

sudo mysqld --default-authentication-plugin=mysql_native_password

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

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

iOShuyang commented 4 years ago

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 commented 4 years ago

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: