Mysql: disable the strict mode on mysql 5.7 docker build file

[alexsunxl]

How can i disable the strict mode in docker build file?
Now,I just exec into container and run this mysql command everytime when container restart

SET GLOBAL sql_mode = "";

So what should I do in this problem? Or can this docker file update ?

thanks you very much

[harryleesan]

Do you really need to change the Dockerfile? What if you just pass the parameter as a flag when you run the container?

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --sql-mode=""

[ltangvald]

You can also add the option to a custom config file you map to the container. Note that if what you want is the default mode from 5.6, that's actually "NO_ENGINE_SUBSTITUTION".
It's generally best to only disable the modes that are actually causing problems for you, since the added default modes in 5.7 are there to help prevent errors. The full default can be seen at http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

[dm-coding]

Good to know affected users can use the flag --sql-mode="". What is the fix for users of docker-compose?

It's generally best to only disable the modes that are actually causing problems for you, since the added default modes in 5.7 are there to help prevent errors.

Noted, but most of the people who need to turn off strict mode are not application developers but system administrators who need database compliance with their application.

[ltangvald]

You can set sql_mode in a custom config file mapped to the image, same as you would when using docker run, or by overriding the command option in the compose file to include the extra argument.
https://docs.docker.com/compose/compose-file/#/command
If I understand it correctly, setting command: mysqld --sql_mode="" or similar should do it
Might be simpler ways, but I'm not too familiar with compose.

[ltangvald]

Tested with a simple docker-compose.yml file, and
command: mysqld --sql_mode=""
worked as expected.

[cassianotartari]

Just to complement the above answer (I couldn't add @ for Itangvald). Here is a example:

services:
    db_host:
        container_name: db_host
        image: mysql
        command: mysqld --sql_mode=""
        volumes:
            - "./.data/db_host:/var/lib/mysql"
        ports:
            - 3306:3306

[yosifkit]

You can also omit the msqld and the entrypoint script will do the right thing:

command: --sql_mode=""

Looks like this issue was solved, Closing old issue.

[cl0ckwork]

sorry to resurrect this.. but the flag doesn't seem to make a difference when used in docker-compose.yml -> command:

in compose:
command: --sql_mode=""

docker-compose COMMAND column:
"docker-entrypoint.sh --sql_mode="

result mysql command (show variables like 'sql_mode):
sql_mode STRICT_TRANS_TABLES

result of creating a timestamp field and using CURRENT_TIMESTAMP as default:

code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sqlState: '42000',
  sqlMessage: 'Invalid default value for \'created_at\''

[gpetz]

in compose:
command: --sql_mode=""

try command: --sql_mode='' or command: --sql_mode=\"\" see http://yaml-multiline.info/

[cl0ckwork]

@gpetz cool, I think I found a workaround, will have to dig it up. But this seems worth a try:

in compose:
command: --sql_mode=""

try command: --sql_mode='' or command: --sql_mode=\"\" see http://yaml-multiline.info/

thanks!

[bguruprasad]

Just to complement the above answer (I couldn't add @ for Itangvald). Here is a example:

services:
    db_host:
        container_name: db_host
        image: mysql
        command: mysqld --sql_mode=""
        volumes:
            - "./.data/db_host:/var/lib/mysql"
        ports:
            - 3306:3306

Will this work with k8s deployment the same way?

spec:
  containers:
  - name: mariadb or mysql
    image: mariadb or mysql
    command: ["mysqld"]
    args: ["--sql_mode=\"\"", "--innodb_strict_mode=OFF"]

[bguruprasad]

Just to complement the above answer (I couldn't add @ for Itangvald). Here is a example:

services:
    db_host:
        container_name: db_host
        image: mysql
        command: mysqld --sql_mode=""
        volumes:
            - "./.data/db_host:/var/lib/mysql"
        ports:
            - 3306:3306

Will this work with k8s deployment the same way?

spec:
  containers:
  - name: mariadb or mysql
    image: mariadb or mysql
    command: ["mysqld"]
    args: ["--sql_mode=\"\"", "--innodb_strict_mode=OFF"]

Works for me as follows:

spec:
  containers:
  - name: mariadb or mysql
    image: mariadb or mysql
    args: ["--sql_mode=\"\"", "--innodb_strict_mode=OFF"]

Does not require mysqld as command.

[yorjaggy]

In my case the only way that works was without quotes:
--sql_mode=

[mbaric]

Solution without changing docker-compose.yml

I've tried and succeeded to turn off _ONLY_FULL_GROUP_BY_ with those two commands, of course you can concatenate it to one but I like being diligent and see what's happening through the steps:

Few notes:

• variable herePutTheFullNameOfYourServiceContainer in the example below will name it _comany_db1_
• Make sure you are connecting to your docker DB container with root user

db_id=$(docker ps -f name=_comany_db1_ --format "{{.ID}}")
docker exec $db_id mysql -hdb -uroot -ppass1 mydatabase -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"

Let me know if you need more help with it I'll try to help, as usual :). Thanks.

[VincentClair]

In my case the only way that works was without quotes:
--sql_mode=

Thanks for this one! It resolves my issue with gitlab-ci:

[ERROR] mysqld: Error while setting value '""' to 'sql_mode'

Here's my config :

services:
    - name: mysql:5.7
      command: ['mysqld', '--sql_mode=']