EVENT

An event is a predefined SQL task that can be automatically executed at a specific time or at regular intervals. Each event consists of a trigger condition (i.e., a schedule), an SQL statement, or a stored procedure. The event definition is stored in the database and is maintained together with the database, ensuring that the event will execute as scheduled even after the server is restarted.

Unlike triggers, which are triggered by data operations, events are triggered by time changes. This allows users to automatically execute SQL statements or stored procedures based on predefined time plans. This feature significantly enhances the database's automation capabilities and is suitable for various scenarios such as regular data maintenance, statistical aggregation, and scheduled notifications.

Features:

• Scheduling rules:

  • Exact time point: You can set an event to execute at a specific time.
  • Periodic execution: Supports repeating execution at intervals of seconds, minutes, hours, days, or months.
  • Complex scheduling: Complex scheduling logic can be achieved by combining keywords such as EVERY, STARTS, and ENDS, such as executing events only on weekdays or within specific date ranges.

• SQL support:

  • Supports executing any valid SQL statement, including data insertion, updates, and deletions.

  Transaction support: Ensures atomicity and consistency of event execution.

• Dynamic control:

  • Allows dynamically enabling or disabling the event scheduler without restarting the service.
  • Supports modifying or deleting existing events to adapt to changing business needs.

Privilege requirements

To use events, you must have the GRANT EVENT privilege. For more information about privileges in OceanBase Database, see Privilege classification in MySQL mode.

Start and stop events

Events are executed by a special event scheduler thread. You can start or stop the event scheduler as follows.

• Start the event scheduler:

  obclient> SET GLOBAL event_scheduler = ON;
  

• Stop the event scheduler:

  obclient> SET GLOBAL event_scheduler = OFF;
  

View events

You can use the SHOW EVENTS statement to view the list of events in the current tenant, including event name, database, schedule, 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%';

Considerations

• If EVERY interval is specified without START TIME, the event will execute indefinitely.
• For recurring events, if the execution time exceeds the schedule interval, multiple events may execute at the same time. Users must ensure that the execution time is less than the schedule interval.
• The maximum execution time for a one-time event is 24 hours.
• It is not recommended to create one event based on another.
• Once an event has started, its schedule cannot be modified.
• Once an event has started, its event_name should not be modified, as this may prevent it from ending correctly.
• If an event fails to execute 16 times consecutively, it will stop running.