How to Schedule Jobs in MariaDB

Galaxy Glossary

How do I schedule recurring SQL jobs in MariaDB?

The MariaDB Event Scheduler lets you run SQL statements automatically at predefined times or intervals.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use the Event Scheduler instead of cron?

Event Scheduler keeps logic close to data, runs inside the DB engine, respects transactions, and works cross-platform—no OS-level access required.

How do I enable the Event Scheduler?

Run SET GLOBAL event_scheduler = ON; once. Add event_scheduler=ON to my.cnf for permanence.

What is the basic syntax for CREATE EVENT?

Use CREATE EVENT plus a timing clause (AT or EVERY) and a SQL body. Add ON COMPLETION PRESERVE to keep one-time events.

How to schedule a daily cleanup job?

CREATE EVENT daily_old_orders_cleanupON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAYDO DELETE FROM Orders WHERE order_date < CURRENT_DATE - INTERVAL 90 DAY;

Can I alter or disable a job without dropping it?

Yes. Use ALTER EVENT ... ON SCHEDULE ... to change timing, or ALTER EVENT ... DISABLE to pause execution.

How do I monitor scheduled jobs?

Query INFORMATION_SCHEMA.EVENTS to see last and next run times, status, SQL body, and execution counts.

Best practice: use DEFINER and SQL SECURITY

Set a DEFINER with the minimum required privileges to limit risk. Pair with SQL SECURITY DEFINER for predictable permissions.

Best practice: log job results

Wrap body inside a procedure that writes to a JobLog table capturing event_name, started_at, rows_affected, and error_message.

Why How to Schedule Jobs in MariaDB is important

How to Schedule Jobs in MariaDB Example Usage


-- Auto-reprice products below competitor average once per hour
CREATE EVENT hourly_reprice
    ON SCHEDULE EVERY 1 HOUR
    DO
    UPDATE Products p
       JOIN (
         SELECT product_id, AVG(price) AS avg_price
         FROM CompetitorPrices
         GROUP BY product_id
       ) c ON c.product_id = p.id
    SET p.price = ROUND(c.avg_price * 0.98, 2)
    WHERE p.stock > 0;

How to Schedule Jobs in MariaDB Syntax


CREATE [DEFINER = user] EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE {
        AT timestamp [+ INTERVAL expr interval]
      | EVERY expr interval
          [STARTS timestamp [+ INTERVAL expr interval]]
          [ENDS timestamp [+ INTERVAL expr interval]]
    }
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'string']
    DO sql_statement;

-- Example (ecommerce)
CREATE EVENT daily_low_stock_alert
ON SCHEDULE EVERY 1 DAY STARTS '2024-08-29 02:00:00'
DO
  INSERT INTO Notifications(message, created_at)
  SELECT CONCAT('Low stock: ', name, ' (', stock, ')'), NOW()
  FROM Products WHERE stock < 10;

Common Mistakes

Forgetting to enable the scheduler. Setting up events while <code>event_scheduler</code> is OFF means nothing runs. Fix: <code>SET GLOBAL event_scheduler = ON;</code>.
Using NOW() in STARTS clause with EVERY interval. This can fire immediately multiple times. Fix: use <code>STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR</code> for hourly jobs.