mysql only_full_group_by off still Showing Error

Question

Database Error Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #27 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'erp.CFPG.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQL Query: CALL get_product_groups(NULL,NULL,NULL,'0','12');

Notice: If you want to customize this error message, create app/View/Errors/pdo_error.ctp

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_product_groups`(   
    IN _product_group_ids TEXT,
    IN _product_ids TEXT,
    IN _customer_id INT,
    IN _start INT,
    IN _limit INT 
)
BEGIN

    SELECT    
        PG.*,
        Image.*,
        AVG(fn_current_product_mrp(PGD.product_id)) AS avg_price,
        MIN(fn_current_product_mrp(PGD.product_id)) AS min_price,
        MAX(fn_current_product_mrp(PGD.product_id)) AS max_price,
        if (CFPG.id IS NULL, 0, 1) AS is_like,
        fn_discount(PG.id, CURDATE()) as discount_per,
        fn_product_rating(PG.id, NULL) AS rating,
        fn_product_rating_user_count(PG.id, NULL) AS rating_user_count,
        (SELECT SUM(fn_current_stock_sevenrock_warehouse(product_id, 1)) from product_group_details WHERE product_group_id = PG.id) as stock
    FROM
        product_groups PG 
        INNER JOIN product_group_details PGD ON PGD.product_group_id = PG.id                
        INNER JOIN images Image ON Image.id = PG.image_id
        LEFT JOIN customer_faviourate_product_groups CFPG ON CFPG.product_group_id = PG.id AND CFPG.customer_id = _customer_id
    WHERE
        PG.is_live = 1
        AND (_product_group_ids IS NULL OR FIND_IN_SET(PG.id, _product_group_ids) )
        AND (_product_ids IS NULL OR FIND_IN_SET(PGD.product_id, _product_ids) )
    GROUP BY
        PG.id
    ORDER BY
        PG.rank ASC, PG.id DESC
    LIMIT
        _start, _limit

    ;
END

However, following is sql_mode

See [Stored procedure raising “incompatible with sql_mode=only_full_group_by” despite sql_mode being blank](https://stackoverflow.com/q/45560680). MySQL uses the sql mode that was active when you created the procedure, not the mode that is active right now. — Solarflare, Jun 13 '19 at 11:12
Actually local mysql version is 5.6 but server mysql version is 5.7 — Hardeep Singh, Jun 13 '19 at 11:45
@Solarflare I wish I could upvote your comment thousands of times. This was super helpful! I had no idea why my stored procedure wasnt taking the new sql_mode settings. Thanks! It works now — ganta, Jul 27 '23 at 16:27
@ganta Thanks for the sentiment, but instead of the comment, you should (also) upvote the linked answer, as that is what actually helped you, the comment just helped you find it/linked to it. (Also note that, coincidently, that answer is by me, but that is not the actual point.) — Solarflare, Jul 28 '23 at 10:16

score 0 · Answer 1 · answered Jun 13 '19 at 11:42

0

Run this command:

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

answered Jun 13 '19 at 11:42

Raj

11
3

mysql only_full_group_by off still Showing Error

1 Answers1