Setting global sql_mode in MySQL

Question

I am trying to set sql_mode in MySQL but it throws an error.

Command:

set global sql_mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'

• Is this not the proper way to set multiple modes?
• What are the advantages of setting session and global modes?
• Which is preferred?

I have different users trying to update the database with different UNC values and instead of setting the session mode to NO_BACKSLASH_ESCAPES, I though it would make sense to set a global mode for this. Does this make sense?

Please let me know.

Thanks.

Salk

Those errant quotes really bother me ;p. As others said I'm sure, this is definitely not right just because of that. – Daphinedaphna

Answer 1

I resolved it.

the correct mode is :

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Salk

WARNING: In our experience this does not retain the setting after a MySQL restart... – Vetiver

Yes this does not retain settings after restart – Isobel

For retaining it after restart for unix, I had to create a ~/.my.cnf file and add [mysqld] sql_mode = "<new mode>" . Hope this helps – Glyptograph

Answer 2

BTW, if you set globals in MySQL:

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

This will not set it PERMANENTLY, and it will revert after every restart.

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after MySQL restart:

Config File: /etc/mysql/my.cnf

[mysqld] 
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

---

UPDATE: Newer versions of Mysql (e.g. 5.7.8 or above) may require slightly different syntax:

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Make sure that there is a dash between sql-mode not an underscore, and that modes are in double quotes.

Always reference the MySQL Docs for your version to see the sql-mode options.

Vetiver

Thank you, setting in my.cnf did the trick for me. For those interested, this page tells you what the default sql_modes are in 5.7 and above: dev.mysql.com/doc/refman/5.7/en/sql-mode.html – Cerf

5.7 version note up! That was the problem. – Pet

In an actual web application the second query works. I just added another query after the mysqli connection query. SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; Thanks Chad! – Brilliantine

In 5.7.20, and for the case of mysql.ini, using an underscore and forgetting the quotes works just fine, like so: sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES – Exegesis

Answer 3

Setting sql mode permanently using mysql config file.

In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnf as mysql.conf.d is included in /etc/mysql/my.cnf. i change this under [mysqld]

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

just removed ONLY_FULL_GROUP_BY sql mode cause it was causing issue.

I am using ubuntu 16.04, php 7 and mysql --version give me this mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

After this change run below commands

sudo service mysql stop
sudo service mysql start

Now check sql modes by this query SELECT @@sql_mode and you should get modes that you have just set.

Emera

Answer 4

For someone who googling this error for MySQL 8.

MySQL 8.0.11 remove the 'NO_AUTO_CREATE_USER' from sql-mode.

MySQL 5.7: Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is deprecated. MySQL 8.0.11: Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

Taken from here

So, your sql_mode can be like this:

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Or if you're using Docker you can add next command to docker-compose.yml

  mysql:
    image: mysql:8.0.13
    command: --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    ports:
      - 13306:${MYSQL_PORT}

Diondione

How does this command --sql_mode=STRICT_TRAN... work? – Roeder

Thanks, I was stuck because of this line. – Cathouse

Answer 5

Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini

[mysqld]
port = 3306
sql-mode=""

MySQL restart.

Or you can also do

[mysqld]
port = 3306
SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

MySQL restart.

Postdoctoral

I think it's sql_mode instead of sql-mode. – Totalizator

Answer 6

For Temporary change use following command

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

For permanent change : go to config file /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf and add following lines then restart mysql service

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Glyptodont

Answer 7

Access the database as the administrator user (root maybe).

Check current SQL_mode

mysql> SELECT @@sql_mode;

To set a new sql_mode, exit the database, create a file

nano /etc/mysql/conf.d/<filename>.cnf 

with your sql_mode content

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart Mysql

mysql> sudo service mysql stop
mysql> sudo service mysql start

We create a file in the folder /etc/mysql/conf.d/ because in the main config file /etc/mysql/my.cnf the command is written to include all the settings files from the folder /etc/mysql/conf.d/

Washington

Answer 8

Check the documentation of sql_mode

Method 1:

Check default value of sql_mode:

SELECT @@sql_mode //check current value for sql_mode

SET GLOBAL sql_mode = "NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Method 2:

Access phpmyadmin for editing your sql_mode

• Login on phpmyadmin and open localhost
• Top on Variables present on the top in menu items and search out for sql mode
• Click on edit button to modify sql_mode based on your requirements
• Save the changes

Restart server after executing above things

Adagietto

Answer 9

In my case mysql and ubuntu 18.04

I set it permanently using this command

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Append the line after the configuration. See example highlighted in the image below.

sql_mode = ""

Note :You can also add different modes here, it depends on your need NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

See Available sql modes reference and Documentation

Then save. After saving you need to restart your mysql service, follow the command below:

sudo service mysql restart

Hope this helps :-)

Went

Answer 10

In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnf change this under [mysqld]

Paste this line on [mysqld] portion

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Oldie

Answer 11

I just had a similar problem where MySQL (5.6.45) wouldn't accept sql_mode from any config file.

The solution was to add init_file = /etc/mysql/mysql-init.sql to the config file and then execute SET GLOBAL sql_mode = ''; in there.

Ampoule

Answer 12

Updating this for users Using MAMP PRO {works with MAMP users too}. Because I seem to have got stuck on finding a solution for this, but people recommended I should edit the my.cnf file in the /Applications/MAMP/tmp/mysql/my.cnf folder which does not work because it gets reset after every restart of mysql server.

Referring this document:

The configuration file “my.cnf” of MySQL can be found here: “/Applications/MAMP/tmp/mysql/my.cnf”. Please note: Editing this file does NOT work as it will be overwritten every time MySQL is restarted by MAMP PRO with a “my.cnf” file that is created from the MySQL template. You must edit this template (menu File > Open Template > MySQL (my.cnf) > 5.7.30) to modify the MySQL configuration. Manually adding “my.cnf” files to other locations is not recommended. Every configuration aspect can be handled with the MySQL template.

Once this is done, add the following in the my.cnf file:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart the Mysql Server. That should do the trick.

Mecham

Answer 13

If someone want to set it only for the current session then use the following command

set session sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Freezer

Answer 14

To do this in Azure, go to the MySQL resource in the portal, server parameters, search sql_mode. Then open the drop down and remove the selection for the parameter in question. Finally click save.

Parable

Answer 15

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Katlin