The world's most popular open source database
By Trudy Pelzer
In this article, I'll give a preliminary description of a new MySQL feature for scheduling and executing tasks. In version 5.1.6, MySQL has added support for events. That is, you can now say: "I want the MySQL server to execute this SQL statement every day at 9:30am, until the end of the year" -- or anything similar that involves any number of SQL statements, and a schedule.
Note that events are new and still in alpha, so there is still a good chance that we'll have to make adjustments as people experiment with them. This article describes the state of affairs only for the 5.1.6 release of MySQL.
While we at MySQL prefer the term "events" to describe this feature, another name is "temporal triggers". Don't confuse this with "temporary" triggers though -- events are triggers that are executed at a specific time, rather than at a specific event on a specific table. Here's a simple example:
CREATE EVENT e /* Event name */ ON SCHEDULE EVERY 1 WEEK /* Interval */ DO INSERT INTO t VALUES (0); /* SQL statement */
The result of this statement is that, once every week starting today, MySQL will add a row to table t. This feature will remind Unix/Linux people of the "crontab job" concept; Windows users will recognize it as a "task scheduler". There's no official standard for events in the DBMS world, but MySQL's concept is reminiscent of Oracle's "Job Scheduler" and our syntax is occasionally the same as Sybase SQL Anywhere's CREATE EVENT syntax.
This feature is intended for database administrators. But anyone can use it for one-shot tasks that run in the background, or start at some low-system-activity moment. In that sense, it's like programming a videotape recorder. In other words, it can be tricky, so try to learn first, program later.
To create an event, use the CREATE EVENT statement. But before you
do, make sure you turn the
The MySQL event scheduler is a thread that runs in the background looking for events to execute. It spends a lot of time sleeping -- and won't do anything unless the new global variable "event_scheduler" is set to ON (1). So if you want to take advantage of this feature, do the following:
SET GLOBAL event_scheduler = 1;
To turn the feature off, do:
SET GLOBAL event_scheduler = 0;
As with all "SET GLOBAL" statements, you need the SUPER privilege to change the setting of the event_scheduler variable.
It is also possible to start the server with:
mysqld ... --event_scheduler=1
You can see what the current state of affairs is with
SHOW VARIABLES LIKE 'event_scheduler'; or SELECT @@event_scheduler;
Now let's look at the syntax for events.
CREATE EVENT [ IF NOT EXISTS ] event_name ON SCHEDULE schedule [ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLED | DISABLED ] [ COMMENT 'comment' ] DO sql_statement;
CREATE EVENT statement, um, creates an event.
The "event_name" must be a valid identifier of up to 64 characters (The_Main_Event, e44), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.
The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.
The possibilities are:
"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:
CREATE EVENT `Something To Do` ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY DO DROP TABLE t;
This event makes the MySQL server drop a table exactly 5 days from now.
CREATE EVENT The_Main_Event ON SCHEDULE AT TIMESTAMP '2006-01-20 12:00:00' DO DROP TABLE t;
This event makes the MySQL server drop a table on January 20, 2006 at exactly 12 o'clock.
"EVERY interval" means "Do this repeatedly". A recurring interval starts with EVERY, followed by a positive integer plus one of the keywords YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.
CREATE EVENT e ON SCHEDULE EVERY 1 YEAR DO DROP TABLE t;
This event makes MySQL drop table t once each year, starting now.
"EVERY interval [ STARTS timestamp1 ] [ ENDS timestamp2 ]" means "Do this repeatedly, starting at timestamp1 if it's specified, ending at timestamp2 if it's specified". The ENDS value must be later than the STARTS value, of course. For example, this event:
CREATE EVENT e ON SCHEDULE EVERY 1 YEAR STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY DO DROP TABLE t;
makes the DBMS drop a table once each year, starting exactly 5 days from now.
CREATE EVENT e ON SCHEDULE EVERY 1 YEAR ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR DO DROP TABLE t;
ensures the table is dropped once each year for five years, starting now.
And this event:
CREATE EVENT e ON SCHEDULE EVERY 1 YEAR STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR DO DROP TABLE t;
causes MySQL to drop a table once each year for five years, starting exactly 5 days from now.
ON COMPLETION [ NOT ] PRESERVE means "When the event execution finishes, then either preserve [or do not preserve] the event". So if you say ON COMPLETION NOT PRESERVE, then the event is a one-timer; it gets dropped once there is no further use for it. If you say ON COMPLETION PRESERVE, then the completed event stays in existence until you drop it deliberately. The default is NOT PRESERVE.
ENABLED | DISABLED means "Create the event in an enabled
state | create in a disabled state".
When an event is enabled, which is the default, then it is activated as
soon as the schedule specifies. When an event is disabled, it is always
inactive -- so, even if the scheduled execution time occurs, nothing
happens. Sometimes it's useful to create an event in one state, and
change the state using another statement, ALTER EVENT.
The "comment" is a string literal. The optional COMMENT clause is analogous to the COMMENT clause in CREATE TABLE ... COMMENT ...; it stores the string in the metadata for documentation purposes.
The "sql_statement" specifies what action will be taken when the event is executed. This is a single statement, but it can be a compound statement, as with stored routines and triggers -- i.e. BEGIN statement1; statement2; END. The general rule is: any SQL statement that can be executed from within a stored routine, can also be executed by an event. Here's a simple example:
CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END //
Note the delimiter at statement end: as is usual with a compound statement, you'll need to change the statement terminator from semicolon (;) if you want to create events using the mysql client (use the DELIMITER statement to do so). Otherwise, the semicolon terminators at the end of each SQL statement within the compound statement will be treated as if they terminate the CREATE EVENT statement.
ALTER EVENT event_name [ ON SCHEDULE schedule ] [ RENAME TO event_name2 ] [ ON COMPLETION [ NOT ] PRESERVE ] [ COMMENT 'comment' ] [ ENABLED | DISABLED ] [ DO sql_statement ]
To change an existing event, use the ALTER EVENT statement.
ALTER EVENT must name an event that already exists. The statement's clauses are the same as the CREATE EVENT clauses, but they are all optional. The settings of any clauses that you omit from ALTER EVENT stay as they were specified in the original CREATE EVENT statement (or the last ALTER EVENT statement executed for the event); the settings of any clauses you include change to the new values you specify.
Thus, if you write an ALTER EVENT statement with an ON SCHEDULE clause then the event's schedule changes; if you don't, the schedule remains the same.
ALTER EVENT also includes one clause that is not found in CREATE EVENT -- namely "RENAME TO event_name2". Use this clause to change the name of an event -- there is no RENAME EVENT statement.
Here's an example of an
ALTER EVENT that changes schedule, comment,
ALTER EVENT event1 ON SCHEDULE EVERY 5 WEEK COMMENT 'This happens every 5 weeks' DO DROP TABLE t1;
DROP EVENT [ IF EXISTS ] event_name;
The DROP EVENT statement should name an event that has already been created by CREATE EVENT. The event is destroyed.
As is usual with MySQL, if you omit the IF EXISTS clause and the event does not exist, the server returns an error:
mysql> DROP EVENT e6; ERROR 1513 (HY000): Unknown event 'e6'
If you're not sure that an event exists but want to drop it without getting a possible error message, do this:
mysql> DROP EVENT IF EXISTS e6; Query OK, 1 row affected, 1 warning
To store event metadata, there is a new table in the mysql database: mysql.event. If you don't see this table in your MySQL installation, run the mysql_fix_privilege_tables script to set it up. (When migrating from earlier versions of MySQL, you should always run this script to set up new objects and privileges that might have been added in the new version.)
The effect of CREATE EVENT is to "insert" a new row in mysql.event; the effect of ALTER EVENT is to "update" a row in mysql.event; the effect of DROP EVENT is to "delete" a row in mysql.event. But it's never a good idea to change the mysql.event table directly -- you should always use CREATE, ALTER, or DROP EVENT to make use of the event feature.
To get metadata information about all existing events, execute this statement:
SELECT * FROM mysql.event;
There is no other way to get event metadata. We do not support statements
SHOW EVENTS, SHOW EVENT STATUS, or SELECT ... FROM
mysql> CREATE EVENT e -> ON SCHEDULE EVERY 5 SECOND -> STARTS TIMESTAMP '2006-01-01 16:00:00' -> ENDS TIMESTAMP '2006-12-31 12:00:00' -> ON COMPLETION PRESERVE -> COMMENT 'runs every 5 seconds in 2006' -> DO INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10); Query OK, 1 row affected mysql> SELECT * FROM mysql.event\G *************************** 1. row *************************** db: tp name: e body: INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10) definer: tp@localhost execute_at: NULL interval_value: 5 interval_field: SECOND created: 2005-12-20 14:53:03 modified: 2005-12-20 14:53:03 last_executed: NULL starts: 2006-01-01 23:00:00 ends: 2006-12-31 20:00:00 status: ENABLED on_completion: PRESERVE comment: runs every 5 seconds in 2006 1 row in set (0.00 sec)
Here's how to read the result:
Look closely at the "STARTS TIMESTAMP '2006-01-01 16:00:00'" and ENDS TIMESTAMP '2006-12-31 12:00:00'" clauses in the example. In mysql.event, the "starts" and "ends" column values are 2006-01-01 23:00:00 and 2006-12-31 20:00:00 respectively, because the values are converted to UTC values.
Here's another example:
mysql> CREATE EVENT e1 -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE -> DISABLED -> DO INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 20); Query OK, 1 row affected mysql> SELECT * FROM mysql.event\G *************************** 2. row *************************** db: tp name: e1 body: INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 20) definer: tp@localhost execute_at: 2006-01-13 03:42:00 interval_value: NULL interval_field: NULL created: 2006-01-12 19:40:00 modified: 2006-01-12 19:40:00 last_executed: NULL starts: NULL ends: NULL status: DISABLED on_completion: DROP comment: 1 row in set (0.00 sec)
Note that the status column shows DISABLED, as specified in the CREATE EVENT statement and that the on_completion column shows DROP, to indicate that the event will not be preserved when it is finished; this is the default.
There is a new privilege for events: the EVENT privilege. You can GRANT it for one database, or for all databases:
GRANT EVENT ON database_name.* TO user [ , user ... ]; GRANT EVENT ON *.* TO user [ , user ... ];
You can also REVOKE it for one or all databases:
REVOKE EVENT ON database_name.* FROM user [ , user ... ]; REVOKE EVENT ON *.* FROM user [ , user ... ];
If you do not have the EVENT privilege, you cannot CREATE an event.
To find out who has an EVENT privilege, search the mysql.user table and look for users with Event_priv = 'Y'.
Every event executes in a thread of its own. Let's create a bad event that causes an infinite loop to run in the background. We'll see how to find that thread and stop the execution.
First make a procedure that has an infinite loop:
DELIMITER // CREATE PROCEDURE pe () BEGIN x: LOOP ITERATE x; END LOOP; END//
Next create an event that will invoke the procedure:
CREATE EVENT ee ON SCHEDULE EVERY 2 SECOND COMMENT 'This is a bad idea' DO CALL tp.pe() //
Now get the scheduler going:
DELIMITER ; SET GLOBAL event_scheduler = 1;
You won't see anything on your client screen when you do this; the event thread doesn't have a screen of its own. But, if you can go back to the console on which you started the mysqld server, you will see the event executing -- there will be a message indicating this.
You might wonder: why does this happen only once? Why doesn't the scheduler start a new thread every minute? Well, if we did things that way, then a bad event like this one would cause the scheduler to try to set up an infinite number of threads. So we decided that if a recurring event is still executing when it's time to do it again, the server won't open another thread and try to execute the event again.
You can see both the scheduler and the executing event with SHOW PROCESSLIST:
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: root Host: localhost db: tp Command: Query Time: 0 State: NULL Info: show processlist *************************** 2. row *************************** Id: 2 User: event_scheduler Host: db: NULL Command: Connect Time: 1 State: Sleeping Info: NULL *************************** 3. row *************************** Id: 31 User: root Host: db: tp Command: Connect Time: 0 State: NULL Info: CALL tp.pe( 3 rows in set (0.00 sec)
Since the event is in a loop, it's a good thing that SHOW PROCESSLIST tells us what the process number is. To stop this rogue-elephant process in its tracks, do this:
mysql> KILL 31; /* the process number */
Naturally this exercise is strictly theoretical, since nobody would ever make a bad event. That's why database administrators sleep well at night.
You can create two events on the same schedule, but there's no way to ensure the order in which they will occur. That is, if you say that event e1 should happen at 2007-01-01 00:00:00, and event e2 should start now and recur every 1 second, then e1 might happen before e2, or e1 might happen after e2 -- there is no way to force a specific order.
An event always runs with definer privileges. So if user Joe creates (defines) the event, then the thread which executes the event will act as if it's Joe. It will have whatever Joe's current privileges are, and the value of the CURRENT_USER variable will be 'Joe'.
You can put SHOW or SELECT statements in the event, but you'll never see the result. In Unix terms, the output is "directed to dev/null".
As with stored routines, execution of an event statement doesn't change the count of times that statement has occurred. So there is no effect on SHOW STATISTICS.