How to fix only full group by incompatibility error in MySQL SELECT queries

While working on a project recently, I’ve landed on the below error when I tried to run a Codeigniter web application in my local development environment:

Screenshot showing the error occurred while executing a select query

This was not an issue on the production server which runs the 10.2.17 version of MariaDB Server. However, my local development environment runs MySQL Server 5.7.23 on Ubuntu 18.04.

To investigate, I logged into my local MySQL server and executed SELECT @@sql_mode; to get a comma-separated list of all the currently enabled modes. It gave me the following list:

List of currently enabled SQL modes on my local MySQL server

Then I’ve executed SELECT @@sql mode; on the production server and it gave me below output:

List of currently enabled SQL modes on my production server

So clearly there is a difference between the two outputs. My local server has ONLY_FULL_GROUP_BY mode enabled while my production server has not. So it became obvious that this should be the issue. So to fix the error I have to disable ONLY_FULL_GROUP_BY in my local server. There are several ways to set the mode in MySQL server.

I’ve logged into my local MySQL server via the CLI and executed the below statement:

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

Before executing the above statement, I was thinking I might need to add a comma after _BY before the ending quote. But it wasn’t necessary. I executed the above statement as it is and got the application working. It appears to me that MySQL automatically removes unwanted commas.

After executing the above command I ran SELECT @@sql_mode again to see if my statement has made any changes to the mode string. But to my surprise, it was still the same as earlier. I was puzzled but went ahead and loaded the application again. Bingo, it has worked. So it’s all good now.

Just as a caution, never do the following unless if you want to clear all the modes currently enabled.

mysql > SET sql_mode = ''
References / Credits
Was this helpful?
+1
0
+1
0
+1
0
+1
0
+1
0
+1
0
+1
0