5

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

I'm using Ubuntu

Please help

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
jonahgeek
  • 111
  • 1
  • 2
  • 11
  • 1
    The error says access denied : do you use the correct password to connect and do you have right to access this database ? – ᴄʀᴏᴢᴇᴛ Jun 27 '18 at 07:15
  • I used the correct username, which is roor. Then i have no password. – jonahgeek Jun 27 '18 at 07:19
  • 1
    Sometimes the root login without password is disabled. you may need to set a root password. have a look here https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html – ᴄʀᴏᴢᴇᴛ Jun 27 '18 at 07:25
  • 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 – GordonM Jun 27 '18 at 10:20
  • put your .env file code – Gaurav Gupta Jun 27 '18 at 10:46
  • APP_NAME=Laravel APP_ENV=local APP_KEY=code APP_DEBUG=true APP_LOG_LEVEL=debug APP_URL=http://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 – jonahgeek Jun 28 '18 at 03:59
  • 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. – stanley mbote Dec 21 '19 at 08:20

4 Answers4

16

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
cryss
  • 400
  • 3
  • 8
3

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

ramprasath .n
  • 100
  • 5
1

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

Arun
  • 421
  • 3
  • 6
0

I encountered this problem in MySQL 8, Ubuntu 20. By default, the policy does not grant "GRANT" rights, including for root, but even after manipulations to obtain them, the application was able to gain access only after granting the mysql_native_password rights to the account. Maybe it will help someone:

UPDATE mysql.user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('password'))))), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

the full sequence of steps to change the password and its format is here: MySQL: How to reset or change the MySQL root password?

ktscript
  • 309
  • 4
  • 11