How to set global and session mode in my.cnf

Question

Following this thread, I have to set global and session as followings when I restart mysql, How can I set this in my.cnf?

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> exit;

My localhost Server version: 5.7.9 - MySQL Community Server (GPL) — shin, Aug 28 '16 at 12:15

score 0 · Answer 1 · answered Aug 24 '16 at 01:43

0

From the MySQL docs:

[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

answered Aug 24 '16 at 01:43

bishop

37,830
11
104
139

I tried it. I restarted mysql but it does not reflect what I put it in. – shin Aug 25 '16 at 02:11

score 0 · Answer 2 · answered Nov 21 '17 at 22:09

0

You need to add ! to the beginning of the line,

!sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

answered Nov 21 '17 at 22:09

hashguide

1
2

How to set global and session mode in my.cnf

2 Answers2