How to Schedule Queries in MariaDB

Galaxy Glossary

How do I schedule recurring SQL queries in MariaDB without external cron jobs?

Automatically executes one-off or recurring SQL statements at defined times via the MariaDB Event Scheduler.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why schedule queries in MariaDB?

Automated events eliminate cron jobs, keep logic inside the database, and guarantee that recurring maintenance, reporting, and data-movement tasks run even if application servers are down.

How do I enable the Event Scheduler?

Session level: SET @@event_scheduler = ON; Global level (persists restart): SET GLOBAL event_scheduler = ON; or add event_scheduler=ON in my.cnf.

What is the basic CREATE EVENT syntax?

Use CREATE EVENT to define a name, schedule, status, and the SQL to run. Schedules accept AT TIMESTAMP for one-off runs or EVERY interval [STARTS ts] [ENDS ts] for recurring runs.

How do I schedule a daily sales summary?

Create an event that runs every night at 00:05 and inserts yesterday’s totals into a daily_sales table.

CREATE EVENT IF NOT EXISTS ev_nightly_sales ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 5 MINUTE ON COMPLETION PRESERVE ENABLE DO INSERT INTO daily_sales(report_date, total_amount) SELECT CURRENT_DATE - INTERVAL 1 DAY, SUM(total_amount) FROM Orders WHERE order_date = CURRENT_DATE - INTERVAL 1 DAY;

How can I modify or disable an event?

Use ALTER EVENT ev_name ON SCHEDULE ... to change timing or ALTER EVENT ev_name DISABLE to pause execution. Dropping an event permanently: DROP EVENT IF EXISTS ev_name;.

What permissions are required?

The EVENT privilege is required to create, alter, or drop events. The SUPER privilege is needed to toggle the global scheduler setting.

How do I monitor event execution?

Query information_schema.EVENTS for schedule metadata and information_schema.EVENT_STATISTICS for run counts and last execution times.

Best practices for production

Namespace event names by project, wrap complex logic in stored procedures, keep events idempotent, and log results into audit tables for easy troubleshooting.

Common mistakes

• Scheduler disabled: confirm SELECT @@event_scheduler returns ON.
• Using CURRENT_TIMESTAMP inside ON SCHEDULE AT: evaluate to a fixed time first; otherwise the event never runs.

Why How to Schedule Queries in MariaDB is important

How to Schedule Queries in MariaDB Example Usage


-- Archive shipped orders to OrdersHistory table every hour
CREATE EVENT ev_archive_shipped_orders
ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO OrdersHistory
  SELECT * FROM Orders WHERE status = 'shipped' AND order_date < NOW() - INTERVAL 30 DAY;

  DELETE FROM Orders WHERE status = 'shipped' AND order_date < NOW() - INTERVAL 30 DAY;

How to Schedule Queries in MariaDB Syntax


CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE
        AT timestamp_value                    -- one-off execution
        | EVERY interval_value [STARTS ts] [ENDS ts]   -- recurring
    [ON COMPLETION [NOT] PRESERVE]            -- keep or drop after run
    [ENABLE | DISABLE]                        -- initial status
    [COMMENT 'text']
DO sql_statement;

-- Example: clear zero-stock products each Sunday night
CREATE EVENT ev_delete_out_of_stock
ON SCHEDULE EVERY 1 WEEK STARTS '2024-06-16 23:55:00'
DO
  DELETE FROM Products WHERE stock = 0;

Common Mistakes

Mistake: Enabling the scheduler only at session level (<code>SET @@event_scheduler=ON</code>). It turns off after disconnect. Fix: run <code>SET GLOBAL event_scheduler=ON</code> or set it in <code>my.cnf</code>.
Mistake: Forgetting <code>ON COMPLETION PRESERVE</code> for recurring maintenance in <code>AT</code> events. One-off events drop by default, so edits fail. Fix: add <code>ON COMPLETION PRESERVE</code> or recreate the event.