Chapter 21. Event Scheduler

Chapter 21. Event Scheduler
Prev		Next

21.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 in order to avoid confusion with triggers of the type discussed in Chapter 20, 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:

An event is uniquely identified by: its name; the schema to which it is assigned; and the user who created it (definer).
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 BEGIN ... END block if desired (see Section 19.2.5, “BEGIN ... END Compound Statement Syntax”). An event's timing can be either transient or recurrent. A transient 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.)
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 Section 21.4, “The Event Scheduler and MySQL 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 Section 21.2.2, “ALTER EVENT Syntax”.
The definer of an event cannot be changed; it is always the user who created the event. An event can be modified only by the event's definer, or by a user having privileges on the mysql.event table (see Section 21.4, “The Event Scheduler and MySQL Privileges”.)
An event's action statement may include most SQL statements permitted within stored routines.

MySQL 5.1.6 introduces a global variable event_scheduler which determines whether the Event scheduler is enabled for the server. This variable defaults to OFF or 0, meaning that event scheduling is not available:

mysql> SHOW GLOBAL VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

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

In order to enable event scheduling, you must first issue one of these statements:

SET GLOBAL event_scheduler = ON;

or

SET GLOBAL event_scheduler = 1;

or

SET @@global.event_scheduler = ON;

or

SET @@global.event_scheduler = 1;

Note: You can issue event-manipulation statements when event_scheduler is set to OFF or 0. No warnings or errors are generated in such cases (so long as the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON or 1. (Once this has been done, all events whose scheduling conditions are met become active.)

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

You can also enable event scheduling by starting mysqld with --event_scheduler=1 or more simply --event_scheduler. (1 is the default value in this case.)

For SQL statements used to create, alter, and drop events, see Section 21.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 21.3, “Event Metadata”.

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

21.2. Event Scheduler Syntax

21.2.1. CREATE EVENT Syntax
21.2.2. ALTER EVENT Syntax
21.2.3. DROP EVENT Syntax

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

New events are defined using the CREATE EVENT statement. See Section 21.2.1, “CREATE EVENT Syntax”.
The definition of an existing event can be changed by means of the ALTER EVENT statement. See Section 21.2.2, “ALTER EVENT Syntax”.
When a scheduled event is no longer wanted or needed, it can be deleted from the server by its definer using the DROP EVENT statement. See Section 21.2.3, “DROP EVENT Syntax”. (Whether an event persists past the end of its schedule also depends on its ON COMPLETION clause, if it has one. See Section 21.2.1, “CREATE EVENT Syntax”.)
An event can be deleted by a user other than its definer, but in this case, the user performing the the deletion must have the necessary privileges on the mysql.event table. See Section 21.4, “The Event Scheduler and MySQL Privileges”.

21.2.1. `CREATE EVENT` Syntax

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}

This statement creates and schedules a new event. The minimum requirements for a valid CREATE EVENT statement are as follows:

The keywords CREATE EVENT plus an event name, which uniquely identifies the event from among those created by the current user in the current schema.
An ON SCHEDULE clause, which determines when and how often the event executes.
A DO clause, which contains the SQL statement to be executed by an event.

This is an example of a minimal CREATE EVENT statement:

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

The previous statement creates an event named myevent. This event executes once — one hour following its creation — by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1.

The event_name must be a valid MySQL identifier with a maximum length of 64 characters. It may be delimited using back ticks, and may be qualified with the name of a database schema. An event is associated with both a MySQL user (the definer) and a schema, and its name must be unique among all events created by that user within that schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 9.2, “Database, Table, Index, Column, and Alias Names”.

If no schema is indicated as part of event_name, then the default (current) schema is assumed. The definer is always the current MySQL user. It is possible for two different users to create different events having the same name on the same database schema. For example, the users jon@ghidora and stefan@athena may each create an event named myevent on the database schema named myschema. These are entirely different events.

Note: MySQL uses case-insensitive comparisons when checking for the uniqueness of event names. This means that, for example, you cannot have two events named myevent and MyEvent created by the same MySQL user in the same database schema.

IF NOT EXISTS functions in the much the same fashion with CREATE EVENT as it does when used with a CREATE TABLE statement; if an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated.)

The ON SCHEDULE clause determines when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms:

AT timestamp is used for a transient event. It specifies that the event executes one time only at the date and time, given as the timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value which is of either the DATETIME or TIMESTAMP type for this purpose. The timestamp must also be in the future — you cannot schedule an event to take place in the past. Trying to do so fails with an error, as shown here:
```
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-02-10 23:59:01 |
+---------------------+
1 row in set (0.04 sec)

mysql> CREATE EVENT e_totals
    ->     ON SCHEDULE AT '2006-02-10 23:59:00'
    ->     DO INSERT INTO test.totals VALUES (NOW());
ERROR 1522 (HY000): Activation (AT) time is in the past
```
CREATE EVENT statements which are themselves invalid — for whatever reason — fail with an error.
You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created.
In order to create an event which occurs at some point in the future relative to the current date and time — such as that expressed by the phrase “three weeks from now” — you can use the optional clause + INTERVAL interval. The interval portion consists of two parts, a quantity and a unit of time, and follows the same syntax rules that govern intervals used in the DATE_ADD() function (see Section 12.5, “Date and Time Functions”. The units keywords are also the same, except that you cannot use any units involving microseconds when defining an event.
You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL.
For actions which are to be repeated at a regular interval, you can use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous dicussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”.
It is not possible to combine + INTERVAL clauses in a single EVERY clause; however, you can use the same complex time units allowed in a + INTERVAL. For example, “every two minutes and ten seconds” can be expressed as EVERY '2:10' MINUTE_SECOND.
An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event.
An EVERY clause may also contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value which tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to “every twelve hours, beginning thirty minutes from now, and ending four weeks from now”. Not using ENDS means that the event continues executing indefinitely.
ENDS supports the same syntax for complex time units as STARTS does.
You may use STARTS, ENDS, both, or neither in an EVERY clause.
Note: Where STARTS or ENDS is given as a datetime value, it is taken to mean local time on the server. However, the values for both of these are reported using Universal Time in the INFORMATION_SCHEMA.EVENTS and mysql.event tables, as well as in the output from SHOW EVENTS. For additional information, see Section 23.20, “The INFORMATION_SCHEMA EVENTS Table”.

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default non-persistent behavior explicit.

You can create an event but keep it from being active using the DISABLE keyword. Alternatively, you may use ENABLE to make explicit the default status, which is active. This is most useful in conjunction with ALTER EVENT (see Section 21.2.2, “ALTER EVENT Syntax”).

You may supply a comment for an event using a COMMENT clause. comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.

The DO clause specifies an action carried by the event, and consists of an SQL statement. Nearly any valid MySQL statement which can be used in a stored routine can also be used as the action statement for a scheduled event. (See Section I.1, “Restrictions on Stored Routines and Triggers”.) For example, the following event e_hourly deletes all rows from the sessions table once per hour, where this table is part of the site_activity schema:

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

Note: The SHOW statement and SELECT statements that merely return a result set have no effect when used in an event; the output from these is not sent to the MySQL Monitor, nor is it stored anywhere. However, you can use statements such as SELECT INTO and INSERT ... SELECT that store a result. (See the next example in this section for an instance of the latter.)

Note: Any reference to a table in the DO clause must be qualified with the name of the schema in which the table occurs.

As with stored routines, you can use multiple statements in the DO clause by bracketing them with the BEGIN and END keywords, as shown here:

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 ;

Note the use of the DELIMITER statement to change the statement delimiter, as with stored routines. See Section 19.2.1, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.

More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct:

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 ;

There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters:

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

In addition, if the event's definer has the SUPER privilege, that event may read and write global variables. As granting this privilege entails a potential for abuse, extreme care must be taken in doing so.

Generally, any statements which are valid in stored routines may be used for action statements executed by events. For more information about statements allowable within stored routines, see Section 19.2, “Stored Routine Syntax”. You can create an event as part of a stored routine, but an event cannot be created by another event.

21.2.2. `ALTER EVENT` Syntax

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

The ALTER EVENT statement is used to change one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the ON SCHEDULE, ON COMPLETION, COMMENT, ENABLE / DISABLE, and DO clauses is exactly the same as when used with CREATE EVENT. (See Section 21.2.1, “CREATE EVENT Syntax”.)

ALTER EVENT works only with an existing event:

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

In each of the following examples, assume that the event named myevent is defined as shown here:

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

The following statement changes the schedule for myevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:

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

To disable myevent, use this ALTER EVENT statement:

ALTER EVENT myevent
    DISABLE;

It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by myevent to one that deletes all records from mytable; it also changes the schedule for the event such that it executes once, one day after this ALTER EVENT statement is run.

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

To rename an event, use the ALTER EVENT statement's RENAME TO clause, as shown here:

ALTER EVENT myevent
    RENAME TO yourevent;

The previous statement renames the event myevent to yourevent. (Note: There is no RENAME EVENT statement.)

You can also move an event to a different schema using ALTER EVENT ... RENAME TO ... and schema_name.table_name notation, as shown here:

ALTER EVENT oldschema.myevent
    RENAME TO newschema.myevent;

It is necessary to include only those options in an ALTER EVENT statement which correspond to characteristics that you actually wish to change; options which are omitted retain their existing values. This includes any default values for CREATE EVENT such as ENABLE.

21.2.3. `DROP EVENT` Syntax

DROP EVENT [IF EXISTS] event_name

This statement drops the event named event_name. The event immediately ceases being active, and is deleted completely from the server.

If the event does not exist, the error ERROR 1517 (HY000): Unknown event 'event_name' results. You can override this and cause the statement to fail silently by using IF EXISTS.

21.3. Event Metadata

Information about events can be obtained as follows:

Querying the EVENTS table of the INFORMATION_SCHEMA database. See Section 23.20, “The INFORMATION_SCHEMA EVENTS Table”.
Using of the SHOW EVENTS and SHOW FULL EVENTS statements. See Section 13.5.4.13, “SHOW EVENTS”.
Using the SHOW CREATE EVENT statement. See Section 13.5.4.5, “SHOW CREATE EVENT”.
A record of events executed on the server can be read from the MySQL Server's error log (see Section 21.4, “The Event Scheduler and MySQL Privileges” for an example).

21.4. 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 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: 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;

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, 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.
Different users can create different events having the same name in the same schema.

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 21.5, “Event Scheduler Limitations and Restrictions”). These are:

Com_create_event: The number of CREATE EVENT statements executed since the last server restart.
Com_alter_event: The number of ALTER EVENT statements executed since the last server restart.
Com_drop_event: The number of DROP EVENT statements executed since the last server restart.
Com_show_create_event: The number of SHOW CREATE EVENT statements executed since the last server restart.
Com_show_events: The number of 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%';.

21.5. Event Scheduler Limitations and Restrictions

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.

The resolution of event schedules is measured 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.

In MySQL 5.1.6, you may not view another user's events in the INFORMATION_SCHEMA.EVENTS table. In other words, any query made against this table is treated as though it contains 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. In 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 is not the definer of that event is 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.

Prev		Next
Chapter 20. Triggers	Home	Chapter 22. Views