MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

Scheduled tasks of this type are also sometimes known as “temporal triggers”, implying that these are objects that are triggered by the passage of time. While this is essentially correct, we prefer to use the term events in order to avoid confusion with triggers of the type discussed in Chapter 21, Triggers. Events should more specifically not be confused with “temporary triggers”. Whereas a trigger is a database object whose statements are executed in response to a specific type of event that occurs on a given table, a (scheduled) event is an object whose statements are executed in response to the passage of a specified time interval.

While there is no provision in the SQL Standard for event scheduling, there are precedents in other database systems, and you may notice some similarities between these implementations and that found in the MySQL Server.

MySQL Events have the following major features and properties:

Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the SUPER privilege in the output of SHOW PROCESSLIST, as shown in the discussion that follows.

The global variable event_scheduler determines whether the Event Scheduler is enabled on the server. Beginning with MySQL 5.1.11, it can have one of these 3 values, which affect event scheduling as follows:

Provided that the event scheduler thread has not been deactivated by starting the server with --event-scheduler=0, you can enable the execution of scheduled events by issuing either of the following two statements:

SET GLOBAL event_scheduler = 1;

or

SET @@global.event_scheduler = 1;

If event scheduling is enabled, you can suspend it by issuing either one of these two statements:

SET GLOBAL event_scheduler = 2;

or

SET @@global.event_scheduler = 2;

Important: Once the server has been started with the event scheduler thread deactivated, the thread cannot be activated — that is, if the server was started with --event-scheduler=0, then event_scheduler cannot be set to 1 or 2 — without restarting mysqld. Similarly, if the server was started with the event scheduler thread running — that is, with --event-scheduler=1 or --event-schedule=2 — you cannot stop the event scheduler thread by issuing SET @@global.event_scheduler = 0;. Attempting to start or stop the event scheduler thread while the server is running causes an error, as shown here:

mysql> SELECT @@global.event_scheduler;
+--------------------------+
| @@global.event_scheduler |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SET @@global.event_scheduler = 0;
ERROR 1231 (42000): Variable 'event_scheduler' can't be set to the value of '0'
mysql> SET @@global.event_scheduler = 1;
Query OK, 0 rows affected (0.01 sec)

Since event_scheduler is a global variable, you must have the SUPER privilege to set its value.

The event scheduler thread cannot be killed. If you attempt to KILL the event scheduler thread, the effect is the same as suspending it. It can be re-enabled by setting the global event_scheduler variable to 1, just as if you had previously set its value to 2. The only way to stop this thread is by stopping the server.

Note: You can issue event-manipulation statements when event_scheduler is set to 0 or 2. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to 1. Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied.

Note: Prior to MySQL 5.1.11, event_scheduler could take one of only the 2 values 0|OFF or 1|ON, the default value being 0|OFF. It was also possible to start and stop the event scheduler thread while the MySQL server was running. For information concerning the reasons for this change in behaviour, see Bug#17619.

For SQL statements used to create, alter, and drop events, see Section 22.2, “Event Scheduler Syntax”.

MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to find out about the events which exist on the server. See Section 22.3, “Event Metadata”.

For information regarding event scheduling and the MySQL privilege system, see Section 22.4, “The Event Scheduler and MySQL Privileges”.

MySQL 5.1.6 and later provides several SQL statements for working with scheduled events:

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

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

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

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

CREATE EVENT e_hourly
    ON SCHEDULE 
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

DELIMITER |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day.'
    DO
      BEGIN
        INSERT INTO site_activity.totals (when, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*) 
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

DELIMITER ;

DELIMITER |

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 |

DELIMITER ;

CREATE EVENT e_call_myproc
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + 1 DAY
    DO CALL myproc(5, 27); 

ALTER EVENT event_name
    [ON SCHEDULE schedule]
    [RENAME TO new_event_name]
    [ON COMPLETION [NOT] PRESERVE]
    [COMMENT 'comment']
    [ENABLE | DISABLE]
    [DO sql_statement]

mysql> ALTER EVENT no_such_event 
     >     ON SCHEDULE 
     >       EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'

CREATE EVENT myevent
    ON SCHEDULE 
      EVERY 6 HOUR
    COMMENT 'A sample comment.'
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

ALTER EVENT myevent
    ON SCHEDULE 
      EVERY 12 HOUR 
    STARTS CURRENT_TIMESTAMP + 4 HOUR;

ALTER EVENT myevent
    DISABLE;

ALTER TABLE myevent
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO
      TRUNCATE TABLE myschema.mytable;

ALTER EVENT myevent
    RENAME TO yourevent;

ALTER EVENT oldschema.myevent
    RENAME TO newschema.myevent;

DROP EVENT [IF EXISTS] event_name

Information about events can be obtained as follows:

To enable or disable the execution of scheduled events, it is necessary to set the value of the global event_scheduler variable. This requires the SUPER privilege.

MySQL 5.1.6 introduces a privilege governing the creation, modification, and deletion of events, the EVENT privilege. This privilege can be bestowed using GRANT. For example, this GRANT statement confers the EVENT privilege for the schema named myschema on the user jon@ghidora:

GRANT EVENT ON myschema.* TO jon@ghidora;

(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.)

To grant this same user the EVENT privilege on all schemas would require the following statement:

GRANT EVENT ON *.* TO jon@ghidora;

The EVENT privilege has schema-level scope. Therefore, trying to grant it on a single table results in an error as shown:

mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used

It is important to understand that an event is executed with the privileges of its definer, and that it cannot perform any actions for which its definer does not have the requisite privileges. For example, suppose that jon@ghidora has the EVENT privilege for myschema. Suppose also that this user has the SELECT privilege for myschema, but no other privileges for this schema. It is possible for jon@ghidora to create a new event such as this one:

CREATE EVENT e_store_ts
    ON SCHEDULE 
      EVERY 10 SECOND
    DO 
      INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());

The user waits for a minute or so, and then performs a SELECT * FROM mytable; query, expecting to see several new rows in the table. Instead, he finds that the table is empty. Since he does not have the INSERT privilege for the table in question, the event has no effect.

If you inspect the MySQL error log (hostname.err), you can see that the event is executing, but the action it is attempting to perform fails, as indicated by RetCode=0:

060209 22:39:44 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0
060209 22:39:54 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0
060209 22:40:04 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0

Since this user very likely does not have access to the error log, he can verify whether the event's action statement is valid by running it himself:

mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user 
'jon'@'ghidora' for table 'mytable'

Inspection of the INFORMATION_SCHEMA.EVENTS table shows that e_store_ts exists and is enabled, but its LAST_EXECUTED column is NULL:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
     >     WHERE EVENT_NAME='e_store_ts'
     >     AND EVENT_SCHEMA='myschema'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: myschema
      EVENT_NAME: e_store_ts
         DEFINER: jon@ghidora
      EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: INTERVAL_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)

(Note: Prior to MySQL 5.1.12, there was no EVENT_DEFINITION column, and EVENT_BODY contained the SQL statement or statements to be executed. See Section 24.20, “The INFORMATION_SCHEMA EVENTS Table”, for more information.)

To rescind the EVENT privilege, use the REVOKE statement. In this example, the EVENT privilege on the schema myschema is removed from the jon@ghidora user account:

REVOKE EVENT ON myschema.* FROM jon@ghidora;

Important: Revoking the EVENT privilege from a user account does not delete or disable any events that may have been created by that account.

For example, suppose that that user jon@ghidora has been granted the EVENT and INSERT privileges on the myschema schema. This user then creates the following event:

CREATE EVENT e_insert
    ON SCHEDULE 
      EVERY 7 SECOND
    DO 
      INSERT INTO myschema.mytable;

After this event has been created, root revokes the EVENT privilege for jon@ghidora. However, e_insert continues to execute, inserting a new row into mytable each seven seconds.

Event definitions are stored in the mysql.event table, which was added in MySQL 5.1.6. To drop an event created by another user account, the MySQL root user (or another user with the necessary privileges) can delete rows from this table. For example, to remove the event e_insert shown previously, root can use the following statement:

DELETE FROM mysql.event
    WHERE db = 'myschema' 
      AND definer = 'jon@ghidora' 
      AND name = 'e_insert';

It is very important to match the event name, database schema name, and user account when deleting rows from the mysql.event table. This is because the same user can create different events of the same name in different schemas.

Note: The namespace for scheduled events changed in MySQL 5.1.12. Prior to that MySQL version, different users could create different events having the same name in the same database; in MySQL 5.1.12 and later, that is no longer the case. When upgrading to MySQL 5.1.12 or later from MySQL 5.1.11 or earlier, it is extremely important to make sure that no events in the same database share the same name, prior to performing the upgrade.

Users' EVENT privileges are stored in the Event_priv columns of the mysql.user and mysql.db tables. In both cases, this column holds one of the values 'Y' or 'N'. 'N' is the default. mysql.user.Event_priv is set to 'Y' for a given user only if that user has the global EVENT privilege (that is, if the privilege was bestowed using GRANT EVENT ON *.*). For a schema-level EVENT privilege, GRANT creates a row in mysql.db and sets that row's Db column to the name of the schema, the User column to the name of the user, and the Event_priv column to 'Y'. There should never be any need to manipulate these tables directly, since the GRANT EVENT and REVOKE EVENT statement perform the required operations on them.

MySQL 5.1.6 introduces five status variables providing counts of event-related operations (but not of statements executed by events — see Section 22.5, “Event Scheduler Limitations and Restrictions”). These are:

You can view current values for all of these at one time by running the statement SHOW STATUS LIKE '%event%';.

This section lists restrictions and limitations applying to event scheduling in MySQL.

In MySQL 5.1.6, any table referenced in an event's action statement must be fully qualified with the name of the schema in which it occurs (that is, as schema_name.table_name).

An event may not be created, altered, or dropped by a trigger, stored routine, or another event. This is by design. However, an event may create, alter, or drop triggers and stored routines.

Event timings using the intervals YEAR, QUARTER, MONTH, and YEAR_MONTH are resolved in months; those using any other interval are resolved in seconds. There is no way to cause events scheduled to occur at the same second to execute in a given order. In addition — due to rounding, the nature of threaded applications, and the fact that a non-zero length of time is required to create events and to signal their execution — events may be delayed by as much as 1 or 2 seconds. However, the time shown in the INFORMATION_SCHEMA.EVENTS table's LAST_EXECUTED column or the mysql.event table's last_executed column is always accurate to within one second of the time the event was actually executed. (See also Bug#16522.)

Execution of event statements have no affect on the server's statement counts such as Com_select and Com_insert that are displayed by SHOW STATUS.

Prior to MySQL 5.1.12, you could not view another user's events in the INFORMATION_SCHEMA.EVENTS table. In other words, any query made against this table was treated as though it contained the condition DEFINER = CURRENT_USER() in the WHERE clause.

Events cannot be created with a start time that is in the past.

Events do not support times later than the end of the Unix Epoch; this is approximately the end of the year 2037). Prior to MySQL 5.1.8, handling in scheduled events of dates later than this was buggy; starting with MySQL 5.1.8, such dates are specifically disallowed by the Event Scheduler. (Bug#16396)

In MySQL 5.1.6, INFORMATION_SCHEMA.EVENTS shows NULL in the SQL_MODE column. Beginning with MySQL 5.1.7, the SQL_MODE displayed is that in effect when the event was created.

In MySQL 5.1.6, the only way to drop or alter an event created by a user who was not the definer of that event was by manipulation of the mysql.event system table by the MySQL root user or by another user with privileges on this table. Beginning with MySQL 5.1.7, DROP USER drops all events for which that user was the definer; also beginning with MySQL 5.1.7 DROP SCHEMA drops all events associated with the dropped schema.

As with stored routines, events are not migrated to the new schema by the RENAME SCHEMA (or RENAME DATABASE) statement. See Section 13.1.15, “RENAME DATABASE Syntax”.

Beginning with MySQL 5.1.8, event names are handled in case-insensitive fashion. For example, this means that you cannot have two events in the same database and with the same definer, and having the names anEvent and AnEvent. Important: If you have events created in MySQL 5.1.7 or earlier, which are assigned to the same database and have the same definer, and whose names differ only with respect to lettercase, then you must rename these events to respect case-sensitive handling before upgrading to MySQL 5.1.8 or later.