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.
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` = 366You 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
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.
- 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.
- 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
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
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
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.
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.
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
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?
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
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?
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.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
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
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:
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:
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
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
Hi Tharuna,
Yes the value 46 is selectable in the dropdown. So the value does exist.
Cheers
Update:
The value is also missing in the child product:
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
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
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
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
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
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
Hi,
74 = Import of stockablecustomfield (Schoenmaat (Shoe categories) & Productvariant (all other categories))
77 = Variants/childs import (Productvariant)
78 = Variants/childs import (Schoenmaat)
Cheers
Hi again,
It is really bad again now. It is almost as if one of the patches I recently installed undid one of the earlier patches. Could this be possible?
Hello Roderic,
It is really bad again now. It is almost as if one of the patches I recently installed undid one of the earlier patches. Could this be possible?
Yes, it is possible that you have installed an outdated patch which would have overridden the latest code changes. Can you tell me what is broken?
I will look into the stockable custom fields issue and send you an update.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello Roderic,
I was testing the Stockable custom fields import on the test site and see that not just one but none of the Size values are linked to the child products. I installed latest RO CSVI Package on the test site so to be sure all the fixes are included. Now when i run the import, i see 403 forbidden error with import preview page. See attached screenshot. May i know what is causing this access forbidden error?
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello Tharuna,
you have installed an outdated patch
What I meant was, that one of the last patches you've given me, fixed one thing but also had older code in there for another problem that was fixed before. Because basically a patch file overwrites a complete file, right?
In the meantime I've added a cronjob that truncates virtuemart_custom_plg_customsforall & virtuemart_product_customfields every night just before the overnight imports. Than I can leave delete customfields in the templates to no and then at least I can make sure everything is imported for now. Running a manual import during the day, in case needed, is then a problem.
May i know what is causing this access forbidden error?
Modsecurity:
[client 49.205.123.xxx] ModSecurity: [file "/etc/httpd/conf/modsecurity.d/rules/comodo_free/17_Outgoing_FilterSQL.conf"] [line "91"] [id "218140"] [rev "1"] [msg "COMODO WAF: mysql SQL Information Leakage|||F|2"] [data "Matched Data: error found within MATCHED_VAR: <!DOCTYPE html>\\x0a<html lang=\\x22en-gb\\x22 dir=\\x22ltr\\x22>\\x0a<head>\\x0a\\x09<meta name=\\x22viewport\\x22 content=\\x22width=device-width, initial-scale=1.0\\x22>\\x0a\\x09<meta http-equiv=\\x22X-UA-Compatible\\x22 content=\\x22IE=edge\\x22 />\\x0a\\x09<meta charset=\\x22utf-8\\x22 />\\x0a\\x09<meta name=\\x22keywords\\x22 content=\\x22WOEST, sport, running, bikes, triathlon\\x22 />\\x0a\\x09<meta name=\\x22description\\x22 content=\\x22WOEST Sport voor alles op het gebied van bike..."] [severity "CRITICAL"] [tag "CWAF"] [tag "FilterSQL"] Access denied with code 403 (phase 4). Pattern match "(?i)(?:supplied argument is not a valid MySQL|Column count doesn't match value count at row|mysql_fetch_array\\\\(\\\\)|on MySQL result index|You have an error in your SQL syntax;|You have an error in y [hostname "woest-sport.nl"] [uri "administrator/index.php"] [unique_id "YgotsDiKwM5olOktmYyk@wAAAEc"], referer: administrator/index.php?option=com_csvi&task=importsource.source&runId=428
I've disabled the triggered rule (218140) and now you should be able to continue.
Hello Roderic,
What I meant was, that one of the last patches you've given me, fixed one thing but also had older code in there for another problem that was fixed before. Because basically a patch file overwrites a complete file, right?
Yes, patch overwrites complete file.
In the meantime I've added a cronjob that truncates virtuemart_custom_plg_customsforall & virtuemart_product_customfields every night just before the overnight imports. Than I can leave delete customfields in the templates to no and then at least I can make sure everything is imported for now. Running a manual import during the day, in case needed, is then a problem.
I have updated test site with latest RO CSVI package to test the import but have been running into errors. There is a duplicate error with category_id. See attached screenshots. The problem is that category_id is getting product_sku value for one product on import which is wrong. Any idea why only this product gets that value for category_id?
I've disabled the triggered rule (218140) and now you should be able to continue.
Yes, there is no 403 forbidden error now. Thank you.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
The problem is that category_id is getting product_sku value for one product on import which is wrong. Any idea why only this product gets that value for category_id?
It's the category ID. There is a conditional rule that changes every Vendit ID to a Virtuemart ID. But since this is a new category (added yesterday), there was no rule for that specific category yet on the test site. There is now and thus the error is gone and the import is able to finish.
Sorry about that
Hello Roderic,
Yes, import went without any problem. Thank you.
So i ran import with three templates as you mentioned and see no stockablecustomfields values added for product 110BA760-CDCA-4FE5-A809-99FBC26ABD5C (the product from your screenshot). Am i missing something?
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
Am i missing something?
No, if you change the imported filename for templates 74+77 to Products-test.xml instead of Products.xml, you will be able to import the Stockablefields (74) and then they will be filled with values. But only because Products-test.xml contains only that product. When importing the complete file, it fails.
Cheers
Hello Roderic,
Yes, i changed those import files and ran the import. I see that Delete custom fields was set to yes in template with ID 74 and with template ID 77, Delete custom fields was set to No. This was creating number of rows for child product in #__virtuemart_product_customfields table. That is the reason why values are not selected with Size custom field. I set Delete custom fields to Yes in template ID 77 and ran the import. This solved the issue of values not selected. May i know why not set Delete custom fields to Yes with child product import template(ID 77)?
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello Roderic,
May i know why not set Delete custom fields to Yes with child product import template(ID 77)?
Hi Tharuna,
As mentioned before in this topic, setting it to YES will then cause the problem of sometimes a value being removed that was already linked to another product:
https://rolandd.com/support/ro-csvi/virtuemart-prices-import-huge-problem#reply-70191
Hello Roderic,
I guess we are going in circles with this issue.
The product you are saying there is an issue in the linked topic, gets all the values when Delete custom fields set to Yes. I have been using that product as reference in checking the issue and have run the import quite a few times on your site. Now i am confused if this product has the issue of values being removed or any other product :o
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
I guess we are going in circles with this issue.
Exactly my thoughts.
Now i am confused if this product has the issue of values being removed or any other product
It happens with many products, but it's quite random. As I've mentioned before, I'm running the import every night now with first truncating the customfields table. This so far gives reliable/consistent results. The setting in the template for "delete custom fields" is then set to no (logically as the table is empty anyway after truncating)
gets all the values when Delete custom fields set to Yes
Yes it does, though it sometimes first removes certain values, then reimports them. Then for this products all is fine but not so much for the products that came before this product in the import because the values are then not linked anymore.
Cheers
Hello Roderic,
It happens with many products, but it's quite random.
Unless we can reproduce the issue, it is difficult to fix it.
As I've mentioned before, I'm running the import every night now with first truncating the customfields table. This so far gives reliable/consistent results. The setting in the template for "delete custom fields" is then set to no (logically as the table is empty anyway after truncating)
Won't it empty the values of products which are not in import file? Delete custom fields set to Yes, deletes the values for products in import file. I am wondering how truncating the table does not create an issue but delete custom fields is a problem.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
Won't it empty the values of products which are not in import file?
Yes it will, but all products are imported every night, including customfields.
Delete custom fields set to Yes, deletes the values for products in import file. I am wondering how truncating the table does not create an issue but delete custom fields is a problem.
Because I have set Delete custom fields set to No, so importing a product halfway the import will not interfere with products at the start of the import
I could run a test where I truncate the table first on the test site, then import with Delete custom fields set to Yes.
Hello Roderic,
Because I have set Delete custom fields set to No, so importing a product halfway the import will not interfere with products at the start of the import
Yes understood the difference :)
I could run a test where I truncate the table first on the test site, then import with Delete custom fields set to Yes.
I think i may have found the cause for the issue of values not linked after the import. I have fixed it with the attached patch file. I have also added few more logging so to track the import process. Run import with attached patch file and if you still see the issue, please post the latest debug log to check.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
That sounds promising!
Have you also installed this on the test site? If not, I'll install it and do some testing there.
Thanks!
Hello Roderic,
That sounds promising!
Not so soon :D
Have you also installed this on the test site? If not, I'll install it and do some testing there.
No, i haven't installed the patch on test site as i don't have access to it anymore. Administrator link takes me to frontend of the site. Please install the patch file and run the import. Let me know on how that goes.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello Roderic,
Did you get a chance to test the patch? Please let us know on how the import go.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Tharuna,
Unfortunately I only now had the chance to test the patch. So far so good. I've attached the logfile.
No, i haven't installed the patch on test site as i don't have access to it anymore. Administrator link takes me to frontend of the site. Please install the patch file and run the import. Let me know on how that goes.
There was a security measure from Akeeba Admin tools, that blocks admin logins during the night. This has been there all the time but I guess you've always logged in after this period. I've now removed it for the test website.
The imports of Variants do seem to take a bit longer. Maybe because of the additional logging?
I need to run now but I can do a few more test runs later on.
Cheers
Hello Roderic,
The imports of Variants do seem to take a bit longer. Maybe because of the additional logging?
From the debug log i see import has taken 5 mins and that is only for the records in debug log. Not sure if there are more records which are not in debug log. So checking the time taken for each record i see they are in few milliseconds but the number of records imported are more. Probably you imported a large file?
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Log in to reply to this topic.