Event Scheduler, Recurring Maintenance Jobs, Failure Handling, and Observability

Learning outcomes

The Event Scheduler is MySQL's in-server clock-driven job runner. It can be convenient for small database-local tasks, but a job that runs “somewhere inside MySQL” can be difficult to own unless scheduling, time zone, definer privileges, execution evidence, failure logging, and cleanup are all explicit.

A realistic problem: a cleanup job that “sometimes did not run”

ServiceHub wants to record a lightweight maintenance heartbeat every few seconds in a disposable lab. In production the real job might prune a staging table or roll up small metadata. A developer creates an event and assumes that seeing it in SHOW EVENTS proves the task is running. It does not.

An event is a schema object with a schedule and SQL body. The Event Scheduler is the server thread that executes enabled events. An event can exist while the scheduler is off. Event execution uses its definer privileges, and scheduled time expressions are interpreted using the session time_zone when the event is created.

Inspect scheduler state before creating anything

SELECT @@GLOBAL.event_scheduler AS event_scheduler,       @@GLOBAL.time_zone AS global_time_zone,       @@SESSION.time_zone AS session_time_zone,       @@system_time_zone AS system_time_zone;SHOW VARIABLES LIKE 'event_scheduler';SHOW PROCESSLIST;

Current MySQL 8.4 documentation lists ON as the default, but never assume the actual server uses the default. OFF means events are not executed until an authorized administrator turns the scheduler on. DISABLED means it was disabled at startup and cannot simply be switched on dynamically; change the startup configuration and restart the disposable server if you need the feature.

Use an explicit session time zone for reproducible scheduling

Set the owner session to UTC before creating the lab event. MySQL records the event's time-zone context so future server time-zone changes do not silently reinterpret the schedule.

USE servicehub_logic_lab;SET SESSION time_zone = '+00:00';DROP EVENT IF EXISTS e_ch10_heartbeat;CREATE EVENT e_ch10_heartbeatON SCHEDULE EVERY 10 SECOND  STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND  ENDS CURRENT_TIMESTAMP + INTERVAL 45 SECONDON COMPLETION PRESERVEENABLECOMMENT 'Chapter 10 disposable heartbeat'DO  INSERT INTO maintenance_runs(job_name,executed_at,execution_user,note)  VALUES('ch10_heartbeat',CURRENT_TIMESTAMP(6),CURRENT_USER(),'event fired');SHOW CREATE EVENT e_ch10_heartbeat\GSHOW EVENTS FROM servicehub_logic_lab LIKE 'e_ch10_heartbeat';

The event is preserved after its end time so you can inspect final metadata. Without ON COMPLETION PRESERVE, an expired one-time/default nonpreserved event can disappear, which makes postmortem diagnosis confusing for beginners.

Prove execution with three kinds of evidence

Wait long enough for at least one scheduled time, then inspect both metadata and side effects:

SELECT EVENT_NAME,DEFINER,TIME_ZONE,EVENT_TYPE,INTERVAL_VALUE,INTERVAL_FIELD,       STARTS,ENDS,STATUS,LAST_EXECUTEDFROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_SCHEMA='servicehub_logic_lab'  AND EVENT_NAME='e_ch10_heartbeat';SELECT run_id,job_name,executed_at,execution_user,noteFROM maintenance_runsWHERE job_name='ch10_heartbeat'ORDER BY run_id;SHOW EVENTS FROM servicehub_logic_lab;

A non-NULL LAST_EXECUTED and inserted rows are stronger evidence than STATUS='ENABLED' alone. Even then, the rows prove only that this body reached its insert statement; a complex event needs job-specific success/failure evidence.

Failure case 1: scheduler OFF

If your local scheduler is currently ON, you may demonstrate this only if you have administrative privilege and first record the original value. Do not do this on a shared server.

-- ADMIN-ONLY, DISPOSABLE INSTANCE ONLY.SELECT @@GLOBAL.event_scheduler AS original_scheduler_state;SET GLOBAL event_scheduler = OFF;-- The event metadata still exists, but scheduled executions stop.SELECT EVENT_NAME,STATUS,LAST_EXECUTEDFROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_SCHEMA='servicehub_logic_lab'  AND EVENT_NAME='e_ch10_heartbeat';-- Restore the original operational policy when finished.SET GLOBAL event_scheduler = ON;

This experiment separates object existence from scheduler execution. If your original value was OFF, restore OFF rather than blindly leaving it ON. If the server reports DISABLED, do not attempt the runtime SET.

Failure case 2: the event definer lacks privileges

MySQL allows an event definition to exist even when its body later requires privileges the definer does not possess. The execution fails at runtime, and errors/warnings from event execution are written to the server error log. Use a disposable account to demonstrate the principle without weakening your real owner account.

-- Admin session.DROP USER IF EXISTS 'event_limited'@'127.0.0.1';CREATE USER 'event_limited'@'127.0.0.1'  IDENTIFIED BY '<Disposable-Lab-Event-2026!>';GRANT EVENT ON servicehub_logic_lab.*TO 'event_limited'@'127.0.0.1';-- Connect as event_limited using -h 127.0.0.1, then:USE servicehub_logic_lab;SET SESSION time_zone='+00:00';CREATE EVENT e_ch10_expected_failureON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECONDON COMPLETION PRESERVEDO  INSERT INTO maintenance_runs(job_name,executed_at,execution_user,note)  VALUES('expected_failure',CURRENT_TIMESTAMP(6),CURRENT_USER(),'should lack INSERT');

The account has EVENT but no INSERT on maintenance_runs. Creation can succeed, while execution later fails under the event's definer privileges. Inspect INFORMATION_SCHEMA.EVENTS and your local MySQL error log. The exact log path depends on Chapter 02 configuration, so discover it rather than guessing.

SHOW VARIABLES LIKE 'log_error';SELECT EVENT_NAME,DEFINER,STATUS,LAST_EXECUTEDFROM INFORMATION_SCHEMA.EVENTSWHERE EVENT_SCHEMA='servicehub_logic_lab'  AND EVENT_NAME='e_ch10_expected_failure';SELECT COUNT(*) AS unexpected_rowsFROM maintenance_runsWHERE job_name='expected_failure';

The repair is not “grant ALL.” Either give the dedicated event definer the one permission its job requires or redesign the task under an external scheduler/service identity. For this demonstration, clean the failing object up instead of broadening privileges.

Recurring-job overlap is a real design problem

If an event takes longer than its recurrence interval, multiple instances can overlap. MySQL's event documentation explicitly warns about this. A safe recurring maintenance design therefore needs idempotency or a serialization mechanism such as advisory locking (GET_LOCK()) or a row/table coordination strategy, plus bounded work per run.

Disable and drop cleanly

ALTER EVENT e_ch10_heartbeat DISABLE;SHOW EVENTS FROM servicehub_logic_lab LIKE 'e_ch10_heartbeat';DROP EVENT e_ch10_heartbeat;DROP EVENT IF EXISTS e_ch10_expected_failure;-- Admin cleanup after disconnecting event_limited:DROP USER IF EXISTS 'event_limited'@'127.0.0.1';SELECT COUNT(*) AS heartbeat_rowsFROM maintenance_runs WHERE job_name='ch10_heartbeat';

Keep the heartbeat rows as evidence for Lesson 5, or delete them only after you have recorded the lab result.

Event Scheduler versus external scheduler

Hands-on lab acceptance checklist

• You recorded the actual event_scheduler state instead of assuming the default.
• The successful event records UTC schedule metadata and produces observable heartbeat rows.
• You can explain why STATUS='ENABLED' does not prove successful execution.
• The underprivileged definer case creates no maintenance row and points you to error-log evidence.
• All disposable events and the limited account are disabled/dropped at the end.

Production judgment and next step

Use events for small, database-local recurring work with a dedicated definer, explicit schedule/time zone, bounded runtime, idempotency, and observable success/failure. Prefer an external scheduler when jobs need cross-system coordination, sophisticated retry/alerting, independent scaling, or application-team ownership.

Lesson 5 brings the chapter together by deciding which logic belongs in views, routines, triggers, events, or application code.

Authoritative references

• MySQL 8.4 — Using the Event Scheduler
• MySQL 8.4 — CREATE EVENT
• MySQL 8.4 — Event Scheduler Configuration
• MySQL 8.4 — Event Scheduler Status
• MySQL 8.4 — Event Scheduler and Privileges