Purpose
This statement is used to modify the schedule, status, comment, body, or name of an existing event. Only the specified clauses can be modified, and the unspecified clauses remain unchanged.
Privilege requirements
The user executing this statement must have the EVENT privilege. To modify the DEFINER, the user must have the SUPER privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.
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}
Considerations
- The event must be in the current database. Use the
USE db_name;statement to switch to the database where the event is located before executing the ALTER EVENT statement. You cannot modify an event in a different database. - The start time (STARTS) after modification cannot be in the past. Otherwise, an error will be returned.
- Once an event has started, its schedule cannot be modified. Additionally, you cannot use
RENAME TOto change the event name (otherwise, an error will be returned). Only when the event has not started can you useRENAME TOto change the event name. The new name cannot be the same as the current name, and it cannot be changed to another database. - To modify the DEFINER, the current user must have the SUPER privilege.
- If the
DO event_bodyclause contains a string literal with single quotes, it may trigger a syntax parsing error. We recommend that you use theCALLstatement to call a stored procedure to implement complex logic. For more information about the DO clause, see CREATE EVENT.
Parameters
| Parameter | Description |
|---|---|
| DEFINER = user | Specifies the event definer. To modify the DEFINER, the current user must have the SUPER privilege. |
| event_name | Specifies the name of the event to be modified. The event must exist in the current database. |
| ON SCHEDULE schedule | Modifies the time and frequency at which the event is executed. The rules are the same as those for the CREATE EVENT statement. The start time after modification cannot be in the past. |
| ON COMPLETION [NOT] PRESERVE | Specifies whether to retain the event after it is executed. PRESERVE indicates that the event is retained, and NOT PRESERVE indicates that the event is automatically deleted after execution. |
| RENAME TO new_event_name | Renames the event to a new name. This option is supported only when the event has not started. The new name cannot be the same as the current name. |
| ENABLE、DISABLE、DISABLE ON SLAVE | Modifies the status of the event. ENABLE enables the event, and DISABLE disables it. |
| COMMENT 'string' | Modifies the comment of the event. |
| DO event_body | Modifies 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 examples are based on a complete scenario that includes database creation, table creation, event creation, and stored procedure creation for use with the DO clause. First, perform the necessary preparations before executing the ALTER statements.
Preparations: Create a database, a table, an event, and a stored procedure for use with the DO clause.
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 ;
Modify the event: Enable the event, change its schedule and comment, disable the event, and then enable it again. Change the event body to call a stored procedure.
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 event_log table is empty, check the event scheduler status. If it is OFF, turn it ON and wait for at least one scheduling interval before querying again.
First, check the event scheduler status:
obclient> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
If the result is OFF, execute the following statement to turn it ON:
obclient> SET GLOBAL event_scheduler = ON;
Then, wait for at least one scheduling interval (1 minute or 2 minutes in this example) before executing 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 (MySQL mode): Overview of events.
- CREATE EVENT: Creates an event.
- DROP EVENT: Drops an event.