Email Module - cron error on large number of recipients

Archived from the Xataface Users forum.

tomhousley — Tue Nov 09, 2010 12:05 pm

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’.

On line 477 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function printStackTrace()

On line 393 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function numRelatedRecords(recipients,sent=0)

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’.

On line 477 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function printStackTrace()

On line 393 of file /home/sites/example.com/public_html/xataface-1.2.5/Dataface/Record.php in function numRelatedRecords(recipients,sent=0)

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 — Sat Nov 13, 2010 7:35 am

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