I am having an issue with ONLY_FULL_GROUP_BY on MySQL 5.7.14 where I can't seem to disable it even though I've set the SQL mode.
I have set both @@sql_mode to remove ONLY_FULL_GROUP_BY by running this command:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
and I've also updated the my.cnf file to exclude it by setting the sql-mode param.
If I run select @@sql_mode I can see that ONLY_FULL_GROUP_BY isn't set anymore.
However when I run my stored procedure I am still getting this error:
1055 Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'product_id' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What am I missing here? Does the SP ignore the sql-mode?
I don't want to modify the SP code as it's been installed from a 3rd party module (for Magento) and that would cause issues if we wanted to upgrade.
This is on WAMP for my local dev server.
For anyone that comes into the same problem, it turns out that the sql mode is stored along with the stored procedure, so even though I have changed the sql mode correctly it doesn't update the SP at all. To fix this I just deleted and recreated the stored procedures while the new sql mode is set. It now works fine!