Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 75 ยท Programmable Objects

Events and the Event Scheduler

Learn MySQL events: CREATE EVENT, one-time and recurring schedules, the event_scheduler variable, a purge-old-rows pattern, and when cron is the better choice.

A MySQL event is a named piece of SQL that the server runs on a schedule, with no client connected. Think of it as cron that lives inside the database: "every night at 03:00, delete sessions older than 30 days" or "on the first day of the month, build the monthly summary table". Because the schedule is stored with the schema, it travels with backups and dumps and needs no extra machine setup.

This lesson shows how to create, inspect and switch off events, the two scheduling styles (one-time and recurring), the event_scheduler switch that surprises many people, and a purge pattern you can reuse. It also compares events with an external scheduler, because events are handy but not always the right tool. You should know Stored Procedures and Functions first, since event bodies are written in the same language.

What an event is

A stored schedule SQL plus a timetable, kept in the database.
No client needed A scheduler thread runs it inside the server.
Part of the schema Dumps and backups carry it along.

Events are cron for SQL-only chores.

1 / 3

Turning the scheduler on

Events run on a background scheduler thread, and that thread is controlled by a system variable:

SQL
SHOW VARIABLES LIKE 'event_scheduler';
-- event_scheduler | ON

The values are ON, OFF and DISABLED. In MySQL 8 the default is ON, but managed services and hardened configurations often set it to OFF, and a replica or a restored test copy may behave differently from production. With OFF you can still create events, but nothing runs until you switch the scheduler on:

SQL
SET GLOBAL event_scheduler = ON;

That command lasts until the next restart. To make it permanent, put event_scheduler = ON in the [mysqld] section of the configuration file, or use SET PERSIST event_scheduler = ON. DISABLED can only be set at startup and means the thread is never created, so you cannot turn it on while the server runs. A very common bug report is "my event never fires", and the cause is nearly always this variable. Check it before you check anything else.

Creating an event

The general shape is:

SQL
CREATE EVENT [IF NOT EXISTS] event_name
  ON SCHEDULE schedule
  [ON COMPLETION [NOT] PRESERVE]
  [ENABLE | DISABLE | DISABLE ON REPLICA]
  [COMMENT 'text']
  DO event_body;

Events belong to a database, so the full name is shop.purge_old_sessions. The body after DO is one statement. For more than one statement, wrap them in BEGIN ... END and change the client delimiter, exactly as you do for procedures.

One-time events

AT runs the body once at a given moment. The moment can be a literal timestamp or an expression based on NOW():

SQL
CREATE EVENT archive_2024_orders
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
  DO
    INSERT INTO orders_archive
    SELECT * FROM orders WHERE order_date < '2025-01-01';

By default a one-time event is dropped automatically after it finishes, because ON COMPLETION NOT PRESERVE is the default for AT events. Add ON COMPLETION PRESERVE when you want the definition to stay, for example so you can read its status or run it again by changing its schedule.

Recurring events

EVERY repeats the body at a fixed interval. The unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR and compound units such as DAY_HOUR:

SQL
CREATE EVENT purge_old_sessions
  ON SCHEDULE EVERY 1 DAY
  STARTS '2026-01-01 03:00:00'
  COMMENT 'Delete sessions that expired more than 30 days ago'
  DO
    DELETE FROM sessions
    WHERE last_seen < NOW() - INTERVAL 30 DAY;

STARTS sets the first run (by default, the moment you create the event) and ENDS stops the schedule at a given time. Because STARTS anchors the interval, "every day at 03:00" is just a daily interval whose start time is 03:00. If STARTS is in the past, MySQL computes the next run from the interval and does not replay the missed runs. MySQL also does not run two executions of the same event at once: if a run takes longer than the interval, the next one is skipped and the schedule continues afterwards.

Time zone matters. An event uses the session time zone that was in effect when it was created, stored with the definition, so NOW() inside it is consistent even if the server zone changes later. Still, say what you mean in your schedule and document it in the COMMENT.

The purge-old-rows pattern

Deleting old data is the most common use of events. A careless version is one huge DELETE:

SQL
DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 90 DAY;

On a table with hundreds of millions of rows, this single statement holds locks for a long time, grows the undo log and can lag replicas by minutes. The safer pattern deletes in small batches and repeats until nothing is left:

SQL
DELIMITER $$

CREATE EVENT purge_audit_log
  ON SCHEDULE EVERY 1 HOUR
  DO
  BEGIN
    DECLARE rows_deleted INT DEFAULT 1;
    DECLARE loops INT DEFAULT 0;

    WHILE rows_deleted > 0 AND loops < 100 DO
      DELETE FROM audit_log
      WHERE created_at < NOW() - INTERVAL 90 DAY
      ORDER BY id
      LIMIT 5000;

      SET rows_deleted = ROW_COUNT();
      SET loops = loops + 1;
    END WHILE;
  END$$

DELIMITER ;

Each pass removes at most 5,000 rows and commits (when autocommit is on), so locks are short. The loops cap guarantees the event ends even if new old rows keep arriving. An index on created_at makes each batch cheap (How MySQL Indexes Work). For tables that you purge by age every day, Partitioning is better still, because dropping a whole partition is nearly instant.

SQLite has no events, but the logic of "delete in batches until changes() is zero" can be tried live. This demo uses a fixed reference date so the result never changes:

SQL Purge old rows in small batches

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

In MySQL the batch loop would stop once ROW_COUNT() returns 0. Here, rows older than 60 days are those with ids 7 to 12, six rows, so two batches of three finish the job and six rows stay. SQLite's DELETE ... LIMIT is only available in special builds, so the demo uses the portable id IN (subquery with LIMIT) form; MySQL itself does not allow LIMIT inside an IN subquery, which is why the MySQL version above uses DELETE ... ORDER BY ... LIMIT.

Managing events

SQL
SHOW EVENTS FROM shop;                      -- list events in a database
SHOW CREATE EVENT shop.purge_old_sessions;  -- full definition

ALTER EVENT purge_old_sessions DISABLE;     -- keep it, but stop running it
ALTER EVENT purge_old_sessions ENABLE;
ALTER EVENT purge_old_sessions
  ON SCHEDULE EVERY 6 HOUR;                 -- change the schedule
ALTER EVENT purge_old_sessions
  RENAME TO purge_expired_sessions;

DROP EVENT IF EXISTS purge_expired_sessions;

The information_schema.EVENTS table shows the status, last run time (LAST_EXECUTED), interval and definer for every event, which makes it easy to build a monitoring query (The Data Dictionary, information_schema and sys Schema).

You need the EVENT privilege on the database to create or alter events. When the event runs it uses the privileges of its DEFINER, which is the account that created it unless you name another with DEFINER = 'maint'@'localhost'. If that account is dropped or loses rights, the event fails quietly. Using a dedicated maintenance account with only the rights the event body needs is the safe choice (Users, Privileges and Roles).

Events and replication

If you replicate, the events on the source also exist on the replica, which would run the same purge twice. The DISABLE ON REPLICA option handles this: the event is created disabled on replicas and is enabled again if a replica is promoted. Events execute locally, but their effects replicate as normal row changes. See The Binary Log and Replication.

Events versus cron

Question MySQL event External scheduler (cron, systemd timer, Laravel scheduler)
Needs only SQL Great fit Works, but needs a client and credentials
Needs files, HTTP, email Impossible Easy
Retries, alerts, logs Error log only Full control
Moves with a database dump Yes No
Runs once on a cluster Needs care with replicas and failover You decide which host runs it
Visibility to developers Hidden in the database In the code repository

A good rule: use an event for pure data housekeeping that belongs to the schema (purging, rolling up, partition maintenance). Use an application or OS scheduler when the job calls other systems, needs good logging, or should be reviewed with the application code. Whichever you pick, put the job's definition in version control: keep CREATE EVENT statements in your migration files so every environment gets the same schedule.

Be careful with long events. They hold a scheduler thread, run with a limited session, and cannot be interrupted except by dropping or disabling them. Keep them short, batch the work and make them safe to run twice.

What ran live here

The batched-delete logic ran in SQLite, using date() arithmetic and changes() in place of NOW() and ROW_COUNT(). CREATE EVENT, the scheduler variable, SHOW EVENTS and everything else on this page are MySQL server features and are shown only as read-only code blocks.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Your CREATE EVENT succeeded, but the body never runs. What should you check first?

    Events run on the scheduler thread. If event_scheduler is OFF, events can be created but nothing fires until it is switched ON (and DISABLED cannot be changed without a restart).
  2. 2. What happens to an ON SCHEDULE AT event after it has run, by default?

    One-time events use ON COMPLETION NOT PRESERVE by default, so the definition disappears after the run. Add ON COMPLETION PRESERVE to keep it.
  3. 3. Why is a purge event written as repeated small DELETE batches instead of one big DELETE?

    One huge DELETE locks many rows, grows the undo log and creates a very large transaction for replicas. Batches of a few thousand rows keep each step short.
  4. 4. Which job is the better fit for an external scheduler than for a MySQL event?

    Events can run only SQL. Calling HTTP APIs or sending email needs application code, and an external scheduler also gives better logging and retries.

Summary

  • An event is SQL with a stored schedule that the server runs for you, with no client connected.
  • AT makes a one-time event (dropped after running unless ON COMPLETION PRESERVE); EVERY ... STARTS ... ENDS makes a recurring one.
  • Nothing runs unless event_scheduler is ON; check it first when an event seems dead.
  • Purge old data in small batches with a loop cap, and index the column you filter on.
  • Events run with the definer's privileges, and failures show up only in the error log; use DISABLE ON REPLICA when replicating.
  • Use events for pure SQL housekeeping and an external scheduler for anything that needs files, networks or alerts.

Next: Transactions and ACID in InnoDB.