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
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 | |
ORIGINATOR |
Originator |
MySQL extension |
CHARACTER_SET_CLIENT |
character_set_client |
MySQL extension |
COLLATION_CONNECTION |
collation_connection |
MySQL extension |
DATABASE_COLLATION |
Database Collation |
MySQL 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
'
format.
user_name
'@'host_name
'
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 Section C.1.35, “Changes in MySQL 5.1.17 (04 April 2007)”, 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.
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.
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 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 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.8, “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 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 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
is the session value
of the character_set_client
system variable when the event was created.
COLLATION_CONNECTION
is the session value
of the collation_connection
system variable when the event was created.
DATABASE_COLLATION
is the collation of the
database with which the event is associated. These columns
were added in MySQL 5.1.21.
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 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: paul@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 12.4.5.19, “SHOW EVENTS
Syntax”.
User Comments
Add your own comment.