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.

Those errant quotes really bother me ;p. As others said I'm sure, this is definitely not right just because of that. — dyasta, May 07 '17 at 00:51

Chadwick Meyer · Answer 1 · 2018-07-30T10:09:02.953

265

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.

edited Jul 30 '18 at 10:09

answered Sep 29 '14 at 16:00

Chadwick Meyer

7,041
7
44
65

2

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: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html – Richard Aug 24 '16 at 14:38
2

5.7 version note up! That was the problem. – Ernestas Stankevičius Jun 02 '17 at 12:43
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! – GTodorov Jan 22 '18 at 23:33
1

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` – Ifedi Okonkwo Jan 25 '18 at 11:08

score 37 · Accepted Answer · answered Feb 23 '10 at 12:33

37

I resolved it.

the correct mode is :

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

answered Feb 23 '10 at 12:33

JPro

6,292
13
57
83

26

WARNING: In our experience this does not retain the setting after a MySQL restart... – Chadwick Meyer Sep 29 '14 at 15:25
1

Yes this does not retain settings after restart – prakashpoudel May 12 '16 at 21:19
4

For retaining it after restart for unix, I had to create a ~/.my.cnf file and add [mysqld] sql_mode = "" . Hope this helps – Raman Singh May 26 '16 at 09:06

score 36 · Answer 3 · answered Aug 27 '16 at 23:44

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.

Serhii Popov · Answer 4 · 2020-03-19T15:47:43.590

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}

How does this command --sql_mode=STRICT_TRAN... work? – java dev Sep 14 '21 at 07:30 — java dev, Sep 14 '21 at 07:30
Thanks, I was stuck because of this line. – Mihail Minkov May 01 '23 at 22:02 — Mihail Minkov, May 01 '23 at 22:02

Devraj Gupta · Answer 5 · 2017-01-19T05:43:53.520

17

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.

edited Jan 19 '17 at 05:43

answered Jul 25 '16 at 10:27

Devraj Gupta

329
3
13

2

I think it's `sql_mode` instead of `sql-mode`. – Raman Sahasi Oct 19 '16 at 05:51

score 6 · Answer 6 · answered Apr 22 '20 at 09:51

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/

score 5 · Answer 7 · answered Apr 08 '20 at 09:10

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"

score 4 · Answer 8 · answered Aug 11 '20 at 05:24

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

Cristiana Chavez · Answer 9 · 2020-08-12T07:20:57.330

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 :-)

score 1 · Answer 10 · edited Oct 09 '17 at 21:35

1

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"

edited Oct 09 '17 at 21:35

alexander.polomodov

5,396
14
39
46

answered Oct 09 '17 at 21:11

shashikant parmar

47
2

score 1 · Answer 11 · answered May 21 '20 at 18:52

1

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.

answered May 21 '20 at 18:52

Cobra_Fast

15,671
8
57
102

score 1 · Answer 12 · answered Sep 14 '21 at 06:59

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.

score 0 · Answer 13 · answered Jul 07 '19 at 08:50

0

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

answered Jul 07 '19 at 08:50

ASHOK MANGHAT

35
2

score -3 · Answer 14 · answered Mar 28 '18 at 04:31

-3

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

answered Mar 28 '18 at 04:31

Shadab Khan Zed

14
2

Setting global sql_mode in MySQL

14 Answers14

Linked