EVENT

An event is a predefined SQL task that can be executed automatically at a specific time or at regular intervals. Each event consists of a trigger condition (i.e., a schedule) and the SQL statement or stored procedure to be executed. The event definition is stored in the database and maintained together with the database, ensuring that it continues to run 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 schedules. 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 repeated execution at intervals of seconds, minutes, hours, days, or months.
  • Complex scheduling: By combining keywords such as EVERY, STARTS, and ENDS, you can implement complex scheduling logic, such as executing events only on weekdays or within specific date ranges.

• SQL support:

  • You can execute any valid SQL statement, including data insertion, update, and deletion.
  • Supports transaction processing to ensure 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 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 types in MySQL-compatible mode.

Enable and disable events

Events are executed by a special event scheduler thread. You can enable or disable the event scheduler as follows:

• Enable the event scheduler:

  obclient> SET GLOBAL event_scheduler = ON;
  

• Disable the event scheduler:

  obclient> SET GLOBAL event_scheduler = OFF;
  

Considerations

• If you do not set a START TIME for an event using EVERY interval, it will run indefinitely.
• For recurring events, if the execution time exceeds the scheduling interval, multiple events may execute simultaneously. You must ensure that the execution time is less than the scheduling 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 scheduling plan cannot be modified.
• Once an event has started, its event_name cannot be modified, or it may fail to end properly.
• If an event fails to execute for 16 consecutive times, it will stop running.