Note
- For OceanBase Database V4.3.x, this view was introduced in OceanBase Database V4.3.2.
- For OceanBase Database V4.2.x, this view was introduced in OceanBase Database V4.2.1 BP5 and V4.2.4.
Purpose
The information_schema.events view displays the information about all events.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| EVENT_CATALOG | varchar(64) | NO | The name of the catalog to which the event belongs. The value of this column is always def. def is a universal identifier indicating that an object in a system view belongs to the default catalog. |
| EVENT_SCHEMA | varchar(128) | NO | The name of the database to which the event belongs. |
| EVENT_NAME | varchar(64) | NO | The name of the event. |
| DEFINER | varchar(93) | NO | The user that created the event. |
| TIME_ZONE | varchar(64) | NO | The time zone of the event. It is used for scheduling the event and takes effect when the event is executed. The default value is SYSTEM. |
| EVENT_BODY | varchar(8) | NO | The language used in the DO clause of the event. The value of this column is always SQL. |
| EVENT_DEFINITION | mediumtext | YES | The text of the SQL statement that constitutes theDO clause. |
| EVENT_TYPE | varchar(9) | NO | The event repetition type. It indicates whether the event is a one-time or recurring event. |
| EXECUTE_AT | datetime | YES | The start time of the event if it is a one-time event. For a recurring event, the value is NULL. |
| INTERVAL_VALUE | varchar(256) | YES | The number of intervals to wait between two consecutive executions of the event if it is a recurring event. For a one-time event, the value is always NULL. |
| INTERVAL_FIELD | varchar(18) | YES | The time duration of an interval to wait between two consecutive executions of the event if it is a recurring event. For a one-time event, the value is always NULL. |
| SQL_MODE | text | NO | The SQL mode that took effect when the event was created or modified, and in which the event is executed. |
| STARTS | datetime | YES | The start date and time of the event if it is a recurring event. If no start date and time are defined for the event, the value is NULL. For a one-time event, the value is always NULL. |
| ENDS | datetime | YES | For a recurring event whose definition contains an ENDS clause, this column contains the corresponding DATETIME value. Like the EXECUTE_AT column, this column resolves any expressions used. If no ENDS clause affecting the timing of the event is used, the value is NULL. |
| STATUS | varchar(18) | NO | The status of the event. Valid values:
|
| ON_COMPLETION | varchar(12) | NO | Specifies whether to preserve the event definition after the event is completed. Value values:
|
| CREATED | datetime | YES | The date and time when the event was created. |
| LAST_ALTERED | datetime | YES | The date and time when the event was last modified. |
| LAST_EXECUTED | datetime | YES | The date and time when the event was last executed. The value NULL indicates that the event has not been executed. This column indicates the start time of the event. Therefore, the value of this column is not later than that of the ENDS column. |
| EVENT_COMMENT | text | YES | The comments on the event. If the event has no comments, this column is left empty. |
| ORIGINATOR | bigint(0) unsigned | NO | The service ID of the MySQL server where the event was created. It is used in replication. If you execute the ALTER EVENT statement in the replication source, the value of this column may be updated to the service ID that is used when the statement is executed. The default value is 0. |
| CHARACTER_SET_CLIENT | varchar(32) | NO | The value of the character_set_client system variable for the session when the event was created. |
| COLLATION_CONNECTION | varchar(32) | NO | The value of the collation_connection system variable for the session when the event was created. |
| DATABASE_COLLATION | varchar(32) | NO | The collation of the database associated with the event. |
Sample query
Create an event named
e_1and specify to execute the event at 00:00:00 on January 1, 3000 with theSELECT 1statement.obclient> CREATE EVENT e_1 ON SCHEDULE AT "3000-01-01 00:00:00" DO SELECT 1;Query all information about the
e_1event.obclient> SELECT * FROM information_schema.events\GThe query result is as follows:
*************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: test EVENT_NAME: e_1 DEFINER: root@% TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: select 1; EVENT_TYPE: ONE TIME EXECUTE_AT: 3000-01-01 00:00:00 INTERVAL_VALUE: NULL INTERVAL_FIELD: NULL SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STARTS: NULL ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2024-06-26 11:29:08 LAST_ALTERED: 2024-06-26 11:29:08 LAST_EXECUTED: NULL EVENT_COMMENT: NULL ORIGINATOR: NULL CHARACTER_SET_CLIENT: NULL COLLATION_CONNECTION: NULL DATABASE_COLLATION: NULL 1 row in set (0.002 sec)