Error: 1064 You have an error in your SQL syntax; check the ....

-- HikaShop version -- : 6.3
-- Joomla version -- : 3.9
-- PHP version -- : 7.3
-- Browser(s) name and version -- : Firefox 140.7.0esr (64-Bit)
-- Error-message(debug-mod must be tuned on) -- : 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 '*, SUM(prod.order_product_quantity) AS total FROM #__hikashop_order AS a L...' at line 1

Sorry for the Joomla version . I am trying to update a intranet website with many plugins. During the update of Hikashop I found this error when I choose in joomla - Administrator -> Komponents->Hikashop:

administrator/index.php?option=com_hikashop

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 '*, SUM(prod.order_product_quantity) AS total FROM #__hikashop_order AS a L...' at line 1

This is not really an upgrade problem from version 3.5.1 to 5.0.4 to 6.3 which I did.
The error was already there before. The shop seems to work....

Hi,

Can you please activate the "debug system" setting of the Joomla configuration page and try again ?
It should display more information on the page with the error.
Please provide the extra information and we should be able to tell you what to do.

Hi,

thanks for your help, here is the debug Output:
maybe the error is a "legacy issue" from a previous update...

JDatabaseExceptionExecuting [500]
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 '*, SUM(prod.order_product_quantity) AS total FROM tlazo_hikashop_order AS a L...' at line 1
.../libraries/joomla/database/driver/mysqli.php:665

660 // The server was not disconnected.
661 else
662 {
663 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database-error');
664
665 throw new JDatabaseExceptionExecuting($query, $this->errorMsg, $this->errorNum);
666 }
667 }
668
669 return $this->cursor;
670 }

Hi,

Do you have other information about the error on that page with the debug activated ?
I would need the "call stack".

Ok, in administrator/index.php
i had to replace $app->execute(); with:

// Execute the application.
try {
$app->execute();
} catch (Throwable $e) {
echo '<pre>';
echo $e;
echo '</pre>';
die;
}

Now I can see a stack trace:

JDatabaseExceptionExecuting: 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 '*, SUM(prod.order_product_quantity) AS total FROM tlazo_hikashop_order AS a L...' at line 1 in /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/joomla/database/driver/mysqli.php:665
Stack trace:
#0 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/joomla/database/driver.php(1701): JDatabaseDriverMysqli->execute()
#1 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/classes/widget.php(1396): JDatabaseDriver->loadObjectList()
#2 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/views/dashboard/view.html.php(90): hikashopWidgetClass->data(Object(stdClass))
#3 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/views/dashboard/view.html.php(26): dashboardViewDashboard->widgets()
#4 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/views/dashboard/view.html.php(17): dashboardViewDashboard->listing(NULL)
#5 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/MVC/Controller/BaseController.php(672): dashboardViewDashboard->display()
#6 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/helpers/helper.php(2649): Joomla\CMS\MVC\Controller\BaseController->display(false, false)
#7 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/helpers/helper.php(2358): hikashopController->display()
#8 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/MVC/Controller/BaseController.php(710): hikashopController->listing()
#9 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/helpers/helper.php(2627): Joomla\CMS\MVC\Controller\BaseController->execute('listing')
#10 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/hikashop.php(75): hikashopController->execute('listing')
#11 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/Component/ComponentHelper.php(402): require_once('/homepages/12/d...')
#12 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/Component/ComponentHelper.php(377): Joomla\CMS\Component\ComponentHelper::executeComponent('/homepages/12/d...')
#13 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/Application/AdministratorApplication.php(101): Joomla\CMS\Component\ComponentHelper::renderComponent('com_hikashop')
#14 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/Application/AdministratorApplication.php(159): Joomla\CMS\Application\AdministratorApplication->dispatch()
#15 /homepages/12/d82533704/htdocs/backup/20260209_intranet/libraries/src/Application/CMSApplication.php(196): Joomla\CMS\Application\AdministratorApplication->doExecute()
#16 /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/index.php(52): Joomla\CMS\Application\CMSApplication->execute()
#17 {main}

Hi,

Thanks, that's exactly what we needed.

We have analyzed the issue. It seems to come from a SQL syntax conflict with your version of MariaDB regarding the use of "SELECT *" combined with "GROUP BY" in the dashboard map widget. We have added a patch for this in the latest build of HikaShop 6.3.0. Please download the install package of HikaShop 6.3.0 again from our website and install it over your current version. This will apply the patch and it should fix the error.

Thank you, the error is vanished. But I can't see a right Main Hika shop page. Please find an image attached. No further error messages on the screen only at the bottom:

Notice: Undefined variable: region in /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/views/dashboard/tmpl/map.php on line 74

Recoverable fatal error: Object of class stdClass could not be converted to string in /homepages/12/d82533704/htdocs/backup/20260209_intranet/administrator/components/com_hikashop/views/dashboard/tmpl/map.php on line 75

But I think that is not the reason for the view.

Hi,

Thanks for the feedback on that other error.
We found and fixed this issue too. The map widget template had a code structure problem following modifications in the last version of HikaShop. Please download the latest build of HikaShop 6.3.0 again and install it over your current version. This will apply both patches (for the SQL error and for these map widget errors).

Thank you very much for the quick help! The main page works again, great.