Email Module - cron error on large number of recipients

[tomhousley]

Hello,

I have just tried to send a bunch of emails to people on my mailing list, I used shared hosting and cron emails me the output of the script.

Before sending the email to all my intended recipients, I tried it with 6 or so and had no problems.

The output of the script is this:

Code: Select all

Sending mail for job 10 ...PHP Fatal error:  Error calculating the number of related records there are for the relationship 'recipients' in the table 'cms_emails'.  There was a problem performing the sql query 'SELECT COUNT(*) as num from `cms_emailsubscribers_newsletter` as `r` inner join `cms_emailsubscribers_newsletter__cms_emails` as `j` on `r`.`ema_email` = `j`.`recipient_email` inner join `cms_emails` as `e` on `e`.`id` = `j`.`messageid` where `e`.`id` = '6' and `sent` = 0'.  The MYSQL error returned was '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>On line 477 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function printStackTrace()
<br>On line 393 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function numRelatedRecords(recipients,sent=0)
<br>On line 575 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php on line 477

Fatal error: Error calculating the number of related records there are for the relationship 'recipients' in the table 'cms_emails'.  There was a problem performing the sql query 'SELECT COUNT(*) as num from `cms_emailsubscribers_newsletter` as `r` inner join `cms_emailsubscribers_newsletter__cms_emails` as `j` on `r`.`ema_email` = `j`.`recipient_email` inner join `cms_emails` as `e` on `e`.`id` = `j`.`messageid` where `e`.`id` = '6' and `sent` = 0'.  The MYSQL error returned was '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>On line 477 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function printStackTrace()
<br>On line 393 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function numRelatedRecords(recipients,sent=0)
<br>On line 575 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php on line 477

The email list had 2383 recipients.

Any ideas on how I can get rond this?

Many thanks, Tom

[jhenry]

Tom,

You can add the following to the select statement to add the SQL_BIG_SELECT function:

Code: Select all

SET SESSION SQL_BIG_SELECTS=1;
SELECT COUNT(*) as num from `cms_emailsubscribers_newsletter` ... <the rest of the query>;

This would add the function to just this select statement. Hope this helps.

Jason