☰

Setting global sql_mode in MySQL Ask Question

tag-icon • tag-icon mysql sql-mode
Setting global sql_mode in MySQL Ask 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.

ベストアンサー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.

おすすめ記事