error on affiliatespage backend

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
2 years 6 months ago #336079

-- HikaShop version -- : 4.4.3
-- Joomla version -- : 4.0.3
-- PHP version -- : 7.4

Hi,

I entered an errorpage when trying to access the menuitem Affiliates-Affiliates in the backendmenu on our Joomla 4 testsite. See screenshot.

Attachments:

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
2 years 6 months ago #336081

Hi,

The partners listing uses views to calculate the affiliate fees of each partner to display it on the listing.
The error you have directly comes from MySQL.
It says that a MySQL query used for these calculations use prepared statements along with views, which is not possible.
However, the MySQL there is:

UPDATE #__hikashop_user AS a JOIN #__hikashop_click_view AS b ON a.user_id=b.user_id SET a.user_unpaid_amount=b.click_price WHERE a.user_partner_activated=1
It doesn't have any prepared statement.
So I'm not sure why this error is popping up. Especially since I don't have it on my end on a Joomla 4 test website.
Reading about the issue online ( stackoverflow.com/questions/4380813/how-...ds-to-be-re-prepared ) it seems that it could happen because of a stored procedure in the MySQL server. Do you have any stored procedure ?
Also, it says there that increasing the table_definition_cache parameter of MySQL can help. So maybe you want to check about this possibility with your hosting provider ?

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
2 years 6 months ago #336136

Ok, I will contact the hostingprovider about this.

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
2 years 4 months ago #337855

Hi,

I contacted the hosting provider, they changed some settings, but this didn't help solving the problem. When I looked into the database I spotted besides the normal tables, some views for the affiliates see screenshot. In the database of the livesite in Joomla 3.10 I don't see these views. Can this have something to do with this issue?

Attachments:

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
2 years 4 months ago #337857

Hi,

If the affiliate plugin is not activated, or if you don't access the Partners listing, these views won't be created.
The views are created specifically to precalculate the affiliate fees for each partner so that the partners listing can display them to you. And the query which is reported as generating an error is the one to precalculate the affiliate fees.
So you don't get the error nor the views on the live website because you don't have partners, or you don't have fees to calculate, or you didn't access the partners listing.
Note that besides the partner fees and the fact that it only displays the partners, the listing is the same as the customers listing.
So you could always access your partners information from the customers listing and not use the partners listing.
Once you edit the user, you'll get the affiliate fees anyways.

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
2 years 4 months ago #337983

Hi,

Thanks. I don't understand what you mean excactly. The config on both sites are the same. On the live site (Joomla 3) I get an overview of the partners and on the testsite (Joomla 4) I get an error. See screenshots.

Attachments:

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
2 years 4 months ago #337985

Hi,

What I mean is that :

- I'm not able to reproduce the issue on the same page with PHP8 and Joomla 4. So it's likely an issue linked to the configuration of your MySQL and there is not much we can do in the code of HikaShop to avoid that.

- You can circumvent the issue by going in the menu Customers instead of Affiliates>Partners. There, you'll also have the partners listed.

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
1 year 11 months ago #341534

Hi,

So this issue still remains and I went to the Customers menu as you suggested. Indeed the fee that should be paid is shown here. When I try to pay (as a test) I get another error. See screenshot. (PHP version is 7.4 as the PHP 8 has errors with the used template.)

Attachments:

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
1 year 11 months ago #341535

Hi,

Could you activate the "debug system" setting of the Joomla configuration and try again ? That way, we'll know where there error is and we'll be able to provide a fix for this one.

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
1 year 11 months ago #341570

Hi,

I did and I removed the Acymailing component. That seems to be the cause of the error.

I think some of the problems only occur after updating from Joomla 3 to Joomla 4. In the beginning when I did a test on a clean new Joomla 4 install there were no problems at all.

Before updating from J3 to 4, the precheck from Joomla (see screenshot) refers to several plugins that could break down the update. For Hikashop the warning contains:

HikaShop massaction plugin
Redirect Joomla registration to HikaShop Plugin
HikaShop user synchronization plugin
Hikashop Google Analytics Plugin
HikaShop geolocation plugin
HikaShop Payment Notification plugin
HikaShop affiliate plugin

So I disabled them before updating and then re-enabled them after the update. Still, the affiliate button still doesn't work.

Attachments:

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
1 year 11 months ago #341572

Hi,

These plugins work fine on Joomla 4, as far as we know. I think you can ignore what Joomla tells you on the page of your screenshot.

So you're saying that the affiliate button still doesn't work.
Which button are you talking about ? The pay button ?
What error do you have now (with the "debug system" setting activated) ?

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
1 year 11 months ago #341589

Hi,

It's the menu Affiliates>Affiliates which still gives the error:

Er heeft zich een fout voorgedaan.
1615 Prepared statement needs to be re-prepared
Call stack
# Function Location
1 () JROOT/libraries/vendor/joomla/database/src/Mysqli/MysqliStatement.php:435
2 Joomla\Database\Mysqli\MysqliStatement->execute() JROOT/libraries/vendor/joomla/database/src/DatabaseDriver.php:675
3 Joomla\Database\DatabaseDriver->execute() JROOT/administrator/components/com_hikashop/views/user/view.html.php:183
4 UserViewUser->handleListingPartner() JROOT/administrator/components/com_hikashop/views/user/view.html.php:67
5 UserViewUser->listing() JROOT/administrator/components/com_hikashop/views/user/view.html.php:26
6 UserViewUser->display() JROOT/libraries/src/MVC/Controller/BaseController.php:697
7 Joomla\CMS\MVC\Controller\BaseController->display() JROOT/administrator/components/com_hikashop/helpers/helper.php:2334
8 hikashopController->display() JROOT/administrator/components/com_hikashop/helpers/helper.php:2069
9 hikashopController->listing() JROOT/libraries/src/MVC/Controller/BaseController.php:735
10 Joomla\CMS\MVC\Controller\BaseController->execute() JROOT/administrator/components/com_hikashop/helpers/helper.php:2312
11 hikashopController->execute() JROOT/administrator/components/com_hikashop/hikashop.php:73
12 require_once() JROOT/libraries/src/Dispatcher/LegacyComponentDispatcher.php:69
13 Joomla\CMS\Dispatcher\LegacyComponentDispatcher::Joomla\CMS\Dispatcher\{closure}() JROOT/libraries/src/Dispatcher/LegacyComponentDispatcher.php:71
14 Joomla\CMS\Dispatcher\LegacyComponentDispatcher->dispatch() JROOT/libraries/src/Component/ComponentHelper.php:389
15 Joomla\CMS\Component\ComponentHelper::renderComponent() JROOT/libraries/src/Application/AdministratorApplication.php:143
16 Joomla\CMS\Application\AdministratorApplication->dispatch() JROOT/libraries/src/Application/AdministratorApplication.php:186
17 Joomla\CMS\Application\AdministratorApplication->doExecute() JROOT/libraries/src/Application/CMSApplication.php:278
18 Joomla\CMS\Application\CMSApplication->execute() JROOT/administrator/includes/app.php:63
19 require_once()

In the clean install it doesn't give this error. So I don't think it's related to the MySQL config.

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
1 year 11 months ago #341596

Hi,

It's probably the MySQL user which doesn't have the right to create views and this generates an error but the error is not interpreted correctly by Joomla's MySQL layer which then provides an error message which is not related to the problem.

Could you provide a backend access to the website and a FTP access so that we could add some debug code to look deeper into the matter ?
www.hikashop.com/support/contact-us.html

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
1 year 11 months ago #341603

Hi,

Could be, allthough the massaction is working fine. I've sended you the access info.

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
1 year 11 months ago #341605

Hi,

Thanks. I've modified a bit the code on our end to hide the error when the MySQL query fails.
I was able to test that the change works on your website.
However, that doesn't mean the problem is fixed on your website, it's just hidden.

To properly fix it, you would have to allow your MySQL user to create views.

Mass actions don't need views. The only place which needs views in the Affiliate>Partners menu.

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

  • Posts: 506
  • Thank you received: 21
  • Hikashop Business
1 year 11 months ago #341643

Hi, thanks. I understand. But these views are created in the same way in Joomla 3.10, don't they? And it does work fine in Joomla 3.10 with the same user account on the same server...

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

  • Posts: 81504
  • Thank you received: 13064
  • MODERATOR
1 year 11 months ago #341644

Hi,

They work the same in Joomla 3 yes. And the MySQL query and code we have in HikaShop for that area is the same for Joomla 3 and Joomla 4.
However, on Joomla 4 the MySQL layer is different than on Joomla 3.

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

Time to create page: 0.088 seconds
Powered by Kunena Forum