Hikashop Updates causing duplication and errors with Paypal on Custom Fields

  • Posts: 18
  • Thank you received: 1
4 years 3 days ago #336609

-- HikaShop version -- : 4.4.3
-- Joomla version -- : 3.10.1
-- PHP version -- : 7.4
-- Browser(s) name and version -- : Chrome 94.0.4606.81 (Official Build) (64-bit)
-- Error-message(debug-mod must be tuned on) -- : No error message
On every update Hikashop duplicates all contact details and increases the counter for the Client ID. Also duplicates all Custom Fields and reconfigures the Required, Front-end and Back-end Form statuses. Have had to turn off Paypal as a payment gateway as it won't allow address information to transfer and fails on the Payment.

No error message
On every update Hikashop duplicates all contact details and increases the counter for the Client ID. Also duplicates all Custom Fields and reconfigures the Required, Front-end and Back-end Form statuses. Have had to turn off Paypal as a payment gateway as it won't allow address information to transfer and fails on the Payment.

Thanks for your help in advance!

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
4 years 2 days ago #336616

Hi,

This indicates that you have a problem with the database. For example, if the unique key on the user_email column of the hikashop_user table has been removed, then it's indeed possible that the users would be duplicated on each update. That should however not happen as the only way to delete that unique key is to manually remove it via your PHPMyAdmin.
Worst is that you won't be able to add it back on the user_email column since you now have duplicate entries with the same value in that column.
Here is someone who had a similar issu for some reasons:
www.hikashop.com/support/forum/customers...mla-user/243020.html

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

  • Posts: 18
  • Thank you received: 1
4 years 2 days ago #336626

Thanks for the quick response

It would seem that at some point there has been a copy of the hikashop_user table made as we have 2 tables:

hikashop_user_ORIGINAL
hikashop_user_ORIGINAL290520

There isn't actually a hikashop_user table

hikashop_user_ORIGINAL table contains 13,092 entries
hikashop_user_ORIGINAL290520 table contains 14,943 entries

It appears that neither of these tables are being updated as there are now 204 new users on the website since the last entry in the hikashop_user_ORIGINAL290520 table

If I open the Hikashop_address table there are 10,507 entries and the 204 new users are present in there

I can not see where the user information is contained for the 204 new users anywhere in any of the other tables. Is there somewhere else that this data would be held other than the joomla users table?

What I am wondering is if I performed the latest update if the hikashop_user_ORIGINAL290520 table would then magically contain the 204 new users data and obviously duplicate everything else yet again

Perhaps a simply fix would be to perform an update so potentially the new 204 users are added and duplicated then rename the hikashop_user_ORIGINAL290520 to hikashop_user?????

I'd prefer to clean this up and fix it now before doing another update but if that seems like a good idea I could try it??

Interestingly there doesn't appear to be any duplicates in the user tables - they are ONLY duplicated on the website backend when you view under Customers/Customers and in the Display/Custom Fields - which also needs to be fixed ....

When I view the hikashop_field table there are a 7 duplicates of the original but also missing some of the custom fields on the duplicates. This potentially may be related after the changes to the way Hikashop handles user points as the first duplicate entry in this table is "User Points" and removes all the additional custom fields after that entry on all duplicates - plus also defaults the custom fields back to their originally naming convention - eg. VAT Number instead of GST Number.

Not sure if the 2 issues are related but they seem to be ...

Any advice and help/support to fix this would be awesome and much appreciated please!!

Thank you :)

Last edit: 4 years 2 days ago by KBIT.

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
4 years 1 day ago #336649

Hi,

HikaShop cannot use any other table than the one named hikashop_user.
And if that table is not there, the processing of the pages where HikaShop is involved should crash with an MySQL query error message because it cannot find the hikashop_user.
There is even no easy way to use another table than hikashop_user in its stead.
So I don't see how that's possible that the website is still displaying fine without the hikashop_user table.
Please double check.
I can only suppose that you have the hikashop_user table. Beware that in the same database, you might have several times the hikashop and joomla tables but with a different table prefix (you can see it in the configuration page of Joomla). So maybe you were looking at the tables with another prefix than the one of your website.

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

  • Posts: 18
  • Thank you received: 1
3 years 11 months ago #336696

Hi Nicholas

You are 100% right as expected :)

In the left side dropdown options there is no hikashop_users table

When I open the table and click on structure it appears in there

I can now see all the duplications in this table and all the duplications in the hikashop_field table

Any suggestions on how to clean this up before doing another update?

Thanks! :)

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
3 years 11 months ago #336703

Hi,

Well, that's not easy to do it properly.
The problem is that since you have several entries for the same email address in the hikashop_user table, it means you might have addresses / orders for the same email address but different entries.
So prior to deleting the duplicate entries, you would first have to update the order_user_id and address_user_id with the user_id that you won't be deleting.
stackoverflow.com/questions/65340539/how...es-duplicate-records
Once you've updated the user_id in the hikashop_order and hikashop_address tables, you can delete the duplicate entries in hikashop_user. And finally, you can add back a unique key on the user_email column of the hikashop_user table. After that, you can update again your HikaShop and you won't get duplicate entries.

The queries should be something like that:

update #__hikashop_address as a join
       (select b.*,
               min(user_id) over (partition by user_email) as min_id
        from #__hikashop_user as b
       ) b
       on a.address_user_id = b.user_id
    set a.address_user_id = b.min_id
    where a.address_user_id <> b.min_id;
update #__hikashop_order as a join
       (select b.*,
               min(user_id) over (partition by user_email) as min_id
        from #__hikashop_user as b
       ) b
       on a.order_user_id = b.user_id
    set a.order_user_id = b.min_id
    where a.order_user_id <> b.min_id;
delete b
    from  #__hikashop_user as b join
         (select b.*,
                 min(user_id) over (partition by user_email) as min_id
          from  #__hikashop_user as b
         ) bb
         on bb.user_id = b.user_id
    where b.user_id <> bb.min_id;
Please note that I didn't test them. I've just replaced the variables with hikashop's based on one of the answers on the link I gave above. So I would recommend you try them on a copy of your database first (or at least that you backup your database so that you can restore it if something goes wrong).
Note also that you need to replace #__ with the table prefix of your Joomla, which I don't know.

Last edit: 3 years 11 months ago by nicolas.

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

  • Posts: 18
  • Thank you received: 1
3 years 11 months ago #336717

Thanks for the reply Nicholas

This may be some good news for resolving it...

The duplications DON'T include addresses or orders.

The orders and addresses are always associated to the first instance of the user.

All duplications after the first user duplicate ONLY the Name, Username, Email address and ONE custom field.

The single custom field duplication is not a major issue if we lose that data although I do wonder if that is when the issue arose with the addition of the Hikashop POINTS field. I'm just guessing on that.

Any further thoughts on this with the new information above before we begin cleaning the table?

Thanks

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

  • Posts: 18
  • Thank you received: 1
3 years 11 months ago #336718

I've also created a local cop of the database and run the query in MySQL Workbench - receive the following error:

Error Code: 1064. 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 ') bb on bb.user_id = b.user_id where bb.user_id <> b.min_id' at line 6

This is on running the last part for the hikashop_user table

Tried running the other ones and received the same error on the relevant lines or address and order as well

Thanks

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
3 years 11 months ago #336721

Hi,

Try adding " as " between the parenthesis and the "b" or "bb"

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

  • Posts: 18
  • Thank you received: 1
3 years 11 months ago #336767

Hi

Tried that - same issue ...

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
3 years 11 months ago #336774

Hi,

I tried the queries on my end and I didn't get that error.
Also, I got another error with the last query as there was a typo in it on the link I found.
I've corrected that query in my previous message.
So now with that correction, I can confirm that all three queries work.
If you're still getting the same error, then you have a problem in the way you're running the MySQL query in your database.

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

  • Posts: 18
  • Thank you received: 1
3 years 11 months ago #336812

Hi

Would you mind posting your 3 updated queries please so I can see where I could have gone wrong with them. Thanks :)

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

  • Posts: 84586
  • Thank you received: 13757
  • MODERATOR
3 years 11 months ago #336818

Hi,

I didn't had to modify the 2 first ones. Only the last one had a problem. And as I sai in my previous message, I've updated the original message here ( www.hikashop.com/forum/install-update/90...m-fields.html#336703 ) with the modification.
So you already have them above.

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

Time to create page: 0.065 seconds
Powered by Kunena Forum