How to disable default sql mode(“ONLY_FULL_GROUP_BY”) in mysql 5.7

by

First, Check what is your current sql mode

 mysql -u root -p -e "select @@sql_mode"

and output will be as below.

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Then, open the “/etc/mysql/my.cnf” and add the following line without  “ONLY_FULL_GROUP_BY” into the [mysqld] section.

 

# ... other stuff will probably be here
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart the mysql.

sudo service mysql restart

 

Leave a Comment