Fixing “this is incompatible with sql_mode=only_full_group_by” in MySQL

Source: http://www.etmedialabs.com/assets/images/grouping.jpg

When running a query having “GROUP BY” against a database in MySQL you might have encountered the above error. Here are the steps to be followed in fixing the issue.

Open “my.cnf” in your favorite editor (replace “vim” with your favorite editor name ex: “nano”, “gedit” and etc) this config file is generally located at “/etc/mysql/” directory in a standard installation.

sudo vim /etc/mysql/my.cnf

Then add the following lines to the end of its content (you need to press “insert” key before typing in “vim”)

[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

then save the file. (press “Esc” and then type “:wq” in “vim” to save and exit)

Once done restart the MySQL server by running

sudo service mysql restart

For Windows users, “my.cnf” will be as “my.ini” inside “C:/xampp/mysql/bin/” directory. Upon adding the above lines, restart the “MySQL” server.