MySQL access denied

Using Django Deployment
1

Hey everyone,

not sure if this is the right place for this issue. I am desperately trying to connect to a MySQL DB via Django and it doesn’t make sense to me at all. My setup is as follows

  1. Creating a docker container running a MySQL DB
#!/bin/sh
# variables
MYSQL_VERSION=8.0.19
PORT=3306
CONTAINER_NAME=db-local
DATABASE_NAME=myDatabase
ROOT_USER=root
ROOT_PASSWORD=admin
DEV_USER=dev
DEV_PASSWORD=dev

# Start to run docker container
docker run --rm -d \
  --name=$CONTAINER_NAME \
  -p $PORT:$PORT \
  -e MYSQL_ROOT_PASSWORD=$ROOT_PASSWORD \
  -e MYSQL_DATABASE=$DATABASE_NAME \
  -t mysql:$MYSQL_VERSION \
  --default-authentication-plugin=mysql_native_password
echo "Created new container"

SLEEP_TIME=30
echo "Wait " $SLEEP_TIME "sec for the database to be started"
sleep $SLEEP_TIME
echo "Finished waiting"

# Create Dev user
docker exec $CONTAINER_NAME bash -c \
  "mysql -hlocalhost -P 3306 -u$ROOT_USER -p$ROOT_PASSWORD -e \"CREATE USER '$DEV_USER'@'localhost' IDENTIFIED WITH mysql_native_password BY '$DEV_PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO '$DEV_USER'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;"\"
  1. Try to run the server / perform migrations
    I have configured the following for the database connection
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "myDatabase",
        "USER": "dev",
        "PASSWORD": "dev",
        "HOST": "localhost",
        "PORT": "3306",
    },
}

But all i get is

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

I have no problems accessing the DB with the root or dev user from the shell, both users even have all the privileges. But Django tells me that the dev user is not allowed to access the db. Please help me!

2

Is your application running in the host, or in another container? If you’re running your application in another container, it’s not connecting to localhost, it’s connecting to the container name. Also, this means that you need to grant permissions to the account at the container in which it’s being run. (For testing purposes, I’d suggest granting permissions to the account to all hosts.)

3

Hey Ken, my application is running in host. I also tried to connect with DBeaver to the database, which also worked just fine (Parameters > host: localhost, port: 3306, user: dev, password: dev)

4

Granting access to all hosts is being done by replacing the ‘localhost’ in the last statement of the bash script by ‘%’ right? I tried that before and the same issue still occurred

5

I can’t address what you’re seeing with DBeaver - it’s possible that it’s using a different / custom library for connection.

But what I am seeing is that it’s not working with the mysql command-line client, unless:

  • I specify the docker container’s IP address for the connection
    or
  • I connect to it from another docker container and reference the server by container name

I believe that it should also work if you specify --network host, but I haven’t been able to get that to work yet.

6

You’re right DBeaver could be totally different. Regarding your two comments:

  1. I tried to get the containers ip by
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' db-local

a got something like 172.17.0.1. Both Django and DBeaver timed out with this ip

  1. I didn’t try that one yet, but im sure that this will work

But what did work for me was to simply use the hostname, configured like so

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "myDatabase",
        "USER": "dev",
        "PASSWORD": "dev",
        "HOST": os.uname().nodename,
        "PORT": "3306",
    },
}