MySQL Event Scheduler, or MySQL's Built-in Event Scheduler

MySQL 5.1 introduced its own task scheduler! Previously, I and many other developers had to use cron for purposes such as “cleaning up outdated information” or “recalculating counters” in the database, and so on.

Enabling the Scheduler

The scheduler is disabled by default. To enable it, add the following to my.ini or my.cnf:

[mysqld]
event_scheduler = on

You can also start the scheduler for a particular session in the MySQL console:

mysql> set global event_scheduler = ON;

To check whether the scheduler is running, execute this query:

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

The official configuration manual.

Creating a Task

The syntax is fairly simple. When creating a task, you must specify:

• The task name (event_name)
• The interval or exact time (schedule and interval)
• The SQL query to execute (sql_statement)

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

You can also specify:

• DEFINER - the name of the user to run the task as (with that user’s privileges, accordingly)
• COMMENT - a comment
• ON COMPLETION PRESERVE - the task is retained after execution
• ON COMPLETION NOT PRESERVE - the task runs once and is then removed from the task list (the default)

Example of the short form:

CREATE EVENT `myevent`
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
      ON COMPLETION PRESERVE
    DO
      UPDATE `myschema`.`mytable` SET `mycol` = `mycol` + 1;

The official CREATE EVENT manual

Modifying a Task

When modifying a task, you can change its owner, rename it, change its SQL query, and modify its other properties.

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO sql_statement]

To display all the task’s properties, use:

SELECT * FROM `INFORMATION_SCHEMA`.`EVENTS`
    WHERE `EVENT_NAME` = 'myevent' AND EVENT_SCHEMA = 'myschema'\G

*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: myschema
      EVENT_NAME: myevent
         DEFINER: john@smith
      EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE: NULL
          STARTS: 0000-00-00 00:00:00
            ENDS: 0000-00-00 00:00:00
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2006-02-09 22:36:06
    LAST_ALTERED: 2006-02-09 22:36:06
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)

Or use:

show events like "event_name%"\G

The official ALTER EVENT manual

Deleting a Task

DROP EVENT [IF EXISTS] event_name

The official DROP EVENT manual

The Event Scheduler and Privileges in MySQL

The official manual on setting privileges

Usage Examples

Task: Delete user accounts that have not been activated within 3 days. The event needs to run every day.

CREATE EVENT `not_active_users`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      DELETE FROM `users`
        WHERE `active` = 'not', UNIX_TIMESTAMP(`date`) >= UNIX_TIMESTAMP(NOW()) - (3 * 24 * 3600);

Task: Now let’s change the time interval for the “not_active_users” task.

ALTER EVENT `not_active_users`
    ON SCHEDULE
      EVERY 12 HOUR;

Task: Delete the task.

DROP EVENT `not_active_users`;

Official manual pages: Using the Event Scheduler