We help IT Professionals succeed at work.

Send email when table row NOT inserted

Bobby
Bobby used Ask the Experts™
on
I have a MySQL database table which I need to monitor for inserts, or specifically lack thereof. One of the fields in the row is a DATETIME, so I was hoping to be able to set up a cron job which would look in the table every 30 minutes and send an email if there is no new record within the last half hour. Can anyone help point the way? Thanks very much.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Make a shell, perl, ruby or whatever script that calls "select count(datetime) from table_name where datetime > date_add(now(), interval -30 minute)".  If it returns zero records then send your alert.

Try it out first with your favorite scripting language, let us know if you encounter problems while doing so.
Sandeep KothariProject Lead
Commented:
ya ...what help you need ... you actually said everything...

1. write a script to check if there is no new record in the last 30 min.
2. wirte a mail function in the script to send mail
3. set up a cron job to execute the script after every 30 min.
Top Expert 2012
Commented:
Also, make sure your datetime column is indexed

Author

Commented:
johanntagle:

Here's my exact query:

select count(date_purchased) from orders where date_purchased > date_add(now(), interval -1 minute)

but that returns nothing, no matter what i set the minute value to. Shouldn't it return a count of how many are in the last 30 minutes, if there are any? for my test, there definitely were records within the last 30 minutes.
Retrieve the primary key/ID column along with date_purchased so we can see what the problem might be.
SELECT order_id, date_purchased FROM orders ORDER BY order_id DESC limit 5

Open in new window

Most Valuable Expert 2011
Top Expert 2016
Commented:
table which I need to monitor for inserts  Don't you control the scripts that do the INSERT queries?

It sounds more like you want to monitor the table for the absence of any inserts, right?  If that is the case you probably want to use ALTER TABLE to add a column of the data type TIMESTAMP.  You can do this with DATETIME, of course, but TIMESTAMP is automatic.

Your decision to trigger the email would come from something like this:

$old = date('c', strtotime('Now - 30 minutes'));
$sql = "SELECT my_timestamp_column FROM my_table WHERE my_timestamp_column >= '$old' LIMIT 1";
$res = mysql_query($sql) or die( mysql_error() );
$num = mysql_num_rows($res);
if (!$num) /* SEND EMAIL - NOTHING CAME IN DURING THE LAST HALF HOUR */