MySQL 5.1 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA EVENTS Table

20.7 The INFORMATION_SCHEMA EVENTS Table

The EVENTS table provides information about scheduled events, which are discussed in Section 19.4, “Using the Event Scheduler”. The SHOW Name values correspond to column names of the SHOW EVENTS statement.

INFORMATION_SCHEMA NameSHOW NameRemarks
EVENT_CATALOG NULL, MySQL extension
EVENT_SCHEMADbMySQL extension
EVENT_NAMENameMySQL extension
DEFINERDefinerMySQL extension
TIME_ZONETime zoneMySQL extension
EVENT_BODY MySQL extension
EVENT_DEFINITION MySQL extension
EVENT_TYPETypeMySQL extension
EXECUTE_ATExecute atMySQL extension
INTERVAL_VALUEInterval valueMySQL extension
INTERVAL_FIELDInterval fieldMySQL extension
SQL_MODE MySQL extension
STARTSStartsMySQL extension
ENDSEndsMySQL extension
STATUSStatusMySQL extension
ON_COMPLETION MySQL extension
CREATED MySQL extension
LAST_ALTERED MySQL extension
LAST_EXECUTED MySQL extension
EVENT_COMMENT MySQL extension
ORIGINATOROriginatorMySQL extension
CHARACTER_SET_CLIENTcharacter_set_clientMySQL extension
COLLATION_CONNECTIONcollation_connectionMySQL extension
DATABASE_COLLATIONDatabase CollationMySQL extension

Notes:

  • The EVENTS table is a nonstandard 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 account of the user who created the event, in 'user_name'@'host_name' format.

  • TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

    This column was added in MySQL 5.1.17. See Release Notes for MySQL 5.1.17 for important information if you are using the Event Scheduler and are upgrading to MySQL 5.1.17 or later from an earlier version.

  • 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: The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

  • 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 is NULL.

  • INTERVAL_FIELD: For recurring events, this column contains the units portion of the EVERY clause governing the timing of the event. Thus, this column contains a value such as 'YEAR', 'QUARTER', 'DAY', and so on.

    Note

    In early MySQL 5.1 releases, this value was prefixed with 'INTERVAL_', and was displayed 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 is NULL.

  • SQL_MODE: The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 5.1.7, “Server SQL Modes”.

  • 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 NULL. (Prior to MySQL 5.1.8, it contained 0000-00-00 00:00:00 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, this value resolves any expressions used.

    If there is no ENDS clause affecting the timing of the event, this column is NULL.

  • STATUS: One of the three values ENABLED, DISABLED, or SLAVESIDE_DISABLED.

    SLAVESIDE_DISABLED was added to the list of possible values for this column in MySQL 5.1.18. This value indicates that the creation of the event occurred on another MySQL server acting as a replication master and was replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. See Section 16.4.1.12, “Replication of Invoked Features”, for more information.

  • ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.

  • CREATED: The date and time when the event was created. This is a TIMESTAMP value.

  • LAST_ALTERED: The date and time when the event was last modified. This is a TIMESTAMP 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 is NULL.

    Before MySQL 5.1.23, LAST_EXECUTED indicates when event finished executing. As of 5.1.23, LAST_EXECUTED instead indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.

  • EVENT_COMMENT: The text of a comment, if the event has one. If not, the value of this column is an empty string.

  • ORIGINATOR: The server ID of the MySQL server on which the event was created; used in replication. The default value is 0. This column was added in MySQL 5.1.18.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the event was created. This column was added in MySQL 5.1.21.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the event was created. This column was added in MySQL 5.1.21.

  • DATABASE_COLLATION: The collation of the database with which the event is associated. This column was added in MySQL 5.1.21.

Example: Suppose that 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
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, 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: test
          EVENT_NAME: e_daily
             DEFINER: me@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: BEGIN
        INSERT INTO site_activity.totals (time, 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: DAY
            SQL_MODE:
              STARTS: 2008-09-03 12:13:39
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2008-09-03 12:13:39
        LAST_ALTERED: 2008-09-03 12:13:39
       LAST_EXECUTED: NULL
       EVENT_COMMENT: Saves total number of sessions then clears the
                      table each day
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci

Times in the EVENTS table are displayed using the event time zone or the current session time zone. Prior to MySQL 5.1.17, some of the times are displayed in UTC rather than the event time zone. For details, see Section 19.4.4, “Event Metadata”.

See also Section 13.7.5.19, “SHOW EVENTS Syntax”.


User Comments
Sign Up Login You must be logged in to post a comment.