Events in MySQL
How about having a database-only crontab? What if there is a requirement to run certain DB operations on a predetermined schedule? MySQL events (added since version 5.1.6) is the solution.
Events vs. Triggers
Sure, triggers can be used to schedule DB operations. However, they are limited in the sense that, a trigger can be fired only before or after certain operations. e.g., before or after an INSERT. On the other hand, events offer time based and periodic scheduling which is independent of the inherent DB operations.
Getting Started
Before getting your hands dirty, we need to make sure that MySQL events is enabled. In other words, the event_scheduler should be running. This is required as it comes disabled by default.
Before directly enabling the MySQL events it is good to check the current
status. One of the following two queries would do the job.
mysql> SHOW VARIABLES LIKE "EVENT_SCHEDULER";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
If above query returns the value
ON, you are good to go. Otherwise, you have to enable it manually as shown below.
mysql> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "EVENT_SCHEDULER";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
Another query to check if events are enabled is
mysql> SHOW PROCESSLIST;
+------+-----------------+----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+----------------+------+---------+------+------------------------+------------------+
| 2 | usr | host.net:38456 | db12 | Sleep | 1357 | | NULL |
| 1365 | usr | localhost | db12 | Query | 0 | NULL | SHOW PROCESSLIST |
| 1366 | event_scheduler | localhost | NULL | Daemon | 18 | Waiting on empty queue | NULL |
+------+-----------------+----------------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
We can explicitly see the event_scheduler running from the above output. Another option to ensure the scheduler starts when MySQL is launched is with the command-line option
--event-scheduler=ON
Setting
event_scheduler=ON in your MySQL configuration file (my.cnf or my.ini on Windows) will enable the event_scheduler to be running by default.
Writing an event
The syntax is almost similar to the one used to write a stored procedure in MySQL. The intention here is to invoke a (pre-written) stored procedure (This call can be replaced by one or more SQL statements as per the requirement) at a specific time.
DELIMITER $$
CREATE EVENT ScheduleWM
ON SCHEDULE AT '2012-03-13 22:03:00'
DO
BEGIN
call ManageWM();
END $$
DELIMITER ;
Scheduling can be done in multiple ways:
- Run once at an absolute date/time:
AT ‘YYYY-MM-DD HH:MM.SS’
e.g. AT ’2011-06-01 02:00.00′
- Run once at a relative time period:
AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
e.g. AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
Easy? Now, many such events can exist on the database. To list them all, the following query can be used.
mysql> show events;
Empty set (0.00 sec)
Surprise... There are no events!
The reason being that, the event has finished executing at the scheduled time and it is gone. The default behavior of an event is such that, it gets deleter from the server after a successful execution.
See it for yourself.
mysql> show create event ScheduleWM\G
*************************** 1. row ***************************
Event: ScheduleWM
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`sms`@`localhost` EVENT `ScheduleWM` ON SCHEDULE AT '2012-03-13 23:03:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
call ManageWM();
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
You can observe a line that says,
ON COMPLETION NOT PRESERVE. This was not part of the event we creates. So this has to be the default behavior added to it. Now we can play with this to make out event immortal :). Just as you guessed; preserve it on completion.
DELIMITER $$
CREATE EVENT ScheduleWM
ON SCHEDULE AT '2012-03-13 22:03:00'
ON COMPLETION PRESERVE
DO
BEGIN
call ManageWM();
END $$
DELIMITER ;
Does it make much sense, to preserve an event, that is meant for a one-time execution? After all default behaviors do make some sense. A much more meaningful use case to preserve an event would be a repetitive event. Let us create one such event. Or... why don't we edit the event, that we had created before (if it still exists)? Even better!
DELIMITER $$
ALTER EVENT ScheduleWM
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
call ManageWM();
END $$
DELIMITER ;
We have now scheduled the event to run every 1 minute. Till when?
Periodic scheduling, again can be done is multiple ways:
- Run at specific intervals forever:
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
e.g. EVERY 1 DAY
- Run at specific intervals during a specific period:
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date
e.g. EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ’2012-01-01 00:00.00′
What if we want to stop our altered event to stop running? It still keeps running once a minute. There are 2 ways. We could alter it once again to set the STARTS and ENDS clause. Or... we could just delete the event.
mysql> DROP EVENT ScheduleWM;
Query OK, 0 rows affected (0.00 sec)
mysql> show events;
Empty set (0.00 sec)
One last thing we can do with an event.
mysql> ALTER EVENT ScheduleWM RENAME TO NewScheduleWM;
Query OK, 0 rows affected (0.00 sec)
No prizes for guessing what this does!
