set mysql session and global sql_mode when service start

Question

I am using mysql Server version: 5.5.37 and in current mysql whenever i will restart mysql service that time i saw below results

mysql> select @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

But i want to set mysql mode for both(session as well as global) whenever service is restart.i have also tried to put below line in /etc/my.cnf file but its not working fine

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

so is it possible to set from any core file or else where ? please shine on this topic i need help to set up session and global both sql_mode to 'NO_ENGINE_SUBSTITUTION'

demisx demisx · Accepted Answer · 2016-06-03T15:30:51

This is how I set SQL mode in my /etc/my.conf file:

[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"

Make sure your MySQL daemon has permissions to read this file. Also, check if there are any other my.conf files that may override your value. MySQL searches and reads config files in multiple locations. Read more here http://dev.mysql.com/doc/refman/5.7/en/option-files.html

set mysql session and global sql_mode when service start

1 Answers