Mysql: Init .sql, .sh, .sql.gz are not executed if a volume is mounted?

[lucile-sticky]

Hello there!

Theme:

I've spotted a difference between the behavior of MySQL container creation if there is a volume or not containing a database mounted in it. I don't know if it's wanted, that's why I ask here.

My configuration:

Custom docker-compose.yml:

version: '2'

services:
  web:
    build: ../custom-web/
    ports:
     - "80:80"
    tty: true
    depends_on:
     - custom-mysql
    links:
     - custom-mysql:custom-mysql

  custom-mysql:
    build: ../custom-mysql/
    ports:
     - "3306:3306"
#    volumes:
#     - /var/lib/mysql:/var/lib/mysql
    environment:
     - MYSQL_ROOT_PASSWORD=root_pwd
     - MYSQL_DATABASE=database
     - MYSQL_USER=test
     - MYSQL_PASSWORD=test_pwd

Custom MySQL Dockerfile:

FROM mysql:5.7

# Add SQL file to run it on the container creation
ADD default.sql /docker-entrypoint-initdb.d/default.sql

Default sql file:

SELECT 'I have been run!' AS '';

Behavior:

• If there is no volume mounted (volumes lines commented in the docker-compose.yml file), the SQL file default.sql is run.
• If the volume is mounted (volumes lines uncommented in the docker-compose.yml file) and there is a database in it, the SQL file default.sql is not run.

Question:

Is that wanted? Because in the Docker Hub documentation, it's not clear that files stored in /docker-entrypoint-initdb.d will be run only if there is no database mounted with Docker volumes :confused:

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. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

[tianon]

They're only run when the database is first initialized, which is why they
won't run if an already-initialized database is present.

[lucile-sticky]

Is that possible to precise it in the Docker hub documentation?

[lucile-sticky]

And second question: is there a way to run SQL file on container creation if the database is already initialized? Thank you!

[ltangvald]

We don't really have support for it right now, but I _think_ it'll work to simply specify --init-file=path/to/your/sqlfile when starting the container
The option will be passed on to the mysql server, which will execute the script at startup. But be careful you don't do this when starting a container with no existing database, as the entrypoint script will pass the option to the server multiple times.

[lucile-sticky]

@ltangvald sorry, but:

docker run custom-mysql --init-file=/some/path/default.sql

results in:

unknown flag: --init-file

:confused:

[ltangvald]

Should work with 5.7. What I did (with the base mysql image):

• Create base database:
  docker run -v /path/mysqldata:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=pass -d mysql:5.7
• Then shut it down
• Make a simple test.sql that creates a schema with a table
• Start it up mapping the location of test.sql
  docker run -v /path/mysqldata:/var/lib/mysql -v /path:/sqltest mysql:5.7 --init-file=/sqltest/tmp.sql
  Then I logged into the database and verified the new schema and table were present.

[ltangvald]

Does your custom-mysql image change the entrypoint script in any way?

[lucile-sticky]

Ok, so what I've done:

• Built my custom image

docker build -t custom-mysql .

• Run a detached container from it to initialize an empty database in a random directory:

docker run -v /home/lucile/Tests/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=pass -d custom-mysql

• Shut this container down:

docker stop b7b74e76f690d84c45ffe1e17806a14aa1a07b6ddce05b7caba0aa564550bb48

• Restarted a container from my custom image, with the test SQL file in it this time:

docker run -v /home/lucile/Tests/mysql:/var/lib/mysql -v /home/lucile/Tests/sql:/sql custom-mysql --init-file=/sql/default.sql

• Logged into it and verify that SQL have been run

docker exec -it 910165aa7be8 bash
mysql -u root -p
#<some MySQL commands to check>

And it works fine!
(my custom image only ADD configuration files for now)

So my question now is: how can I adapt it to Docker compose?

[tianon]

To adapt that to Docker Compose, you'll use command:. See https://docs.docker.com/compose/compose-file/ for details on the supported fields in docker-compose.yml.