mysql

[SOLVED] CircleCI job creates docker MySQL 8 but nothing can connect

Issue

(See UPDATE at end of post for potentially helpful debug info.)

I have a CircleCI job that deploys MySQL 8 via - setup_remote_docker+docker-compose and then attempts to start a Java app to communicate with MySQL 8. Unfortunately, even though docker ps shows the container is up and running, any attempt to communicate with MySQL–either through the Java app or docker exec–fails, saying the container is not running (and Java throws a "Communications Link Failure" exception). It’s a bit confusing because the container appears to be up, and the exact same commands work on my local machine.

Here’s my CircleCI config.yml:

  Build and Test:
    <<: *configure_machine
    steps:
      - *load_repo
      - ... other unrelated stuff ...
      - *load_gradle_wrapper
      - run:
          name: Install Docker Compose
          environment:
            COMPOSE_VERSION: '1.29.2'
          command: |
            curl -L "https://github.com/docker/compose/releases/download/${COMPOSE_VERSION}/docker-compose-$(uname -s)-$(uname -m)" -o ~/docker-compose
            chmod +x ~/docker-compose
            sudo mv ~/docker-compose /usr/local/bin/docker-compose
      - setup_remote_docker
      - run:
          name: Start MySQL docker
          command: docker-compose up -d
      - run:
          name: Check Docker MySQL
          command: docker ps
      - run:
          name: Query MySQL #test that fails
          command: docker exec -it mysql8_test_mysql mysql mysql -h 127.0.0.1 --port 3306 -u root -prootpass -e "show databases;"

And here’s my docker-compose.yml that is run in one of the steps:

version: "3.1"

services:
  # MySQL Dev Image
  mysql-migrate:
    container_name: mysql8_test_mysql
    image: mysql:8.0
    command:
      mysqld --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
      --log-bin-trust-function-creators=true
    environment:
      MYSQL_DATABASE: test_db
      MYSQL_ROOT_PASSWORD: rootpass
    ports:
      - "3306:3306"
    volumes:
      - "./docker/mysql/data:/var/lib/mysql"
      - "./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf"
      - "./mysql_schema_v1.sql:/docker-entrypoint-initdb.d/mysql_schema_v1.sql"

It’s a fairly simple setup and the output from CircleCI is positive until it reaches the docker exec, which I added to test the connection. Here is what the output from CircleCI says per step:

Start MySQL Docker:

#!/bin/bash -eo pipefail
docker-compose up -d

Creating network "project_default" with the default driver
Pulling mysql-migrate (mysql:8.0)...
8.0: Pulling from library/mysql

5158dd02: Pulling fs layer
f6778b18: Pulling fs layer
a6c74a04: Pulling fs layer
4028a805: Pulling fs layer
7163f0f6: Pulling fs layer
cb7f57e0: Pulling fs layer
7a431703: Pulling fs layer
5fe86aaf: Pulling fs layer
add93486: Pulling fs layer
960383f3: Pulling fs layer
80965951: Pulling fs layer
Digest: sha256:b17a66b49277a68066559416cf44a185cfee538d0e16b5624781019bc716c122    121B/121BkBBB
Status: Downloaded newer image for mysql:8.0
Creating mysql8_******_mysql ... 
Creating mysql8_******_mysql ... done

So we know MySQL 8 was pulled fine (and therefore the previous step worked). Next step is to ask Docker what’s running.

Check Docker MySQL:

#!/bin/bash -eo pipefail
docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                  PORTS                               NAMES
cb6b7941ad65        mysql:8.0           "docker-entrypoint.s…"   1 second ago        Up Less than a second   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql8_test_mysql

CircleCI received exit code 0

Looks good so far. But now let’s actually try to run a command against it via docker exec.

Query MySQL:

#!/bin/bash -eo pipefail
docker exec -it mysql8_test_mysql mysql mysql -h 127.0.0.1 --port 3306 -u root -prootpass -e "show databases;"

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

Exited with code exit status 1

CircleCI received exit code 1

So now we can’t connect to MySQL even though docker ps showed it up and running. I even tried adding an absurd step to wait in case MySQL needed more time:

      - run:
          name: Start MySQL docker
          command: docker-compose up -d
      - run:
          name: Check Docker MySQL
          command: docker ps
      - run:
          name: Wait Until Ready
          command: sleep 120
      - run:
          name: Query MySQL
          command: docker exec -it mysql8_test_mysql mysql mysql -h 127.0.0.1 --port 3306 -u root -prootpass -e "show databases;"

Of course adding a 2 minute wait for MySQL to spin up didn’t help. Any ideas as to why this is so difficult in CircleCI?

Thanks in advance.

UPDATE 1: I can successfully start MySQL if I SSH into the job’s server and run the same command myself:

docker-compose up

Then in another terminal run this:

docker exec -it mysql8_test_mysql mysql mysql -h localhost --port 3306 -u root -prootpass -e "show databases;"

+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

So it is possible to start MySQL. It’s just not working right when through job steps.

UPDATE 2: I moved the two minute wait between docker-compose up -d and docker ps and now it shows nothing is running. So the container must be starting then crashing and that’s the reason for why it’s not available moments later.

Solution

The cause of the problem was the volumes entry in my docker-compose.yml with this line:

 - "./mysql_schema_v1.sql:/docker-entrypoint-initdb.d/mysql_schema_v1.sql"

The container appeared to be up when I checked immediately after docker-compose up -d but in actuality it would crash seconds later because CircleCI appears to have an issue with Docker volume, potentially related to this: https://discuss.circleci.com/t/docker-compose-doesnt-mount-volumes-with-host-files-with-circle-ci/19099.

To make it work I removed that volume entry and added run commands to copy and import the schema like so:

      - run:
          name: Start MySQL docker
          command: docker-compose up -d
      # Manually copy schema file instead of using docker-compose volumes (has issues with CircleCI)
      - run:
          name: Copy Schema
          command: docker cp mysql_schema_v1.sql mysql8_mobile_mysql:docker-entrypoint-initdb.d/mysql_schema_v1.sql
      - run:
          name: Import Schema
          command: docker exec mysql8_mobile_mysql /bin/sh -c 'mysql -u root -prootpass < docker-entrypoint-initdb.d/mysql_schema_v1.sql'

With this new setup I’ve been able to create the tables and connect to MySQL. However, there appears to be an issue running tests against MySQL causing hangups but that might be unrelated. I will follow up with more information, but at least I hope this can help someone else.

Answered By – Justin-Deq

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *