#!/linuxSucks/Disable SQL Mode Only Full Group
Mar 05, 2019MySql supports the concept of SQL modes which are global configuration for every SQL command. Sometimes one needs to remove particular SQL mode like ONLY_FULL_GROUP_BY. Here is a quick guide about how to disable SQL modes.
MySql - Disabling Specific SQL Mode
To find, you current SQL mode, you can run following commands:
# for global sql mode
SELECT @@GLOBAL.sql_mode;
# for session sql mode
SELECT @@SESSION.sql_mode;
Can disable all SQL modes by setting the SQL mode by running:
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE'
If you want to permanently disable the ONLY_FULL_GROUP_BY, edit the file /etc/mysql/my.cnf. In the terminal:
sudo nano /etc/mysql/my.cnf
In side the file it’ll show something like this:
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
sql_mode=sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
At he end of the file add this
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Save, exit, and restart MySQL
sudo service mysql restart
You can restart the server to see if everything goes well with this changes.