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.

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