MySQL: only_full_group_by issue on joins

Issue

#1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column xxxx which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After upgrading ubuntu 14.04 to 18.04 and MySQL 5.5 to 5.7, I faced an issue for select queries where more than 1 tables are joined, either self-joined, left joined. Joins may be simple or complex. I was facing the issue mostly for complex joins,

“#1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column xxxx which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”

Due to this issue, no records were shown on phpmyadmin and Website’s UI screen.

I have used the following approaches to resolve this issue. Any of these approaches can be used and the data will be displayed on phpmyadmin and the website’s UI screen.

Method 1:

You can remove ONLY_FULL_GROUP_BY by executing the following query either in PHPMyAdmin by login or in the command line or terminal by connecting to MySQL:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));           // in PHPMyAdmin

mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
// in terminal

Method 2:

Following command can be executed on command line or terminal:

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';  				// in phpmyadmin

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';				// in terminal

Method 3:

You can remove ONLY_FULL_GROUP_BY from PHPMyAdmin. Following are the steps:

• Login to PHPMyAdmin.
• At Home of PHPMyAdmin, select tab Variables
• Now, either scroll down for SQL mode or enter SQL mode in the filters search textbox.
• Click on the Edit link of SQL mode, remove ONLY_FULL_GROUP_BY, and then click on the Save link.

Following screenshot has been attached to understand the above steps visually:

Method 4:

The above three methods are temporary methods, which means defaults are restored when the MySQL server or system is restarted. The following last method is a persistent solution. On the terminal, type following command:

	sudo gedit /etc/mysql/my.cnf

After entering this command, the terminal will ask for a password. Enter the password and then my.cnf file will be opened. Add the following lines at the end of the file.

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

Finally, close the file and then type the following command in a terminal:

sudo service mysql restart