[PrestaShopDatabaseException] Expression #76 of SELECT list is not in GROUP BY clause and contains nonaggregated

[alloure59]

Bonjour,

Suite à la migration de mes données vers un autre serveur ,  j'ai l'erreur suivante :  

[PrestaShopDatabaseException]

Expression #76 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DB.stock.out_of_stock' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Je joins en copie la requête qui ne passe pas.

Malgré l'ajout des autres valeurs dans le GROUP BY : GROUP BY product_shop.id_product,stock.out_of_stock,stock.quantity,product_attribute_shop.minimal_quantity,il.legend

la requête ne passe pas : Empty set (0.00 sec)

Requête complete :

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
					INTERVAL 0
						DAY)) > 0 AS new, product_shop.price AS orderprice
				FROM `ps_category_product` cp
				LEFT JOIN `ps_product` p
					ON p.`id_product` = cp.`id_product`
				 INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
				LEFT JOIN `ps_product_attribute` pa
				ON (p.`id_product` = pa.`id_product`)
				 LEFT JOIN ps_product_attribute_shop product_attribute_shop
		ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1)
				 LEFT 
			JOIN ps_stock_available stock
			ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1  )
				LEFT JOIN `ps_category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = 5 AND cl.id_shop = 1 )
				LEFT JOIN `ps_product_lang` pl
					ON (p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = 5 AND pl.id_shop = 1 )
				LEFT JOIN `ps_image` i
					ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
		ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
				LEFT JOIN `ps_image_lang` il
					ON (image_shop.`id_image` = il.`id_image`
					AND il.`id_lang` = 5)
				LEFT JOIN `ps_manufacturer` m
					ON m.`id_manufacturer` = p.`id_manufacturer`
				WHERE product_shop.`id_shop` = 1
					AND cp.`id_category` = 12 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product,stock.out_of_stock,stock.quantity,product_attribute_shop.minimal_quantity,il.legend ORDER BY cp.`position` asc
			LIMIT 0,6

 

Prestashop Error

Edited December 29, 2019 by alloure59 (see edit history)

[doekia]

Repasser en MySQL5.6 et/ou demander à votre hébergeur d'ajuster les paramètres de base comme en 5.6 (désactiver sql_mode=only_full_group_by)

[Eolia]

Ou mettez à jour votre Presta dans la dernière version 1.6.1.24  

[alloure59]

Bonjour merci pour la réponse,

Je ne peux pas mettre prestashop sur la dernière version.  De plus mon hébergeur fonctionne sur la version 5.7

Cela va créer trop de dégâts.

Je verifie si sql_mode=only_full_group_by. est disabled.

Il y a t'il d'autres paramètres de bases à ajuster ??

Merci bien

[Eolia]

Si vous lisez le post que j'ai mis juste au-dessus vous avez juste à modifier la classe DB comme indiqué et vous n'aurez plus le souci.

[alloure59]

Merci beaucoup. 

Edited December 29, 2019 by alloure59
Translation in the right language (see edit history)