Post by mothercruncher » Mon Jul 16, 2012 11:14 pm

Ack, think I just posted my issue in the wrong forum, apologies mods :choke:

http://forum.opencart.com/viewtopic.php?f=112&t=70406


Basically, post a server move (terrible caching issues) the Import/Export tool is outputting corrupt files. The excel file should be around 800k. I'm getting a tiny 4k file outputted with the info inside reading:

----------------------------
Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay<br />Error No: 1104<br />SELECT p.product_id, pd.name, GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR "," ) AS categories, p.sku, p.location, p.quantity, p.model, m.name AS manufacturer, p.image AS image_name, p.shipping, p.price, p.date_added, p.date_modified, p.date_available, p.weight, wc.unit, p.length, p.width, p.height, p.status, p.tax_class_id, p.viewed, p.sort_order, pd.language_id, ua.keyword, pd.description, pd.meta_description, pd.meta_keywords, p.stock_status_id, mc.unit AS length_unit, p.subtract, p.minimum, p.cost, GROUP_CONCAT( DISTINCT CAST(pr.related_id AS CHAR(11)) SEPARATOR "," ) AS related, GROUP_CONCAT( DISTINCT pt.tag SEPARATOR "," ) AS tags FROM `product` p LEFT JOIN `product_description` pd ON p.product_id=pd.product_id AND pd.language_id=1 LEFT JOIN `product_to_category` pc ON p.product_id=pc.product_id LEFT JOIN `url_alias` ua ON ua.query=CONCAT('product_id=',p.product_id) LEFT JOIN `manufacturer` m ON m.manufacturer_id = p.manufacturer_id LEFT JOIN `weight_class_description` wc ON wc.weight_class_id = p.weight_class_id AND wc.language_id=1 LEFT JOIN `length_class_description` mc ON mc.length_class_id=p.length_class_id AND mc.language_id=1 LEFT JOIN `product_related` pr ON pr.product_id=p.product_id LEFT JOIN `product_tags` pt ON pt.product_id=p.product_id AND pt.language_id=1 GROUP BY p.product_id ORDER BY p.product_id, pc.category_id;
-------------------------------------------------

I'm guessing the config on the new server does not match that of the old where the tool was working perfectly.

I'm trying to research what may be causing the issue but am not developer so it's sort of like wading through Russian :laugh:

The max_join_size error it seems might be caused by an MySql config setting. I'm guessing I've just hit a limit on this new server with my latest import of products and now the thing falls over due to too many tables when trying to read the Excel file?

The Excel file stands at around 800k. Can I play with the MySQL config max_join_size limit somehow?

User avatar
New member

Posts

Joined
Mon Jan 31, 2011 9:10 pm

Post by JNeuhoff » Tue Jul 17, 2012 12:08 am

In file admin/model/tool/export.php, in function
populateProductsWorksheet, after the line containing

$storeIds = $this->getStoreIdsForProducts( $database );

add this:

$database->query( "SET SQL_BIG_SELECTS=1" );

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by mothercruncher » Tue Jul 17, 2012 2:08 am

Really appreciate the help J, honestly, you're a knight in shining armour at the moment.

So, I'd add the line straight afterwards to get:

$storeIds = $this->getStoreIdsForProducts( $database );
$database->query( "SET SQL_BIG_SELECTS=1" );
$query = "SELECT ";
$query .= " p.product_id,";
$query .= " pd.name,";
$query .= " GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR \",\" ) AS categories,";
$query .= " p.sku,";
$query .= " p.location,";
etc...


?

User avatar
New member

Posts

Joined
Mon Jan 31, 2011 9:10 pm

User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by mothercruncher » Wed Jul 18, 2012 7:47 pm

Really appreciate the help J, I'll sit down Friday and try to figure this out.

*EDIT* It worked, and I'm so grateful, thank you.

Sincerely, I don't know how you are fixed work/skills-wise, but if there is something I can help you with from a design point of view- an opinion, some advice etc, then send me a PM :)

User avatar
New member

Posts

Joined
Mon Jan 31, 2011 9:10 pm

Post by sportyhabits » Wed Aug 15, 2012 11:28 pm

_x000D_ problem happens because of line break in single cell of excel. One can use formula "=clean(A1)" to remove line breaks and then upload excel. This should solve the problem. If you need more help you can write me at sportyhabits@gmail.com

- Sportyhabits
http://www.sportyhabits.com

Newbie

Posts

Joined
Sat Jun 09, 2012 3:21 pm

Post by JNeuhoff » Thu Aug 16, 2012 3:21 am

sportyhabits wrote:_x000D_ problem happens because of line break in single cell of excel. One can use formula "=clean(A1)" to remove line breaks and then upload excel. This should solve the problem. If you need more help you can write me at sportyhabits@gmail.com

- Sportyhabits
http://www.sportyhabits.com
Line breaks are perfectly legal in a spreadsheet cell. Use LibreOffice Calc instead of MS Excel if the latter can't cope with line breaks.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by wahid » Sun Aug 19, 2012 1:50 am

Honestly I already googling for hours and found no answer for my problem :crazy:
I installed Export/Import tool for my OC 1.4.9.4 and seems okay.

The problem is when I export, I always got 0 bytes excel file named backup_categories_products.xls.
When I open that file there is nothing inside.

Am I stupid or what ;D

Please give me a clue...

Newbie

Posts

Joined
Sat Jul 02, 2011 2:08 pm


Post by JNeuhoff » Mon Aug 20, 2012 4:35 pm

wahid wrote:Honestly I already googling for hours and found no answer for my problem :crazy:
I installed Export/Import tool for my OC 1.4.9.4 and seems okay.

The problem is when I export, I always got 0 bytes excel file named backup_categories_products.xls.
When I open that file there is nothing inside.

Am I stupid or what ;D

Please give me a clue...
The most obvious thing to do is to check your server's error_log, and OpenCart's system/logs/error.txt.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by wahid » Tue Aug 21, 2012 10:00 pm

JNeuhoff wrote: The most obvious thing to do is to check your server's error_log, and OpenCart's system/logs/error.txt.
I checked both log, but nothing there.
I guess it is web host problem.
I will try locally.

Thanks

Newbie

Posts

Joined
Sat Jul 02, 2011 2:08 pm


Post by tmorgan » Tue Aug 28, 2012 8:08 pm

G'day,

Apologies in advance if this one has been answered previously! (haven't seen a relevant post).

Prior to this month the Export/Import tool was working fine for me. Since then I have been receiving the following error on all my exports and content is missing from the file:

"Excel found unreadable content in 'backup_categories_products.xls'. Do you want to recover the contents of this workbook?..."

- Opening the file in Office 2010 I receive the above error with missing data
- Opening the file in Openoffice calc I simply have missing data from the file

Running Opencart 1.4.9.3

A followon issue to the above is the fact that I cannot get the export/import tool working since removing and reintalling the extension (as I originally contemplating extension corruption) - the option is now no longer available in Admin \ System...

- Any help would be greatly appreciated.

Thanks

Newbie

Posts

Joined
Wed Sep 16, 2009 11:59 am

Post by JNeuhoff » Tue Aug 28, 2012 8:27 pm

tmorgan wrote: A followon issue to the above is the fact that I cannot get the export/import tool working since removing and reintalling the extension (as I originally contemplating extension corruption) - the option is now no longer available in Admin \ System...
Upload all the files, and install VQmod, and it will show up in the admin> system

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by tmorgan » Wed Aug 29, 2012 10:15 am

Thanks J,

I followed the instructions within the readme for opencart-1-4-9-x-27-export-import.zip but it still will not appear? Ideas?

... Since having this issue I have installed a TEST site that I was planning to migrate to that is running Opencart v1.5.4 alongwith opencart-1-5-4-x-export-import-12.zip - I can confirm that this installation is working fine.

----> So in summary I have another instance (TEST) of Opencart running V1.5.4 working fine with the the exporter)

---->> But original (PROD) version running 1.4.9.3 not working

--------->>> Any recommendations you can provide would be greatly appreciated!
Last edited by tmorgan on Mon Sep 03, 2012 9:12 am, edited 1 time in total.

Newbie

Posts

Joined
Wed Sep 16, 2009 11:59 am

Post by sys1rak » Fri Aug 31, 2012 5:08 am

Hi - just purchased extension, running 1.5.4.1 and vqmod 2.1.7. I can export with no issue. However, after adding a category and importing nothing happens. I see the upload(%) go to 100% but it then returns me to the import/export screen with all my options but no success or failure messages. Is there a max products (I have 3700). Any help will be appreciated

Newbie

Posts

Joined
Sun Aug 26, 2012 9:41 pm

Post by JNeuhoff » Fri Aug 31, 2012 5:42 am

sys1rak wrote:Hi - just purchased extension, running 1.5.4.1 and vqmod 2.1.7. I can export with no issue. However, after adding a category and importing nothing happens. I see the upload(%) go to 100% but it then returns me to the import/export screen with all my options but no success or failure messages. Is there a max products (I have 3700). Any help will be appreciated
This thread is about Opencart 1.4.9. I don't have a commercial Export/Import tool, ours is free under the GPL3 license. If you got it from someone else, ask for the full source code, as per the GPL3 license, before someone can help you here.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by sys1rak » Fri Aug 31, 2012 7:39 am

Thank you. I have yours installed and the same thing is happening. Am I missing a php or apache2 memory configuration? If I cut the products down to a handful the product works.

Newbie

Posts

Joined
Sun Aug 26, 2012 9:41 pm

Post by JNeuhoff » Fri Aug 31, 2012 9:03 pm

sys1rak wrote:Thank you. I have yours installed and the same thing is happening. Am I missing a php or apache2 memory configuration? If I cut the products down to a handful the product works.
Check the server's error_log and OpenCart's system/logs/error.txt. Most likely an issue with your PHP settings, especially the memory_limit, see embedded readme.txt for details.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by sys1rak » Sat Sep 01, 2012 1:31 pm

thank you - seems to work better, however. the instructions are not clear to me. I want to be able to export all this information, modify it (products too) and add to it if needed. Then I want to upload again. Can I do that?
I am updating products and uploading them and getting a duplicate key message? How do I fix that?

Thank you

Newbie

Posts

Joined
Sun Aug 26, 2012 9:41 pm

Post by tmorgan » Mon Sep 03, 2012 9:12 am

Thanks J,

I followed the instructions within the readme for opencart-1-4-9-x-27-export-import.zip but it still will not appear? Ideas?

... Since having this issue I have installed a TEST site that I was planning to migrate to that is running Opencart v1.5.4 alongwith opencart-1-5-4-x-export-import-12.zip - I can confirm that this installation is working fine.

----> So in summary I have another instance (TEST) of Opencart running V1.5.4 working fine with the the exporter)

---->> But original (PROD) version running 1.4.9.3 not working

--------->>> Any recommendations you can provide would be greatly appreciated!

Newbie

Posts

Joined
Wed Sep 16, 2009 11:59 am

Post by roksport » Mon Sep 10, 2012 9:34 pm

Nvm

Newbie

Posts

Joined
Sun Sep 02, 2012 7:22 pm
Who is online

Users browsing this forum: No registered users and 34 guests