SQLSTATE[HY000] [1698]

Question

How can I overcome this error ?

Error: SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' (SQL: select count(*) as aggregate from users where email = [email protected]

I'm using Ubuntu

Please help

Solution 1

Login as root first:

$ sudo mysql -u root

Then CREATE or ALTER a non-root user (use '127.0.0.1' instead of 'localhost' if needed):

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'adminspassword';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

Exit and restart:

exit
$sudo service mysql restart
$sudo service apache2 restart

And edit the .env file:

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3630
DB_DATABASE=yourdbname
DB_USERNAME=admin
DB_PASSWORD=adminspassword

Solution 2

MySQL will make a difference between "localhost" and "127.0.0.1".

It might be possible that 'root'@'localhost' is not allowed because there is an entry in the user table that will only allow root login from 127.0.0.1.

This could also explain why some application on your server can connect to the database and some not because there are different ways of connecting to the database. And you currently do not allow it through "localhost".

Solution 3

I know its late however looking for answers, I couldn't find anything and at last I got this answer.

$sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q

Now you should be able to log in as root. Thanks @Matematikisto in this thread

Comments

[ᴄʀᴏᴢᴇᴛ]

The error says access denied : do you use the correct password to connect and do you have right to access this database ?

[jonahgeek]

I used the correct username, which is roor. Then i have no password.

[ᴄʀᴏᴢᴇᴛ]

Sometimes the root login without password is disabled. you may need to set a root password. have a look here dev.mysql.com/doc/refman/8.0/en/default-privileges.html

[GordonM]

Logging in as root is never a good idea and shouldn't be used for general purpose operations. You should create a user in MySQL with the bare minimum permissions needed for whatever it is that your application does

[Gaurav Gupta]

put your .env file code

[jonahgeek]

APP_NAME=Laravel APP_ENV=local APP_KEY=code APP_DEBUG=true APP_LOG_LEVEL=debug APP_URL=localhost DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=homestead DB_USERNAME=root DB_PASSWORD= BROADCAST_DRIVER=log CACHE_DRIVER=file SESSION_DRIVER=file SESSION_LIFETIME=120 QUEUE_DRIVER=sync REDIS_HOST=127.0.0.1 REDIS_PASSWORD=null REDIS_PORT=6379 MAIL_DRIVER=smtp MAIL_HOST=smtp.mailtrap.io MAIL_PORT=2525 MAIL_USERNAME=null MAIL_PASSWORD=null MAIL_ENCRYPTION=null PUSHER_APP_ID= PUSHER_APP_KEY= PUSHER_APP_SECRET= PUSHER_APP_CLUSTER=mt1

[stanley mbote]

Ensure that when you have edited the *.env file in your laravel application run php artisan config:clear and php artisan cache:clear to reset the config files to the new values.

[hackernewbie]

After a lot of breaking my head with different solutions, this seems to have worked for me.