How to start flyway after database initialization in Docker

Question

I have following docker compose file(docker-compose-dev.yml):

version: '3'
services:
  my_sql_db:
    image: percona:latest
    container_name: my_sql_db
    environment:

---

      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: abhs
    ports:
    - "3306:3306"

  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
      - ./flyway_scripts/src/main/resources/db/migration:/flyway/sql
    command: -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate
    depends_on:
    - my_sql_db

and following docker-compose.yml:

version: '3'
services:
  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
     - ./flyway_scripts/src/main/resources/db/migration:/flyway/sql

Then I execute following command:

docker-compose -f docker-compose.yml -f docker-compose-dev.yml up

And It lead to error:

In logs I see following:

my_sql_db    | Initializing database
flyway_migration  | Flyway Community Edition 5.1.4 by Boxfuse
flyway_migration  |
my_sql_db    | 2018-08-24T08:47:41.616694Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
my_sql_db    | 2018-08-24T08:47:41.616747Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
flyway_migration  | ERROR:
flyway_migration  | Unable to obtain connection from database (jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false) for user 'root': Could not connect to address=(host=my_sql_db)(port=3306)(type=master) : Connection refused (Connection refused)
flyway_migration  | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
flyway_migration  | SQL State  : 08
flyway_migration  | Error Code : -1
flyway_migration  | Message    : Could not connect to address=(host=my_sql_db)(port=3306)(type=master) : Connection refused (Connection refused)
my_sql_db    | 2018-08-24T08:47:43.024690Z 0 [Warning] InnoDB: New log files created, LSN=45790
flyway_migration  |
my_sql_db    | 2018-08-24T08:47:43.443625Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
my_sql_db    | 2018-08-24T08:47:43.588008Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5dc59a4f-a77a-11e8-b6cb-0242ac130002.
my_sql_db    | 2018-08-24T08:47:43.760654Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
my_sql_db    | 2018-08-24T08:47:44.518107Z 0 [Warning] CA certificate ca.pem is self signed.
my_sql_db    | 2018-08-24T08:47:44.925466Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
my_sql_db    | 2018-08-24T08:47:54.762213Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.762517Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.762889Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.763244Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.763472Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.763788Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.763928Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | 2018-08-24T08:47:54.764128Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
my_sql_db    | Database initialized
my_sql_db    | MySQL init process in progress...
my_sql_db    | 2018-08-24T08:47:58.970290Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
my_sql_db    | 2018-08-24T08:47:58.970345Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
my_sql_db    | 2018-08-24T08:47:58.974061Z 0 [Note] mysqld (mysqld 5.7.22-22) starting as process 58 ...
my_sql_db    | 2018-08-24T08:47:58.999651Z 0 [Note] InnoDB: PUNCH HOLE support available
my_sql_db    | 2018-08-24T08:47:58.999685Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
my_sql_db    | 2018-08-24T08:47:58.999689Z 0 [Note] InnoDB: Uses event mutexes
my_sql_db    | 2018-08-24T08:47:58.999692Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
my_sql_db    | 2018-08-24T08:47:58.999695Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
my_sql_db    | 2018-08-24T08:47:58.999698Z 0 [Note] InnoDB: Using Linux native AIO
my_sql_db    | 2018-08-24T08:47:59.000153Z 0 [Note] InnoDB: Number of pools: 1
my_sql_db    | 2018-08-24T08:47:59.000426Z 0 [Note] InnoDB: Using CPU crc32 instructions
my_sql_db    | 2018-08-24T08:47:59.002306Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
my_sql_db    | 2018-08-24T08:47:59.006893Z 0 [Note] InnoDB: Completed initialization of buffer pool
my_sql_db    | 2018-08-24T08:47:59.013219Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
my_sql_db    | 2018-08-24T08:47:59.024242Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
my_sql_db    | 2018-08-24T08:47:59.026263Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
my_sql_db    | 2018-08-24T08:47:59.066469Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
my_sql_db    | 2018-08-24T08:47:59.071752Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
my_sql_db    | 2018-08-24T08:47:59.072052Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
my_sql_db    | 2018-08-24T08:47:59.422155Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
my_sql_db    | 2018-08-24T08:47:59.423325Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
my_sql_db    | 2018-08-24T08:47:59.423376Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
my_sql_db    | 2018-08-24T08:47:59.423900Z 0 [Note] InnoDB: Waiting for purge to start
my_sql_db    | 2018-08-24T08:47:59.474066Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.22-22 started; log sequence number 2595255
my_sql_db    | 2018-08-24T08:47:59.474647Z 0 [Note] Plugin 'FEDERATED' is disabled.
my_sql_db    | 2018-08-24T08:47:59.499970Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
my_sql_db    | 2018-08-24T08:47:59.500004Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
my_sql_db    | 2018-08-24T08:47:59.500382Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
my_sql_db    | 2018-08-24T08:47:59.501263Z 0 [Warning] CA certificate ca.pem is self signed.
my_sql_db    | 2018-08-24T08:47:59.522151Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
my_sql_db    | 2018-08-24T08:47:59.531657Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180824  8:47:59

Looks like flyway starts before database initialization and hence could not connect to database and I see the error below.

How can I fix that problem?

P.S.

I googled similar questions and I found the following piece of advice: https://github.com/vishnubob/wait-for-it but I am novice in docker and I don't understand how to put it into my docker compose file

P.S.2

I tried to put file wait-fot-it.sh near the compose file and execute:

command: ["./wait-for-it.sh", "mysql:3306", "--", "-url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate"]

But I returns ERROR: Invalid argument: ./wait-for-it.sh

P.S.3

I tried approach from "Duplicated" topic:

version: '3'
services:
  my_sql_db:
    image: percona:latest
    container_name: my_sql_db
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: abhs
    ports:
    - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 20s
      retries: 10
  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
      - ./flyway_scripts/src/main/resources/db/migration:/flyway/sql
    command: -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate
    depends_on:
      my_sql_db:
        condition: service_healthy

but I see following error:

$ docker-compose -f docker-compose.yml -f docker-compose-dev.yml up
The Compose file '.\docker-compose-dev.yml' is invalid because:
services.migration.depends_on contains an invalid type, it should be an array

P.S.4

for that approach I see following error:

version: '3'
services:
  my_sql_db:
    image: percona:latest
    container_name: my_sql_db
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: abhs
    ports:
    - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 20s
      retries: 10
  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
      - ./flyway_scripts/src/main/resources/db/migration:/flyway/sql
    command: dockerize wait jdbc:mysql://my_sql_db:3306 -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate
    depends_on:
      - my_sql_db

I see following error:

flyway_migration  | ERROR: Invalid argument: dockerize

UPDATE_1

wait-for-it.sh content:

#!/usr/bin/env bash
#   Use this script to test if a given TCP host/port are available

cmdname=$(basename $0)

echoerr() { if [[ $QUIET -ne 1 ]]; then echo "$@" 1>&2; fi }

usage()
{
    cat << USAGE >&2
Usage:
    $cmdname host:port [-s] [-t timeout] [-- command args]
    -h HOST | --host=HOST       Host or IP under test
    -p PORT | --port=PORT       TCP port under test
                                Alternatively, you specify the host and port as host:port
    -s | --strict               Only execute subcommand if the test succeeds
    -q | --quiet                Don't output any status messages
    -t TIMEOUT | --timeout=TIMEOUT
                                Timeout in seconds, zero for no timeout
    -- COMMAND ARGS             Execute command with args after the test finishes
USAGE
    exit 1
}

wait_for()
{
    if [[ $TIMEOUT -gt 0 ]]; then
        echoerr "$cmdname: waiting $TIMEOUT seconds for $HOST:$PORT"
    else
        echoerr "$cmdname: waiting for $HOST:$PORT without a timeout"
    fi
    start_ts=$(date +%s)
    while :
    do
        if [[ $ISBUSY -eq 1 ]]; then
            nc -z $HOST $PORT
            result=$?
        else
            (echo > /dev/tcp/$HOST/$PORT) >/dev/null 2>&1
            result=$?
        fi
        if [[ $result -eq 0 ]]; then
            end_ts=$(date +%s)
            echoerr "$cmdname: $HOST:$PORT is available after $((end_ts - start_ts)) seconds"
            break
        fi
        sleep 1
    done
    return $result
}

wait_for_wrapper()
{
    # In order to support SIGINT during timeout: http://unix.stackexchange.com/a/57692
    if [[ $QUIET -eq 1 ]]; then
        timeout $BUSYTIMEFLAG $TIMEOUT $0 --quiet --child --host=$HOST --port=$PORT --timeout=$TIMEOUT &
    else
        timeout $BUSYTIMEFLAG $TIMEOUT $0 --child --host=$HOST --port=$PORT --timeout=$TIMEOUT &
    fi
    PID=$!
    trap "kill -INT -$PID" INT
    wait $PID
    RESULT=$?
    if [[ $RESULT -ne 0 ]]; then
        echoerr "$cmdname: timeout occurred after waiting $TIMEOUT seconds for $HOST:$PORT"
    fi
    return $RESULT
}

# process arguments
while [[ $# -gt 0 ]]
do
    case "$1" in
        *:* )
        hostport=(${1//:/ })
        HOST=${hostport[0]}
        PORT=${hostport[1]}
        shift 1
        ;;
        --child)
        CHILD=1
        shift 1
        ;;
        -q | --quiet)
        QUIET=1
        shift 1
        ;;
        -s | --strict)
        STRICT=1
        shift 1
        ;;
        -h)
        HOST="$2"
        if [[ $HOST == "" ]]; then break; fi
        shift 2
        ;;
        --host=*)
        HOST="${1#*=}"
        shift 1
        ;;
        -p)
        PORT="$2"
        if [[ $PORT == "" ]]; then break; fi
        shift 2
        ;;
        --port=*)
        PORT="${1#*=}"
        shift 1
        ;;
        -t)
        TIMEOUT="$2"
        if [[ $TIMEOUT == "" ]]; then break; fi
        shift 2
        ;;
        --timeout=*)
        TIMEOUT="${1#*=}"
        shift 1
        ;;
        --)
        shift
        CLI=("$@")
        break
        ;;
        --help)
        usage
        ;;
        *)
        echoerr "Unknown argument: $1"
        usage
        ;;
    esac
done

if [[ "$HOST" == "" || "$PORT" == "" ]]; then
    echoerr "Error: you need to provide a host and port to test."
    usage
fi

TIMEOUT=${TIMEOUT:-15}
STRICT=${STRICT:-0}
CHILD=${CHILD:-0}
QUIET=${QUIET:-0}

# check to see if timeout is from busybox?
# check to see if timeout is from busybox?
TIMEOUT_PATH=$(realpath $(which timeout))
if [[ $TIMEOUT_PATH =~ "busybox" ]]; then
        ISBUSY=1
        BUSYTIMEFLAG="-t"
else
        ISBUSY=0
        BUSYTIMEFLAG=""
fi

if [[ $CHILD -gt 0 ]]; then
    wait_for
    RESULT=$?
    exit $RESULT
else
    if [[ $TIMEOUT -gt 0 ]]; then
        wait_for_wrapper
        RESULT=$?
    else
        wait_for
        RESULT=$?
    fi
fi

if [[ $CLI != "" ]]; then
    if [[ $RESULT -ne 0 && $STRICT -eq 1 ]]; then
        echoerr "$cmdname: strict mode, refusing to execute subprocess"
        exit $RESULT
    fi
    exec "${CLI[@]}"
else
    exit $RESULT
fi

P.S.5

Also I tried this:

version: '3'
services:
  my_sql_db:
    image: percona:latest
    container_name: my_sql_db
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: abhs
    ports:
    - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 20s
      retries: 10
  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
     - ./flyway_scripts/src/main/resources/db/migration:/flyway/sql
    entrypoint: ["wait-for-it.sh", "mysql:3306", "--", "docker-entrypoint.sh"]      
    command: -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate
    depends_on:
      - my_sql_db

It leads to error:

Creating flyway_migration ... error

ERROR: for flyway_migration  Cannot start service migration: OCI runtime create failed: container_linux.go:348: starting container process caused "exec: \"wait-for-it.sh\": executable file not found in $PATH": unknown

ERROR: for migration  Cannot start service migration: OCI runtime create failed: container_linux.go:348: starting container process caused "exec: \"wait-for-it.sh\": executable file not found in $PATH": unknown
Encountered errors while bringing up the project.

Answer 1

Dockerize / wait-for-it.sh

For the errors:

Invalid argument: ./wait-for-it.sh

and

Invalid argument: dockerize

This is because the entrypoint for the Flyway container is the flyway executable, and the contents of the command you have specified are appended to the entrypoint as arguments. So, in effect, the container is running the following:

flyway dockerize ...

or

flyway wait-for-it.sh ...

Neither of these are valid arguments to the Flyway command line.

The entrypoint needs to be updated, as you have done in P.S.5. However, you have then hit the error:

"wait-for-it.sh": executable file not found in $PATH"

This is because wait-for-it.sh (and dockerize) are not available in the Flyway container.

You can either create a Dockerfile that extends the Flyway container then ADD or COPY the scripts, e.g.:

FROM boxfuse/flyway:latest

RUN mkdir /flyway/bin

ADD wait-for-it.sh /flyway/bin/wait-for-it.sh

RUN chmod 755 /flyway/bin/wait-for-it.sh

Or mount a volume that contains the script / executable:

version: '3'
services:
  ...
  migration:
    image: boxfuse/flyway:latest
    container_name: flyway_migration
    volumes:
     - ../sql:/flyway/sql
     - ../bin:/flyway/bin
    entrypoint: ["/flyway/bin/dockerize", "-wait", "tcp://my_sql_db:3306", "-timeout", "15s", "--", "flyway"]      
    ...

where the local directory ../bin contains dockerize (or wait-for-it.sh).

That should be enough to get dockerize / wait-for-it.sh working. However, both tools only check that a port is available and not that the database itself is actually ready to serve requests.

Compose v2.1

That said, using the docker-compose v2.1 depends_on: condition syntax might be a reasonable approach. As you have mentioned in the comments, that syntax has been removed in v3 and a lot of people are unhappy about it.

However, as one of the Docker developers says in a comment on that issue:

There's no reason to use the v3 format if you don't intend to use swarm services.

Custom healthcheck script

Another approach is to extend the Flyway container to add a custom MySQL healthcheck script, similar to the Postgres one shown in the docker compose documentation:

#!/bin/bash
# wait-for-mysql.sh

set -e

host="$1"
shift
cmd="$@"

until MYSQL_PWD=$MYSQL_ROOT_PASSWORD /usr/bin/mysql --host="$host" --user="root" --execute "SHOW DATABASES;"; do
  >&2 echo "MySQL is unavailable - sleeping"
  sleep 1
done

>&2 echo "MySQL is up - executing command"
exec $cmd

Then create a Dockerfile to extend Flyway, install the MySQL client and add this script:

FROM boxfuse/flyway:latest

RUN apt-get update && \
    apt-get install -y mysql-client && \
    mkdir /flyway/bin

ADD wait-for-mysql.sh /flyway/bin/wait-for-mysql.sh

RUN chmod 755 /flyway/bin/wait-for-mysql.sh

You can then use the custom Flyway image in your compose file:

version: '3'
services:
  my_sql_percona:
    ...
  migration:
    build: ./flyway_mysql_client
    container_name: flyway_migration
    environment:
      MYSQL_ROOT_PASSWORD: password
    volumes:
     - ../sql:/flyway/sql
    entrypoint: ["bash", "/flyway/bin/wait-for-mysql.sh", "my_sql_percona", "--", "flyway"]      
    command: -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate
    depends_on:
      - my_sql_percona

The downside of this approach is that you would need to extend every container with a custom healthcheck script for each of its dependencies.

docker stack

The v2.1 depends_on: condition syntax seems to have been removed in favour of restart policies in v3. However, these are nested under the deploy section, which:

only takes effect when deploying to a swarm with docker stack deploy, and is ignored by docker-compose up and docker-compose run.

So, a further option is to ditch docker-compose and run on docker swarm, as follows:

Add an on-failure restart policy to the Flyway container:

version: '3'
services:
  my_sql_percona:
    ...
  migration:
    image: boxfuse/flyway:latest
    ...
    depends_on:
      - my_sql_percona
    deploy:
      restart_policy:
        condition: on-failure

Create a swarm cluster (single-node in this case):

docker swarm init --advertise-addr <your-ip-address>

Deploy the services:

docker stack deploy --compose-file docker-compose.yml flyway_mysql

The Flyway container will then be restarted by swarm every time it exits with an error, until it eventually exits successfully.

Whilst this does seem to work, I'm not sure it is the best approach in this case. For instance, if the Flyway container exits because of an error in a migration script, swarm will continue to restart the container even though it will never succeed.

Summary

I have created a repository with these five different approaches.

Personally, I think I would use the v2.1 approach as the healthcheck is kept with the database container itself and not duplicated in each container that depends on it. I don't need to use swarm services though, so pick whatever works for you. :-)

Answer 2

With flyway 5.2.0, you can add the parameter connectRetries which specifies the maximum number of times in 1 second intervals that flyway will try to reconnect.

command: -connectRetries=20 -url=jdbc:mysql://my_sql_db:3306/abhs?useUnicode=true&characterEncoding=utf8&useSSL=false -user=root -password=password migrate

Flyway Docs