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 24, 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 PROCESS privilege in the output of SHOW PROCESSLIST, as shown in the discussion that follows.

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. Beginning with MySQL 5.1.12, it has one of these 3 values, which affect event scheduling as described here:

If the Event Scheduler status has not been set to DISABLED, event_scheduler can be toggled between ON and OFF (using SET). It is also possible to use 0 for OFF, and 1 for ON when setting this variable. Thus, any of the following 4 statements can be used in the mysql client to turn on the Event Scheduler:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

Similarly, any of these 4 statements can be used to turn off the Event Scheduler:

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

Although ON and OFF have numeric equivalents, the value displayed for event_scheduler by SELECT or SHOW VARIABLES is always one of OFF, ON, or DISABLED. DISABLED has no numeric equivalent. For this reason, ON and OFF are usually preferred over 1 and 0 when setting this variable.

Note that attempting to set event_scheduler without specifying it as a global variable causes an error:

mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL

To disable the event scheduler, use one of the following two methods:

To enable the Event Scheduler, restart the server without the --event-scheduler=DISABLED command-line option, or after removing or commenting out the line containing event_scheduler=DISABLED in the server configuration file, as appropriate. Alternatively, you can use ON (or 1) or OFF (or 0) in place of the DISABLED value when starting the server.

In MySQL 5.1.11, event_scheduler behaved as follows: this variable could take one of the values 0 (or OFF), 1 (or ON), or 2. Setting it to 0 turned event scheduling off, so that the event scheduler thread did not run; the event_scheduler variable could not be set to this value while the server was running. Setting it to 1 so that the event scheduler thread ran and executed scheduled events. In this state, the event scheduler thread appeared to be sleeping when viewed with SHOW PROCESSLIST. When event_scheduler was set to 2 (which was the default value), the Event Scheduler was considered to be “suspended”; the event scheduler thread ran and could be seen in the output of SHOW PROCESSLIST (where Suspended was displayed in the State column), but did not execute any scheduled events. The value of event_scheduler could be changed only between 1 (or ON) and 2 while the server was running. Setting it to 0 (or OFF) required a server restart, as did changing its value from 0 (or OFF) to 1 (or ON) or 2.

Prior to MySQL 5.1.11, event_scheduler could take one of only the 2 values 0|OFF or 1|ON, and the default value was 0|OFF. It was also possible to start and stop the event scheduler thread while the MySQL server was running.

For more information concerning the reasons for these changes in behaviour, see Bug#17619.

Beginning with MySQL 5.1.17, starting the MySQL server with the --skip-grant-tables option causes event_scheduler to be set to DISABLED, overriding any other value set either on the command line or in the my.cnf or my.ini file (Bug#26807).

For SQL statements used to create, alter, and drop events, see Section 25.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 obtain information about scheduled events which have been defined on the server. See Section 25.3, “Event Metadata”, and Section 27.20, “The INFORMATION_SCHEMA EVENTS Table”, for more information.

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

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

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]

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 TABLE myevent
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO
      TRUNCATE TABLE myschema.mytable;

ALTER EVENT myevent
    DISABLE;

ALTER EVENT myevent
    RENAME TO yourevent;

ALTER EVENT olddb.myevent
    RENAME TO newdb.myevent;

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}

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 + INTERVAL 1 DAY
    DO CALL myproc(5, 27); 

DROP EVENT [IF EXISTS] event_name

Information about events can be obtained as follows:

Information about the state of the Event Scheduler for debugging and troubleshooting purposes 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 system 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, use the following statement:

GRANT EVENT ON *.* TO jon@ghidora;

The EVENT privilege has global or 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)

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;

For example, suppose that the 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. The same would be true if root had issued either of these statements:

You can verify that this is true by examining the mysql.event table (discussed later in this section) or the INFORMATION_SCHEMA.EVENTS table (see Section 27.20, “The INFORMATION_SCHEMA EVENTS Table”) before and after issuing a DROP USER or RENAME USER statement.

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.

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 25.6, “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 5.1.

Qualification of identifiers.  In MySQL 5.1.6 only, 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).

Case sensitivity of event identifiers.  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 — prior to MySQL 5.1.12 — with the same definer) with the names anEvent and AnEvent.

Modification of events by stored routines and triggers.  An event may not be created, altered, or dropped by a trigger, stored routine, or another event. An event also may not create, alter, or drop triggers or stored routines. (Bug#16409, Bug#18896)

Resolution of event timings.  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.)

Effects on statement counts.  Each execution of the statements contained in the body of an event takes place in a new connection; thus, these statements has no effect in a given user session on the server's statement counts such as Com_select and Com_insert that are displayed by SHOW STATUS. However, such counts are updated in the global scope. (Bug#16422)

Visibility of events belonging to other users.  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.

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

Latest time supported.  Events do not support times later than the end of the Unix Epoch; this is approximately the beginning of the year 2038. 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)

Server SQL mode.  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.

Dropping or altering events.  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.

Database object references in ON SCHEDULE clauses.  References to stored functions, user-defined functions, and tables in the ON SCHEDULE clauses of CREATE EVENT and ALTER EVENT statements are not supported. Beginning with MySQL 5.1.13, these sorts of references are disallowed. (See Bug#22830 for more information.)

Disallowed statements.  Generally speaking, statements which are not permitted in stored routines or in SQL prepared statements are also not allowed in the body of an event. See Section D.1, “Restrictions on Stored Routines, Triggers, and Events”, and Section 12.7, “SQL Syntax for Prepared Statements”, for more information.

Upgrading to MySQL 5.1.18 or later.  When upgrading to MySQL 5.1.18 or later from a previous MySQL version where scheduled events were in use, the upgrade utilities mysql_upgrade and mysql_fix_privilege_tables do not accomodate changes in system tables relating to the Event Scheduler. As a workaround, you can dump events before the upgrade, then restore them from the dump afterwards. This issue was fixed in MySQL 5.1.20 (see Bug#28521).