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