OperationalError at /admin/login/ (1698, "Access denied for user 'root'@'localhost'")

Question

Yes, this is a common question in Stack Overflow but my problem is kinda different!

I have a server on Digital Ocean. Os: Ubuntu 18.04

I was trying to host my django project on the apache server with mysql.

I have completed the first part but the problem is integrating the mysql. I have installed the mysql and mysqlclient. There is a user root with password in my mysql. I have created a database and gave the following permission.

GRANT ALL PRIVILEGES ON urp_test.* TO 'root'@'localhost' identified by 'password_for_root';

The I configured my settings.py file. Here is the db portion:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'urp_test',
        'USER': 'root',
        'PASSWORD': 'password_for_the_db',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

Then I did a python manage.py makemigrations, python manage.py migrate, python manage.py createsuperuser.

All of them were executed successfully. So when I go login from the admin module, I see the error:

OperationalError at /admin/login/ (1698, "Access denied for user 'root'@'localhost'")

I tried deleting the database and doing the same thing a couple of times but the same problem.

Edit:

If I log in with the same user and password from terminal, i.e. like this:

mysql -u root -p with the password, I can access the databases.

As I mentioned earlier, python manage.py createsuperuser does create a user and I can get the user from the database table.

The problem is when I try to log in that user from the admin part from the browser i.e. like this:

W.X.Y.Z:80/admin/login

I got the mentioned error. Here W.X.Y.Z is the IP for my DigitalOcean server

So, it's definitely a permission error still, isn't it?

1. How can I resolve this error?
2. And also I planned to develop a sign-up module for my normal user. If they sign up, will this permission be accessible to them? Do they get any access to the database?

Answer 1

There are some complications to opening a db under root user. You can see details here

What I did:

1. logged in as root user in mysql

   mysql> mysql -u root -p

2. created a new user

   mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

3. mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

4. mysql> FLUSH PRIVILEGES;

5. mysql> exit

6. Then I logged in as new user

   mysql -u newuser -p

and entered the new password I set up earlier to create this user.

7. Then I created the db by

   create database my_database_name

Answer 2

I was having the same problem and I just change my host from 127.0.0.1 to localhost in the settings.py and is now working perfectly fine on my vps.