Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 40 ยท Stored Programs and Triggers

Cursors and Scheduled Events in MariaDB

Loop over query results with MariaDB cursors (DECLARE, OPEN, FETCH, CLOSE) and run SQL on a schedule with CREATE EVENT and the event scheduler, with safe patterns and alternatives.

Two more stored-program features finish this chapter. A cursor lets a stored program walk through a result set one row at a time. An event is a job stored in the database that MariaDB runs on a schedule, like a built-in cron for SQL.

Both are MariaDB server features, so the MariaDB code is read-only here. Each part has a runnable stand-in showing the set-based alternative that you should usually prefer.

Cursors

SQL normally works on sets. Sometimes you must treat rows individually: call a procedure for each row, build a text report, or process a queue. A cursor does that inside a stored procedure, function or event.

The life cycle has four steps:

  1. DECLARE cursor_name CURSOR FOR select_statement;
  2. OPEN cursor_name;
  3. FETCH cursor_name INTO var1, var2, ...; repeatedly
  4. CLOSE cursor_name;

When FETCH runs out of rows, MariaDB raises the condition NOT FOUND (SQLSTATE 02000). You catch it with a handler that sets a flag:

SQL
DELIMITER //
CREATE PROCEDURE build_salary_report(OUT p_report TEXT)
BEGIN
  DECLARE v_done  BOOL DEFAULT FALSE;
  DECLARE v_name  VARCHAR(80);
  DECLARE v_pay   INT;

  DECLARE cur CURSOR FOR
    SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

  SET p_report = '';
  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_name, v_pay;
    IF v_done THEN
      LEAVE read_loop;
    END IF;
    SET p_report = CONCAT(p_report, v_name, ': ', v_pay, '\n');
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;

Details that trip people up:

  • Declaration order is variables, cursors, handlers. Declaring the handler before the cursor is an error.
  • The variables in FETCH ... INTO must match the select list in number and compatible type.
  • Cursors are read-only and forward-only: no scrolling back, no update through the cursor. To change the row use a separate UPDATE ... WHERE id = v_id.
  • A cursor sees a snapshot taken when it opens in some cases and live data in others (MariaDB may materialize it into a temporary table), so do not rely on seeing your own changes while looping.
  • MariaDB 10.3+ also supports FOR rec IN (SELECT ...) DO ... END FOR (in Oracle mode as FOR rec IN cur LOOP) which declares, opens, fetches and closes for you.

Why cursors are a last resort

Row-by-row processing is often 10 to 1000 times slower than one statement, because each fetched row and each statement inside the loop has overhead. Before reaching for a cursor, ask whether a join, an aggregate, a window function or an INSERT ... SELECT can do it.

Stand-in: the report above is one query, with GROUP_CONCAT building the text:

SQL The set-based alternative to a report cursor ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Another typical cursor job is "running balance". With a window function it needs no loop:

SQL Running balance without a cursor

Press Run (or Ctrl + Enter) to see the result here.

The event scheduler

An event runs SQL at a set time or at regular intervals, without any external cron job. Typical uses: delete old rows, rebuild a summary table, close expired sessions, archive data.

The scheduler is a background thread. Check and enable it:

SQL
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;      -- until restart; set event_scheduler=ON in the option file to keep it

Creating events

SQL
-- one-off
CREATE EVENT send_reminder_once
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO INSERT INTO outbox (kind) VALUES ('reminder');

-- recurring
CREATE EVENT purge_old_sessions
ON SCHEDULE EVERY 1 DAY
STARTS '2026-04-01 03:00:00'
ON COMPLETION PRESERVE
COMMENT 'Remove sessions older than 30 days'
DO
  DELETE FROM sessions WHERE last_seen < NOW() - INTERVAL 30 DAY LIMIT 10000;

Clauses:

  • AT timestamp for a single run; EVERY n unit (MINUTE, HOUR, DAY, WEEK, MONTH...) for repeats, optionally with STARTS and ENDS.
  • ON COMPLETION PRESERVE keeps the event after its last run (the default NOT PRESERVE drops a finished one-off event).
  • ENABLE, DISABLE, DISABLE ON SLAVE (DISABLE ON REPLICA in newer versions): events replicate as definitions, and you normally want them active only on the primary. Replicas disable them automatically.
  • A multi-statement body uses BEGIN ... END, or CALL maintenance_proc(); to run a stored procedure.

Manage events with:

SQL
SHOW EVENTS;
SHOW CREATE EVENT purge_old_sessions;
ALTER EVENT purge_old_sessions DISABLE;
DROP EVENT IF EXISTS purge_old_sessions;
SELECT * FROM information_schema.EVENTS;

Events run with the privileges of their definer and need the EVENT privilege. They use the server's time_zone setting at creation time, so daylight-saving shifts can move a run by an hour.

Pattern: purge in small batches

Deleting millions of old rows in one statement holds locks and bloats the undo log, so delete in chunks. The LIMIT 10000 above does one chunk per run; run it every minute until the backlog is gone, or loop inside a procedure until ROW_COUNT() = 0:

SQL
DELIMITER //
CREATE PROCEDURE purge_sessions()
BEGIN
  REPEAT
    DELETE FROM sessions WHERE last_seen < NOW() - INTERVAL 30 DAY LIMIT 5000;
  UNTIL ROW_COUNT() < 5000 END REPEAT;
END //
DELIMITER ;

Runnable stand-in of the purge (the date is fixed so the result is repeatable):

SQL Purge old rows

Press Run (or Ctrl + Enter) to see the result here.

Pattern: refresh a summary table

MariaDB has no materialized views, so an event can rebuild a report table (REPLACE INTO or truncate and insert in a transaction):

SQL
CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO daily_sales (day, orders, revenue)
  SELECT DATE(ordered_on), COUNT(*), SUM(total)
  FROM orders
  WHERE ordered_on >= CURDATE() - INTERVAL 2 DAY
  GROUP BY DATE(ordered_on);
SQL Rebuild a summary table

Press Run (or Ctrl + Enter) to see the result here.

Events versus cron

Events are convenient because they travel with the database (dumps, replicas), need no extra credentials and run inside the server. External schedulers (cron, systemd timers, a job queue) are easier to monitor, log, retry and alert on, and they can call other systems. A common split: events for pure SQL housekeeping, cron or a queue for anything that needs logging, retries or non-SQL work. Whatever you choose, make jobs idempotent so a double run is harmless.

Common mistakes

  • Creating an event and forgetting event_scheduler=ON, so nothing ever runs.
  • Declaring the NOT FOUND handler before the cursor.
  • Using a cursor where one UPDATE ... JOIN would do.
  • Long-running events that overlap themselves: MariaDB skips a run if the previous one is still active, which can silently drop executions.
  • Forgetting the event runs as its definer: dropping that account stops it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the correct declaration order inside a stored program with a cursor?

    MariaDB requires variables first, then cursors, then handlers.
  2. 2. How does a cursor loop know it has fetched the last row?

    FETCH past the end raises NOT FOUND (SQLSTATE 02000); the handler sets the flag the loop checks.
  3. 3. What must be true for events to run?

    The scheduler thread is what executes events; check SHOW VARIABLES LIKE 'event_scheduler'.
  4. 4. Which is usually better than a cursor for computing a running balance?

    Window functions do the cumulative work in one set-based pass.

Summary

  • Cursors (DECLARE, OPEN, FETCH, CLOSE + NOT FOUND handler) process rows one by one; they are slow, so prefer set-based SQL.
  • Events (CREATE EVENT ... ON SCHEDULE) run SQL on a timer once event_scheduler is ON.
  • Purge or archive in small batches and make scheduled jobs idempotent.
  • Use external schedulers when you need logging, retries or non-SQL work.

Next: Creating and Managing Indexes