MySQL connection error while migrating to 2.0

Build Environment Databases
1

Hi there,

I am trying to migrate to version 2.0, and having some problems to integrate the database.

In version 1.0 we run a docker on dependency post. Then on the database part we the database preparation for running tests.

  - docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3306:3306 -d mysql:5.6; sleep 15

database:
  override:
  - mysql -u root --host 127.0.0.1 --port 3306 -e "CREATE DATABASE backpack_test"
  - find schema -type f -exec echo "{}" \;
  - for i in schema/sql/*.sql;do mysql -u root --host 127.0.0.1 --port 3306 backpack_test < $i;done
  - node_modules/.bin/knex seed:run --env test

Approach 1:

version: 2
 jobs:
   build:
   parallelism: 1
   shell: /bin/bash --login
docker:
  - image: circleci/build-image:ubuntu-14.04-XXL-upstart-1189-5614f37
    command: /sbin/init

steps:
  - checkout
  - setup_remote_docker
  - run: export DEBIAN_FRONTEND=noninteractive
  - run: sudo apt-get -y remove mysql-server
  - run: sudo apt-get -y autoremove
  - run: sudo apt-get -y install mysql-client
  - run: docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3306:3306 -d mysql:5.6; sleep 15
  - run: mysql -u root -h 127.0.0.1 --port 3306 -e "CREATE DATABASE backpack_test"
  - run: find schema -type f -exec echo "{}" \;
  - run: for i in schema/sql/*.sql;do mysql -u root -h 127.0.0.1 --port 3306 backpack_test < $i;done
  - run: node_modules/.bin/knex seed:run --env test

I have tried running same docker command, with setup_remote_docker. Then MYSQL server gets started but not on localhost, it gets started on 0.0.0.0, when I am trying to call the steps after override portion, then it shows this:

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
Exited with code 1

I have also tried to isolate the docker using a MySQL image as indicated on some discussions, but the after the environment spins up, the MySQL container shows error and says that the port is already in use, whereas I haven’t used this port.

Approach 2:

# versioning 2
version: 2
jobs:
build:
branches:
  ignore:
    - develop


parallelism: 1
shell: /bin/bash --login
docker:
  - image: circleci/build-image:ubuntu-14.04-XXL-upstart-1189-5614f37
    command: /sbin/init

  - image: mysql:5.6
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: yes
      MYSQL_ROOT_PASSWORD: ''

steps:
  - checkout
  - setup_remote_docker
  - run: export DEBIAN_FRONTEND=noninteractive
  - run: sudo apt-get -y remove mysql-server
  - run: sudo apt-get -y autoremove
  - run: sudo apt-get -y install mysql-client
  - run: mysql -u root -h 127.0.0.1 --port 3306 -e "CREATE DATABASE backpack_test"
  - run: find schema -type f -exec echo "{}" \;
  - run: for i in schema/sql/*.sql;do mysql -u root -h 127.0.0.1 --port 3306 backpack_test < $i;done
  - run: node_modules/.bin/knex seed:run --env test

Now my question is, what am I missing here to make these work? I have been on it for like 5-6 hours trying different approaches, all in vain.

Thanks in advance.

2

I suspect your first approach won’t work. What happens is that CircleCI adds some hooks to force Docker containers to be run remotely. However, if you do it manually as you have done here (docker run), you do not have sufficient permissions inside your build container to publish ports. I am also not sure if the networking would be merged back into localhost either.

Your second approach is much more likely to work. This will launch a remote MySQL container and make the database connection available on localhost:3306. However, early in your steps, I would suggest adding something like dockerize or waitforit to wait for the port to open.

The usual problem here is that CircleCI will spin up your build/primary container and your MySQL container in parallel. Once they are both started, the primary will carry on with checkout and the other steps, but note that just because the MySQL container has started, it does not mean that MySQL is ready. That may take another few seconds, so unless you wait, you have a race condition between your build and your database.

Further reading:

https://discuss.circleci.com/t/prevent-race-conditions-by-waiting-for-services-with-dockerize/11215

3

I understood what you have said. But on that second approach, the problem was that the container tries to start and then shuts down because some port was being occupied. But it shouldn’t be the case because the primary image is just an ubuntu image.

2018-08-28 18:55:02 8 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2018-08-28 18:55:02 8 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2018-08-28 18:55:02 8 [ERROR] Aborting

I added the mysql image and added the dockerize step just after the checkout as you have said and the error still persists. Now the docker image portion looks like this.

parallelism: 1
shell: /bin/bash --login
docker:
  - image: circleci/build-image:ubuntu-14.04-XXL-upstart-1189-5614f37
    command: /sbin/init

  - image: circleci/mysql:5.6
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: yes

Added the dockerize portion just after the checkout like this.

- run:
      name: install dockerize
      command: wget https://github.com/jwilder/dockerize/releases/download/$DOCKERIZE_VERSION/dockerize-linux-amd64-$DOCKERIZE_VERSION.tar.gz && sudo tar -C /usr/local/bin -xzvf dockerize-linux-amd64-$DOCKERIZE_VERSION.tar.gz && rm dockerize-linux-amd64-$DOCKERIZE_VERSION.tar.gz
      environment:
        DOCKERIZE_VERSION: v0.3.0
  - run:
      name: Wait for db
      command: dockerize -wait tcp://localhost:3306 -timeout 1m

I think that portion might help me after the container is setup. But as I have mentioned previously, for some reason the port 3306 seems occupied and the container is shutting down.

4

Try getting an SSH session on your failing build (in the build detail screen in the UI) and see what is hanging onto that port. I believe you can use Netstat for this.

I wonder if it might be worth swapping to a simpler image? I don’t know what circleci/build-image:ubuntu-14.04-XXL-upstart, but I’d probably swap to an official Ubuntu image on Docker Hub, unless CircleCI are offering something there that you need.

5

Got into ssh session and saw that something was occupying the port .

Removed the larger ubuntu image and went for a small one, installed the packages necessary, got the database migration done. Seems like for that robust ubuntu image given by migration translation is giving me connection error.

6

OK, so to clarify: did the new image work? If you still have an error, put the logs here.

7

Yes, the new image helped me to get over these errors. The errors for database connection is resolved. Thanks for your help.

1 Like
9

I have edited my response and started a new thread. :slightly_smiling_face:

Here’s the conversation link:

10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.