[PrestaShopDatabaseException] - shop crashes

[drzazga78]

Hi,

I'm having a problem in my online shop (https://przystangier.pl)

Whenever I put some words in search box, an error appear. It is not about every word in search box but some specific words I type and then my shop crashes.

Words I noticed so far: "star", "gra", "oko"

and this is what I see:

 

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14,709,737,740,1113,1413,1436,1437,1459,1536,2016,2631,2936) AND id_category IN ' at line 1
 

SELECT id_product FROM `ps_category_product` WHERE id_product IN (25,34,1060,1323,1411,1414,1435,1458,1569,1571,1584,1630,1636,1675,1796,2022,2042,2051,2064,2065,2066,2071,2072,2085,2099,2101,2116,2157,11,19,30,46,48,49,57,71,79,80,84,92,103,124,134,157,159,160,227,228,239,364,389,397,408,421,423,429,437,441,598,610,621,622,626,637,639,647,648,680,684,698,701,712,725,728,729,736,738,777,792,798,802,804,807,815,821,822,827,833,875,895,900,907,920,925,926,932,939,972,992,994,1011,1055,1062,1063,1087,1092,1094,1103,1109,1110,1111,1112,1114,1115,1122,1134,1138,1142,1144,1148,1149,1150,1152,1154,1167,1168,1175,1181,1184,1185,1187,1205,1207,1210,1213,1215,1216,1217,1226,1228,1233,1237,1244,1246,1247,1248,1249,1268,1272,1285,1286,1288,1293,1295,1302,1312,1314,1316,1317,1318,1328,1333,1334,1335,1336,1337,1396,1406,1409,1420,1445,1446,1448,1450,1526,1529,1534,1535,1537,1538,1539,1540,1579,1676,1677,1681,1682,1683,1684,1685,1787,1789,1832,1874,1980,1988,2012,2017,2019,2020,2021,2023,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2043,2044,2045,2046,2048,2049,2050,2052,2053,2054,2055,2056,2057,2058,2060,2061,2062,2063,2067,2068,2069,2070,2073,2074,2075,2076,2077,2078,2079,2081,2082,2083,2084,2086,2087,2088,2089,2090,2091,2092,2094,2096,2100,2102,2103,2104,2105,2106,2107,2108,2109,2111,2112,2113,2114,2115,2117,2118,2119,2120,2121,2122,2123,2124,2125,2126,2127,2128,2129,2130,2131,2132,2133,2134,2135,2136,2137,2138,2139,2140,2141,2142,2143,2144,2145,2146,2147,2148,2150,2151,2152,2153,2154,2155,2156,2158,2159,2160,2161,2162,2163,2164,2165,2166,2167,2168,2169,2170,2171,2172,2173,2174,2175,2176,2177,2178,2179,2180,2182,2183,2184,2185,2186,2187,2188,2189,2190,2193,2191,2192,70,1803,902,205,1574,2080,2098,2093,,14,709,737,740,1113,1413,1436,1437,1459,1536,2016,2631,2936) AND id_category IN  (112,113) GROUP BY id_product

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]

  420.                 $this->result = $this->_query($sql);
  421.             }
  422.         }
  423. 
  424.         if (_PS_DEBUG_SQL_) {
  425.             $this->displayError($sql);
  426.         }
  427. 
  428.         return $this->result;
  429.     }
  430. 
  

• DbCore->query - [line 643 - classes/db/Db.php] - [1 Arguments]

  638.                 throw new PrestaShopDatabaseException('Db->executeS() must be used only with select, show, explain or describe queries');
  639.             }
  640.             return $this->execute($sql, $use_cache);
  641.         }
  642. 
  643.         $this->result = $this->query($sql);
  644. 
  645.         if (!$this->result) {
  646.             $result = false;
  647.         } else {
  648.             if (!$array) {
  

• DbCore->executeS - [line 1050 - modules/zaawansowanawyszukiwarka/zaawansowanawyszukiwarka.php] - [1 Arguments]

  1045. 
  1046.     public function getAllProductInCategory($ids, $id_categories)
  1047.     {
  1048.         if ($ids != "" && $id_categories != "") {
  1049.             $sql = "SELECT id_product FROM `" . _DB_PREFIX_ . "category_product` WHERE id_product IN ($ids) AND id_category IN  ($id_categories) GROUP BY id_product";
  1050.             $res = (Db::getInstance()->executeS($sql));
  1051.             if (!$res)
  1052.                 return 0;
  1053.             else
  1054.                 return sizeof($res);
  1055.         }
  

• zaawansowanawyszukiwarka->getAllProductInCategory - [line 1018 - modules/zaawansowanawyszukiwarka/zaawansowanawyszukiwarka.php] - [2 Arguments]

  1013.             }
  1014.         if (!isset($resultIds[$id_category]))
  1015.             return false;
  1016. 
  1017.         $id_categories = $this->categoriesChild($id_category);
  1018.         $sum = $this->getAllProductInCategory($ids, $id_categories);
  1019. 
  1020.         $name = $resultIds[$id_category]['name'] . " ($sum)";
  1021.         if ($sum == 0)
  1022.             return false;
  1023. 
  

• zaawansowanawyszukiwarka->getTree - [line 1010 - modules/zaawansowanawyszukiwarka/zaawansowanawyszukiwarka.php] - [6 Arguments]

  1005.             $id_category = $this->context->shop->getCategory();
  1006. 
  1007.         $children = array();
  1008.         if (isset($resultParents[$id_category]) && count($resultParents[$id_category]) && ($maxDepth == 0 || $currentDepth < $maxDepth))
  1009.             foreach ($resultParents[$id_category] as $subcat) {
  1010.                 $res = $this->getTree($resultParents, $resultIds, $maxDepth, $subcat['id_category'], $currentDepth + 1, $ids);
  1011.                 if ($res !== false)
  1012.                     $children[] = $res;
  1013.             }
  1014.         if (!isset($resultIds[$id_category]))
  1015.             return false;
  

• zaawansowanawyszukiwarka->getTree - [line 1119 - modules/zaawansowanawyszukiwarka/zaawansowanawyszukiwarka.php] - [6 Arguments]

  1114.             foreach ($result as &$row) {
  1115.                 $resultParents[$row['id_parent']][] = &$row;
  1116.                 $resultIds[$row['id_category']] = &$row;
  1117.             }
  1118. 
  1119.             $blockCategTree2 = $this->getTree($resultParents, $resultIds, 1, $id_category, 0, $ids);
  1120.             if ($id_category > 2 && sizeof($blockCategTree2['children']) == 0 && is_array($blockCategTree2))
  1121.                 $blockCategTree = array('children' => array($blockCategTree2));
  1122.             else
  1123.                 $blockCategTree = $blockCategTree2;
  1124. 
  

• zaawansowanawyszukiwarka->hookLeftColumn - [line 591 - classes/Hook.php] - [1 Arguments]

  586.             }
  587.         }
  588. 
  589.         // Immediately return the result if we do not log performances
  590.         if (!Module::$_log_modules_perfs) {
  591.             return $module->{$method}($params);
  592.         }
  593. 
  594.         // Store time and memory before and after hook call and save the result in the database
  595.         $time_start = microtime(true);
  596.         $memory_start = memory_get_usage(true);
  

• HookCore::coreCallHook - [line 548 - classes/Hook.php] - [3 Arguments]

  Argument [0]
  
  Argument [1]
  hookleftcolumn
  Argument [2]
  
  

• HookCore::exec - [line 581 - classes/controller/FrontController.php] - [1 Arguments]

  Argument [0]
  displayLeftColumn
  

• FrontControllerCore->initContent - [line 101 - classes/controller/ModuleFrontController.php]

  96.             $minimal_purchase = Tools::convertPrice((float)Configuration::get('PS_PURCHASE_MINIMUM'), $currency);
  97.             if ($this->context->cart->getOrderTotal(false, Cart::ONLY_PRODUCTS) < $minimal_purchase) {
  98.                 Tools::redirect('index.php?controller=order&step=1');
  99.             }
  100.         }
  101.         parent::initContent();
  102.     }
  103. }
  

• ModuleFrontControllerCore->initContent - [line 189 - classes/controller/Controller.php]

  184.             if (!$this->content_only && ($this->display_header || (isset($this->className) && $this->className))) {
  185.                 $this->initHeader();
  186.             }
  187. 
  188.             if ($this->viewAccess()) {
  189.                 $this->initContent();
  190.             } else {
  191.                 $this->errors[] = Tools::displayError('Access denied.');
  192.             }
  193. 
  194.             if (!$this->content_only && ($this->display_footer || (isset($this->className) && $this->className))) {
  

• ControllerCore->run - [line 367 - classes/Dispatcher.php]

  362.             if (isset($params_hook_action_dispatcher)) {
  363.                 Hook::exec('actionDispatcher', $params_hook_action_dispatcher);
  364.             }
  365. 
  366.             // Running controller
  367.             $controller->run();
  368.         } catch (PrestaShopException $e) {
  369.             $e->displayMessage();
  370.         }
  371.     }
  372. 
  

• DispatcherCore->dispatch - [line 28 - index.php]

  23. *  @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
  24. *  International Registered Trademark & Property of PrestaShop SA
  25. */
  26. 
  27. require(dirname(__FILE__).'/config/config.inc.php');
  28. Dispatcher::getInstance()->dispatch();
  

[bellini13]

You have a custom module named zaawansowanawyszukiwarka that is producing this error.  What is that exactly?  Perhaps it was included with your theme?  If so, you should contact the author for support. 

[drzazga78]

That is advanced search module (zaawansowanawyszukiwarka).

It is strange that only some words (star,oko, gra) typed in search box make the shop crashes - in most cases it's working fine.

So probably it's not that simple to find the solution in your opinion ?

 

 

[selectshop.at]

Collation of your database is which one ? You should use utf8_general_ci. (ask your provider, if you don't know what this is and how to change).

If not solve, so you have two options, change mysql version in use (not recommendable without shop clone and possible other debug) or to contact developer and let him fix the problem.

[bellini13]

10 hours ago, drzazga78 said:

That is advanced search module (zaawansowanawyszukiwarka).

It is strange that only some words (star,oko, gra) typed in search box make the shop crashes - in most cases it's working fine.

So probably it's not that simple to find the solution in your opinion ?

I would suggest you contact the author of your advanced search module about this issue.  It has nothing to do with the core functionality of Prestashop.