An event is a predefined SQL task that can be executed automatically at a specific point in time or at regular intervals. Each event consists of trigger conditions (i.e., a schedule plan) and the SQL statements or stored procedures to be executed. The definition of an event is stored in the database and is maintained together with the database, ensuring that events continue to execute as scheduled even after a server restart.
Unlike triggers, which fire SQL execution based on data operations, EVENTS trigger SQL execution based on time changes. They allow users to automatically execute SQL statements or stored procedures according to predefined time schedules. This feature significantly enhances the database's automated processing capabilities and is suitable for various scenarios such as regular data maintenance, statistical aggregation, and scheduled notifications.
Note
The CALL statement can be executed in an event to invoke a stored procedure. Time expressions and functions are now supported when setting the start or end time.
Features:
Scheduling rule:
- Exact Time Point: You can set the event to execute once at a specific point in time.
- Periodic execution: Supports repeated execution on a periodic basis, such as every second, minute, hour, day, or month.
- Complex scheduling: Use keywords such as
EVERY,STARTS, andENDSto create complex schedules, such as executing only on weekdays or within a specific date range.
SQL support:
- You can execute any valid SQL statements, including data insertion, update, and deletion.
- Supports transaction processing to ensure the atomicity and consistency of event execution.
Dynamic control:
- You can dynamically enable or disable the event scheduler at runtime without restarting the service.
- Supports modifying and deleting existing events to adapt to changing business requirements.
Privilege requirements
To use the EVENT statement, you must have the GRANT EVENT privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Start and stop an event
Events are executed by a dedicated event scheduler thread. You can enable or disable the event scheduler as follows.
Enable the event scheduler:
obclient> SET GLOBAL event_scheduler = ON;Stop the event scheduler:
obclient> SET GLOBAL event_scheduler = OFF;
View events
You can execute the SHOW EVENTS statement to view the event list for the current tenant, including information such as the event name, database, scheduled plan, status (enabled/disabled), and next execution time.
-- View All Events
obclient> SELECT * FROM information_schema.events\G
-- Filter by database and name
obclient> SELECT EVENT_SCHEMA, EVENT_NAME, STATUS, STARTS, ENDS, EVENT_DEFINITION
FROM information_schema.events
WHERE EVENT_SCHEMA = 'mydb' AND EVENT_NAME LIKE '%my%';
Usage instructions and considerations
- If
EVERY INTERVALis specified without settingSTART TIME,END TIMEwill be executed indefinitely. - For repeated EVENTS, if the event execution time exceeds the scheduling interval, multiple EVENTS may occur at the same time. Users must ensure that the execution time is less than the scheduling interval.
- The maximum execution time cannot be set (the maximum execution time for a one-time event is 24 hours).
- It is not recommended to create another event from an existing one.
- The schedule of an already started event cannot be modified.
- The
event_nameof an already started event should not be modified, otherwise the event may fail to end correctly. - If the event fails to execute consecutively for 16 times, it will stop running.