[Solved]-How to setup connection to MySql database with gitlab CI/CD

There can be multiple reasons for your issue:

• Incorrect MySQL version.
  • Solution: Use mysql:5.7 instead of mysql:latest
• MySQL host is missing.
  • Solution: add MYSQL_HOST in the variables with the hostname of the MySQL server. (Should be mysql when using mysql:5.7 in services key)
• Django uses different credentials of DB.
  • Solution: check that the credentials in the variables section of your .gitlab-ci.yml and compare against Django’s settings.py. They should be the same.
• MySQL client not installed.
  • Solution: install the mysql-client in the script section and check if it is able to connect.

Here is a sample script that installs MySQL client and connects to the database in a debian based image (or a python:latest image):

script:
  - apt-get update && apt-get install -y git curl libmcrypt-dev default-mysql-
  - mysql --version
  - sleep 20
  - echo "SHOW tables;"| mysql -u root -p"$MYSQL_ROOT_PASSWORD" -h "${MYSQL_HOST}" "${MYSQL_DATABASE}"

Here is a complete and valid example of using MySQL 5.7 as a service and a python image with mysql-client installed successfully connecting to the MySQL database:

stages:
  - test

variables:
  MYSQL_DATABASE: "db_name"
  MYSQL_ROOT_PASSWORD: "dbpass"
  MYSQL_USER: "username"
  MYSQL_PASSWORD: "dbpass"
  MYSQL_HOST: mysql

test:
  image: python:latest
  stage: test
  services:
    - mysql:5.7
  script:
    - apt-get update && apt-get install -y git curl libmcrypt-dev default-mysql-client
    - mysql --version
    - sleep 20
    - echo "SHOW tables;" | mysql -u root -p"$MYSQL_ROOT_PASSWORD" -h "${MYSQL_HOST}" "${MYSQL_DATABASE}"
    - echo "Database host is '${MYSQL_HOST}'"

I see there is an accepted answer but with mysql 8.0 and python3:buster some things broke. The Python Debian images ship with mariadb and it is not easy to set up the standard mysql-client packages, resulting in the error:

"django.db.utils.OperationalError: 2059, “Authentication plugin…"

I got a working YAML below, using Ubuntu as the base image and mysql 8.0 as a service. You could either use the root user in both the .gitlab-ci and the test_settings or give the MYSQL user the privileges to create new databases and alter existing ones.

The initial MYSQL_DB _USER and _PASS variables can be set in Gitlab under Settings -> CI/CD -> Variables.

.gitlab-ci.yml:

variables:
    # "When using a service (e.g. mysql) in the GitLab CI that needs environtment variables
    # to run, only variables defined in .gitlab-ci.yml are passed to the service and
    # variables defined in GitLab GUI are unavailable."
    # https://gitlab.com/gitlab-org/gitlab/-/issues/30178
    # DJANGO_CONFIG: "test"
    MYSQL_DATABASE: $MYSQL_DB
    MYSQL_ROOT_PASSWORD: $MYSQL_PASS
    MYSQL_USER: $MYSQL_USER
    MYSQL_PASSWORD: $MYSQL_PASS
    # -- In your django settings file for the test environment you could put:
    # 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'),
    #       'HOST': 'mysql',
    #       'PORT': '3306',
    #         'CONN_MAX_AGE':60,
    #         },
    # }

    # -- You could us '--settings' to specify a custom settings file on the command line
    # -- below or use an environment variable to trigger an include in your settings:
    #   if os.environ.get('DJANGO_CONFIG')=='test':
    #       from .settings_test import *  # or specific overrides
    #


default:
    image: ubuntu:20.04

    # -- Pick zero or more services to be used on all builds.
    # -- Only needed when using a docker container to run your tests in.
    # -- Check out: http://docs.gitlab.com/ee/ci/docker/using_docker_images.html#what-is-a-service
    services:
    - mysql:8.0

    # This folder is cached between builds
    # http://docs.gitlab.com/ee/ci/yaml/README.html#cache
    # cache:
    #   paths:
    #     - ~/.cache/pip/

    before_script:
    - echo -e "Using Database $MYSQL_DB with $MYSQL_USER"
    - apt --assume-yes update
    - apt --assume-yes install apt-utils
    - apt --assume-yes install net-tools python3.8 python3-pip mysql-client libmysqlclient-dev
    # - apt --assume-yes upgrade
    - pip3 install -r requirements.txt

djangotests:
    script:
    # -- The MYSQL user gets only permissions for MYSQL_DB and therefor cant create a test_db.
    - echo "GRANT ALL on *.* to '${MYSQL_USER}';"| mysql -u root --password="${MYSQL_ROOT_PASSWORD}" -h mysql
    # -- use python3 explicitly. see https://wiki.ubuntu.com/Python/3
    - python3 manage.py test


migrations:
    script:
    - python3 manage.py makemigrations
    - python3 manage.py makemigrations myapp
    - python3 manage.py migrate
    - python3 manage.py check