Docker-compose / init.sql doesn't get executed correctly

Question

I am trying to setup a local MySQL instance through docker-compose, which it looks like this:

version: '3'

services:
  database:
    image: mysql:latest
    ports:
      - '3306:3306'
    command: '--init-file /data/application/init.sql'
    command: 'mysqld --default-authentication-plugin=mysql_native_password'
    volumes:
      - './init.sql:/data/application/init.sql'
      - './data:/var/lib/mysql'
    env_file:
      - ./.env
    expose:
      - '3306'
    # cap_add:
    #   - SYS_NICE
    environment:
      - MYSQL_ROOT_PASSW="${MYSQL_ROOT_PASSWORD}"
      - MYSQL_DATABASE="${MYSQL_DATABASE}"
      - MYSQL_USER="${MYSQL_USER}"
      - MYSQL_ROOT_USER="${MYSQL_ROOT_USER}"
      - MYSQL_PASSWORD="${MYSQL_PASSWORD}"
    # network_mode: host

With the following init.sql script, that should take care of creating the db and setting the table up:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS `listings`(
    id INTEGER
    ,PRIMARY KEY (id)
);

The container gets built and starts normally:

Attaching to so-feed-parse_database_1
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Initializing database files
database_1  | 2019-12-12T14:58:39.976524Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:39.976669Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.18) initializing of server in progress as process 46
database_1  | 2019-12-12T14:58:41.624212Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
database_1  | 2019-12-12 14:58:44+00:00 [Note] [Entrypoint]: Database files initialized
database_1  | 2019-12-12 14:58:44+00:00 [Note] [Entrypoint]: Starting temporary server
database_1  | 2019-12-12T14:58:44.542746Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:44.542845Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 95
database_1  | 2019-12-12T14:58:45.012478Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
database_1  | 2019-12-12T14:58:45.016362Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
database_1  | 2019-12-12T14:58:45.054899Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
database_1  | 2019-12-12 14:58:45+00:00 [Note] [Entrypoint]: Temporary server started.
database_1  | 2019-12-12T14:58:45.128416Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
database_1  | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Creating database "test"
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Creating user "utest"
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Giving user "utest" access to schema "test"
database_1  | 
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Stopping temporary server
database_1  | 2019-12-12T14:58:47.355373Z 14 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.18).
database_1  | 2019-12-12T14:58:49.162768Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
database_1  | 2019-12-12 14:58:49+00:00 [Note] [Entrypoint]: Temporary server stopped
database_1  | 
database_1  | 2019-12-12 14:58:49+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
database_1  | 
database_1  | 2019-12-12T14:58:49.662509Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:49.662675Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1
database_1  | 2019-12-12T14:58:50.034163Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
database_1  | 2019-12-12T14:58:50.036925Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
database_1  | 2019-12-12T14:58:50.062397Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
database_1  | 2019-12-12T14:58:50.222174Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

Now, after logging into the server, show databases; gives the following output (trying use test; outputs unknown database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| "test"             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

Executing manually the init script works like a charm, no idea if it could be something depending on the --init-file command.

Edit: The OP in this question is following a similar approach without using command: '--init-file' but rather mounting the init.sql file on ./init.sql:/docker-entrypoint-initdb.d/init.sql. After reshuffling my docker-compose.yml around the db is now created correctly. I am not quite sure why this is happening though...

Yes, I had to switch the default auth plugin as without that command I was having protocol conflicts. — anddt, Dec 12 '19 at 15:23
....and what would happen if you combine both lines into a single one? I don't think that multiple `command`s are supported — Nico Haase, Dec 12 '19 at 15:24
Doesn't really help, apparently I was misusing `--init-file`. For MySQL init it should always be mounted and executed on the very first run. [Here](https://stackoverflow.com/questions/48808069/how-to-create-database-in-database-docker-container) more on the issue. — anddt, Dec 12 '19 at 15:55

score 2 · Answer 1 · answered Aug 16 '21 at 13:44

2

Refer here https://onexlab-io.medium.com/docker-compose-mysql-initdb-4c3388047dea

Note： mysql5.7 MYSQL_SERVICE_USER If set MYSQL_SERVICE_USER to user, then startup will not be executed

answered Aug 16 '21 at 13:44

取一个好的名字

1,677
14
16

It's been a while since I last worked on this. Think one of the problems I was having might be related to the comment in your note. Thanks for yoru time, upvoting this. – anddt Aug 22 '21 at 21:06

score 1 · Accepted Answer · answered Dec 12 '19 at 15:52

'./init.sql:/data/application/init.sql' is not correct path, it should be mapped with /docker-entrypoint-initdb.d, as the entrypoint looking for files under /docker-entrypoint-initdb.d.

Also better to see this warning from the documentation.

Warning:

scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

postgres Initialization scripts

why do you talk about Postgres when the problem is connected to the mysql? I am having the same issue and unable to apply this solution by placing my init.sql to `/docker-entrypoint-initdb.d` (which does exist if it matters) — feedthemachine, Jun 21 '21 at 19:03

Docker-compose / init.sql doesn't get executed correctly

2 Answers2