MAX_JOIN_SIZE rows email customers not working

[paddy_uk2007]

Hi i have a client using zen cart and has tried to send an email from the website and they now get the following error message


1104 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
in:
[SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM customers c LEFT JOIN orders o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.



I contacted my hosting provider and they gave me the following info


MySQL will refuse to execute select statements that process a very large number of rows. You should either adjust your select statement to make it more efficient, or set SQL_BIG_SELECTS=1 in the session before executing the large select statement. Use something like this to do that ..

$db->sql_query("SET OPTION SQL_BIG_SELECTS=1");

but i have no idea what this means??? what do I need to do to increase this so it will fix the problem??

I then contacted them again saying i didnt understand how to do this and they said the following

You need to send . You can either do it on the page throwing the error, or set it on a global level by including the query after the database connection. The syntax is likely to differ depending on the software in use, so if you are having trouble using this you may want to review the software documentation or community forums as its very likely this has occurred in the past

am still as lost as i first was, how do i send "SET OPTION SQL_BIG_SELECTS=1" before the query is executed??

can anyone help please,

below is a screen shot of how the contact customers pag now looks

[paddy_uk2007]

can anyone help me please?

[DrByte]

Admin->Configuration->Email Options->Audience-Select Count Display --- set this to false.

Audience-Select Count Display
When displaying lists of available audiences/recipients, should the recipients-count be included?
(This may make things slower if you have a lot of customers or complex audience queries)

[paddy_uk2007]

Admin->Configuration->Email Options->Audience-Select Count Display --- set this to false.

Hi ive change this but this hasnt fixed it, when i click on email customers its still displaying the error like the image below

[DrByte]

That query is from the pulldown menu that offers various customer-lists to send to, and specifically refers to the one that deals with "customers who have never placed an order".

It's very rare that you'd encounter such an error using that query unless you've done something to mass-import hundreds or thousands of customers into your database without any corresponding purchases associated with them. Or maybe mass-deleted a huge number of orders, thus orphaning the customers.

When did this problem first start happening?
What's been changed on your site lately?

[paddy_uk2007]

That query is from the pulldown menu that offers various customer-lists to send to, and specifically refers to the one that deals with "customers who have never placed an order".

It's very rare that you'd encounter such an error using that query unless you've done something to mass-import hundreds or thousands of customers into your database without any corresponding purchases associated with them. Or maybe mass-deleted a huge number of orders, thus orphaning the customers.

When did this problem first start happening?
What's been changed on your site lately?

Hi it was all working fine then my client put an email together to send to all the newsletter subscribers. she then clicked send then got this error message and now its permanently there when they click on email customers.... how will i remove / fix this so they can then send emails again?? nothing has been changed on the site.

any help would be appreciated
thanks

[DrByte]

This doesn't seem like something caused by Zen Cart. More likely something your hosting company did to restrict limits because others were abusing them.

You have a few options, all of which are destructive:
a) start deleting records from the query_builder table in your database. This will remove various options from the available lists of recipients in admin screens.
b) delete customers which have never made purchases
or, less destructive:
c) follow the SET SQL_BIG_SELECTS=1 suggestion by your hosting company, by writing custom code to run the SQL statement, perhaps in a custom file you'd place in your /admin/includes/extra_configures/ folder.

Make backups before you do anything.

[paddy_uk2007]

This doesn't seem like something caused by Zen Cart. More likely something your hosting company did to restrict limits because others were abusing them.

You have a few options, all of which are destructive:
a) start deleting records from the query_builder table in your database. This will remove various options from the available lists of recipients in admin screens.
b) delete customers which have never made purchases
or, less destructive:
c) follow the SET SQL_BIG_SELECTS=1 suggestion by your hosting company, by writing custom code to run the SQL statement, perhaps in a custom file you'd place in your /admin/includes/extra_configures/ folder.

Make backups before you do anything.

hi thanks for the info when you say the following

c) follow the SET SQL_BIG_SELECTS=1 suggestion by your hosting company, by writing custom code to run the SQL statement, perhaps in a custom file you'd place in your /admin/includes/extra_configures/ folder.


do you have any idea on how to do this?? could you provide me with the coding for the file??

please let me know.

thanks

[DrByte]

I'm assuming that the SQL instructions your hosting company have provided are correct. If so, then you could put the following into a new file as shown:

Put the file here:
/your-renamed-admin/includes/functions/extra_functions/my_custom_big_selects_fix.php
("your-renamed-admin" refers to whatever you've renamed your /admin/ folder to)

<?php
// added because hosting company is flagging errors on big selects
$db->Execute("SET SQL_BIG_SELECTS=1");

Note that I used the "extra_functions" folder, instead of the "extra_configures" folder I'd suggested earlier, due to a chicken/egg situation that would occur.

ALSO NOTE: THIS IS JUST A BANDAGE, and does NOT really fix the true root cause, which I suspect is damaged data as described earlier.