Resolved Product Export throwing database table error

Hello Tim,

Glad to hear that worked

thanks Roland, splitting into three has worked fine.

I have another site i need to upgrade coming up, so may well try the above to see if that works.

Thanks for your advice

Kind regards

Tim

Hello Tim,

I havent used this before but are you suggesting it would be best to create three seperate xml feeds for Google using "Number of records to export" filter option on the template?

Correct, that is the option I was referring to.

Just out of curiosity, would you be able to setup a test site with Joomla 4, VirtueMart 3 and CSVI 6 beta 4? If so, you could see if the export would work on that for you. Just an idea.

Thanks Roland.

I think i have exhausted all options server side, so perhaps i should come at this from a different way and look at the export.

Basically i am exporting an xml file for Google shopping.

This is an example product extract that the csvi export creates:

<item>
<g:id><![CDATA[00054X]]></g:id>
<title><![CDATA[GBC ID Neck Chain 30 inch (760mm) Ref EB100000 [Pack 100] 00054X]]></title>
<description><![CDATA[<ul><li>ID neck chains</li><li>Suitable for laminated name tags</li><li>30 inches (760mm)</li><li>Packed 100. </li><li>Supplier reference: EB100000</li><li>Our code: 00054X. </li></ul><br /><br /><br /><a href="http://officeneedsdirect.opnet.co.uk/AddToBasket.aspx?ProductCode=00054X"; rel="nofollow"><img src="images/stories/addtobasket-small.gif" width="91" height="40" alt="addtobasket-small" /></a><br /><br /><br /><a href="http://officeneedsdirect.opnet.co.uk/Detail.aspx?SearchText=00054X"; rel="nofollow">View Quantity Breaks & Full Info</a>]]></description>
<g:google_product_category><![CDATA[Office Supplies]]></g:google_product_category>
<g:condition><![CDATA[new]]></g:condition>
<g:availability><![CDATA[in stock]]></g:availability>
<grice><![CDATA[71.20]]></grice>
<g:brand><![CDATA[GBC]]></g:brand>
<g:mpn><![CDATA[00054X]]></g:mpn>
<g:image_link><![CDATA[http://www.officeneedsdirect.co.uk/images/stories/virtuemart/product/00054X.jpg]]></g:image_link>
<groduct_type><![CDATA[Facilities Management>Safety & Security>I D Badges>Others]]></groduct_type>
<link><![CDATA[http://www.officeneedsdirect.co.uk/GBC-ID-Neck-Chain-30-inch-(760mm)-Ref-EB100000-[Pack-100]-00054X-details.html]]></link>
</item>

I have about 15000 products.

Looking at the xml file, it fails generally around line 80,000 which is appox 5000 products.

I havent used this before but are you suggesting it would be best to create three seperate xml feeds for Google using "Number of records to export" filter option on the template?

Kind regards

Tim

Hello Tim,

This means that the memory for the script is over 512mb, which Premium Hosting or Shared Hosting cannot handle. Honestly, a file shouldn't need that much memory anyway.

How much memory a file needs depends on how much data it needs to process. Actually the memory on import is not an issue as long as you use CSV or XML files, since they are read line by line.

Have you contacted the creator of the script to see why this is taking up so much memory? While I understand that you need to export this file, it shouldn't be failing with 512mb available. In this case, the script is doing something else while exporting and is going over the limit."

The issue is that I don't know if it is CSVI taking up this memory or parts of VirtueMart that is used to export certain data. The question is also, how many records and fields are you trying to export.

What would you need from me? Could i send you log in details seperately to view the issue?

There isn't really anything I can do, the root cause is that you run out of memory. So you can either split your export into smaller parts or export less fields. The former is more likely than the latter one.

Thanks Roland, i thought that may be the case.

Would you be kind enough to have a look at this export template with me. I have had great difficulty resolving this issue with our host Heart and am not sure what next to try!

This is their latest reply after i gave them access to our Joomla admin (and i have been speaking to them at length about possible RLimitMem settings which they have advised is not an issue)

"After going through and testing this on our end, we have found that the issue here is that the script itself is taking up too much memory, causing the memory issues here. While the file is uploaded, the file seems to be doing other things, causing the memory to be as big as it is.

This means that the memory for the script is over 512mb, which Premium Hosting or Shared Hosting cannot handle. Honestly, a file shouldn't need that much memory anyway.

Have you contacted the creator of the script to see why this is taking up so much memory? While I understand that you need to export this file, it shouldn't be failing with 512mb available. In this case, the script is doing something else while exporting and is going over the limit."

What would you need from me? Could i send you log in details seperately to view the issue?

Kind regards

Tim

Hello Tim,

is there any similar procedure that can be done for exporting?

Not with the current CSVI because it exports straight into the browser, so you get a download window. Any interference in that will break the download of the file.

I am looking into it for CSVI 6 but it's just on the drawing board, nothing concrete at this point. For now, all that can be done is to tune the server so it won't stop the export process.

Hi Roland

I have been looking into getting the RLimitMEM extended with our host to allow this export to work, and although we have made some headway i am still struggling to export the entire product range for Google shopping as an XML file.

On imports there is the ability to break the import lines down into smaller chunks, is there any similar procedure that can be done for exporting? As our host server keeps throwing an internal server error message at about 9.5MB xml file which is only part way through

Kind regards

Tim

thanks Roland, appreciate the advice!

5.211 installed fine

Kind regards

Tim

Sorry guys, it was a stupid space at the end of a file. Who would have known hmy:

This is fixed in version 5.21.1 available from the Downloads.

Hi Roland

I installing CSVI 5.21 and same error message.

0 SQL=



Kind regards

Hello Chris,

It is not CSVI who decides when you run out of memory, it is the server itself. Have a look at this StackOverflow post for example. It turned out to be a setting in the httpd.conf file. So you may want to check that as well.

yes, simple settings has worked and it brings up

Fatal error: Out of memory (allocated 225705984) (tried to allocate 130968 bytes) in /home/cluster-sites/1050/o/http://officeneedsdirect.co.uk/public_html/libraries/joomla/database/driver/mysqli.php on line 1038

We do however have our memory_limit set at 800M as you can see on our http://www.officeneedsdirect.co.uk/info.php page

Kind regards

Tim

Hello Tim,

Let's check the update issue later and focus on the export.

The white screen you posted is a typical fatal error with error reporting turned off. If it is not the execution time it might be the memory. The only way to know for sure is to see the error.

Go to System -> Global Configuration -> Server -> Error reporting and set this to Simple. Save the configuration and then run the export and see if you get to see the error. If not, increase it to Maximum.

Hi Roland

Unfortunately uninstall and reinstall still generated same error message on installation.

As for export, after a period of time just goes white screen as attached and creates a partial xml file of about 7.6MB. Dont think it can be the max execution time as we have this set as max_execution_time = 300 and its not getting that far

Kind regards

Tim

Hello Tim,

Sorry, I didn't realize that. What you can do is to uninstall CSVI via the Extension Manager and then install again. This will keep your templates but cleans up the filesystem. You could give that a try.

As for the export, if it fails half way, it is another issue unrelated to the update. Is there an error in the export file or it just stops exporting? In the latter case it might be the server just stopping the export because it takes too long.

Hi Roland

I am afraid it does show the same error message from update available fields

0 SQL=

The export is working now, but fails half way through and i wondered if it was linked to the above issue and csvi has not updated to 5.21 correctly

Kind regards

Tim

Hello Tim,

I wasn't aware you were waiting for me. The CSVI 5.21 version should deal with your import problem, doesn't it?

As for the installation error, this isn't a problem or you get it also when you run the update available fields?

... do you need any more info from me to help?

Kind regards

Tim

The problem that you see is that it tries to execute an SQL query but there is no query to execute. That makes it so weird, it could be a parsing issue, that it thinks there is another query to execute while it is an empty line. Just guessing from the top of my head. Updating the available fields can be done via the steps explained earlier. This second step is sort of a quick link to it.