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

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