Got an error creating the test database - django, docker, mysql

Question

I have an issue with creating test database. I use mysql for db and docker compose.

I have no problem running docker containers with docker-compose, but when I run test it spits this error message.

Note that the name of django service is web, and mysql service is db.

$ docker-compose run --rm web sh -c "python manage.py test"
Creating sociallogin_web_run ... done
Creating test database for alias 'default'...
Got an error creating the test database: (1044, "Access denied for user 'myuser'@'%' to database 'test_mydb'")

my docker-compose.yml: version: "3.9"

services:

  db:
    image: mysql:8
    env_file:
      - .env
    command: 
      - --default-authentication-plugin=mysql_native_password
    restart: always
    # ports:
    #   - "3306:3306"
    # volumes:
    #   - data:/var/lib/mysql

  web:
    build: .
    command: >
      sh -c "python manage.py wait_for_db &&
             python manage.py makemigrations && 
             python manage.py migrate && 
             python manage.py runserver 0.0.0.0:8000"
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on: 
      - db
    env_file:
      - .env

# volumes:
#   data:

my .env file looks like this:

MYSQL_ROOT_PASSWORD=rootpass
MYSQL_USER=exampleuser
MYSQL_PASSWORD=examplepass
MYSQL_DATABASE=exampledb
MYSQL_PORT=3306
SECRET_KEY=exmaple_random_characters

DATABASES in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': os.environ.get('MYSQL_DATABASE'),
        'USER': os.environ.get('MYSQL_USER'),
        'PASSWORD': os.environ.get('MYSQL_PASSWORD'),
        'PORT': os.environ.get('MYSQL_PORT'),
        'HOST': 'db',
        'TEST': {
            'NAME': 'test_mydb',
        }
    }
}

I looked at this, and I even tried this. It didn't help me.

Anyone who's been stuck at similiar problem?

Thanks guys in advance.

score 1 · Accepted Answer · answered May 02 '21 at 06:08

I think this is issue is not related django app, this is look like basic mysql error where you try to create/access database with that user which is not have permission for create/manage database, please test it again via root user of database.

You can visit on this url ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db' for.

score 0 · Answer 2 · answered May 05 '21 at 07:43

As K.D Singh mentioned, It was indeed mysql user permission problem.

In short, use root user when initializing mysql container.

Not sure what keeps making separate user from making test though. Because mysql docker hub says

MYSQL_USER, MYSQL_PASSWORD These variables are optional, used in conjunction to create a new user and to set that user's password. This user will be granted superuser permissions (see above) for the database specified by the MYSQL_DATABASE variable. Both variables are required for a user to be created.

, doesn't this mean if I create user and password then it has superuser permissions like creating database as well? Not sure why it has permission issue.

Anyway, below is what I changed.

Do note that there is no need to use this mechanism to create the root superuser, that user gets created by default with the password specified by the MYSQL_ROOT_PASSWORD variable.

I changed my DATABASES setting as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': os.environ.get('MYSQL_DATABASE'),
        'PASSWORD': os.environ.get('MYSQL_ROOT_PASSWORD'),
        'PORT': os.environ.get('MYSQL_PORT'),
        'HOST': os.environ.get('MYSQL_HOST'),
    }
}

So basically I removed USER key and updated PASSWORD with MYSQL_ROOT_PASSWORD, not MYSQL_PASSWORD tied to MYSQL_USER that I initiated my container with.

my .env file has a change like this:

MYSQL_ROOT_PASSWORD=myrootpassword
MYSQL_DATABASE=mydatabasename
MYSQL_PORT=3306
MYSQL_HOST=db
SECRET_KEY=random_secret_key_that_has_no_dollor_sign

my docker-compose.yml:

version: "3.9"

services:

  db:
    image: mysql:8
    env_file:
      - .env
    command: 
      - --default-authentication-plugin=mysql_native_password
    restart: always
    ports:
      - "3308:3306"
    # volumes:
    #   - data:/var/lib/mysql

  web:
    build: .
    command: >
      sh -c "python manage.py wait_for_db &&
             python manage.py makemigrations && 
             python manage.py migrate && 
             python manage.py runserver 0.0.0.0:8000"
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on: 
      - db
    env_file:
      - .env

# volumes:
#   data:

Interestingly as for ports part, notice that I set 3308:3306. Since I already have another running mysql container that is running with default port 3306, When I use ports 3306:3306, it didn't work. So I only changed outer port into 3308 and inner port 3306. Since django app communicates with inner port, .env file's port number stays put as 3306.

Got an error creating the test database - django, docker, mysql

2 Answers2

In short, use root user when initializing mysql container.

Anyway, below is what I changed.