How to setup connection to MySql database with gitlab CI/CD

Question

I'm trying to set up automatic testing of django project using CI/CD gitlab. The problem is, I can't connect to the Mysql database in any way.

gitlab-ci.yml

services:
  - mysql:5.7

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

stages:
  - test

test:
  stage: test
  before_script:
  - apt update -qy && apt-get install -qqy --no-install-recommends default-mysql-client
  - mysql --user=$MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DATABASE --host=$MYSQL_HOST --execute="SHOW DATABASES; ALTER USER '$MYSQL_USER'@'%' IDENTIFIED WITH mysql_native_password BY '$MYSQL_PASSWORD'"
  script:
  - apt update -qy
  - apt install python3 python3-pip virtualenvwrapper -qy
  - virtualenv --python=python3 venv/
  - source venv/bin/activate
  - pwd
  - pip install -r requirement.txt
  - python manage.py test apps

With this file configuration, I get error

 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

What have I tried to do

add to mysql script tcp connection unstead socket

mysql --protocol=TCP --user=$MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DATABASE --host=$MYSQL_HOST --execute="SHOW DATABASES; ALTER USER '$MYSQL_USER'@'%' IDENTIFIED WITH mysql_native_password BY '$MYSQL_PASSWORD'"

And in this case I got

ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (99)

How do I set up properly?

Answer 1

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}'"