Chapter聽20.聽Event Scheduler - MySQL 5.0参考手册英文版

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聽19, 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 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:

When the server is runningevent_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

Important: It is not possible to enable or disable the Event Scheduler when the server is running. That is, you can change the value of event_scheduler to DISABLED 鈥� or from DISABLED to one of the other permitted values for this option 鈥� only when the server is stopped. Attempting to do so when the server is running fails with an error.

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.

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.

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聽20.2, 鈥淓vent Scheduler Syntax鈥�.

MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtian information about scheduled events which have been defined on the server. See Section聽20.3, 鈥淓vent Metadata鈥�, and Section聽22.20, 鈥淭he INFORMATION_SCHEMA EVENTS Table鈥�, for more information.

For information regarding event scheduling and the MySQL privilege system, see Section聽20.5, 鈥淭he Event Scheduler and MySQL Privileges鈥�.

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

ALTER EVENT
    [DEFINER = { user | CURRENT_USER }]
    event_name
    [ON SCHEDULE schedule]
    [RENAME TO new_event_name]
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [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 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 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]
    [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); 

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 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聽22.20, 鈥淭he 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;

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. 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聽22.20, 鈥淭he 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.

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聽20.6, 鈥淓vent 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. An event also may not create, alter, or drop triggers or stored routines. (Bug#16409, Bug#18896)

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 has no effect 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 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)

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.18, 鈥�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 鈥� prior to MySQL 5.1.12 鈥� with the same definer) with 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.

References to stored routines, 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.)