I have installed CSVI_Pro 6.5.2 today.
I want to export my virtuemart-orders, but I get the error seen below:
How can I Set SQL_BIG_SELECTS=1?
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay SQL=SELECT zv8oh_virtuemart_orders.virtuemart_order_id, order_number, user_info1.email, user_info1.first_name, zv8oh_virtuemart_orders.created_on, zv8oh_virtuemart_orders.order_status, zv8oh_virtuemart_orders.virtuemart_paymentmethod_id, coupon_discount, order_salesPrice, order_tax, order_shipment, order_shipment_tax, order_payment_tax, order_payment, order_total, order_billTaxAmount, order_billTax, address_1 FROM zv8oh_virtuemart_orders LEFT JOIN zv8oh_virtuemart_order_items ON zv8oh_virtuemart_orders.virtuemart_order_id = zv8oh_virtuemart_order_items.virtuemart_order_id LEFT JOIN zv8oh_virtuemart_order_userinfos AS user_info1 ON zv8oh_virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id LEFT JOIN zv8oh_virtuemart_orderstates ON zv8oh_virtuemart_orders.order_status = zv8oh_virtuemart_orderstates.order_status_code LEFT JOIN zv8oh_virtuemart_product_manufacturers ON zv8oh_virtuemart_order_items.virtuemart_product_id = zv8oh_virtuemart_product_manufacturers.virtuemart_product_id LEFT JOIN zv8oh_virtuemart_manufacturers ON zv8oh_virtuemart_product_manufacturers.virtuemart_manufacturer_id = zv8oh_virtuemart_manufacturers.virtuemart_manufacturer_id LEFT JOIN zv8oh_users ON zv8oh_users.id = user_info1.virtuemart_user_id LEFT JOIN zv8oh_virtuemart_countries ON zv8oh_virtuemart_countries.virtuemart_country_id = user_info1.virtuemart_country_id LEFT JOIN zv8oh_virtuemart_invoices ON zv8oh_virtuemart_orders.virtuemart_order_id = zv8oh_virtuemart_invoices.virtuemart_order_id LEFT JOIN zv8oh_virtuemart_paymentmethods_de_de ON zv8oh_virtuemart_orders.virtuemart_paymentmethod_id = zv8oh_virtuemart_paymentmethods_de_de.virtuemart_paymentmethod_id LEFT JOIN zv8oh_virtuemart_shipmentmethods ON zv8oh_virtuemart_orders.virtuemart_shipmentmethod_id = zv8oh_virtuemart_shipmentmethods.virtuemart_shipmentmethod_id LEFT JOIN zv8oh_virtuemart_products ON zv8oh_virtuemart_products.product_sku = zv8oh_virtuemart_order_items.order_item_sku WHERE zv8oh_virtuemart_orders.created_on >= '2014-10-01 00:00:00' AND zv8oh_virtuemart_orders.created_on <= '2016-12-31 00:00:00' ORDER BY zv8oh_virtuemart_orders.virtuemart_order_id
Hello,
CSVI actually sets the big select itself before starting the export, I guess your server ignores that. The only way would be to modify the database ini (configuration) file. How that needs to be done is something you need to ask your hosting provider as it all depends on how they setup their servers.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello,
OK, but I tested the following:
I executed the SQL-Statement via phpmyAdmin - without "SQL_BIG_SELECTS=1" - same error messages
with "SET SQL_BIG_SELECTS=1" - no error messages - sql statements works well
So I think, the server does not ignore the big_select.
Any other ideas?
best regards
Norbert
Hello Norbert,
Comparing PhpMyAdmin to Joomla is not an equal comparison, the question is, if you execute such queries via Joomla, if you get the same error. This is what CSVI is doing:
$this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
After that other queries are executed as well. Perhaps it only works on the next query in your case?
Any other ideas?
The real fix is to set this value in the MySQL configuration so it always works and no extra queries are needed. This is something your hosting provider can do.
Other idea is that you copy that line of code I just posted to the export file just after the line
// Execute the query
. That would be more a bandaid than anything else because on any update you will need to do this. That is why I suggest to use the real fix.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello,
my provider said he cannot set SQL_BIG_SELECTS=1 globaly.
So I tried to change
administrator/components/com_csvi/addon/com_virtuemart/model/export/order.php
as follows:
// Execute the query
$this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
$this->csvidb->setQuery($query, $limits['offset'], $limits['limit']);
$this->log->add('Export query' . $query->__toString(), false);
But I get the same error message.
I don't know, what I can do now!?
Hello,
my provider said he cannot set SQL_BIG_SELECTS=1 globaly.
Strange but not surprising if it doesn't even work on their server when executed via code.
But I get the same error message.
I don't know, what I can do now!?
Look for a better hosting provider, one that will allow you to execute queries.
Another option is to modify the export code by removing the tables you don't need. Looking at the query you can remove the tables product_manufacturers, manufacturers, shipmentmethods, paymentmethods_de_de, orderstates, countries, and invoices.
However, by removing these tables from the query, you may not be able to run other queries because they may need the tables.
One final other thought is, instead of using $this->db->, try using $this->csvidb-> to execute the big select query. Perhaps that may be of some help.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Log in to reply to this topic.