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 = ''