MariaDB Tutorial / Schedule Database Tasks and Verify Event Results

The MariaDB Event Scheduler runs SQL at a scheduled time or interval. An existing event definition, an enabled scheduler, and a successful business task are three different conditions, so inspect both scheduling metadata and the data produced by the task.

Choose an appropriate task and execution account

Events can maintain a short database-local summary or perform bounded cleanup. External APIs, file transfers, and coordinated retries or alerts across systems may be better handled by an application worker or operations scheduler. This lab records an execution row instead of deleting data, making its result easy to inspect. Use Inspect Server Settings, SQL Modes, Time Zones, and Logs to inspect scheduling time zones and failure logs; decide which replica may run jobs using the write-path policy in Understand Replication, Lag, Errors, and Failover.

Use a dedicated appops_lab schema with CREATE TABLE, INSERT, and EVENT privileges. The event executes with its DEFINER account’s privileges; the currently connected client does not execute the scheduled task on its behalf. Account or privilege changes can therefore affect subsequent runs. See the MariaDB events overview.

Inspect scheduler state and time zones first

SELECT @@GLOBAL.event_scheduler, @@GLOBAL.time_zone, @@SESSION.time_zone;

The scheduler must be ON to execute schedules. OFF allows definitions to exist without running them. DISABLED requires reviewing startup configuration and DBA policy rather than assuming an ordinary runtime switch to ON will work. Enabling the global scheduler on a shared server can activate other events as well.

Only on a new dedicated lab server should its administrator enable it if needed with SET GLOBAL event_scheduler = ON;. Record the original state. Stop your own event first when finishing; turning the global scheduler off again requires checking the effect on other jobs.

The creation script below uses session time zone +00:00 to make schedule interpretation explicit. Record the previous session value and restore it afterward. The client PC’s clock and display zone are not necessarily the server’s scheduling context.

Create the result table and a disabled definition

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE event_runs (
  run_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ran_at DATETIME(6) NOT NULL,
  run_label VARCHAR(40) NOT NULL
) ENGINE=InnoDB;

SET @event_saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+00:00';
CREATE EVENT appops_lab.record_run
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
  ON COMPLETION PRESERVE
  DISABLE
  DO INSERT INTO appops_lab.event_runs (ran_at, run_label) VALUES (UTC_TIMESTAMP(6), 'scheduled-check');

Creating the event as DISABLE prevents execution before review. ON COMPLETION PRESERVE retains a one-time event definition after its schedule completes. It is not a data-retention rule or a guarantee that the task will be retried. The CREATE EVENT reference explains schedules and completion handling.

SELECT @@GLOBAL.event_scheduler;
SELECT EVENT_NAME, STATUS, TIME_ZONE, EXECUTE_AT, LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'appops_lab' AND EVENT_NAME = 'record_run';
SELECT run_id, ran_at, run_label FROM appops_lab.event_runs ORDER BY run_id;

Initially, the metadata should contain one event whose STATUS is DISABLED, and the result table should be empty. You can inspect the definition even while the scheduler is off. Check the event name, schema, and time zone before enabling it.

Set a fresh future time and verify the actual result

The ten-second time in the original definition may have passed while you were reading or connecting. Reschedule the event into the future as you enable it instead of reusing a stale one-time schedule.

ALTER EVENT appops_lab.record_run
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
  ENABLE;

After roughly ten seconds, run the inspection SQL again. This scheduler is not a precise real-time timer; exact wall-clock equality is not a success criterion. You should find one scheduled-check row and a populated LAST_EXECUTED value. The preserved one-time event becomes DISABLED after completion.

LAST_EXECUTED is useful evidence of an execution attempt, not independent proof that the entire business task succeeded. Reconcile the actual result row as well. Each execution uses a separate connection, so the current client’s affected-row count also does not establish that the scheduled task ran.

Bound recurring experiments and design for retries

After observing the one-time run, this bounded schedule can demonstrate recurring execution. An end time avoids accidentally leaving an unlimited lab job active.

ALTER EVENT appops_lab.record_run
  ON SCHEDULE EVERY 1 MINUTE
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
  ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE
  ENABLE;

The example body appends one row per execution. A real summary needs a unique key for its reporting period and an explicit rerun policy. Recomputing a period from source rows or otherwise making the operation idempotent prevents a retry from adding the same total twice. Also assess overlap if a run can last longer than its interval.

The event limitations describe timing, ordering, and separate execution connections. Decide which server owns scheduled work during replication and failover so the same task does not run on multiple servers unexpectedly.

Stop, diagnose, and remove the lab objects

ALTER EVENT appops_lab.record_run DISABLE;
SHOW CREATE EVENT appops_lab.record_run;

1. Disable future runs and confirm the task has stopped. Disabling a schedule does not mean an already running invocation is immediately canceled.
2. Inspect scheduler state, event STATUS, DEFINER privileges, TIME_ZONE, and schedule. If LAST_EXECUTED changes without the expected result, inspect the server error log and failure conditions of the SQL body.
3. Remove the event before its disposable result table when those lab results are no longer needed.

DROP EVENT appops_lab.record_run;
DROP TABLE appops_lab.event_runs;

SET SESSION time_zone = @event_saved_time_zone;

Restore the original session time zone as well. When a recurring job fails, stop and establish its cause and reprocessing scope before editing an active definition; otherwise missing work and duplicated work become difficult to distinguish.