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.
You can set the SQL_MODE either from the command line (the --sql-mode option) or by setting the sql_mode system variable. SET sql_mode = 'modes'; SET GLOBAL sql_mode = 'modes'; The session value only affects the current client, and can be changed by the client when required.
If you are using Windows Operating System, check your directory my. cnf or my. ini file. sql_mode="TRADITIONAL".
To determine the current value of the session or global SQL mode, use these statements: SELECT @@SESSION. sql_mode; SELECT @@GLOBAL.
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; happens when different version of mysql is being used. When you are transfering from one server to another you should keep in mind the versions of database use in a new environment especially the mysql.
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.
I resolved it.
the correct mode is :
set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With