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 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.

Solution 1:

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.

Solution 2:

I resolved it.

the correct mode is :

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

Solution 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.

Solution 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}