SQL 1064 syntax error when updating related products via a mass action

  • Posts: 20
  • Thank you received: 1
  • Hikashop Business
3 years 5 months ago #333317

-- HikaShop version -- : 4.4.1
-- Joomla version -- : 3.9.26
-- PHP version -- : 7.4
-- Browser(s) name and version -- : Firefox 90.0b3
-- Error-message(debug-mod must be tuned on) -- : An error has occurred.

1064 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 '(4564,5765,'related'),(4565,5765,'related'),(4566,5765,'related'),(4567,5765,...' at line 1

File : plugins/hikashop/massaction_product/massaction_product.php
function : onProcessProductMassActionupdateRelateds

Line 1068 to Line 1075

$c = ceil((int)count($deleteIds) / $max);
  for($i = 0; $i < $c; $i++){
    $offset = $max * $i;
    $id = array_slice($deleteIds, $offset, $max);
    $deleteQuery = $deleteQuery . implode(',',$id) .')';
    $db->setQuery($deleteQuery);
    $db->execute();
  }
This leads to a problem on the 2nd and subsequent loops, as $deleteQuery is overwritten on each loop.
The fix is to replace the lines 1072 and 1073 with
$dq = $deleteQuery . implode(',',$id) .')';
    $db->setQuery($dq);


The same problem occurs on lines 1088 and 1089 with the
$insertQuery
variable being overwritten.

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
3 years 5 months ago #333322

Hi,

Thanks for the feedback !
We've added the fix on our end.

The following user(s) said Thank You: speru

Please Log in or Create an account to join the conversation.

  • Posts: 231
  • Thank you received: 28
  • Hikashop Business
3 years 4 months ago #334577

I get the same error from time to time.
It happend like this:
Filter settings: Submit on click= ON , Deletable filter=ON, Dynamic display=ON
I check 1 filter witch show only 1 product, not all the time but after I delete the filter, no product it's show, and if i refresh that page I get this error

1064 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 ') ) ORDER BY a.ordering ASC LIMIT 0, 21' at line 1

That fix apply for this filter bug also?

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
3 years 4 months ago #334579

Hi,

No. The fix here is for the mass action system.
From what you say the issue you have comes from the filter system.
Also, while the part "1064 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" of the error message is the same, it just means that there is an error somewhere in a MySQL query.
So even if that part of the message is the same, it doesn't mean that the error is linked at all besides the fact that both errors com from a MySQL query.

In your case, we'll need that you :
- activate the "error reporting" and "debug mode" settings of the Joomla configuration
- do that modification: www.hikashop.com/support/forum/install-u...ror-1064.html#326783
- reproduce the error. That should give you an error message with the full MySQL query and the location of the error. Please provide all the information.
- provide also a screenshot of the settings of your filter so that we can better understand the situation.

That will allow us to understand what's going on and create a patch to fix the problem.

Please Log in or Create an account to join the conversation.

  • Posts: 231
  • Thank you received: 28
  • Hikashop Business
3 years 4 months ago #334599 

1064 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 ') ) ORDER BY a.ordering ASC LIMIT 0, 21' at line 1 SELECT DISTINCT b.* FROM wbj9o_hikashop_product_category AS a LEFT JOIN wbj9o_hikashop_product AS b ON a.product_id=b.product_id WHERE (b.product_published = 1) AND (b.product_type = 'main') AND (a.category_id IN (28)) AND ((b.product_access = 'all' OR b.product_access LIKE '%,9,%')) AND ( () ) ORDER BY a.ordering ASC LIMIT 0, 21

Call stack
# Function Location
1 () JROOT/libraries/joomla/database/driver/mysqli.php:668
2 JDatabaseDriverMysqli->execute() JROOT/libraries/joomla/database/driver.php:1701
3 JDatabaseDriver->loadObjectList() JROOT/components/com_hikashop/views/product/view.html.php:982
4 ProductViewProduct->listing() JROOT/components/com_hikashop/views/product/view.html.php:28
5 ProductViewProduct->display() JROOT/libraries/src/MVC/Controller/BaseController.php:672
6 Joomla\CMS\MVC\Controller\BaseController->display() JROOT/administrator/components/com_hikashop/helpers/helper.php:2302
7 hikashopController->display() JROOT/components/com_hikashop/controllers/product.php:46
8 productController->listing() JROOT/libraries/src/MVC/Controller/BaseController.php:710
9 Joomla\CMS\MVC\Controller\BaseController->execute() JROOT/administrator/components/com_hikashop/helpers/helper.php:2280
10 hikashopController->execute() JROOT/components/com_hikashop/hikashop.php:92
11 require_once() JROOT/libraries/src/Component/ComponentHelper.php:402
12 Joomla\CMS\Component\ComponentHelper::executeComponent() JROOT/libraries/src/Component/ComponentHelper.php:377
13 Joomla\CMS\Component\ComponentHelper::renderComponent() JROOT/libraries/src/Application/SiteApplication.php:194
14 Joomla\CMS\Application\SiteApplication->dispatch() JROOT/libraries/src/Application/SiteApplication.php:233
15 Joomla\CMS\Application\SiteApplication->doExecute() JROOT/libraries/src/Application/CMSApplication.php:203
16 Joomla\CMS\Application\CMSApplication->execute() JROOT/index.php:49

So I check the filter, uncheck and refresh the page.
At the end of gif 2, I refresh the page to see the error.

Let me know if you need more info.
I use the last version of Joomla and last of Hikashop Bussines

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
3 years 3 months ago #334607

Hi,

Thank you for the detailed answer. That's great !
I've made a patch on our end to fix the issue.
Download again the install package of HikaShop on our website and install it on your website. It will add the patch and it should then hopefully work.

The following user(s) said Thank You: oxido

Please Log in or Create an account to join the conversation.

Time to create page: 0.070 seconds
Powered by Kunena Forum