PrestaShopDatabaseException PS 1.6.0.8

[neocreations]

Hi everybody

I have an e-commerce website with PS 1.6.0.8.
The web space and the database have been upgraded.
Now you no longer see the Front Office and the "products" page of the Back Office.
This error appears:

--------

[PrestaShopDatabaseException]

Expression #13 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lamercer94089.pd.nb_downloadable' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 

			SELECT SQL_CALC_FOUND_ROWS
			a.`id_product`,b.name as name,`reference`,a.price as price,sa.active as active
			, shop.name as shopname, a.id_shop_default, MAX(image_shop.id_image) id_image, cl.name `name_category`, sa.`price`, 0 AS price_final, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` as sav_quantity, sa.`active`, IF(sav.`quantity`<=0, 1, 0) badge_danger
			FROM `ps_product` a
			LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
			
		LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product`)
		LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0
		 AND sav.id_shop = 1  AND sav.id_shop_group = 0 )  JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
				LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)
				LEFT JOIN `ps_shop` shop ON (shop.id_shop = a.id_shop_default) 
				LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default)
				LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = a.`id_product`) 
			
			WHERE 1 
			GROUP BY sa.id_product 
			
			ORDER BY a.`id_product` ASC LIMIT 0,50

at line 635 in file classes/db/Db.php

 

629. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
630. 		}
631. 		else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
632. 		{
633. 			if ($sql)
634. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
635. 			throw new PrestaShopDatabaseException($this->getMsgError());
636. 		}
637. 	}
638. 
639. 	/**

DbCore->displayError - [line 325 - classes/db/Db.php] - [1 Arguments]
DbCore->query - [line 501 - classes/db/Db.php] - [1 Arguments]
DbCore->executeS - [line 2791 - classes/controller/AdminController.php] - [3 Arguments]
AdminControllerCore->getList - [line 358 - controllers/admin/AdminProductsController.php] - [6 Arguments]
AdminProductsControllerCore->getList - [line 1992 - classes/controller/AdminController.php] - [1 Arguments]
AdminControllerCore->renderList - [line 2431 - controllers/admin/AdminProductsController.php]
AdminProductsControllerCore->renderList - [line 1768 - classes/controller/AdminController.php]
AdminControllerCore->initContent - [line 2362 - controllers/admin/AdminProductsController.php]
AdminProductsControllerCore->initContent - [line 180 - classes/controller/Controller.php]
ControllerCore->run - [line 373 - classes/Dispatcher.php]
DispatcherCore->dispatch - [line 54 - admin/index.php]

------

 

The technical service of the hosting writes me that the problem is the query not compatible with the "strict mode" of MySQL 5.7.

How do I solve?

Thank you

Sorry for my bad english.

 

 

 

 

 

 

[musicmaster]

With Mysql 5.7.5 it has introduced a stricter handling of some issues (MariaDb is still tolerant) and that seems to cause your problems.

You can tell Mysql to switch off this stricter regime with the command:

mysqli($conn, "SET SESSION sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'ONLY_FULL_GROUP_BY',''))");

It is possible - but unlikely that you also need another command with a similar function:

mysqli($conn, "SET SESSION sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'STRICT_TRANS_TABLES',''))"); 

Up to you to find a place for this - as long as it happens before the query it is ok - and to fit it into the Prestashop code.