Laravel 5.4 access denied for user root@localhost on migrate

Question

I have a stange one with Laravel. I have a bad habit of creating projects on virtual box with laravel project for testing. (debian last stable currently 9.1, nginx, php7.1, MariaDB).

Before you tell me to go read another post somewhere, please read everything, I mays have missed the post but I tried quite à lot I tried most of the solution that may have worked for others, but they don't in my case (thanks).

I tried different thing and always end breaking everything and doing hover (that's normal...).

I have already tried a number of installation and never had any problem with

php artisan migrate

That is until today. I prepared my migrations files and tried the migrate and I got this message :

[Illuminate\Database\QueryException]
  SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' (SQL: sele
  ct * from information_schema.tables where table_schema = test and table_nam
  e = migrations)

And this one:

[PDOException]
  SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'

I did everything as always, I checked my credentials that are in the .env file and tested them. No problem there.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=Password2

The database does exist, can't be wrong with this name :

SHOW DATABASEs;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

When looking for code error 1698 in MariaDB, they tell me that this code appear when it's trying to connect without password.

So my question is why is it doing this? I tried with a new project, just installed and doing the artisan command line, it changes nothing.

I would like to avoid doing a fresh os install.

Thanks in advance for your help and sorry for my poor english.

user_3049675 · Answer

For me 'root' user could not connect. Here's how to solve it:

1.login to mysql console $ mysql -u root -p

2.create new user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

3.grant privilages to your laravel database

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

or grant privilages to all tables

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

4.flush privilages for changes to take place

FLUSH PRIVILEGES;

5.exit mysql console

exit

6.edit .env file, update your MASTER_DB_USER and MASTER_DB_PASS to match your new user

7.run migration to test things out

php artisan migrate

user_5290004 · Answer

How this article explains, the authentication is made using auth_socket, so you have to change it to mysql_native_password

Run this sql into mysql with root user:

ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 
FLUSH PRIVILEGES;

user_11618108 · Answer

Same happened to me. I did the following steps to change the root user's password.

Goto Sudo mode with sudo su and open mysql with either mysql or msyql -u root (no password required for database)
Now use this command to change password ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
Done

Laravel 5.4 access denied for user root@localhost on migrate

7 Answers