Magento Question posted in
The official documentation can be found here.

Can't disable ONLY_FULL_GROUP_BY – Magento

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!


  2. 0

    To fix this drop and recreate the stored procedures
    it’s working .

    USE your_schema_name;
    DROP procedure IF EXISTS your_sp_name;

    DELIMITER $$
    USE your_schema_name$$
    CREATE PROCEDURE your_sp_name(IN your_input VARCHAR(50))
    BEGIN

    — sp data here

    END$$

    DELIMITER ;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search