Event Scheduler, Recurring Jobs, Maintenance, and Failure Visibility

Learning outcomes

ServiceHub needs to expire abandoned reservation claims and record maintenance outcomes every few minutes. Putting the task in the web application means it only runs while that process is healthy; putting it in MariaDB's Event Scheduler keeps data-local work near the data. But a recurring job that has no owner, no run log, ambiguous time-zone behavior or overlapping executions can be harder to operate than an external scheduler. An event is a named database object whose SQL body is executed according to a schedule by the MariaDB server when the Event Scheduler is enabled.

1. A scheduled object and an enabled scheduler are two separate states

An event can exist in metadata while the server's scheduler is OFF. Start diagnosis by checking both layers. Do not treat “CREATE EVENT succeeded” as evidence that the job will run.

SELECT VERSION();SHOW VARIABLES LIKE 'event_scheduler';SELECT @@global.time_zone AS global_time_zone,       @@session.time_zone AS session_time_zone,       NOW() AS session_now,       UTC_TIMESTAMP() AS utc_now;SHOW PROCESSLIST;

When the scheduler is ON, process metadata normally includes the scheduler thread. That proves the scheduler is active on this server; it does not prove a particular event is enabled, due, successful or owned by the right definer. If the lab server permits it and the variable is OFF, an administrator can enable it with SET GLOBAL event_scheduler=ON. Persisting that choice requires the server's configuration mechanism, not just a runtime statement.

2. Create a recurring job with an explicit UTC scheduling contract

Use a fixed offset for the lab so named time-zone tables are not required. MariaDB records an event's scheduling time zone in metadata. Production teams should decide whether “every day at 02:00” means a local civil time that can move with daylight-saving rules or a UTC instant. Hidden time-zone assumptions are a common source of missed maintenance windows.

DROP DATABASE IF EXISTS servicehub_programmability_lab;CREATE DATABASE servicehub_programmability_lab;USE servicehub_programmability_lab;SET time_zone='+00:00';CREATE TABLE reservation_claims (  claim_id BIGINT PRIMARY KEY,  state ENUM('held','confirmed','expired') NOT NULL,  expires_at DATETIME NOT NULL) ENGINE=InnoDB;CREATE TABLE maintenance_runs (  run_id BIGINT AUTO_INCREMENT PRIMARY KEY,  job_name VARCHAR(80) NOT NULL,  started_at DATETIME NOT NULL,  finished_at DATETIME NULL,  rows_changed INT NULL,  outcome ENUM('running','ok','failed','skipped') NOT NULL,  detail VARCHAR(500) NULL) ENGINE=InnoDB;INSERT INTO reservation_claims VALUES(4001,'held',UTC_TIMESTAMP()-INTERVAL 10 MINUTE),(4002,'held',UTC_TIMESTAMP()+INTERVAL 30 MINUTE),(4003,'confirmed',UTC_TIMESTAMP()-INTERVAL 1 DAY);DELIMITER $$CREATE OR REPLACE PROCEDURE sp_expire_claims()SQL SECURITY INVOKERMODIFIES SQL DATABEGIN  DECLARE v_run BIGINT;  DECLARE v_rows INT DEFAULT 0;  DECLARE v_message TEXT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    GET DIAGNOSTICS CONDITION 1 v_message=MESSAGE_TEXT;    UPDATE maintenance_runs      SET finished_at=UTC_TIMESTAMP(),outcome='failed',detail=v_message      WHERE run_id=v_run;    RESIGNAL;  END;  INSERT INTO maintenance_runs(job_name,started_at,outcome)  VALUES('expire_claims',UTC_TIMESTAMP(),'running');  SET v_run=LAST_INSERT_ID();  UPDATE reservation_claims  SET state='expired'  WHERE state='held' AND expires_at < UTC_TIMESTAMP();  SET v_rows=ROW_COUNT();  UPDATE maintenance_runs  SET finished_at=UTC_TIMESTAMP(),rows_changed=v_rows,outcome='ok'  WHERE run_id=v_run;END$$DELIMITER ;

CREATE OR REPLACEDEFINER=CURRENT_USEREVENT ev_expire_claimsON SCHEDULE EVERY 1 MINUTESTARTS CURRENT_TIMESTAMP + INTERVAL 10 SECONDON COMPLETION PRESERVEENABLEDO CALL servicehub_programmability_lab.sp_expire_claims();SHOW EVENTS FROM servicehub_programmability_lab;SHOW CREATE EVENT servicehub_programmability_lab.ev_expire_claims\GSELECT EVENT_NAME,DEFINER,TIME_ZONE,EVENT_TYPE,INTERVAL_VALUE,INTERVAL_FIELD,       STATUS,LAST_EXECUTEDFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub_programmability_lab';

The metadata should show a recurring enabled event and the time zone captured for scheduling. After a run, LAST_EXECUTED should advance and the run table should show an ok row. LAST_EXECUTED is evidence that MariaDB attempted a scheduled execution; your own run log provides business-level outcome details such as rows changed.

3. Scheduled code needs failure visibility because there is no waiting client

When a procedure called interactively fails, the client receives an error. An event has no human client waiting for that result. MariaDB can write event errors/warnings to server logs, but production observability should not depend on somebody eventually reading an error file. Record a minimal job run state and alert when expected successful executions stop appearing.

SELECT claim_id,state,expires_atFROM reservation_claims ORDER BY claim_id;SELECT run_id,job_name,started_at,finished_at,rows_changed,outcome,detailFROM maintenance_runsORDER BY run_id DESCLIMIT 10;SELECT EVENT_NAME,STATUS,LAST_EXECUTEDFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub_programmability_lab';

A useful alert compares “now” with the expected cadence and the last successful run, not only the last attempted execution. If the server was down during one or more intervals, do not assume every missed interval will be replayed exactly once after restart. Make maintenance idempotent so rerunning it is safe.

4. Deliberately wrong: let a fast schedule overlap a slow job

A recurring event can be scheduled more frequently than its body completes. Overlap can multiply locks and I/O, especially when each execution scans the same rows. Do not use the schedule interval as a concurrency control. A simple single-server guard can use an advisory lock; in multi-node topologies, an advisory lock is only local to one server and is not a cluster-wide fencing mechanism.

DELIMITER $$CREATE OR REPLACE PROCEDURE sp_expire_claims_guarded()SQL SECURITY INVOKERMODIFIES SQL DATABEGIN  DECLARE v_lock INT DEFAULT 0;  SELECT GET_LOCK('servicehub:expire_claims',0) INTO v_lock;  IF v_lock <> 1 THEN    INSERT INTO maintenance_runs(job_name,started_at,finished_at,outcome,detail)    VALUES('expire_claims',UTC_TIMESTAMP(),UTC_TIMESTAMP(),'skipped','previous execution still owns local advisory lock');  ELSE    BEGIN      DECLARE EXIT HANDLER FOR SQLEXCEPTION      BEGIN        DO RELEASE_LOCK('servicehub:expire_claims');        RESIGNAL;      END;      CALL sp_expire_claims();      DO RELEASE_LOCK('servicehub:expire_claims');    END;  END IF;END$$DELIMITER ;ALTER EVENT ev_expire_claimsDO CALL servicehub_programmability_lab.sp_expire_claims_guarded();

This guard converts overlap on one MariaDB instance into an observable skipped outcome. It is not a universal distributed lock. For asynchronous replication, Galera, or active-active service layouts, choose one scheduler owner and fence it explicitly, or move the orchestration to a system that has cluster-aware singleton semantics.

5. Definers, replicas and Galera make ownership a topology decision

Events have definers. If the definer disappears or loses required privileges, scheduled execution can fail even though the event remains visible. Validate event definers after restore/migration exactly as you do for views and routines. Multi-node deployments add a second ownership question: which server is allowed to execute the recurring work?

Promotion runbooks should include event ownership. Otherwise a failover can produce either no scheduler owner or two. The job body should also be idempotent and transactionally scoped so a retry after uncertain failure does not corrupt state.

6. Event Scheduler versus external schedulers

External options include cron/systemd timers, Windows Task Scheduler, Kubernetes CronJob or workflow platforms such as Airflow. None is automatically better; the key requirement is a documented owner, failure signal, retry/idempotency story and deployment path.

7. Verification, cleanup, and bridge

1. Verify server scheduler state and persisted configuration policy.
2. Capture SHOW CREATE EVENT and INFORMATION_SCHEMA.EVENTS metadata including definer, time zone, status and last execution.
3. Confirm at least one successful run row and the expected expired claim.
4. Test the overlap guard by invoking the guarded procedure from two sessions if practical; record that the lock is instance-local.
5. Document promotion/failover ownership before enabling the same event in any multi-node topology.

ALTER EVENT servicehub_programmability_lab.ev_expire_claims DISABLE;SHOW EVENTS FROM servicehub_programmability_lab;DROP DATABASE IF EXISTS servicehub_programmability_lab;

Lesson 5 turns the whole chapter into an operational release problem: stored objects must be versioned, ordered, privilege-tested, diffable and reversible like application code.