Categories
View Replies (5)

Resolved SET SQL_BIG_SELESQLCTS=1

  1. nkarner
    nkarner
  2. RO CSVI
  3. Tuesday, 16 February 2016
  4.  Subscribe via email
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`
0
Responses (5)
rolandd
rolandd
Accepted Answer Pending Moderation
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
  1. more than a month ago
  2. RO CSVI
  3. # 1
nkarner
nkarner
Accepted Answer Pending Moderation
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
  1. more than a month ago
  2. RO CSVI
  3. # 2
rolandd
rolandd
Accepted Answer Pending Moderation
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
  1. more than a month ago
  2. RO CSVI
  3. # 3
nkarner
nkarner
Accepted Answer Pending Moderation
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!?
  1. more than a month ago
  2. RO CSVI
  3. # 4
rolandd
rolandd
Accepted Answer Pending Moderation
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
  1. more than a month ago
  2. RO CSVI
  3. # 5
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!