12

I'm having difficulty getting MySQL 8 to work. This is the error that appears everytime I attempt a php artisan migrate. I've reinstalled MySQL only once so far because I didn't want to hurt my head anymore on what was going on. I've edited the database.php from other possible answers, but that also doesn't seem to work. I saw a possible answer that it's because of MySQL 8's sha256 encryption of the root password, which is why I want to go back to MySQL 5.7 which I've looked up works with laravel just fine. Though, I want to keep the packages up to date and keep MySQL 8 only if i can get it to work with laravel.

PHP 7.2

How do I get MySQL 8 to work with Laravel?

 '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' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'version' => 8,
            'modes' => [
                'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ],
        ],

``

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

  at /Users/home/Projects/laravel_tut/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]")
      /Users/home/Projects/laravel_tut/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=laravel_tut", "root", "fdgkadgaf9g7ayaig9fgy9ad8fgu9adfg9adg", [])
      /Users/home/Projects/laravel_tut/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

UPDATE ANOTHER FIX I DID TO FIX THIS: With a fresh install of MySQL, i selected NO for encrypting passwords in the set up ( using legacy encryption, not the SHA encryption ) and it started to work with Laravel without any problems - Just use a long and strong password. reference of the installation step: https://www.percona.com/blog/wp-content/uploads/2018/05/Installing-MySQL-8.0-on-Ubuntu-2.png

Zac
  • 1,719
  • 3
  • 27
  • 48
  • 2
    well, laravel did not interface to mysql directly, actually, php handles the connection with somekind of plugin (PDO). so to start, please let us know the php version and you might to try [this](https://serverfault.com/questions/924367/php-7-2-8-pdo-fails-to-connect-to-mysql-8-0-12-ga-in-aws-ec2-lemp-stack), or [this official documentation](http://php.net/manual/en/ref.pdo-mysql.php). mysql 8 seems not supported easily by PDO.. – Bagus Tesa Oct 17 '18 at 23:22

2 Answers2

34

Since PHP doesn't understand caching_sha2_password, set the user back to mysql_native_password:

ALTER USER 'forge'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'new_password'
dns_nx
  • 3,651
  • 4
  • 37
  • 66
danblack
  • 12,130
  • 2
  • 22
  • 41
  • 3
    This worked for me with an addition to the my.ini `default_authentication_plugin=mysql_native_password` and removing old `caching_sha2_password` value. – endo64 Jan 08 '19 at 12:52
  • This worked for me when working with a MySQL 8.0+ and doing the Magento 2.3.1 web installation. Don't forget to add the ";" character at the end of the command if you don't want to make additional changes. – Seb Jul 17 '20 at 06:56
3

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

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

It would also help:

https://php.watch/articles/PHP-7.4-MySQL-8-server-gone-away-fix

Vinod Raut
  • 63
  • 6