How to send email from MySQL 5.1

views:

3991

answers:

+3 Q:

How to send email from MySQL 5.1

I'm trying to send an email to several recipients when a new row is inserted into a table. The list of recipients varies. I would like to be able to set this list using a select statement. I also have installed Navicat which allows me to send email notifications but only to a predetermined set of people.

Thanks.

+4 A:

I would be very concerned about putting the load of sending e-mails on my database server (small though it may be). I might suggest one of these alternatives:

Have application logic detect the need to send an e-mail and send it.
Have a MySQL trigger populate a table that queues up the e-mails to be sent and have a process monitor that table and send the e-mails.

Jim Blizard 2008-12-22 22:02:45

Thanks for the response! About option number 2, how would you implement it?

David Jimenez 2008-12-22 23:05:08

You create a table that can hold the email message you're sending (maybe columns for subject line and body; but maybe you'll just refer to "form email, id=2") and then you have a program that checks this table every so often for new rows, and emails out if it finds any.

Max Lybbert 2008-12-23 22:44:35

Just putting it out there, but Microsoft has their Database Mail component which runs on the same server as the database (in normal scenarios at least). It's a different process/thread but on the same server. Just sayin'.

colithium 2010-05-31 05:04:56

+1 A:

I agree with Jim Blizard. The database is not the part of your technology stack that should send emails. For example, what if you send an email but then roll back the change that triggered that email? You can't take the email back.

It's better to send the email in your application code layer, after your app has confirmed that the SQL change was made successfully and committed.

Bill Karwin 2008-12-22 22:12:15

More good reasons to avoid this sort of solution.

Jim Blizard 2008-12-22 22:26:50

One tricky scenario is how to notify by email when an action occurs as a result of cascading updates, or by a change made inside a trigger.

Bill Karwin 2008-12-22 22:36:36

I saw this thread and had a follow up question. I could design a set of tools to manage the emails in my code layer, but imagine there are a number of tools to do so. In the case described above, the tools should be able to work off of a custom table or view. How the emails to be sent got there is determined as described above (perhaps from several triggers and other sources.

2009-03-26 10:51:19

ansaurus

tags:

views:

answers:

How to send email from MySQL 5.1

related questions