CODEWITHSUNDEEP
mysqllaraveldockerubuntuglobal
randosev
randosev

Reputation: 45

Setting sql_mode command when spinning up mysql docker container

I need to set this global config value in mysql to allow zeros in Date fields, to be automatically set as part of the docker images setup and configuration.

SET GLOBAL sql_mode='ALLOW_INVALID_DATES,NO_ZERO_DATE';

No method I have tried works successfully.

My environment:

I tried creating a file "sql_mode.cnf" that contains

SET GLOBAL sql_mode='ALLOW_INVALID_DATES,NO_ZERO_DATE';

and called the file from mysql.prod.dockerfile via docker-compose.prod.yml as follows but it's not working. Possibly some error issues with file access?

Is this even the right method? Is there a better one?

docker-compose.prod.yml (extract):

  mysql:
    build:
      context: .
      dockerfile: mysql.prod.dockerfile
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: database
      MYSQL_USER: datauser
      MYSQL_PASSWORD: pwd
      MYSQL_ROOT_PASSWORD: pwd
    networks:
      - laravel

mysql.prod.dockerfile:

FROM mysql:8.0.32

COPY sql_mode.cnf /etc/mysql/conf.d/

RUN echo "SET GLOBAL sql_mode='ALLOW_INVALID_DATES,NO_ZERO_DATE';" > /docker-entrypoint-initdb.d/init_sql_mode.sql

sql_mode.cnf:

SET GLOBAL sql_mode='ALLOW_INVALID_DATES,NO_ZERO_DATE';

This is the "docker logs mysql" output:

This is the log from running the docker. But the zeroes in date setting did not take. Why?

remote: #3 [internal] load metadata for docker.io/library/mysql:8.0.32
remote: #3 DONE 0.0s
remote:
remote: #4 [1/3] FROM docker.io/library/mysql:8.0.32
remote: #4 CACHED
remote:
remote: #5 [internal] load build context
remote: #5 transferring context: 94B 0.5s done
remote: #5 DONE 0.6s
remote:
remote: #6 [2/3] COPY sql_mode.cnf /etc/mysql/conf.d/
remote: #6 DONE 0.1s
remote:
remote: #7 [3/3] RUN echo "SET GLOBAL sql_mode='ALLOW_INVALID_DATES,NO_ZERO_DATE';" > /docker-entrypoint-initdb.d/init_sql_mode.sql
remote: #7 DONE 0.7s
remote:
remote: #8 exporting to image
remote: #8 exporting layers 0.1s done

Why didn't this work? Is there a better way to do this? I have not found any guides online for how to set this up - are there any?

I would be so grateful for any help - I've worked on this for days :-)

Upvotes: 2

Views: 4680

Answers (2)

tiborrr
tiborrr

Reputation: 1

It is easier to do it as follows, using the default mysql docker image:

services:
  db:
    image: mysql:latest
    container_name: db
    restart: unless-stopped
    ports:
      - "3306:3306"
    command:
      - --sql_mode=ALLOW_INVALID_DATES,NO_ZERO_DATE # Make sure to leave out the double quotes
    environment:
      MYSQL_DATABASE: database
      MYSQL_USER: datauser
      MYSQL_PASSWORD: pwd
      MYSQL_ROOT_PASSWORD: pwd
    networks:
      - laravel

Upvotes: 0

Stepan
Stepan

Reputation: 171

You can do something like this:

mysql:
    build:
      context: .
      dockerfile: mysql.prod.dockerfile
    command: mysqld --sql_mode="ALLOW_INVALID_DATES,NO_ZERO_DATE"
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: database
      MYSQL_USER: datauser
      MYSQL_PASSWORD: pwd
      MYSQL_ROOT_PASSWORD: pwd
    networks:
      - laravel

Upvotes: 5

Related Questions