CODEWITHSUNDEEP
mysqlsqldatetimeevents
celestialorb
celestialorb

Reputation: 1989

MySQL Event Execution Based on Table DATETIME

Is is possible to have an event in MySQL run at every given instance of a DATETIME in a set (i.e. a column of a table)?

I have a table that needs to have a column updated based on a DATETIME in that row. A simplified view of that table is shown below:

+--------------+---------------------+------------------+
|      ID      |     Execution       |     Status       |
+--------------+---------------------+------------------+
|     5        | 2016-01-01+00:00:00 |     Sent         |
+--------------+---------------------+------------------+

||
||   when 2016/01/01+00:00:00 occurs
VV

+--------------+---------------------+------------------+
|      ID      |     Execution       |     Status       |
+--------------+---------------------+------------------+
|     5        | 2016-01-01+00:00:00 |     Executed     |
+--------------+---------------------+------------------+

I imagine the CREATE EVENT syntax would look something like:

CREATE EVENT <name> ON SCHEDULE AT (SELECT Execution FROM <table> WHERE Status = 'Sent')
DO BEGIN
  -- perform procedure here
END

If this is possible, it introduces some questions. How often would MySQL check for new events in this set, or would it be a static set pulled at the time of creation? How would it behave for datetimes in the past? Would it simply run on them? From these questions I presume that running on a dynamic set of datetimes is not possible in SQL.

Alternatively I suppose I could create an event to run once a second to update all 'Sent' rows to 'Executed' if their time is in the past, but I was wondering if there was a more 'real-time' way to do this.

Perhaps a better way would be to construct a view to show 'Executed' if Status is 'Sent' AND time is in the past?

Upvotes: 1

Views: 2398

Answers (2)

Peter Bowers
Peter Bowers

Reputation: 3093

The cron utility in linux will do this for you. I don't believe capabilities like this exist in mysql.

This command allows you to edit the crontab file

crontab -e

or

sudo crontab -e

Then add a new line that will look something like this:

* * * * * mysql -u x -p y script.sql

This will run once every minute and execute your script.sql. If you wanted to be kinder to your host you might run it once an hour:

0 * * * * mysql -u x -p y script.sql

Alternatively you could run a PHP script instead of the SQL script. Basically anything you can do from the command line can be done from cron. Just be aware that it will run as another user and with a different environment.

Alternatively (if you have access to cron on a different server but not on the server on which you want to run the scripts) you could use a completely different server (or easycron.com or setcronjob.com - no recommendation of these - I just found them with google) which does a wget of a PHP page on your server. That PHP page on your server could then execute the logic you want to do.

In each case you could obviously check your datetime column first to see if there is anything you want to do and then do it if it is found.

If you are running on the same server you could theoretically run each day at a certain interval (say at midnight) and query all the dates for that day and create at jobs at those times. So a cron script would create at jobs based on your data and then at would kick these off at the appropriate time.

Upvotes: 0

nvuono
nvuono

Reputation: 3363

The SCHEDULE AT syntax does not accept any sort of query according to the event creation syntax. You're limited to a timestamp and optional interval.

You could create a trigger on insert or update of a row in that table to create a new event at the future timestamp. However, you'll now have to worry about creating a unique event name for each future event and managing potentially large numbers of events in your system.

A better option in most cases would be to create a new event scheduled at a reasonable interval for your application that will query for ALL rows that need to be processed and then execute that logic.

Upvotes: 1

Related Questions