Strange SQL DB exception with status quantity update

[Smiles]

Due to an unexpected change error in shipping I decided to set back an snapshot from the webhoster. This did not work well. So I had to put a backup back from my own resources. So far so good. Happend yesterday. Today I wanted to change some statuses to shipped (set order as shipped is ticked). This is not possible to an error (see below). I think that restoring the backup by the hosters system caused some DB setting change on higher level.... 

 

Someone an idea?

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND TO_DAYS("2017-08-19 00:00:00") - TO_DAYS(sm.`date_add`) <= 7 AND s.`id_p' at line 14


			SELECT SUM(view.quantity) as quantity_out
			FROM
			(	SELECT sm.`physical_quantity` as quantity
				FROM `ps_stock_mvt` sm
				LEFT JOIN `ps_stock` s ON (sm.`id_stock` = s.`id_stock`)
				LEFT JOIN `ps_product` p ON (p.`id_product` = s.`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)
				WHERE sm.`sign` = -1
				AND sm.`id_stock_mvt_reason` != 
				AND TO_DAYS("2017-08-19 00:00:00") - TO_DAYS(sm.`date_add`) <= 7
				AND s.`id_product` = 769
				AND s.`id_product_attribute` = 0 AND s.`id_warehouse` = 1
				GROUP BY sm.`id_stock_mvt`
			) as view LIMIT 1

at line 791 in file classes/db/Db.php
786.         if ($webservice_call && $errno) {
787.             $dbg = debug_backtrace();
788.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
789.         } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {
790.             if ($sql) {
791.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
792.             }
793. 
794.             throw new PrestaShopDatabaseException($this->getMsgError());
795.         }
796.     }
DbCore->displayError - [line 425 - classes/db/Db.php] - [1 Arguments]
DbCore->query - [line 690 - classes/db/Db.php] - [1 Arguments]
DbCore->getRow - [line 723 - classes/db/Db.php] - [2 Arguments]
DbCore->getValue - [line 859 - classes/stock/StockManager.php] - [1 Arguments]
StockManagerCore->getProductCoverage - [line 631 - modules/mailalerts/mailalerts.php] - [4 Arguments]
MailAlerts->hookActionProductCoverage - [line 587 - classes/Hook.php] - [1 Arguments]
HookCore::coreCallHook - [line 542 - classes/Hook.php] - [3 Arguments]
HookCore::exec - [line 167 - override/classes/Hook.php] - [7 Arguments]
Hook::exec_16 - [line 35 - override/classes/Hook.php] - [7 Arguments]
Hook::exec - [line 1017 - classes/stock/StockManager.php] - [2 Arguments]
StockManagerCore->hookCoverageOnProductRemoval - [line 539 - classes/stock/StockManager.php] - [4 Arguments]
StockManagerCore->removeProduct - [line 241 - classes/order/OrderHistory.php] - [9 Arguments]
OrderHistoryCore->changeIdOrderState - [line 531 - controllers/admin/AdminOrdersController.php] - [3 Arguments]
AdminOrdersControllerCore->postProcess - [line 178 - classes/controller/Controller.php]
ControllerCore->run - [line 391 - override/classes/Dispatcher.php]
Dispatcher->dispatch_16 - [line 104 - override/classes/Dispatcher.php]
Dispatcher->dispatch - [line 58 - admin/index.php]

Edited August 19, 2017 by Smiles (see edit history)

[razaro]

If I see correct maybe this line 

AND sm.`id_stock_mvt_reason` != 

missing value ?

[Smiles]

Would you be willing to explain a bit more? Im quite a noob in reading code... and do you have any idea how this could have changed?

 

The problem exists with new and old orders...

Edited August 19, 2017 by Smiles (see edit history)

[razaro]

OK I think in that query one variable is missing so you got that error.

 

Following code that is from Advanced stock manager that is enabled on you shop I guess.

Check Stock tab in back office and bit down on page you should see "Default label for increasing stock" 

dropdown with some values. Those are default if you do not have it defined elsewhere.

 

You did not mention your version, and also is backup same version. But in that process some values could be

messed up in database or it is just bug in PrestaShop. And I am not sure how that could be fixed.

[Smiles]

Super weird, in the live store without doing anything is started working again. The test clone I made for fixing it though still has this error..... Makes me very curious.