[mysql] setting global sql_mode in mysql

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 preffered? I have different users trying to update the database with different UNC values and insted od setting the session mode to 'NO_BACKSLASH_ESCAPES', I though it would make sense to et a gloabl mode for this. Does this make sense?

Please let me know.

Thanks.

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}

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

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.

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/

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

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

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.

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"

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.

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"

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

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.