My SQL query is not working showing this error:
this is incompatible with sql_mode=only_full_group_by
I am running this query in other mysql pannel there have working fine. In new server is not working....
If i run this then error is not working and also group by is not working
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
New server version
Client API library version : mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $ PHP Version 7.0.15-0ubuntu0.16.04.4
Can't be sure without seeing your query, but I assume you're using different sets of columns in your select and in your group by clauses.
select a, b, sum(c)
from table
group by a
If that's the case, you can write your query like on of these, depending on what you actually want
select a, b, sum(c)
from table
group by a, b
or
select a, sum(c)
from table
group by a
Note that this is how group by has to be used in almost any database. MySQL allowing for different sets is the anomaly.
See Disable ONLY_FULL_GROUP_BY for more answers. The answers here didn't work for me, but editing my.cnf, as suggested there worked. I tested mysql-server 5.7.19-0ubuntu0.16.04.1 from Ubuntu 16.04.
But of course the proper solution is to fix your query... reverting the old bad behavior is just a workaround.
You only have to replace global statement by session:
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));