Daily Alert for No New Database Records

Question

How would one set up an email alert to be sent, if there are no new records inserted into a MySQL database by a certain time?

I have a simple table consisting of an id and a datetime field. Ideally I would like to receive an email alert if no records are inserted to the database by 10:00am every day. Is this at all feasible?

Thank you.

possible duplicate of [How to send email from MySQL 5.1](http://stackoverflow.com/questions/387483/how-to-send-email-from-mysql-5-1) — Shakti Singh, Feb 23 '12 at 11:41
@Click - there's more to the this question than just sending emails. So it's not an exact duplicate. — APC, Feb 23 '12 at 12:17

score 1 · Answer 1 · 2012-02-23T11:46:45.000

1

You seed to set up a cron job by sql query with datetime field for gettng records of 1 day interval.

It no record (count=0) then you need to execute code for sending mail alert.

Query wil be like below:

SELECT  count(id)
FROM    mytable
WHERE   datetime_field_date BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()

Please do required changes.

edited Feb 23 '12 at 11:46

answered Feb 23 '12 at 11:40

Cron or the Windows equivalent which is Windows Task Scheduler. – APC Feb 23 '12 at 14:04

score 0 · Answer 2 · answered Feb 23 '12 at 11:44

0

You can set up a cron job

For ex

 0 10 * * * Your job over here

0 - minute

10 - hour

so Every day 10 AM cron will execute you job.

For More reference go through this site http://www.adminschoice.com/crontab-quick-reference

answered Feb 23 '12 at 11:44

sangeethkumar

821
1
7
17

score 0 · Accepted Answer · edited May 23 '17 at 11:45

Assuming you want to keep this entirely within the database, you need to create an EVENT which runs once a day (say 10:00:01). Find out more.

This event would execute a query to check whether any records have been received since the last time you checked. Something like the query proposed by Teez would suffice.

However, you might want to add a little more smarts into this , to allow for outages. If the database is down at 10.00.01 and the query ends up running at 10:05 you wouldn't want a message received at 10:04 to corrupt the result (or would you? depends on the business rules - perhaps that record should have been inserted at 09:59 but the database was down...)

Anyway, if the query count is zero then you want to send an alert. There is a project named MySQL Messages which provides APIs to do this. Check it out.

As there are a couple of calls here you'll need to bookend the statements with BEGIN and END.

If you do go down this route, make sure the event scheduler is switched on. Find out more.

Thank you! This is a very thorough reply which basically provides me with all the pieces I need to put together. — , Feb 23 '12 at 13:25

Daily Alert for No New Database Records

3 Answers3