The other day at work we had a request from the client to send an e-blast (mass e-mail) to all the email addresses listed in our database (~55k emails).

In the past this was done using a procedure in the oracle database which used one of our smtp mail servers and oracle UTL_SMTP package. But due to a missing smtp mail server we could not run the procedure. We tried changing the mail server ip from a non-working one to a working one, but this didnt work. We kept getting an error that said

ORA-24247 network access denied by access control list (ACL)

As this was a time critical task and we did not have time to figure out where the missing server went, I created a PHP script that retrieves the email addresses from the oracle database and sends the message to all of them one by one using the mail() method in PHP. This when tested worked great. I estimated the script runtime to be 4 hours. I started the script at 630 PM and came back next morning and it was still running!

The script had already sent 50 – 300 messages to each recipient by then. To stop the php script I stopped the PHP page which was running all night. This did NOT stop the script in the backend. To stop the mailing process I went through google and figured out that the mail() process used the sendmail service on the redhat server. So I manually stopped the sendmail service which stopped the mails from sending.

I then asked the network administrator to check if there were any mails in the SMTP server queue. There were 1000s of messages in queue. I requested him to stop all the messages in the queue but the messages with RETRY status could not be stopped. There was a unapplied patch that needed to be run to make this work. At least the mails was not sending the mail but stuck at the queue.

After 3 days when I restarted the sendmail service on the server, it resumed sending the messages to all the recipients in the queue. This increased concerns. I then researched a little bit more and came to know that the sendmail service in redhat has its own mail queue which can be viewed using the mailq command in linux or using the sendmail –v -q command. There were 181k messages in queue waiting to be sent. All the queueud messages were stored in the folder /var/spool/mqueue. (Reference)

To delete all the messages in queue I ran the command rm /var/spool/mqueue but this didn’t work and gave me an error. “/bin/rm: Argument list too long” . This was probably due to the limitations of rm command to have a length of arguments as a max of 1024. The alternate command (reference) to delete all the 181k files is “find . -name ‘*’ | xargs rm”. This deletes all the files in the current directory regardless of the number of files.

This way the spooled messages were cleared from the queue but the problem the database procedure not able to access the mail server still existed. So I went through various google articles referring to the ORA-24247 error. I came to know that this error was due to an extra security layer in oracle 11g. There is an XML table in  the oracle 11g database that tells the packages about particular accessible server ips. This article helped me out in understanding the extra security ACL list information and updating it to serve my purpose.

This way I fixed the oracle script access to the smtp server and solved the mailq problem with the e-blast messaging project. Just shared it to help others fix this problem if the come across it.