Remove ONLY_FULL_GROUP_BY from SQL MODE

Asked Apr 07 '20 at 06:54

Active Apr 07 '20 at 07:54

Viewed 3,390 times

I am migrating my one live project from shared hosting to VPS. After successfully importing the database and all APIs while testing I was getting below error.

#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table1.user_address.flatNumber' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I had read a lot about it this is because of change in PHP admin versions and trying to fix it with some below solution.

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

Once I am running any of such a solution I am getting another error.

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Now there are a lot of APIs in my live project It's a bit difficult to modify each and every query of API.

Is there any easy and better solution for it. I am new to MySQL

edited Apr 07 '20 at 07:54

Mike Doe

16,349
11
65
88

asked Apr 07 '20 at 06:54

sofquestion 9

Change the mode in MySQL’s config file obviously. – Mike Doe Apr 07 '20 at 06:56
Where I will be getting this MySQL’s config can you please help me – sofquestion 9 Apr 07 '20 at 07:05
Does this answer your question? [Disable ONLY\_FULL\_GROUP\_BY](https://stackoverflow.com/questions/23921117/disable-only-full-group-by) – Mike Doe Apr 07 '20 at 07:55
@emix that not working and I have mentioned above I had to try them but yet getting issue which I have mentioned above too. – sofquestion 9 Apr 07 '20 at 08:09
You didn't read the answer thoroughly. There are many answers, not just the single one on the top. – Mike Doe Apr 07 '20 at 08:40
I had read work around it yet getting this issue '#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation' which I have mention above – sofquestion 9 Apr 07 '20 at 08:41
I already said: you are supposed to change the mode in the config not in the runtime. Sorry I'm not going to discuss this any further. – Mike Doe Apr 07 '20 at 08:42
Maybe you should just fix your queries. – P.Salmon Apr 07 '20 at 08:54

Remove ONLY_FULL_GROUP_BY from SQL MODE

0 Answers0