mysql5.7 upgrade issue GROUP BY incompatible with sql_mode=only_full_group_by

Question

I upgraded my server to mysql5.7 and i have this issue on this request:

Error Number: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vuillermoz.pi.id_pierre' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT          `pi`.`id_pierre`, 
                `pi`.`couleur_nom`, 
                `pi`.`couleur_code`, 
                `pi`.`image`, 
                count(p.id_prod) AS countproduits 
FROM            (`produit` p) 
LEFT JOIN       `categorie` c1_4 
ON              `p`.`afficher` = `p`.`afficher` 
AND             p.rid_cat = c1_4.id_cat 
AND             c1_4.afficher = 1 
LEFT OUTER JOIN `categorie` c1_3 
ON              `c1_4`.`rid_cat` = `c1_3`.`id_cat` 
AND             c1_3.afficher = '1' 
LEFT OUTER JOIN `categorie` c1_2 
ON              `c1_3`.`rid_cat` = `c1_2`.`id_cat` 
AND             c1_2.afficher = '1' 
LEFT OUTER JOIN `categorie` c1_1 
ON              `c1_2`.`rid_cat` = `c1_1`.`id_cat` 
AND             c1_1.afficher = '1' 
LEFT OUTER JOIN `type` t 
ON              `p`.`rid_type` = `t`.`id_type` 
INNER JOIN      `pierre` pi 
ON              `p`.`rid_pierre` = `pi`.`id_pierre` 
LEFT OUTER JOIN `matiere` ma 
ON              `p`.`rid_matiere` = `ma`.`id_matiere` 
LEFT OUTER JOIN `couleur` co 
ON              `p`.`rid_couleur` = `co`.`id_couleur` 
WHERE           `rid_pierre` <> 20 
AND             `p`.`afficher` = 1 
AND             ( 
                                p.id_prod IN 
                                ( 
                                       SELECT rid_prod 
                                       FROM   produit__sexe 
                                       WHERE  rid_sexe IN ("1"))) 
AND             ( 
                                p.id_prod IN 
                                ( 
                                       SELECT rid_prod 
                                       FROM   produit__decli 
                                       WHERE  stock > "0") 
                OR              p.disposurcommande = "1") 
GROUP BY        `pi`.`couleur_nom` 
ORDER BY        `couleur_nom` ASC

can i fix the request instead of changing the sql_mode param ? best regards

Answer 1

try this

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Answer 2

Normally, MySQL allows SELECT statements with non-aggregate columns in the output column list or the HAVING clause that are not named in the GROUP BY clause. For example: SELECT a, b, COUNT() FROM t GROUP BY a; The ONLY_FULL_GROUP_BY flag requires non-aggregate output columns (or HAVING columns) to be named in the GROUP BY: SELECT a, b, COUNT() FROM t GROUP BY a, b;