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:
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:
Then I’ve 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 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