Categories
View Replies (41)

Resolved Virtuemart Prices import - Huge problem

  1. roderic
    roderic
  2. RO CSVI
  3. Sunday, 16 January 2022
  4.  Subscribe via email
Hello,

I have 5 templates (attached) to import prices and discounted prices. These are imported every night and usually works like expected. There have been a few times, and it seems to occur more often recently, that the discounted price of product X also gets added to Product Y. The product that gets the price from the other product, is one row below in the XML file. This has lead to 2 orders of products with a price of around 100 euro for a product that's actually about 6000 euro and one that was 1700 euro. This of course is a big issue. As soon as we've seen problems like this, I've manually run the 5 templates and then the price was normal again.

Today however I also had this problem after manually importing and thus I have the corresponding log file. I've attached this logfile (log.1703) and if you look for:
virtuemart_product_id` = 366

You will find the product that got the wrong price (101.2500). This price belongs to virtuemart_product_id` = 5896

As you can see in the logfile, this product is being dealt with just before the other one. The product with ID 366 should be skipped with this template. That's what happened when I ran the same import again.

Could it be that sometimes the import is still working on one product and it already starts with the next product but then these values get mixed up?

The XML file is also attached.
Attachments (3)
com_csvi.log.1703.rar
csvi_templates_www.woest-sport.nl_20220116.xml
Products.rar
0
Responses (41)
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
Today however I also had this problem after manually importing and thus I have the corresponding log file. I've attached this logfile (log.1703) and if you look for:
virtuemart_product_id` = 366

You will find the product that got the wrong price (101.2500). This price belongs to virtuemart_product_id` = 5896

As you can see in the logfile, this product is being dealt with just before the other one. The product with ID 366 should be skipped with this template. That's what happened when I ran the same import again.

The difference i see is that product with ID 366 and ID 5896 is that ID 366 product does not have node ActionPrices/ActionPrice to get product_override_price. I am still looking into this issue. Will update you with my findings.


Could it be that sometimes the import is still working on one product and it already starts with the next product but then these values get mixed up?

If you look at the debug log you can see that line number are mixed up. When the line number 181 is processed the log for line number 182 is entered with line number 181. You can see that happening all over the import debug log. One possibility with this case is that your database processing is slower than import. So by the time the query is finished the next import process is already ready and running. Still testing this so i have confirmation on what is going on.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
I was testing this issue with two files(Original file that you sent and a smaller file with only records of product ID 366 and 5896) and with template Vendit -> VM - Parent - Action Prices Import 2nd row and i am seeing two different results with import of two files.

1. The original file imports 250 records which includes both products with ID 366 and 5896 and with issue of having wrong price 101.2500 for product ID 366.
2. Small file imports only product ID 5896 and skips the product with ID 366. There is no problem of using wrong price in this case.

Both these imports are using the same template but acts different with import files. May i know on what basis the records are set to Skip for this template? I need to see to reproduce this issue with smaller file so it is easy for me to find the problem. With larger files we do not know on how the values are used unless we see the debug log after the import.

If you look at the debug log you can see that line number are mixed up. When the line number 181 is processed the log for line number 182 is entered with line number 181. You can see that happening all over the import debug log. One possibility with this case is that your database processing is slower than import. So by the time the query is finished the next import process is already ready and running. Still testing this so i have confirmation on what is going on.

Looking at this more deeper, i found that with Skip rule we are decrementing line number by 1. So with skip rule on the record the line number gets decremented and gets logged in the debug log. There is no issue with database processing or with import.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 2
roderic
roderic
Accepted Answer Pending Moderation
May i know on what basis the records are set to Skip for this template?

Some products have several actionprices. With the skip rule I'm getting the 2nd, 3rd and fourth row if exists. If it doesn't exist, it gives an empty result and then the whole product is skipped
  1. more than a month ago
  2. RO CSVI
  3. # 3
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
Still looking into this issue, the problem is that the field ActionPriceInc is getting value 101.25 for product with ID 366 and so the Skip rule does not apply here. I understand that product with ID 366 does not have this node so the record should be skipped but that is not the case.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 4
roderic
roderic
Accepted Answer Pending Moderation
Hello,

Any update on this? I still have to manually import the prices and then check if there are no weird prices after every import.

Also, there are more problems that are possibly related. Maybe not:
Last week my clients website had products that had missing custom values. After importing they were still empty.
roland-empty-cf4all.jpg

The only thing that fixed it, was emptying the virtuemart_product_customfields table and then running the same import again. But the question is for how long. I've disabled the automatic overnight imports since then as I was on a skiing trip and wanted to make sure the products were showing normally (and thus my client not buggering me on my trip). I have attached a logfile from last week that should've added values to customfieldsforall fields but it did not. Hopefully you can find something in the logfile that could be wrong.

Cheers

UPDATE:

Added picture of how it looked after importing and the XML file that belongs to the logfile.
Attachments (3)
com_csvi.log.3024.zip
roland-empty-cf4all.jpg roland-empty-cf4all.jpg
Products-test.xml
  1. more than a month ago
  2. RO CSVI
  3. # 5
rolandd
rolandd
Accepted Answer Pending Moderation
Hey Roderic,

Tharuna asked me to look into this as it is a rather complex problem. I can see the extra price with discounted value added to that product when I import the file manually. Running the same file with the same import as a cron job does not add the price to that one product. Creating a file with just a few products including the one that gets the faulty price, shows no issue importing manually or as a cron job. As you can see it is a little bit like chasing a mouse, now you see me, now you don't :D

There is however one question I have. In the template I see you have added skip rules with the exact same XML node as the regular fields. Is there any use for these fields? As far as I can see they are obsolete.

Tharuna will get back to you on the custom values.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 6
roderic
roderic
Accepted Answer Pending Moderation
Hi Roland,

As you can see it is a little bit like chasing a mouse, now you see me, now you don't


Yeah, I've been testing for hours and hours with different set-ups before posting here. It's not easy to pin-point the exact problem.

There is however one question I have. In the template I see you have added skip rules with the exact same XML node as the regular fields. Is there any use for these fields? As far as I can see they are obsolete.


So Let's say there are 1000 products. Then 250 of them have an "Action price". In the Action prices import, those 750 products without an actionprice, will be skipped.

Of those 250 with an actionprice, 200 have a 2nd action price. The 50 without, will be skipped. Then again in the third and fourth template. In the fourth template it's only about 50 products that then get an additional price added, all the other products are skipped.

Makes sense?
  1. more than a month ago
  2. RO CSVI
  3. # 7
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
We will appreciate if you can create a new topic for new issues. It is difficult to keep track of all issues in one post.

Also, there are more problems that are possibly related. Maybe not:
Last week my clients website had products that had missing custom values. After importing they were still empty.

Running the import with the file you have attached on my local site and also on your test site does not empty the custom values for the product with SKU B1137BB2-489A-4477-B90F-ADE25A6C7CAD. Am i missing something?
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 8
roderic
roderic
Accepted Answer Pending Moderation
Hello Tharuna,

The reason I posted this in the same topic, is because I feel it is a similar issue and maybe it could help in finding the solution for both situations.

Running the import with the file you have attached on my local site and also on your test site does not empty the custom values for the product with SKU B1137BB2-489A-4477-B90F-ADE25A6C7CAD. Am i missing something?


So it works most of the times, especially on first try, but once in a while it doesn't work like expected. Or like last week, I could only get it back working after I truncated the table. I was hoping you could find something weird going on in the log I attached.

So with both imports (Price / Parent+customfields) it doesn't always give the same results what leads me thinking there is a bigger problem underneath. That was the reason for this topic in the first place. Maybe something like the order of rules is not always respected?
  1. more than a month ago
  2. RO CSVI
  3. # 9
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
Please load the attached patch file to solve the price issue. Roland fixed it.:D

So it works most of the times, especially on first try, but once in a while it doesn't work like expected. Or like last week, I could only get it back working after I truncated the table. I was hoping you could find something weird going on in the log I attached.

That is going to be a difficult scenario to find a solution. If there is an issue, it should happen all the time with the import. From the debug log i am see that we are deleting all the obsolete values in customfieldsforall table when Delete Custom fields relation field is set to Yes. But that should not be a problem. Can you tell me if values of the product which is imported are emptied or the product which is not in import file?

So with both imports (Price / Parent+customfields) it doesn't always give the same results what leads me thinking there is a bigger problem underneath. That was the reason for this topic in the first place. Maybe something like the order of rules is not always respected?

You are right about bigger problem underneath. We found that the previous fields values are not cleared when reading values from XML and that will affect the rules applied on the fields. Now that is solved with the patch file, please run the import and let us know on how it goes.
Attachments (1)
patch_xml_2022_02_01.zip
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 10
roderic
roderic
Accepted Answer Pending Moderation
Hello,

Just wanted to give a short update, the automatic import has been running for several nights now and so far everything looks good. I also split the Parent import to import stockable customfields and customfieldsforall. Makes imports faster when I need to import something manually and easier with rules.

Cheers
  1. more than a month ago
  2. RO CSVI
  3. # 11
roderic
roderic
Accepted Answer Pending Moderation
Hello,

I haven't seen any weird prices or had complaints from my client. So I beleive the price import problem is fixed.

Unfortunately I can't say the same for stockablecustomfields. It does seem a lot better but there are still products that after a while, do not have all stockablefields filled. Then it looks like:
missing-fields.jpg

missing-fields2.jpg

So the products are imported, the stockablefields are also filled with the correct product, but the actual selectable value is not always filled. If I set the value to 46 manually in the example, then import again, it will stay at 46.
If I truncate the product_customfields table, it will also fill all values.

I just imported that product solo, now it also looks good:

missing-fields4.jpg

So there still seems to be some kind of problem when importing more products compared to just a few or one.
Maybe I should set a cronjob to truncate that table every night just before the nightly imports start. But of course it would be nicer to have it working as expected.

Log from last night is attached. You can see the SKU of the product that imported incorrectly and the one that did in the screenshot.

Cheers
Attachments (4)
missing-fields.jpg missing-fields.jpg
missing-fields2.jpg missing-fields2.jpg
missing-fields4.jpg missing-fields4.jpg
com_csvi.log.5179.rar
  1. more than a month ago
  2. RO CSVI
  3. # 12
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
So the products are imported, the stockablefields are also filled with the correct product, but the actual selectable value is not always filled. If I set the value to 46 manually in the example, then import again, it will stay at 46.

As per the debug log, the value 46 has been updated to the product Stockablecustomfields. See the query below. The value 46 is not selected in the list but you see the value in that dropdown or the value is not imported at all?




INSERT INTO `si1bf8ru_virtuemart_product_customfields` (`virtuemart_product_id`,`virtuemart_custom_id`,`customfield_value`,`disabler`,`override`,`noninheritable`,`customfield_params`,`published`,`created_on`,`created_by`,`modified_on`,`modified_by`,`locked_on`,`locked_by`,`ordering`) VALUES  ('4869','10','46','0','0','0','','1','2022-02-10 02:20:01','0','2022-02-10 02:20:01','0','0000-00-00 00:00:00','0','0')
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 13
roderic
roderic
Accepted Answer Pending Moderation
Hi Tharuna,

Yes the value 46 is selectable in the dropdown. So the value does exist.

Cheers
  1. more than a month ago
  2. RO CSVI
  3. # 14
roderic
roderic
Accepted Answer Pending Moderation
Update:

The value is also missing in the child product:
missing-fields5.jpg

Probably makes sense but just wanted to add.

Also a theory I have:
With Product X it deletes value 46 and reinserts 46 with a later product. Thus 46 exists after everything is done but it has become another ID so the products that were imported before the deletion, do not have the proper value.

In the logfile I searched for 


`customfield_value` = '46'


Most looked the same but then I found Importline 347, 



347	[QUERY]	 DELETE  FROM `si1bf8ru_virtuemart_product_custom_plg_customsforall` WHERE `customsforall_value_id` IN ( 2362)


Not sure why it deletes this value. Delete custom relations is set to NO.

Cheers
Attachments (1)
missing-fields5.jpg missing-fields5.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 15
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
Not sure why it deletes this value. Delete custom relations is set to NO.

Debug log shows Delete custom fields relations is set to Yes. With Delete custom fields set to yes, we delete the existing values and import the new values so i am thinking the value is imported with one product but when the same value is linked to another product, it is deleted. What if you set Delete custom fields relations to No, do you still see the issue?
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 16
roderic
roderic
Accepted Answer Pending Moderation
Hi Tharuna,

Oops, you are right, must have clicked on the wrong template to check. But, I should have known out of memory as well as when I set this template to NO, it will add additional values on every import and thus lead to one product having size "42" 10 times after 10 nights.

So setting it to No would fix this issue but a new issue would arise.

Why does it then sometimes delete a value and sometimes it doesn't? 42 = 42 I would say.

Cheers
  1. more than a month ago
  2. RO CSVI
  3. # 17
tharuna
tharuna
Accepted Answer Pending Moderation
Hello Roderic,
Why does it then sometimes delete a value and sometimes it doesn't? 42 = 42 I would say.

It deletes the value for the products which exists in database already. I see the value 46 with customsforall_value_id 2362 linked to many products in debug log. There is no delete query with these products so i am guessing the value 46 is new and is added to these products. The Importline 347 where we see delete query is the only product which has 46 existing in database and has been deleted with Delete custom relations to yes. There are no other delete query i could see in the debug log to remove the value 46 or customsforall_value_id 2362. Even with product at line 347, the value 46 or customsforall_value_id 2362 is deleted but it is again inserted into database. You can see the insert query in the following lines after delete query. That means the value is added for the product. What i don't understand is why the value is not selected when the value has been added and linked to the product.

Do you have this setup in the test site? I can check the database and see if the product which does not have 46 selected has the value exist in database or not.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 18
roderic
roderic
Accepted Answer Pending Moderation
Hi Tharuna,

Even with product at line 347, the value 46 or customsforall_value_id 2362 is deleted but it is again inserted into database.


And then it will have a new value_id and thus not be linked to the already imported products above line 347. It will most likely be linked to the product on line 347 and to products after line 347.

Do you have this setup in the test site? I can check the database and see if the product which does not have 46 selected has the value exist in database or not.


I have deleted all templates and rules on the test site and re-imported all relevant templates as I've been changing quite a few things over the week. Now everything is in sync again.

Cheers
  1. more than a month ago
  2. RO CSVI
  3. # 19
tharuna
tharuna
Accepted Answer Pending Moderation
Hello,
And then it will have a new value_id and thus not be linked to the already imported products above line 347. It will most likely be linked to the product on line 347 and to products after line 347.

I doubt that. The product in the screenshot you sent with SKU 127782-0 and did not have 46 value linked is at line 355 as per debug log. The value is not linked to the product as per screenshot.

I have deleted all templates and rules on the test site and re-imported all relevant templates as I've been changing quite a few things over the week. Now everything is in sync again.

May i know the template name to run the import? Debug log shows template ID 75 but the template with ID 75 on test site is for deleting products.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 20
  • Page :
  • 1
  • 2
  • 3


There are no replies made for this post yet.
Be one of the first to reply to this post!