23.4 Using the Event Scheduler

23.4.1 Event Scheduler Overview

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 to avoid confusion with triggers of the type discussed in *note 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:

• In MySQL, an event is uniquely identified by its name and the schema to which it is assigned.

• An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a note 'BEGIN ... END': begin-end. block if desired (see note sql-compound-statements::). An event's timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval, and the schedule for a recurring event can be assigned a specific start day and time, end day and time, both, or neither. (By default, a recurring event's schedule begins as soon as it is created, and continues indefinitely, until it is disabled or dropped.)

  If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the 'GET_LOCK()' function, or row or table locking.

• Users can create, modify, and drop scheduled events using SQL statements intended for these purposes. Syntactically invalid event creation and modification statements fail with an appropriate error message. A user may include statements in an event's action which require privileges that the user does not actually have. The event creation or modification statement succeeds but the event's action fails. See *note events-privileges:: for details.

• Many of the properties of an event can be set or modified using SQL statements. These properties include the event's name, timing, persistence (that is, whether it is preserved following the expiration of its schedule), status (enabled or disabled), action to be performed, and the schema to which it is assigned. See *note alter-event::.

  The default definer of an event is the user who created the event, unless the event has been altered, in which case the definer is the user who issued the last note 'ALTER EVENT': alter-event. statement affecting that event. An event can be modified by any user having the 'EVENT' privilege on the database for which the event is defined. See note events-privileges::.

• An event's action statement may include most SQL statements permitted within stored routines. For restrictions, see *note stored-program-restrictions::.

 File: manual.info.tmp, Node: events-configuration, Next: events-syntax, Prev: events-overview, Up: event-scheduler

23.4.2 Event Scheduler Configuration

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 *note 'SHOW PROCESSLIST': 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. It has one of the following values, which affect event scheduling as described:

• 'OFF': The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of *note 'SHOW PROCESSLIST': show-processlist, and no scheduled events execute. 'OFF' is the default 'event_scheduler' value.

  When the Event Scheduler is stopped ('event_scheduler' is 'OFF'), it can be started by setting the value of 'event_scheduler' to 'ON'. (See next item.)

• 'ON': The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.

  When the Event Scheduler is 'ON', the event scheduler thread is listed in the output of *note 'SHOW PROCESSLIST': show-processlist. as a daemon process, and its state is represented as shown here:

    mysql> SHOW PROCESSLIST\G
    *************************** 1. row ***************************
         Id: 1
       User: root
       Host: localhost
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    *************************** 2. row ***************************
         Id: 2
       User: event_scheduler
       Host: localhost
         db: NULL
    Command: Daemon
       Time: 3
      State: Waiting for next activation
       Info: NULL
    2 rows in set (0.00 sec)

  Event scheduling can be stopped by setting the value of 'event_scheduler' to 'OFF'.

• 'DISABLED': This value renders the Event Scheduler nonoperational. When the Event Scheduler is 'DISABLED', the event scheduler thread does not run (and so does not appear in the output of *note 'SHOW PROCESSLIST': show-processlist.). In addition, the Event Scheduler state cannot be changed at runtime.

If the Event Scheduler status has not been set to 'DISABLED', 'event_scheduler' can be toggled between 'ON' and 'OFF' (using note 'SET': set-variable.). 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 note 'mysql': 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 note 'SELECT': select. or note 'SHOW VARIABLES': 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

Important:

It is possible to set the Event Scheduler to 'DISABLED' only at server startup. If 'event_scheduler' is 'ON' or 'OFF', you cannot set it to 'DISABLED' at runtime. Also, if the Event Scheduler is set to 'DISABLED' at startup, you cannot change the value of 'event_scheduler' at runtime.

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

• As a command-line option when starting the server:

    --event-scheduler=DISABLED

• In the server configuration file ('my.cnf', or 'my.ini' on Windows systems), include the line where it can be read by the server (for example, in a '[mysqld]' section):

    event_scheduler=DISABLED

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.

Note:

You can issue event-manipulation statements when 'event_scheduler' is set to 'DISABLED'. 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 'ON' (or '1'). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied.

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 *note events-syntax::.

MySQL provides an note 'EVENTS': information-schema-events-table. 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 note events-metadata::, and *note information-schema-events-table::, for more information.

For information regarding event scheduling and the MySQL privilege system, see *note events-privileges::.

 File: manual.info.tmp, Node: events-syntax, Next: events-metadata, Prev: events-configuration, Up: event-scheduler

23.4.3 Event Syntax

MySQL provides several SQL statements for working with scheduled events:

• New events are defined using the note 'CREATE EVENT': create-event. statement. See note create-event::.

• The definition of an existing event can be changed by means of the note 'ALTER EVENT': alter-event. statement. See note alter-event::.

• When a scheduled event is no longer wanted or needed, it can be deleted from the server by its definer using the note 'DROP EVENT': drop-event. statement. See note drop-event::. Whether an event persists past the end of its schedule also depends on its 'ON COMPLETION' clause, if it has one. See *note create-event::.

  An event can be dropped by any user having the 'EVENT' privilege for the database on which the event is defined. See *note events-privileges::.

 File: manual.info.tmp, Node: events-metadata, Next: events-status-info, Prev: events-syntax, Up: event-scheduler

23.4.4 Event Metadata

To obtain metadata about events:

• Query the 'event' table of the 'mysql' database.

• Query the note 'EVENTS': information-schema-events-table. table of the 'INFORMATION_SCHEMA' database. See note information-schema-events-table::.

• Use the note 'SHOW CREATE EVENT': show-create-event. statement. See note show-create-event::.

• Use the note 'SHOW EVENTS': show-events. statement. See note show-events::.

Event Scheduler Time Representation

Each session in MySQL has a session time zone (STZ). This is the session 'time_zone' value that is initialized from the server's global 'time_zone' value when the session begins but may be changed during the session.

The session time zone that is current when a note 'CREATE EVENT': create-event. or note 'ALTER EVENT': alter-event. statement executes is used to interpret times specified in the event definition. This becomes the event time zone (ETZ); that is, the time zone that is used for event scheduling and is in effect within the event as it executes.

For representation of event information in the 'mysql.event' table, the 'execute_at', 'starts', and 'ends' times are converted to UTC and stored along with the event time zone. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. The 'last_executed' time is also stored in UTC.

If you select information from 'mysql.event', the times just mentioned are retrieved as UTC values. These times can also be obtained by selecting from the Information Schema note 'EVENTS': information-schema-events-table. table or from note 'SHOW EVENTS': show-events, but they are reported as ETZ values. Other times available from these sources indicate when an event was created or last altered; these are displayed as STZ values. The following table summarizes representation of event times.

Value 'mysql.event' note 'EVENTS': information-schema-events-table.note 'SHOW EVENTS': show-events. Table

Execute at

UTC ETZ ETZ

Starts

UTC ETZ ETZ

Ends

UTC ETZ ETZ

Last executed

UTC ETZ n/a

Created

STZ STZ n/a

Last altered

STZ STZ n/a

 File: manual.info.tmp, Node: events-status-info, Next: events-privileges, Prev: events-metadata, Up: event-scheduler

23.4.5 Event Scheduler Status

The Event Scheduler writes information about event execution that terminates with an error or warning to the MySQL Server's error log. See *note events-privileges:: for an example.

To obtain information about the state of the Event Scheduler for debugging and troubleshooting purposes, run note 'mysqladmin debug': mysqladmin. (see note mysqladmin::); after running this command, the server's error log contains output relating to the Event Scheduler, similar to what is shown here:

 Events status:
 LLA = Last Locked At  LUA = Last Unlocked At
 WOC = Waiting On Condition  DL = Data Locked

 Event scheduler status:
 State      : INITIALIZED
 Thread id  : 0
 LLA        : n/a:0
 LUA        : n/a:0
 WOC        : NO
 Workers    : 0
 Executed   : 0
 Data locked: NO

 Event queue status:
 Element count   : 0
 Data locked     : NO
 Attempting lock : NO
 LLA             : init_queue:95
 LUA             : init_queue:103
 WOC             : NO
 Next activation : never

In statements that occur as part of events executed by the Event Scheduler, diagnostics messages (not only errors, but also warnings) are written to the error log, and, on Windows, to the application event log. For frequently executed events, it is possible for this to result in many logged messages. For example, for 'SELECT ... INTO VAR_LIST' statements, if the query returns no rows, a warning with error code 1329 occurs ('No data'), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs ('Result consisted of more than one row'). For either condition, you can avoid having the warnings be logged by declaring a condition handler; see *note declare-handler::. For statements that may retrieve multiple rows, another strategy is to use 'LIMIT 1' to limit the result set to a single row.

 File: manual.info.tmp, Node: events-privileges, Prev: events-status-info, Up: event-scheduler

23.4.6 The Event Scheduler and MySQL Privileges

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 privileges sufficient to set global system variables. See *note system-variable-privileges::.

The 'EVENT' privilege governs the creation, modification, and deletion of events. This privilege can be bestowed using note 'GRANT': grant. For example, this note 'GRANT': 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, the table is empty. Since the user 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:

 2013-09-24T12:41:31.261992Z 25 [ERROR] Event Scheduler:
 [jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
 'jon'@'ghidora' for table 'mytable'
 2013-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
 [jon@ghidora].[myschema.e_store_ts] event execution failed.
 2013-09-24T12:41:41.271796Z 26 [ERROR] Event Scheduler:
 [jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
 'jon'@'ghidora' for table 'mytable'
 2013-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
 [jon@ghidora].[myschema.e_store_ts] event execution failed.

Since this user very likely does not have access to the error log, it is possible to verify whether the event's action statement is valid by executing it directly:

 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 *note 'EVENTS': information-schema-events-table. 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: 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 *note 'REVOKE': 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 does not delete or disable any events that may have been created by that user.

An event is not migrated or dropped as a result of renaming or dropping the user who created it.

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:

• 'DROP USER jon@ghidora;'

• 'RENAME USER jon@ghidora TO someotherguy@ghidora;'

You can verify that this is true by examining the 'mysql.event' table (discussed later in this section) or the Information Schema note 'EVENTS': information-schema-events-table. table before and after issuing a note 'DROP USER': drop-user. or *note 'RENAME USER': rename-user. statement.

Event definitions are stored in the 'mysql.event' table. 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 name = 'e_insert';

It is very important to match the event name and database schema name when deleting rows from the 'mysql.event' table. This is because different events of the same name can exist 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, note 'GRANT': 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 note 'GRANT EVENT': grant. and 'REVOKE EVENT' statements perform the required operations on them.

Five status variables provide counts of event-related operations (but not of statements executed by events; see *note stored-program-restrictions::). These are:

• 'Com_create_event': The number of *note 'CREATE EVENT': create-event. statements executed since the last server restart.

• 'Com_alter_event': The number of *note 'ALTER EVENT': alter-event. statements executed since the last server restart.

• 'Com_drop_event': The number of *note 'DROP EVENT': drop-event. statements executed since the last server restart.

• 'Com_show_create_event': The number of *note 'SHOW CREATE EVENT': show-create-event. statements executed since the last server restart.

• 'Com_show_events': The number of *note 'SHOW EVENTS': show-events. statements executed since the last server restart.

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

 File: manual.info.tmp, Node: views, Next: stored-objects-security, Prev: event-scheduler, Up: stored-objects