Django : mysql : 1045, \"Access denied for user

问题:

I have the whole setup working for months on my local computer.
I'm installing on a remote site now.
Created a fresh mysql DB, and created a new user ("someuser") and gave it complete grants, like so -

GRANT ALL PRIVILEGES ON . TO 'someuser'@'localhost' IDENTIFIED BY 'somepassword' WITH GRANT OPTION;

I have sync'd the db, using "python manage.py syncdb" and the correct tables were created. My settings.py has this same user.

But when I try to login a user through the application, and it hits the DB, I see the following in the logs -

(1045, "Access denied for user 'someuser'@'localhost' (using password: YES)")

I logged in through mysql (installed on the same box as django) and checked the grants and it correctly shows -

Grants for someuser@localhost
GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD '*thesaltedpasswordOverHere' WITH GRANT OPTION

I don't want to use the root user/password for django, since it doesn't seem the correct way.

Any pointers as to what might be wrong ?

回答1:

I do it like this for a database named foo_db:

create database foo_db;
create user foo_user identified by 'foo_password';
grant all on foo_db.* to 'foo_user'@'%';
flush privileges;

回答2:

In my case the settings.py has the following:

    DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'TRYDJANGO',
        'USERNAME':'user_trydjango',
        'PASSWORD':'passtry',
        'PORT':'3306',
        'HOST': 'localhost',
    }
}

And it works if I change the 'USERNAME' to 'USER':

    DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'TRYDJANGO',
        'USER':'user_trydjango',
        'PASSWORD':'passtry',
        'PORT':'3306',
        'HOST': 'localhost',
    }
}

回答3:

My error message indicated that there wasn't password supplied to the database:

django.db.utils.OperationalError: (1045, "Access denied for user 'mylocalusername'@'localhost' (using password: NO)")

The manage.py should pick up the database access credentials from the settings.py, that's a specific database user (and of course that user's password as well). It's a security mistake to react to my error message by granting database login privileges to the local user.

In my case there were problems with the settings.py (we were restructuring our build pipeline from django pipeline to webpack) and I needed to remove pipeline related parts from my settings.py for the fix. It's a question why I didn't get any python error message about the settings.py problems, but instead I got this error which is not local to the real problem. That error message is just a transitive result of the source problem. Once I fixed the manage.py, the credentials were picked up from the DATABASE settings as usual and everything went smooth.

---

In our case we were using django-pipeline before webpack (more specifically pip packages django-pipeline-browserify==0.4.1 and django-pipeline==1.6.8), so once we transitioned I had to just remove these lines from settings:

NODE_MODULES_BIN = '/home/myuser/sourcerepositorydir/node_modules/.bin/'
PIPELINE['SASS_BINARY'] = '/var/lib/gems/2.3.0/gems/sass-3.5.3/bin/sass'
PIPELINE['BABEL_BINARY'] = '{}{}'.format(NODE_MODULES_BIN, 'babel')
PIPELINE['BROWSERIFY_BINARY'] = '{}{}'.format(NODE_MODULES_BIN, 'browserify')
PIPELINE_BROWSERIFY_BINARY = PIPELINE['BROWSERIFY_BINARY']
PIPELINE['BROWSERIFY_ENV'] = {'NODE_ENV': 'development'}
PIPELINE['BROWSERIFY_ARGUMENTS'] = PIPELINE['BROWSERIFY_ARGUMENTS'] + ' --debug'

Until that I was just getting nonsensical error messages.

回答4:

@ mattblang:

In this case it helps if the host is set in the actually used settings.py file too.

For me it is /etc/graphite/local_settings.py where the DATABASES Stanzas had a wrong value, for HOST there was: 'HOST': '127.0.0.1',

Because during the runtime of syncdb command it searched for localhost, I changed it to 'HOST': 'localhost',

Now it looks something like this:

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

... and now the syncdb command python manage.py syncdb runs successfully.

回答5:

Also change the port number to '3307'if MySQl-python 64-bit in settings.py, Then only the connection happening in Windows and MySql 5.7 django.

'3306' for MySQl-python 32-bit

DATABASES = {
    'default': {
        'NAME': 'graphite',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'graphite',
        'PASSWORD': 'thepasswordyouchoose',
        'HOST': 'localhost',
        'PORT': '3307'
    }
}

回答6:

Refering to the answer of @duffymo and changed the last statement could work for me.

create database foo_db;
create user foo_user identified by 'foo_password';
grant all on foo_db.* to foo_user@localhost identified by 'foo_password' with grant option;

回答7:

as far as i understood someuser is a DB guest user and not the admin one, right?

If so, from your MySQL DB admin user, grant someuser to access MySQL environment table 'mysql.user' as follow:

GRANT SELECT ON mysql.user TO 'someuser'@'%';

To me it worked, Ivan

回答8:

It seems that all I have done is:

1. drop my database
2. drop the user
3. re-create the user
4. re-create the database
5. grant to this user, flush privileges

and then it begin to work.

回答9:

I use this config and it works.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': '/etc/my.cnf',
        },
        #'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

回答10:

I got the following error:

ModuleNotFoundError: No module named 'MySQLdb' django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module. Did you install mysqlclient?

Solution # 01: Verify that If you user has permission to access the database and perform the DDL & DML operations on it.

Solution # 02: Changed the database configuration in settings.py file

From:

DATABASES = {

'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'root'
    }
}

To:

DATABASES = {

'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'root',
        'PASSWORD': 'root',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

And it started working.