How to fix SQLSTATE[42000]: Syntax error or access violation: 1055

We have recently set up and configured Mautic on one of our Ubuntu 20.04.2 LTS systems. While accessing the email reports section, we noticed that the page was not loading. While troubleshooting the issue and checking the error logs we noticed the following mentioned MySQL error1 .

Problem:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Reason:

As per the MySQL documentation 2 , having the only full group by mode in the SQL mode will reject queries which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on GROUP BY columns.

Solution:

We could make the required changes in the query to solve the SQL mode. In our case, we bypassed the error using the second option.

Run the following MySQL command to log in as MySQL root user and view the existing values

sudo mysql -u root -p 
mysql> SELECT @@global.sql_mode;

Keep a backup of the existing values to make sure that we will be able to revert the changes if required. Now run the below command to make the changes required.

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

Another possible solution is to make the changes directly on the my.cnf file. You can find my.cnf file using the following command. In our case, We have followed the second option of adding the entries to the ‘my.cnf’ file. 

find / -name my.cnf

In our case, the path is ‘/etc/mysql/my.cnf’. Now modify the my.cnf file to add the required sql_mode in the mysqld block. For example,

[mysqld]

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Restart the MySQL service to have the changes reflected.

sudo systemctl restart mysql