Purpose
This statement is used to modify the schedule, enabled status, comments, execution body, or name of an existing event. Only specified clauses can be modified; unspecified clauses remain unchanged.
Privilege requirements
The user executing this statement must have the EVENT privilege. Modifying the DEFINER requires superuser privileges. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
ALTER
[DEFINER = user]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
[DO event_body]
schedule: {
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
}
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Usage instructions and considerations
- The event must exist in the current database. First, use
USE db_name;to switch to the database where the event is located, and then execute ALTER EVENT. Cross-database modification is not supported. - The modified start time (STARTS) cannot be in the past; otherwise, an error will be reported.
- The schedule of an event that has already started cannot be modified, nor can the event name be changed using
RENAME TO(otherwise, an error will be reported). You can only useRENAME TOto change the event name when the event has not yet started. The new name must be different from the current one and cannot be a name in another database. - The current user must have the SUPER privilege to modify the DEFINER clause.
- If the
DO event_bodycontains a string literal with single quotes, it may trigger a syntax parsing error. It is recommended to use theCALLstatement to invoke stored procedures for implementing complex logic. For more information, see the description of DO in CREATE EVENT.
Parameters
Parameter |
Description |
|---|---|
| DEFINER = user | Specifies the event definer. The current user must have the SUPER privilege to modify the DEFINER. |
| event_name | Specifies the name of the event to be modified. The event must already exist in the current database. |
| ON SCHEDULE schedule | Modifies the execution time and frequency of an event. The rules are the same as those for CREATE EVENT; the modified start time cannot be in the past. |
| ON COMPLETION [NOT] PRESERVE | Whether to retain the modified event after execution. PRESERVE indicates that the field is reserved. NOT PRESERVE Indicates that the object will be automatically deleted after execution. |
| RENAME TO new_event_name | Renames the event to a new name. This operation is supported only if the event has not yet started. The new name must be different from the current name. |
| ENABLE, DISABLE, DISABLE ON SLAVE | Modify the event status. ENABLE Enable, DISABLE Disabled. |
| COMMENT 'string' | Modify the event comment. |
| DO event_body | Modify the SQL statement or stored procedure call to be executed by the event. Supports a single SQL statement or CALL proc_name;. |
Examples
The following example covers the complete scenario of "creating a database → creating a table → creating an event → modifying an event". Prepare the necessary components in advance and then execute the corresponding ALTER statements.
Prerequisites: Create a database, tables, an event, and a stored procedure for DO CALL.
obclient> CREATE DATABASE IF NOT EXISTS test_event_db;
obclient> USE test_event_db;
obclient> CREATE TABLE event_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
evt_name VARCHAR(128),
action VARCHAR(32),
ts DATETIME DEFAULT CURRENT_TIMESTAMP
);
obclient> CREATE EVENT evt_demo
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'Test: write a log entry every minute'
DO INSERT INTO event_log (evt_name, action) VALUES ('evt_demo', 'tick');
obclient> DELIMITER /
obclient> CREATE PROCEDURE sp_evt_log()
BEGIN
INSERT INTO event_log (evt_name, action) VALUES ('evt_demo', 'altered');
END;
/
obclient> DELIMITER ;
Event modifications: enable, modify the schedule and comments, disable, re-enable, and change the executor to a stored procedure call.
obclient [test_event_db]> ALTER EVENT evt_demo ENABLE;
obclient [test_event_db]> ALTER EVENT evt_demo
ON SCHEDULE EVERY 2 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
COMMENT 'Run every 2 minutes, preserve after completion';
obclient [test_event_db]> ALTER EVENT evt_demo DISABLE;
obclient [test_event_db]> ALTER EVENT evt_demo ENABLE;
obclient [test_event_db]> ALTER EVENT evt_demo DO CALL sp_evt_log();
View and Clean Up (Optional):
obclient [test_event_db]> SELECT EVENT_NAME, STATUS, EVENT_COMMENT FROM information_schema.events WHERE EVENT_SCHEMA = 'test_event_db';
The query result is as follows:
+-------------+--------+-------------------------------------------+
| EVENT_NAME | STATUS | EVENT_COMMENT |
+-------------+--------+-------------------------------------------+
| evt_demo | ENABLED| Run every 2 minutes, preserve after completion |
+-------------+--------+-------------------------------------------+
1 row in set
If the query result of event_log is empty, first check the status of the event scheduler. If it is set to OFF, enable it, wait for one scheduling interval, and then query again.
Check the status of the event scheduler first:
obclient> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
If the result is OFF, execute the following statement to enable it:
obclient> SET GLOBAL event_scheduler = ON;
Then wait for at least one scheduling interval (1 or 2 minutes in this example) and execute the following query:
obclient [test_event_db]> SELECT * FROM event_log ORDER BY id DESC LIMIT 5;
The query result is as follows:
+------+----------+---------+---------------------+
| id | evt_name | action | ts |
+------+----------+---------+---------------------+
| 5 | evt_demo | altered | 2026-02-06 15:27:11 |
| 4 | evt_demo | altered | 2026-02-06 15:25:11 |
| 3 | evt_demo | altered | 2026-02-06 15:23:11 |
| 2 | evt_demo | altered | 2026-02-06 15:21:11 |
| 1 | evt_demo | altered | 2026-02-06 15:19:11 |
+------+----------+---------+---------------------+
obclient [test_event_db]> DROP EVENT IF EXISTS evt_demo;
References
- EVENT: Overview of events.
- CREATE EVENT : Creates an event.
- DROP EVENT : Drops an event.