Fixing sql_mode=only_full_group_by incompatibility error

I’ve landed on the below error while trying to load a Codeigniter web application in my local development environment.

This is not an issue on the production server which runs 10.2.17-MariaDB – 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 modes currently enabled. It gave me the following list:

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

So clearly there is a difference between the two outputs. My local server has ‘ONLY_FULL_GROUP_BY’ mode is enabled while my production has not. So its obvious that this could be the issue. So to fix the error I have to disable ‘ONLY_FULL_GROUP_BY’ in my local. There are several ways to set the mode in mysql server. Below I have shown how I did it:

Logged into mysql server 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 its 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:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar



Do NOT follow this link or you will be banned from the site!