CODEWITHSUNDEEP
mysqlspring-bootdockerdocker-compose
greenPadawan
greenPadawan

Reputation: 1571

Docker compose MySql initialisation scripts not executing

I am having a problem having the mysql container to run my initialisation scripts.

I have two files create.sql and insert.sql, which I use to initialise the database.

I create the images using the command docker-compose.yml and it runs successfully and creates the images.

I am facing two problems.

  1. When I run the docker-compose up command, the mysql container is created and started successfully. However the two initialisation scripts (create.sql and insert.sql) don't run on the database.

  2. I explicitly use the docker run command to run the created mysql container. In this scenario the initialisation scripts run successfully.

I am using Docker version 18.09.0 and docker-compose version 1.23.1 and ubuntu 16.04 LTS

I am new to docker and can't seem to figure out the problem.

The following are the files I am using to create images.

docker-compose.yml file.

version: '3'

services:
  demo-mysql:
    image: demo-mysql
    build: ./demo-mysql
    volumes:
      - /mnt/data/mysql-data:/var/lib/mysql
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=demo
      - MYSQL_PASSWORD=root

  demo-api:
    image: demo-api-1.0
    build: ./api
    depends_on:
      - demo-mysql
    ports:
      - 8080:8080
    environment:
      - DATABASE_HOST=demo-mysql
      - DATABASE_USER=root
      - DATABASE_PASSWORD=root
      - DATABASE_NAME=demo
      - DATABASE_PORT=3306

  demo1-app:
    image: demo1-app-1.0
    build: ./demo1
    depends_on:
      - demo-mysql
    ports:
      - 8090:8090
    environment:
      - DATABASE_HOST=demo-mysql
      - DATABASE_USER=root
      - DATABASE_PASSWORD=root
      - DATABASE_NAME=demo
      - DATABASE_PORT=3306

The following is the Dockerfile for the spring boot project

FROM java:8

VOLUME /tmp

ARG DATA_PATH=/src/main/resources
ARG APP_PORT=8080

EXPOSE ${APP_PORT}

ADD /build/libs/demo-api.jar demo-api.jar

ENTRYPOINT ["java","-jar","demo-api.jar"]

The following is the Dockerfile I used to create my mysql image

FROM mysql:5.7

ENV MYSQL_DATABASE=demo \
    MYSQL_USER=root \
    MYSQL_ROOT_PASSWORD=root

ADD ./1.0/create.sql /docker-entrypoint-initdb.d
ADD ./1.0/insert.sql /docker-entrypoint-initdb.d

EXPOSE 3306

Upvotes: 2

Views: 624

Answers (1)

Simonluca Landi
Simonluca Landi

Reputation: 921

From documentation (https://hub.docker.com/_/mysql/)

Initializing a fresh instance

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d.

I suspect that, because of the persisted volume

volumes:
      - /mnt/data/mysql-data:/var/lib/mysql

when docker starts the mysql image, there is already a DB. So the image isn't "fresh" and the scripts are not run.

Update: we can confirm this suspect looking at the source code of the docker-entrypoint.sh here: https://github.com/docker-library/mysql/blob/696fc899126ae00771b5d87bdadae836e704ae7d/5.7/docker-entrypoint.sh

if [ ! -d "$DATADIR/mysql" ]; then
...
...
        ls /docker-entrypoint-initdb.d/ > /dev/null
        for f in /docker-entrypoint-initdb.d/*; do
            process_init_file "$f" "${mysql[@]}"
        done

The scripts run only if the "$DATADIR/mysql" is not present already.

btw, I personally consider a better design to have the "application" create the database schema, preload the required application data, manage schema migrations etc... at startup, but this another topic :)

Upvotes: 1

Related Questions