We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

New Event Feature

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 event_scheduler on.

event_scheduler

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

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

The 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 [+ interval integer_value time_keyword ]
EVERY interval
EVERY interval STARTS timestamp
EVERY interval ENDS timestamp
EVERY interval STARTS timestamp ENDS timestamp

"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.

For example:

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.

This event:

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

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, and sql_statement:

ALTER EVENT event1
  ON SCHEDULE EVERY 5 WEEK
  COMMENT 'This happens every 5 weeks'
  DO DROP TABLE t1;

DROP EVENT

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

mysql.event

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 like SHOW EVENTS, SHOW EVENT STATUS, or SELECT ... FROM INFORMATION_SCHEMA.EVENTS.

For example:

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:

the db column shows the name of the database that contains the event
the name column holds the name of the event; recall that it must be unique within the database
the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the created column shows the timestamp when the event was created
the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
the comment column shows the comment included in the CREATE EVENT statement

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.

Privileges

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'.

Finding the thread

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.

Things we threw in to make it harder

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.