MySQL Events Scheduler

What Is the MySQL Event Scheduler?

The Event Scheduler is MySQL's server-side facility for running SQL at a specified time or interval. An event is a named schema object with a schedule and a body. It is useful for bounded database maintenance such as expiring sessions, refreshing summary tables or calling a stored procedure.

CREATE EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO event_body;

Events do not accept parameters directly. An event body can call a stored procedure and pass fixed or computed values.

Check and Configure the Scheduler Safely

SHOW VARIABLES LIKE 'event_scheduler';

-- Requires privilege to set a global system variable
SET GLOBAL event_scheduler = ON;

SHOW PROCESSLIST;

MySQL 8.4 documents ON as the default, but hosting providers and managed services can enforce different operational policies. Never assume the value—inspect the live server. If it is DISABLED, change the server startup configuration and restart through the authorized hosting workflow.

Build a Reproducible Session-Cleanup Lab

DROP TABLE IF EXISTS event_run_log_lab;
DROP TABLE IF EXISTS sessions_event_lab;

CREATE TABLE sessions_event_lab (
  session_id INT PRIMARY KEY,
  user_id INT NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX ix_sessions_expires (expires_at)
) ENGINE = InnoDB;

CREATE TABLE event_run_log_lab (
  run_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(64) NOT NULL,
  rows_changed INT NOT NULL,
  ran_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

INSERT INTO sessions_event_lab VALUES
(1,101,CURRENT_TIMESTAMP - INTERVAL 2 DAY),
(2,102,CURRENT_TIMESTAMP + INTERVAL 1 DAY),
(3,103,CURRENT_TIMESTAMP - INTERVAL 1 HOUR),
(4,104,CURRENT_TIMESTAMP + INTERVAL 3 DAY);

Sessions 1 and 3 are expired. The indexed predicate keeps cleanup bounded. The log table makes execution visible because a scheduled event has no interactive terminal for output.

Create and Verify a One-Time Event

DELIMITER //
CREATE EVENT ev_cleanup_once
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
COMMENT 'Tutorial: purge expired sessions once'
DO
BEGIN
  DELETE FROM sessions_event_lab
  WHERE expires_at < CURRENT_TIMESTAMP;

  INSERT INTO event_run_log_lab
    (event_name, rows_changed)
  VALUES ('ev_cleanup_once', ROW_COUNT());
END//
DELIMITER ;

DELIMITER is a mysql-client command, not server SQL; it lets the client send the compound BEGIN...END body as one statement. GUI tools may provide their own delimiter handling. After the scheduled time:

SELECT session_id FROM sessions_event_lab ORDER BY session_id;
SELECT event_name, rows_changed
FROM event_run_log_lab ORDER BY run_id;

ON COMPLETION PRESERVE retains the event object after its last execution so metadata can be inspected; without PRESERVE, the default completion behavior can remove an expired one-time event. The event itself must still be enabled and the global scheduler ON.

Create a Recurring Event with STARTS and ENDS

DELIMITER //
CREATE EVENT ev_cleanup_hourly
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
ENDS CURRENT_TIMESTAMP + INTERVAL 30 DAY
COMMENT 'Delete expired sessions and log each run'
DO
BEGIN
  DELETE FROM sessions_event_lab
  WHERE expires_at < CURRENT_TIMESTAMP
  LIMIT 1000;

  INSERT INTO event_run_log_lab
    (event_name, rows_changed)
  VALUES ('ev_cleanup_hourly', ROW_COUNT());
END//
DELIMITER ;

Batching with LIMIT 1000 bounds one run; repeat runs continue cleanup. Design a supporting index, retention policy and alert for persistent backlog. A statement such as plain SELECT COUNT(*) produces no useful event output; store a value in a table if it must be observed.

To pass parameters, place reusable logic in a procedure:

CREATE EVENT ev_call_cleanup
ON SCHEDULE EVERY 1 DAY
DO CALL purge_expired_sessions(1000);

Time Zones, Delays and Overlapping Runs

• Document the session time_zone used when the event is created or altered, and inspect the event's TIME_ZONE metadata.
• Prefer UTC for cross-region systems; convert business-local reporting boundaries deliberately.
• Calendar intervals such as MONTH behave differently from fixed-second intervals around month lengths and daylight-saving changes.
• Scheduler activation is not a real-time guarantee; brief delays are possible.
• Two events scheduled for the same second have no guaranteed order.

If a recurring event takes longer than its interval, executions can overlap. For exclusive work, use an advisory lock and always release it:

IF GET_LOCK('cleanup_sessions', 0) = 1 THEN
  DELETE FROM sessions_event_lab
  WHERE expires_at < CURRENT_TIMESTAMP
  LIMIT 1000;
  DO RELEASE_LOCK('cleanup_sessions');
END IF;

For critical workloads, a durable queue/status table is stronger than an advisory lock because it can record ownership, attempts and recovery after failure.

Privileges, Definer and Monitoring

• EVENT privilege is required at schema or global scope to create, alter or drop events.
• Execution uses the event DEFINER account and requires that account's privileges for the body statements.
• Use a durable, least-privileged definer; do not depend on an employee account.
• Revoking EVENT from a creator does not automatically delete or disable existing events.
• Test the body manually with the intended security context before scheduling it.

SHOW EVENTS FROM your_database;
SHOW CREATE EVENT ev_cleanup_hourly;

SELECT EVENT_NAME, STATUS, EVENT_TYPE, INTERVAL_VALUE,
       INTERVAL_FIELD, STARTS, ENDS, LAST_EXECUTED,
       TIME_ZONE, DEFINER
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();

LAST_EXECUTED shows activation, not business success. Record outcome and row counts in a log table, monitor failures in the MySQL error log and alert when expected runs are missing.

Alter, Disable, Enable and Drop Events

ALTER EVENT ev_cleanup_hourly DISABLE;

ALTER EVENT ev_cleanup_hourly
ON SCHEDULE EVERY 2 HOUR
ENABLE;

DROP EVENT IF EXISTS ev_cleanup_hourly;

1. Create new events DISABLED when deployment requires review before activation.
2. Keep CREATE/ALTER/DROP definitions in version-controlled migrations.
3. Test body SQL, empty input, backlog, lock contention and duplicate execution.
4. Verify scheduler state after server restart and failover.
5. Review time zone, definer, privileges, LAST_EXECUTED and error logs.
6. Back up events explicitly with mysqldump --events.

Continue with prepared statements, review stored procedures and protect recoverability with backup and restore.

Official References

• MySQL 8.4: CREATE EVENT Statement
• MySQL 8.4: Event Scheduler Configuration
• MySQL 8.4: Event Scheduler Privileges

Syntax, scheduler states, completion behavior, privileges and monitoring guidance were checked against the official MySQL 8.4 manual.