Docker MySQL: How to load data from csv file to MySQL table, on container startup

Question

I am trying to load data from a .csv file to a MySql database running inside a docker container. I am using the official mysql image from dockerhub. The csv file and the sql files (containing LOAD LOCAL sql statement) are inside the container

here is my sql file 2-dump.sql

SET GLOBAL local_infile=1;

USE `default`;

TRUNCATE TABLE `Tweet`;

LOAD DATA LOCAL
    INFILE      '/app/ProjectTweets.csv' 
    INTO TABLE  `Tweet`
    FIELDS 
        TERMINATED BY ',' 
        OPTIONALLY ENCLOSED   BY '"'
    LINES 
        TERMINATED BY '\r\n'

    (`id`, `number`, `createdAt`, `flag`, `userName`, `text`)
;

In order to write data from a file to a database MySql requires that the flag local_infile is set on both mysql client and server.

we can set this value on the server dynamically by executing the statement (this statement is in 2-dump.sql as well)

SET GLOBAL local_infile=1;

on the client side, we can set it from the cli wile calling the mysql client

$ mysql --local-infile=1 -u root -ppassword 
mysql> 
mysql> source /app/sql/2-dump.sql;

or simply

$ mysql --local-infile=1 -u root -ppassword < /app/sql/2-dump.sql

if i run the above command inside the container's shell, It produces the desired results. That is , it copies the data from the csv file into the target table.

But I want to automate this step, such that, this file is executed when the container starts.

Fortunately for the mysql docker image, any sql files inside the /docker-entrypoint-initdb.d directory are executed when the container starts.

here is my Dockerfile

FROM mysql

ENV MYSQL_ROOT_PASSWORD password

COPY sql/1-init.sql /docker-entrypoint-initdb.d
COPY sql/2-dump.sql /docker-entrypoint-initdb.d
COPY . ./app

EXPOSE 3306

I am copying the 1-init.sql and 2-dump.sql files into the /docker-entrypoint-initdb.d. 1-init.sql is irrelevant and just creates the schema.

but if I run the container I get the following error

2024-05-03 00:49:44 ERROR 2068 (HY000) at line 12: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

This is because when the docker container tries to execute 2-dump.sql, it does not set the --local-infile flag on the client (like I did previously).

How can I make the docker container execute this file with the --local-infile set? How can I change the behavior of the official image?

Here are the complete docker logs

2024-05-03 00:49:28 2024-05-02 19:49:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el8 started.
2024-05-03 00:49:28 2024-05-02 19:49:28+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-05-03 00:49:29 2024-05-02 19:49:29+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el8 started.
2024-05-03 00:49:29 2024-05-02 19:49:29+00:00 [Note] [Entrypoint]: Initializing database files
2024-05-03 00:49:29 2024-05-02T19:49:29.347074Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2024-05-03 00:49:29 2024-05-02T19:49:29.349590Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.4.0) initializing of server in progress as process 80
2024-05-03 00:49:29 2024-05-02T19:49:29.378109Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-03 00:49:29 2024-05-02T19:49:29.934494Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-03 00:49:32 2024-05-02T19:49:32.793193Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-05-03 00:49:38 2024-05-02T19:49:38.294176Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
2024-05-03 00:49:38 2024-05-02 19:49:38+00:00 [Note] [Entrypoint]: Database files initialized
2024-05-03 00:49:38 2024-05-02 19:49:38+00:00 [Note] [Entrypoint]: Starting temporary server
2024-05-03 00:49:38 2024-05-02T19:49:38.337498Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-05-03 00:49:38 2024-05-02T19:49:38.550501Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.0) starting as process 125
2024-05-03 00:49:38 2024-05-02T19:49:38.569597Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-03 00:49:39 2024-05-02T19:49:39.440305Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-03 00:49:41 2024-05-02T19:49:41.412327Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-03 00:49:41 2024-05-02T19:49:41.412373Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-03 00:49:41 2024-05-02T19:49:41.416424Z 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.
2024-05-03 00:49:41 2024-05-02T19:49:41.448366Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
2024-05-03 00:49:41 2024-05-02 19:49:41+00:00 [Note] [Entrypoint]: Temporary server started.
2024-05-03 00:49:41 '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-05-03 00:49:41 2024-05-02T19:49:41.704970Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2024-05-03 00:49:42 Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
2024-05-03 00:49:42 Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
2024-05-03 00:49:42 Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
2024-05-03 00:49:44 Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
2024-05-03 00:49:44 Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
2024-05-03 00:49:44 Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2024-05-03 00:49:44 
2024-05-03 00:49:44 2024-05-02 19:49:44+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1-init.sql
2024-05-03 00:49:44 
2024-05-03 00:49:44 
2024-05-03 00:49:44 2024-05-02 19:49:44+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/2-dump.sql
2024-05-03 00:49:44 ERROR 2068 (HY000) at line 12: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.