MySQL AB :: MySQL 5.1 Reference Manual :: 22.20 The INFORMATION

22.20. The `INFORMATION_SCHEMA EVENTS` Table

The EVENTS table provides information about scheduled events, which are discussed in Chapter 20, Event Scheduler.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`EVENT_CATALOG`		`NULL`, MySQL extension
`EVENT_SCHEMA`	`Db`	MySQL extension
`EVENT_NAME`	`Name`	MySQL extension
`DEFINER`	`Definer`	MySQL extension
`TIME_ZONE`	`Time zone`	MySQL extension
`EVENT_BODY`		MySQL extension
`EVENT_DEFINITION`		MySQL extension
`EVENT_TYPE`	`Type`	MySQL extension
`EXECUTE_AT`	`Execute at`	MySQL extension
`INTERVAL_VALUE`	`Interval value`	MySQL extension
`INTERVAL_FIELD`	`Interval field`	MySQL extension
`SQL_MODE`		MySQL extension
`STARTS`	`Starts`	MySQL extension
`ENDS`	`Ends`	MySQL extension
`STATUS`	`Status`	MySQL extension
`ON_COMPLETION`		MySQL extension
`CREATED`		MySQL extension
`LAST_ALTERED`		MySQL extension
`LAST_EXECUTED`		MySQL extension
`EVENT_COMMENT`		MySQL extension

Notes:

The EVENTS table is a non-standard table. It was added in MySQL 5.1.6.
EVENT_CATALOG: The value of this column is always NULL.
EVENT_SCHEMA: The name of the schema (database) to which this event belongs.
EVENT_NAME: The name of the event.
DEFINER: The user who created the event. Always displayed in 'user_name'@'host_name' format.
TIME_ZONE: The time zone in effect when schedule for the event was last modified. If the event's schedule has not been modified since the event was created, then this is the time zone that was in effect at the event's creation. The default value is SYSTEM.

This column was added in MySQL 5.1.17.
EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.1, this is always SQL.

This column was added in MySQL 5.1.12. It is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.
EVENT_DEFINITION: The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

Note: Prior to MySQL 5.1.12, this column was named EVENT_BODY.
EVENT_TYPE: One of the two values ONE TIME or RECURRING.
EXECUTE_AT: For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event, or of the last ALTER EVENT statement that modified the event. The value shown in this column reflects the addition or subtraction of any INTERVAL value included in the event's AT clause. For example, if an event is created using ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR, and the event was created at 2006-02-09 14:05:30, the value shown in this column would be '2006-02-10 20:05:30'.

If the event's timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL.
INTERVAL_VALUE: For recurring events, this column contains the numeric portion of the event's EVERY clause.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column's value is NULL.
INTERVAL_FIELD: For recurring events, this column contains the units portion of the EVERY clause governing the timing of the event, prefixed with 'INTERVAL_'. Thus, this column contains a value such as 'INTERVAL_YEAR', 'INTERVAL_QUARTER', 'INTERVAL_DAY', and so on.

For a one-time event (that is, an event whose timing is determined by an AT clause), this column's value is NULL.
SQL_MODE: The SQL mode in effect at the time the event was created or altered.
STARTS: For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used.

If there is no STARTS clause affecting the timing of the event, this column is empty. (Prior to MySQL 5.1.8, it contained NULL in such cases.)
ENDS: For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column (see previous example), this value resolves any expressions used.

If there is no ENDS clause affecting the timing of the event, this column contains NULL.
STATUS: One of the two values ENABLED or DISABLED.
ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.
CREATED: The date and time when the event was created. This is a DATETIME value.
LAST_ALTERED: The date and time when the event was last modified. This is a DATETIME value. If the event has not been modified since its creation, this column holds the same value as the CREATED column.
LAST_EXECUTED: The date and time when the event last executed. A DATETIME value. If the event has never executed, this column's value is NULL.
EVENT_COMMENT: The text of a comment, if the event has one. If there is no comment, the value of this column is an empty string.

Example: Suppose the user jon@ghidora creates an event named e_daily, and then modifies it a few minutes later using an ALTER EVENT statement, as shown here:

DELIMITER |

CREATE EVENT e_daily
    ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
    DISABLE
    COMMENT 'Saves total number of sessions and
             clears the table once per 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 ;

ALTER EVENT e_daily
    ENABLED;

(Note that comments can span multiple lines.)

This user can then run the following SELECT statement, and obtain the output shown:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
     > WHERE EVENT_NAME = 'e_daily' 
     > AND EVENT_SCHEMA = 'myschema'\G

*************************** 1. row ***************************
  EVENT_CATALOG: NULL
   EVENT_SCHEMA: myschema
     EVENT_NAME: e_daily
        DEFINER: jon@ghidora
     EVENT_BODY: BEGIN
                   INSERT INTO site_activity.totals (when, total)
                     SELECT CURRENT_TIMESTAMP, COUNT(*) 
                       FROM site_activity.sessions;
                   DELETE FROM site_activity.sessions;
                 END
     EVENT_TYPE: RECURRING
     EXECUTE_AT: NULL
 INTERVAL_VALUE: 1
 INTERVAL_FIELD: INTERVAL_DAY
       SQL_MODE: NULL
         STARTS: 2006-02-09 10:41:23
           ENDS: NULL
         STATUS: ENABLED
  ON_COMPLETION: DROP
        CREATED: 2006-02-09 14:35:35
   LAST_ALTERED: 2006-02-09 14:41:23
  LAST_EXECUTED: NULL
  EVENT_COMMENT: Saves total number of sessions and
                 clears the table once per day.
1 row in set (0.50 sec)

Important: The times displayed by the STARTS, ENDS, and LAST_EXECUTED columns are currently given in terms of Universal Time (GMT or UTC), regardless of the server's time zone setting. (The same is true for the starts, ends, and last_executed columns of the mysql.event table and the Starts and Ends columns in the output of SHOW [FULL] EVENTS.) The CREATED and LAST_ALTERED columns use the server time zone (as do the created and last_altered columns of the mysql.event table).

For example, the e_daily event shown previously was created on a computer in Brisbane, Australia, at 14:35:35 on the 9^th of February, 2006, Eastern Australia Standard Time, which can also be expressed as the GMT+10.00 time zone. The event definition was updated (using ALTER EVENT as shown earlier in this section) a few minutes later, at 14:41:23. These are the values displayed for CREATED and LAST_ALTERED. The event is scheduled to begin executing 6 hours later — that is, at 20:41:23 on the same date, local time. Subtracting 10 hours from this to obtain Universal Time yields 10:41:23, and it is this value that is shown for STARTS.

This use of Universal Time should not be relied upon in applications, as it is expected to change to server local time in an upcoming MySQL release. (Bug#16420)

22.20. The INFORMATION_SCHEMA EVENTS Table

User Comments

22.20. The `INFORMATION_SCHEMA EVENTS` Table