Dear all,
on Joomla 3.3.6, Virtuemart 3.02 CSVI 6 PHP 5.5.16, Database 5.5.39-MariaDB (test server on Synology NAS, external links can be provided)
I was able to install CSVI 6-and I have generated some Virtuemart Export templates
The following exports are working as far as I can see:
Category
Products
Manufacturer
are running fine!
When trying to export the order (there is just one order present in the database, just the field "CITY" selected), the following error message appears first:
Table 'Joomla.pxv6t_virtuemart_paymentmethods_' doesn't exist SQL=SELECT city FROM pxv6t_virtuemart_orders LEFT JOIN pxv6t_virtuemart_order_items ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_order_items.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_order_userinfos AS user_info1 ON pxv6t_virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_orderstates ON pxv6t_virtuemart_orders.order_status = pxv6t_virtuemart_orderstates.`.....
Then, after fully configuring the shop I managed to get the first order export
"city"
"xxx" => great!
But then, when trying to expand the fields by selecting the "Templates" Tab, I got a new error message:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') GROUP BY value ORDER BY text' at line 5 SQL=SELECT virtuemart_user_id AS value,IF (LENGTH(TRIM(CONCAT_WS(' ', first_name, middle_name, last_name))) = 0, '', IF (TRIM(CONCAT_WS(' ', first_name, middle_name, last_name)) IS NULL, '', TRIM(CONCAT_WS(' ', first_name, middle_name, last_name)) ) ) AS text FROM pxv6t_virtuemart_order_userinfos WHERE virtuemart_user_id IN () GROUP BY value ORDER BY text (end of error message)
And I'm not allowed anymore to edit the corresponding template, but DELETE Template is still possible.
So I deleted and tried again.
Unfortunately ... as soon as I try to set the filename for the exported file, the same message appeared again.
Then, justr playing with new templates, I got once again the error message
Table 'Joomla.pxv6t_virtuemart_paymentmethods_' doesn't exist SQL=SELECT address_1, city FROM pxv6t_virtuemart_orders LEFT JOIN pxv6t_virtuemart_order_items ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_order_items.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_order_userinfos AS user_info1 ON pxv6t_virtuemart_orders.`...
How shall I proceed?
Shall I provide login credentials?
Thanks & best regards
Hendrik
Remark: By directly selecting Templates => Template Fields I was able to export e.g. 18 fields with the order template, but it is still a bit tricky to overcome the error messages.
If I once again try to add almost all fields (without payment methods) I still get an error message... now concerning virtuemart_orders.customer_note
Do I have to tune the fields until I do not get an error message... or what is the right way?
Thanks for your help!
Hello Hendrik,
Thank you for test driving CSVI 6.
The first error of the payment table is to be expected when VirtueMart is not completely configured. There is nothing CSVI can do about that.
The second problem is related to this part of the query
WHERE `virtuemart_user_id` IN ()
. I will email you an updated version of the VM addon that solves this issue.
Table 'Joomla.pxv6t_virtuemart_paymentmethods_' doesn't exist
The table name should be like pxv6t_virtuemart_paymentmethods_en_gb. You need to select the correct language on the Options page. Have you done that?
The customer_note field issue is also fixed in the version I will send you.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
thanks, I've updated the virtuemart plugin and updated the available virtuemart fields after installation.
Better now after your fast delivery, customer note is exporting fine, editing of Template is stable now, no more error messages there.
But export of orders (with fresh templates, language set to DE-CH-AT, all fields but customer note) still delivers error messages (but not for small amount of fields)
Table 'Joomla.pxv6t_virtuemart_paymentmethods_' doesn't exist SQL=SELECT address_1, address_2, address_type, address_type_name, agreed, city, company, user_info1.virtuemart_country_id, coupon_code, coupon_discount, pxv6t_virtuemart_orders.created_by, pxv6t_virtuemart_orders.created_on, customer_number, pxv6t_virtuemart_orders.delivery_date, (order_discount/order_total)100 AS discount_percentage, user_info1.email, fax, first_name, user_info1.first_name, user_info1.middle_name, user_info1.last_name, pxv6t_virtuemart_orders.virtuemart_order_id, ip_address, last_name, pxv6t_virtuemart_orders.locked_by, pxv6t_virtuemart_orders.locked_on, middle_name, pxv6t_virtuemart_orders.modified_by, pxv6t_virtuemart_orders.modified_on, order_billDiscountAmount, order_billTax, order_billTaxAmount, order_currency, order_discount, order_discountAmount, order_item_currency, order_item_name, order_item_sku, order_language, order_number, order_pass, order_payment, order_payment_tax, order_salesPrice, order_shipment, order_shipment_tax, pxv6t_virtuemart_orders.order_status, order_status_name, order_subtotal, order_tax, order_total, pxv6t_virtuemart_shipmentmethods.shipment_element, pxv6t_virtuemart_orders.virtuemart_paymentmethod_id, payment_name, phone_1, phone_2, product_attribute, product_basePriceWithTax, product_discountedPriceWithoutTax, product_final_price, product_item_price, product_priceWithoutTax, product_item_priceproduct_quantity AS product_price_total, product_quantity, product_subtotal_discount, product_subtotal_with_tax, product_tax, shipment_element, user_info1.virtuemart_state_id, title, tos, pxv6t_virtuemart_orders.virtuemart_user_id, pxv6t_virtuemart_orders.user_currency_id, user_currency_id, user_currency_rate, virtuemart_order_item_id, virtuemart_order_userinfo_id, pxv6t_virtuemart_order_items.virtuemart_product_id, pxv6t_virtuemart_orders.virtuemart_shipmentmethod_id, virtuemart_state_id, pxv6t_virtuemart_orders.virtuemart_vendor_id, zip FROM pxv6t_virtuemart_orders LEFT JOIN pxv6t_virtuemart_order_items ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_order_items.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_order_userinfos AS user_info1 ON pxv6t_virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_orderstates ON pxv6t_virtuemart_orders.order_status = pxv6t_virtuemart_orderstates.order_status_code LEFT JOIN pxv6t_virtuemart_product_manufacturers ON pxv6t_virtuemart_order_items.virtuemart_product_id = pxv6t_virtuemart_product_manufacturers.virtuemart_product_id LEFT JOIN pxv6t_virtuemart_manufacturers ON pxv6t_virtuemart_product_manufacturers.virtuemart_manufacturer_id = pxv6t_virtuemart_manufacturers.virtuemart_manufacturer_id LEFT JOIN pxv6t_users ON pxv6t_users.id = user_info1.virtuemart_user_id LEFT JOIN pxv6t_virtuemart_countries ON pxv6t_virtuemart_countries.virtuemart_country_id = user_info1.virtuemart_country_id LEFT JOIN pxv6t_virtuemart_invoices ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_invoices.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_paymentmethods_ ON pxv6t_virtuemart_orders.virtuemart_paymentmethod_id = pxv6t_virtuemart_paymentmethods_.virtuemart_paymentmethod_id LEFT JOIN pxv6t_virtuemart_shipmentmethods ON pxv6t_virtuemart_orders.virtuemart_shipmentmethod_id = pxv6t_virtuemart_shipmentmethods.virtuemart_shipmentmethod_id ORDER BY address_1, address_2, address_type, address_type_name, agreed, city, company, coupon_code, coupon_discount, created_by, created_on, customer_number, delivery_date, email, fax, first_name, invoice_number, ip_address, last_name, locked_by, locked_on, middle_name, modified_by, modified_on, order_billDiscountAmount, order_billTax, order_billTaxAmount, order_currency, order_discount, order_discountAmount, order_item_currency, order_item_name, order_item_sku, order_language, order_number, order_pass, order_payment, order_payment_tax, order_salesPrice, order_shipment, order_shipment_tax, order_status, order_status_name, order_subtotal, order_tax, order_total, payment_name, phone_1, phone_2, product_attribute, product_basePriceWithTax, product_discountedPriceWithoutTax, product_final_price, product_item_price, product_priceWithoutTax, product_quantity, product_subtotal_discount, product_subtotal_with_tax, product_tax, title, tos, username, user_currency_id, user_currency_rate, virtuemart_order_id, virtuemart_order_item_id, virtuemart_order_userinfo_id, virtuemart_paymentmethod_id, virtuemart_product_id, virtuemart_shipmentmethod_id, virtuemart_state_id, virtuemart_user_id, virtuemart_vendor_id, zip
Or, next time, same template/ settings:
Out of sort memory, consider increasing server sort buffer size SQL=SELECT address_1, address_2, address_type, address_type_name, agreed, city, company, user_info1.virtuemart_country_id, coupon_code, coupon_discount, pxv6t_virtuemart_orders.created_by, pxv6t_virtuemart_orders.created_on, customer_number, pxv6t_virtuemart_orders.delivery_date, (order_discount/order_total)100 AS discount_percentage, user_info1.email, fax, first_name, user_info1.first_name, user_info1.middle_name, user_info1.last_name, pxv6t_virtuemart_orders.virtuemart_order_id, ip_address, last_name, pxv6t_virtuemart_orders.locked_by, pxv6t_virtuemart_orders.locked_on, middle_name, pxv6t_virtuemart_orders.modified_by, pxv6t_virtuemart_orders.modified_on, order_billDiscountAmount, order_billTax, order_billTaxAmount, order_currency, order_discount, order_discountAmount, order_item_currency, order_item_name, order_item_sku, order_language, order_number, order_pass, order_payment, order_payment_tax, order_salesPrice, order_shipment, order_shipment_tax, pxv6t_virtuemart_orders.order_status, order_status_name, order_subtotal, order_tax, order_total, pxv6t_virtuemart_shipmentmethods.shipment_element, pxv6t_virtuemart_orders.virtuemart_paymentmethod_id, payment_name, phone_1, phone_2, product_attribute, product_basePriceWithTax, product_discountedPriceWithoutTax, product_final_price, product_item_price, product_priceWithoutTax, product_item_priceproduct_quantity AS product_price_total, product_quantity, product_subtotal_discount, product_subtotal_with_tax, product_tax, shipment_element, user_info1.virtuemart_state_id, title, tos, pxv6t_virtuemart_orders.virtuemart_user_id, pxv6t_virtuemart_orders.user_currency_id, user_currency_id, user_currency_rate, virtuemart_order_item_id, virtuemart_order_userinfo_id, pxv6t_virtuemart_order_items.virtuemart_product_id, pxv6t_virtuemart_orders.virtuemart_shipmentmethod_id, virtuemart_state_id, pxv6t_virtuemart_orders.virtuemart_vendor_id, zip FROM pxv6t_virtuemart_orders LEFT JOIN pxv6t_virtuemart_order_items ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_order_items.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_order_userinfos AS user_info1 ON pxv6t_virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_orderstates ON pxv6t_virtuemart_orders.order_status = pxv6t_virtuemart_orderstates.order_status_code LEFT JOIN pxv6t_virtuemart_product_manufacturers ON pxv6t_virtuemart_order_items.virtuemart_product_id = pxv6t_virtuemart_product_manufacturers.virtuemart_product_id LEFT JOIN pxv6t_virtuemart_manufacturers ON pxv6t_virtuemart_product_manufacturers.virtuemart_manufacturer_id = pxv6t_virtuemart_manufacturers.virtuemart_manufacturer_id LEFT JOIN pxv6t_users ON pxv6t_users.id = user_info1.virtuemart_user_id LEFT JOIN pxv6t_virtuemart_countries ON pxv6t_virtuemart_countries.virtuemart_country_id = user_info1.virtuemart_country_id LEFT JOIN pxv6t_virtuemart_invoices ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_invoices.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_paymentmethods_de_de ON pxv6t_virtuemart_orders.virtuemart_paymentmethod_id = pxv6t_virtuemart_paymentmethods_de_de.virtuemart_paymentmethod_id LEFT JOIN pxv6t_virtuemart_shipmentmethods ON pxv6t_virtuemart_orders.virtuemart_shipmentmethod_id = pxv6t_virtuemart_shipmentmethods.virtuemart_shipmentmethod_id GROUP BY address_1,address_2,address_type,address_type_name,agreed,city,company,coupon_code,coupon_discount,created_by,created_on,customer_number,delivery_date,email,fax,first_name,invoice_number,ip_address,last_name,locked_by,locked_on,middle_name,modified_by,modified_on,order_billDiscountAmount,order_billTax,order_billTaxAmount,order_currency,order_discount,order_discountAmount,order_item_currency,order_item_name,order_item_sku,order_language,order_number,order_pass,order_payment,order_payment_tax,order_salesPrice,order_shipment,order_shipment_tax,order_status,order_status_name,order_subtotal,order_tax,order_total,payment_name,phone_1,phone_2,product_attribute,product_basePriceWithTax,product_discountedPriceWithoutTax,product_final_price,product_item_price,product_priceWithoutTax,product_quantity,product_subtotal_discount,product_subtotal_with_tax,product_tax,title,tos,username,user_currency_id,user_currency_rate,virtuemart_order_id,virtuemart_order_item_id,virtuemart_order_userinfo_id,virtuemart_paymentmethod_id,virtuemart_product_id,virtuemart_shipmentmethod_id,virtuemart_state_id,virtuemart_user_id,virtuemart_vendor_id,zip ORDER BY address_1, address_2, address_type, address_type_name, agreed, city, company, coupon_code, coupon_discount, created_by, created_on, customer_number, delivery_date, email, fax, first_name, invoice_number, ip_address, last_name, locked_by, locked_on, middle_name, modified_by, modified_on, order_billDiscountAmount, order_billTax, order_billTaxAmount, order_currency, order_discount, order_discountAmount, order_item_currency, order_item_name, order_item_sku, order_language, order_number, order_pass, order_payment, order_payment_tax, order_salesPrice, order_shipment, order_shipment_tax, order_status, order_status_name, order_subtotal, order_tax, order_total, payment_name, phone_1, phone_2, product_attribute, product_basePriceWithTax, product_discountedPriceWithoutTax, product_final_price, product_item_price, product_priceWithoutTax, product_quantity, product_subtotal_discount, product_subtotal_with_tax, product_tax, title, tos, username, user_currency_id, user_currency_rate, virtuemart_order_id, virtuemart_order_item_id, virtuemart_order_userinfo_id, virtuemart_paymentmethod_id, virtuemart_product_id, virtuemart_shipmentmethod_id, virtuemart_state_id, virtuemart_user_id, virtuemart_vendor_id, zip
But with e.g. just 17 fields in order export template everything is working now, even with customer_note!!!
For example, the following fields are working:
"address_1","city","email","customer_note","address_2","address_type","address_type_name","agreed","company","country_2_code","country_3_code","country_name","coupon_code","coupon_discount","created_by","created_on","custom"
Do you have an idea concerning the difference?
Best regards
Hendrik
Hello Hendrik,
I have added a check to the order export to make sure there is a language so you don't end up with a broken query.
As for the other error you get, this is a server configuration issue.
Out of sort memory, consider increasing server sort buffer size
As you can see the server is telling you to increase the server sort buffer size. With more fields on the export, the server simply needs more power to process the query. The order query is quite large.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
thanks for your answer.
I've moved onto a real, external server.
And I have reduced the fields for order export to below 30.
No order message anymore, but the export times out. Last server Response counts up to 5... and then ends without message.
The Joomla debug console, section database queries, shows some duplicate queries and quiet often the message
NO INDEX KEY COULD BE USED.
Same appears when exporting just 3 order fields instead of 30.
Do you have some hints how to solve this?
Would you like to get login credentials?
Thanks & best regards
Hendrik
Hello Hendrik,
No order message anymore, but the export times out. Last server Response counts up to 5... and then ends without message.
You need to increase the maximum execution time on the server then.
The Joomla debug console, section database queries, shows some duplicate queries and quiet often the message
NO INDEX KEY COULD BE USED.
That is normal but you don't see the CSVI query here anyway.
Do you have some hints how to solve this?
As I said above, if it times out, you need an increase in the maximum execution time. You can double check if the export works fine by setting the number of records to export to 10 for example. Just to see if the export completes.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
thanks so far! Even with two orders and 3 fields to export it is not working.
Since this not yet the final server, I will check the execution time issue with the final server first and will then reply here.
Best regards, Hendrik
Hello Hendrik,
Make sure you don't have any JS errors during export. This will affect the export as well.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
no exceptions found in csvi.js, but in total 12 exceptions in jquery.js and mootools-core.js when calling this export function once... perhaps not related to this issue?
I tried to export with a different browser as well, same result.
Thanks & best regards
Hendrik
Hello Hendrik,
but in total 12 exceptions in jquery.js and mootools-core.js when calling this export function once... perhaps not related to this issue?
Can you post a screenshot of that? One thing you need to know about JS is, if it finds an error, it stops executing the rest. So it may very well be that this is your problem.
The same result in another browser is expected if it is JS, since it means the loading of JS is not correct. Often caused by faulty plugins.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
hmm, for now I have moved the site to the final webserver.
Now at least I get an error message, with the small export:
Error
Table 'db562188883.pxv6t_virtuemart_paymentmethods_' doesn't exist SQL=SELECT address_1, address_2, address_type FROM pxv6t_virtuemart_orders LEFT JOIN pxv6t_virtuemart_order_items ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_order_items.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_order_userinfos AS user_info1 ON pxv6t_virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_orderstates ON pxv6t_virtuemart_orders.order_status = pxv6t_virtuemart_orderstates.order_status_code LEFT JOIN pxv6t_virtuemart_product_manufacturers ON pxv6t_virtuemart_order_items.virtuemart_product_id = pxv6t_virtuemart_product_manufacturers.virtuemart_product_id LEFT JOIN pxv6t_virtuemart_manufacturers ON pxv6t_virtuemart_product_manufacturers.virtuemart_manufacturer_id = pxv6t_virtuemart_manufacturers.virtuemart_manufacturer_id LEFT JOIN pxv6t_users ON pxv6t_users.id = user_info1.virtuemart_user_id LEFT JOIN pxv6t_virtuemart_countries ON pxv6t_virtuemart_countries.virtuemart_country_id = user_info1.virtuemart_country_id LEFT JOIN pxv6t_virtuemart_invoices ON pxv6t_virtuemart_orders.virtuemart_order_id = pxv6t_virtuemart_invoices.virtuemart_order_id LEFT JOIN pxv6t_virtuemart_paymentmethods_ ON pxv6t_virtuemart_orders.virtuemart_paymentmethod_id = pxv6t_virtuemart_paymentmethods_.virtuemart_paymentmethod_id LEFT JOIN pxv6t_virtuemart_shipmentmethods ON pxv6t_virtuemart_orders.virtuemart_shipmentmethod_id = pxv6t_virtuemart_shipmentmethods.virtuemart_shipmentmethod_id ORDER BY address_1, address_2, address_type
Do you have an idea how to proceed?
Thanks & best regards
Hendrik
Hello Hendrik,
Table 'db562188883.pxv6t_virtuemart_paymentmethods_' doesn't exist
Notice the _ and nothing after it. This means no language has been selected in the export template. Check your template if you have selected a language and save the template. The language should probably be Danish I guess?
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hi Roland,
:woohoo: yepp, you really got it, somehow I forget to double-check the export template language, it was pointing to English (default) instead of German (the only language in this shop), this was the reason for the last error!
And I have checked once again: Before this, execution time limits on the server (just a starter package) are the most likely reason for no export response at all, even with 3 fields only.
Now, on the final server (provider 1&1), export with 35 fields are working stable
Thanks & best regards, Hendrik
Log in to reply to this topic.