docker-compose mariadb docker-entrypoint-initdb.d sql is not executed
I faced an identical issue and I investigated the logs more closely. I wasn't sure if the following error was related but I decided to get rid of it.
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'root'@'%'
It seems that root
user is created by default so I changed the environment
variable of the user to user
for example instead of root
:
MYSQL_USER: user
After that I built and run the docer-compose.yml
file and the SQL
scripts executed succesfuly.
BU0
Updated on July 20, 2022Comments
-
BU0 almost 2 years
I am attempting to have my docker db container automatically populate the database with a dataset when created. According to the mariadb documentation, there is an
docker-entrypoint-initdb.d
folder in the volume that can be used for this purpose.I set up my
docker-compose.yml
file to mirror examples that I have found on StackOverflow, but am still unable to get my SQL scripts to execute. Here are the relevant pieces of mydocker-compose.yml
file:version: '3.6' services: db: image: mariadb:10.5.4-focal container_name: db volumes: - ./cms/conf/mysql/data/:/var/lib/mysql/:rw - ./cms/sql/:/docker-entrypoint-initdb.d/:ro environment: - MYSQL_ROOT_PASSWORD=password - MYSQL_USER=root - MYSQL_PASSWORD=password - MYSQL_DATABASE=wordpress restart: always adminer: image: adminer:4.7.7-standalone container_name: adminer links: - db ports: - 8080:8080 restart: always
Next to the
docker-compose.yml
file, I have acms/sql/init.sql
file with the following contents:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE `test`;
Prior to each test iteration, I clean up any old/cached Docker content:
-
$ docker-compose down -v
I've found this doesn't actually remove the db volumes. Running adocker volume ls
still shows past volumes present. -
$ docker volume prune
This clears out the volumes. Runningdocker volume ls
after running this shows an empty list. -
$ rm -rf ./cms/conf/mysql
This cleans out any actual filesystem changes that the db container made. -
$ docker-compose up -d --build
To rebuild the containers. This should trigger my initialization SQL scripts.
Docker runs, and the
db
container gets successfully created. I am able to access theadminer
container running onlocalhost:8080
. I am able to log in as theroot
user with the password specified in thedocker-compose.yml
file.The Adminer interface shows the standard MySQL databases (
information_schema
,mysql
,performance_schema
) and additionally shows thewordpress
database defined in theMYSQL_DATABASE
value of thedocker-compose.yml
file. But mytest
database from theinit.sql
is nowhere to be found.I found some other similar StackOverflow questions that have had similar problems. I've followed the accepted answers, and am still unable to get any SQL scripts to get executed.
- docker-compose.yml, postgress, how to setup db, tables and prepop in an init.sql file?
- Issue with docker compose initial DB setup once
- mySQL init scripts not running with docker-compose
- Create database on docker-compose startup
Here's the Docker log from my
db
container. I didn't see anything that looked strange; no error messages about initialization, etc:2020-07-15 19:21:34+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Initializing database files PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/bin/mysqladmin' -u root password 'new-password' '/usr/bin/mysqladmin' -u root -h password 'new-password' Alternatively you can run: '/usr/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at https://mariadb.com/kb or the MySQL manual for more instructions. Please report any problems at https://mariadb.org/jira The latest information about MariaDB is available at https://mariadb.org/. You can find additional information about the MySQL part at: https://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Database files initialized 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Starting temporary server 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Waiting for server startup 2020-07-15 19:21:47 0 [Note] mysqld (mysqld 10.5.4-MariaDB-1:10.5.4+maria~focal) starting as process 107 ... 2020-07-15 19:21:47 0 [Note] InnoDB: Using Linux native AIO 2020-07-15 19:21:47 0 [Note] InnoDB: Uses event mutexes 2020-07-15 19:21:47 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-07-15 19:21:47 0 [Note] InnoDB: Number of pools: 1 2020-07-15 19:21:47 0 [Note] InnoDB: Using SSE4.2 crc32 instructions 2020-07-15 19:21:47 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-07-15 19:21:47 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2020-07-15 19:21:47 0 [Note] InnoDB: Completed initialization of buffer pool 2020-07-15 19:21:47 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-07-15 19:21:48 0 [Note] InnoDB: 128 rollback segments are active. 2020-07-15 19:21:48 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-07-15 19:21:48 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-07-15 19:21:49 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-07-15 19:21:49 0 [Note] InnoDB: 10.5.4 started; log sequence number 45041; transaction id 21 2020-07-15 19:21:49 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-07-15 19:21:49 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-07-15 19:21:49 0 [Note] InnoDB: Buffer pool(s) load completed at 200715 19:21:49 2020-07-15 19:21:49 0 [Warning] 'user' entry 'root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Warning] 'user' entry '@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Warning] 'proxies_priv' entry '@% root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Note] Reading of all Master_info entries succeeded 2020-07-15 19:21:49 0 [Note] Added new Master_info '' to hash table 2020-07-15 19:21:49 0 [Note] mysqld: ready for connections. Version: '10.5.4-MariaDB-1:10.5.4+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution 2020-07-15 19:21:49+00:00 [Note] [Entrypoint]: Temporary server started. Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it. 2020-07-15 19:21:58 5 [Warning] 'proxies_priv' entry '@% root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:58+00:00 [Note] [Entrypoint]: Creating database wordpress23 2020-07-15 19:21:58+00:00 [Note] [Entrypoint]: Creating user root ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'root'@'%' 2020-07-15 19:21:34+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:21:35+00:00 [Note] [Entrypoint]: Initializing database files PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/bin/mysqladmin' -u root password 'new-password' '/usr/bin/mysqladmin' -u root -h password 'new-password' Alternatively you can run: '/usr/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at https://mariadb.com/kb or the MySQL manual for more instructions. Please report any problems at https://mariadb.org/jira The latest information about MariaDB is available at https://mariadb.org/. You can find additional information about the MySQL part at: https://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Database files initialized 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Starting temporary server 2020-07-15 19:21:47+00:00 [Note] [Entrypoint]: Waiting for server startup 2020-07-15 19:21:47 0 [Note] mysqld (mysqld 10.5.4-MariaDB-1:10.5.4+maria~focal) starting as process 107 ... 2020-07-15 19:21:47 0 [Note] InnoDB: Using Linux native AIO 2020-07-15 19:21:47 0 [Note] InnoDB: Uses event mutexes 2020-07-15 19:21:47 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-07-15 19:21:47 0 [Note] InnoDB: Number of pools: 1 2020-07-15 19:21:47 0 [Note] InnoDB: Using SSE4.2 crc32 instructions 2020-07-15 19:21:47 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-07-15 19:21:47 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2020-07-15 19:21:47 0 [Note] InnoDB: Completed initialization of buffer pool 2020-07-15 19:21:47 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-07-15 19:21:48 0 [Note] InnoDB: 128 rollback segments are active. 2020-07-15 19:21:48 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-07-15 19:21:48 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-07-15 19:21:49 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-07-15 19:21:49 0 [Note] InnoDB: 10.5.4 started; log sequence number 45041; transaction id 21 2020-07-15 19:21:49 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-07-15 19:21:49 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-07-15 19:21:49 0 [Note] InnoDB: Buffer pool(s) load completed at 200715 19:21:49 2020-07-15 19:21:49 0 [Warning] 'user' entry 'root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Warning] 'user' entry '@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Warning] 'proxies_priv' entry '@% root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:49 0 [Note] Reading of all Master_info entries succeeded 2020-07-15 19:21:49 0 [Note] Added new Master_info '' to hash table 2020-07-15 19:21:49 0 [Note] mysqld: ready for connections. Version: '10.5.4-MariaDB-1:10.5.4+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution 2020-07-15 19:21:49+00:00 [Note] [Entrypoint]: Temporary server started. Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it. 2020-07-15 19:21:58 5 [Warning] 'proxies_priv' entry '@% root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:21:58+00:00 [Note] [Entrypoint]: Creating database wordpress 2020-07-15 19:21:58+00:00 [Note] [Entrypoint]: Creating user root ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'root'@'%' 2020-07-15 19:21:59+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:22:00+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2020-07-15 19:22:00+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.4+maria~focal started. 2020-07-15 19:22:01 0 [Note] mysqld (mysqld 10.5.4-MariaDB-1:10.5.4+maria~focal) starting as process 1 ... 2020-07-15 19:22:01 0 [Note] mysqld: Aria engine: starting recovery recovered pages: 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% (0.3 seconds); tables to flush: 4 3 2 1 0 (0.0 seconds); 2020-07-15 19:22:01 0 [Note] mysqld: Aria engine: recovery done 2020-07-15 19:22:01 0 [Note] InnoDB: Using Linux native AIO 2020-07-15 19:22:01 0 [Note] InnoDB: Uses event mutexes 2020-07-15 19:22:01 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-07-15 19:22:01 0 [Note] InnoDB: Number of pools: 1 2020-07-15 19:22:01 0 [Note] InnoDB: Using SSE4.2 crc32 instructions 2020-07-15 19:22:01 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-07-15 19:22:01 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2020-07-15 19:22:01 0 [Note] InnoDB: Completed initialization of buffer pool 2020-07-15 19:22:01 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-07-15 19:22:01 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=45069 2020-07-15 19:22:01 0 [Note] InnoDB: 128 rollback segments are active. 2020-07-15 19:22:01 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-07-15 19:22:01 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-07-15 19:22:01 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-07-15 19:22:02 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-07-15 19:22:02 0 [Note] InnoDB: 10.5.4 started; log sequence number 45081; transaction id 21 2020-07-15 19:22:02 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-07-15 19:22:02 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-07-15 19:22:02 0 [Note] InnoDB: Buffer pool(s) load completed at 200715 19:22:02 2020-07-15 19:22:02 0 [Note] Server socket created on IP: '::'. 2020-07-15 19:22:02 0 [Warning] 'proxies_priv' entry '@% root@5666e0d8e52e' ignored in --skip-name-resolve mode. 2020-07-15 19:22:02 0 [Note] Reading of all Master_info entries succeeded 2020-07-15 19:22:02 0 [Note] Added new Master_info '' to hash table 2020-07-15 19:22:02 0 [Note] mysqld: ready for connections. Version: '10.5.4-MariaDB-1:10.5.4+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
[Edit] I logged into the CLI for the
db
container, and verified that my init.sql script is in the docker-entrypoint-initdb.d folder:$ cd docker-entrypoint-initdb.d $ ls init.sql $ pwd /docker-entrypoint-initdb.d
Anyone have any ideas on what else to check?
-
-
Giovanne Afonso over 2 yearsSame here... changed from
MARIADB_USER=root
toMARIADB_USER=admin
and the scripts on/docker-entrypoint-initdb.d
were executed successfuly!