setting global sql_mode in mysql

Setting sql mode permanently using mysql config file.

In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnf as mysql.conf.d is included in /etc/mysql/my.cnf. i change this under [mysqld]

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

just removed ONLY_FULL_GROUP_BY sql mode cause it was causing issue.

I am using ubuntu 16.04, php 7 and mysql --version give me this mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

After this change run below commands

sudo service mysql stop
sudo service mysql start

Now check sql modes by this query SELECT @@sql_mode and you should get modes that you have just set.

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.