Sending bulk mail from MySQL -> PHP -> Exchange 2010 fails without any errors after a random number of sends.

Hi,

We are using WAMP to bulk send emails through our Exchange 2010 server using MySQL/PHP script (and mail.php) but every time I do a send, Exchange sends to some users then just stops without any errors.

I've tried adding a usleep(100000); to throttle it but I still have the issue and there's nothing in any logs on either Exchange or PHP to explain why.

It doesn't stop after a certain user/number of emails. Exchange does not have any send limits and I'm baffled. It is also very hard to test as I don't want to annoy our customers (at least the ones who receive the emails) with repeated testing.

The code I'm using is:

If anyone can suggest some extra error trapping I could add it would be most appreciated.


###############################################
// Function: Email Send
function email_alerter_send($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase, $email_body) {
#################################################	
//echo "send email function" . $email_body;
require_once "Mail.php";
require_once "Mail/mime.php";
$host = "EXCHANGE IP ADDRESS";
$port = "25";
$email_username = "user@ourdomain.com";
$email_password = "THEPASSWORD";
$from = "COMPANYNAME WebServices <administrator@ourdomain.com>";
$subject = "COMPANYNAME WebServices Notice";
$conn = new mysqli($SQLservername, $SQLusername, $SQLpassword, $SQLdatabase);
// Check connection
if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);} 	
$sql = "SELECT * FROM user ORDER BY first_name ASC";

$result = $conn->query($sql);
if ($result->num_rows > 0) {
	 while($row = $result->fetch_assoc()) {
$first_name = $row["first_name"];
$last_name = $row["last_name"];
$to = $row["email"];
$html = <<< HTML
<head>
	<style>

body {
    font-family: arial;
}
	body {background: #eee;  font-family: myFirstFont;}
	.container {background: #fff; width:90%; padding:auto; margin:auto; margin-top:20px;}
	.email_header {background:red; color: #fff; font-size:26px; text-align:center; padding:10px;}
	.email_body {padding:20px; font-size: 20px;}
	.email_footer {background:red; color: #fff; font-size:12px; text-align:center; padding:10px;}	
	</style>
</head>
<body>
<div class="container">
<div class="email_header">IMPORTANT SERVICE ANNOUNCEMENT</div>
<div class="email_body">
Dear $first_name, <br><br>
$email_body

<br><br><br>
Thank you,<br><br>
COMPANYNAME WebServices Team
<br><br>
</div>
<div class="email_footer">
ADDRESS
</div>
</div>
</body>
HTML;

 $headers = array ('From' => $from,
   'To' => $to,
   'Subject' => $subject);
   
$crlf = "\n";
$mime = new Mail_mime(['eol' => $crlf]);
$mime->setHTMLBody($html);
$body = $mime->get();
$headers = $mime->headers($headers);  
 $smtp = Mail::factory('smtp',
   array ('host' => $host,
   'port' => $port,
     'auth' => false,
     'username' => $email_username,
     'password' => $email_password));
 $mail = $smtp->send($to, $headers, $body);
 sleep(1);
 if (PEAR::isError($mail)) {
   echo("<p>" . $mail->getMessage() . "</p>");
   error_log("Failed to send an email to $to", 0);
  } else {
  	echo "Email sent to: " . $first_name." ".$last_name . " at: " .$to . "<br>";
  	 error_log("Email sent to $to", 0);
   echo("<p>Message successfully sent.</p>");
  }
		}
	}
}

Open in new window

LVL 1
LetterpartAsked:
Who is Participating?
 
gr8gonzoConnect With a Mentor ConsultantCommented:
Also, on a side note, I wouldn't suggest using a sleep() unless you're sending more than two or three emails to any single user. The sleep(1) makes the PHP process pause for 1 second, which can be good for rate-limiting, but it also GREATLY decreases your scalability and likely increases the amount of work Exchange has to do.

Exchange handles its own queuing automatically, so even though it may take a few milliseconds to "send" the message, you're really only adding the message to Exchange's SMTP queue and then Exchange will deliver it on its own schedule, which is USUALLY as-soon-as-possible.

Adding rate-limiting can potentially prevent Exchange from doing some of its own optimization. For example, let's say 2 messages are going to addresses that end in @abc.com. If Exchange has them both in its SMTP queue, it could potentially make one connection to the abc.com mail servers and then deliver both messages within that same connection. If you add in your own rate-limiting, Exchange might end up having to make two connections (one per message) instead of just the one.

Additionally, Exchange has its own rate-limiting and throttle logic that can be used to protect itself if there are any problems.

The only reason to add in your own rate-limiting/sleep mechanism is if you are sending so many emails that it's impacting the delivery times of unrelated, non-bulk emails (e.g. a normal user just trying to send out a normal email via Outlook and it is delayed a long time due to the massive amounts of email ahead of it in the queue). Even in that case, you might consider sleep()ing every 10 emails (or so) instead of every 1 email.

Moving on, here's a suggested change on your query - instead of ordering by first name, try this ORDER BY:

ORDER BY SUBSTRING_INDEX(email,'@',-1);

That should order your records based on the domain name, which might help out Exchange a little bit with its optimizations (depending on how fast you push in the messages and any sleep calls you make).

Finally, unless email is a non-nullable field in the database, I'd recommend you do some simple validation of the address before you proceed to use it. Assuming you already have some other validation in place in the scripts that collect the data in the first place, you could probably use an over-simplified validation and simply check to see if there's an @ symbol in the email address. That way, in case anyone manually adds in some testing/dummy/invalid records, you're not going to hit warnings/errors when you try to send it.

$to = $row["email"];
if(strpos($to, "@") === false) { continue; }

Adding the bolded line should just ensure there's an @ symbol and if there isn't, it will "continue" (which skips the rest of the current iteration / user record and continues onto the next record).

Obviously, it's not full validation, but again, I'm assuming you have some more thorough validation in your normal data collection processes (e.g. form validation), and the strpos() check is EXTREMELY fast, so it shouldn't impact the speed of your script.
0
 
Gaurav SinghConsultantCommented:
is the email you are using, is a mailbox on exhcange server?
0
 
gr8gonzoConnect With a Mentor ConsultantCommented:
Add this PHP to the top of your script:

set_time_limit(300);
error_reporting(E_ALL);
ini_set("display_errors", 1);

That should enable error output (if it is suppressed by default) and extend the default time limit to 5 minutes.

Then add an echo at the VERY end like this:
echo "Finished.";

That way, if the script ends early because it was interrupted, you should see an error, but if it ends early due to a logic error, then you'll see the "Finished" message at the end and can then start looking for the logic issue.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LetterpartAuthor Commented:
@Gaurav Singh

yes. it is a mailbox on the Exchange server.

@gr8gonzo

Thank you. I will add this and test on Friday when we are planning some system down time and need to advise the customers.
0
 
Gaurav SinghConnect With a Mentor ConsultantCommented:
whats the maximum recipients configurec on your exchange global transport settings?

Check below things:


Exchange Management Console , Organization Configuration, Hub Transport, click on Global Settings tab and then double click on Transport Settings check the max reciepient number
0
 
LetterpartAuthor Commented:
@Gaurav Singh

Hi, Max no of recipients is 5000. Way under our limit.

@gr8gonzo

Thank you. An excellent reply with background info & something to try. I will work through this today.
0
 
LetterpartAuthor Commented:
That's it working.

I had to increase the set_time_limit(300); to 900 (it actually took 9 minutes to send all 166 emails but we want a bit of a buffer).

I also added GROUP BY a.email to the SQL query to stop any duplicates being sent.

Thank you for your assistance. much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.