Declare MySQL scheduled events instead of scripting them

Last recipe you watched a guarded CREATE DOMAIN swallow every edit you made to it. Same trick, different family — and this time the thing that goes quiet has teeth.

Someone shortened your session retention from thirty days to seven. Compliance asked, it went through review, it merged. Here’s the script it merged into:

CREATE EVENT IF NOT EXISTS purge_old_sessions
  ON SCHEDULE EVERY 1 DAY
  DO DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 30 DAY;

The edit changed 30 to 7. The deploy ran green. And every night since, on both MySQL and MariaDB, that event has deleted on a thirty-day window, because IF NOT EXISTS stopped the body from executing the first time the event already existed — which was eighteen months ago.

mysql    deployed = DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 30 DAY
mariadb  deployed = DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 30 DAY

An inert domain type lets bad rows in. An inert event keeps doing the old thing, on a schedule, unattended, while a compliance ticket sits closed. That’s the difference worth caring about here.

Declare it

MySQL and MariaDB scheduled events get an Events/ folder next to Tables/, and the same treatment everything else in the package has had — compared on every deploy, converged, and removable by absence.

{
  "Name": "purge_old_sessions",
  "ScheduleType": "EVERY",
  "Interval": "1 DAY",
  "Status": "ENABLE",
  "Definition": "DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 30 DAY"
}

The schedule splits into fields instead of syntax: ScheduleType is EVERY (recurring, paired with Interval) or AT (once, paired with ExecuteAt), and Status takes ENABLE, DISABLE, or DISABLE ON SLAVE. The body is just the body.

Now make that retention edit again — 30 DAY to 7 DAY, this time in the declaration — and re-quench:

  Quenching scheduled events

mysql    deployed = DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 7 DAY
mariadb  deployed = DELETE FROM app_session WHERE started_at < NOW() - INTERVAL 7 DAY

Seven days. Both engines. Identical edit to the one that vanished, opposite outcome — and the reason is the same as it was for domain types: nothing is standing between the file and the comparison. There’s no guard to go deaf behind, because there’s no guard.

Removal is opt-in, and narrower than you’d expect

Deleting a declaration doesn’t delete the object by default. You turn that on:

{ "DropEventsRemovedFromProduct": true }

One wrinkle worth knowing before you go looking in the wrong place: unlike most of the drop-control flags you’ve met, this one is environment level only. It isn’t settable per product or per template — it lives at the root of your settings file.

With it on, delete an event’s .json and re-quench, and the event is gone. But plant an event SchemaSmith never created — the one some DBA wrote at 2am during an incident — and watch that one survive the same run:

handmade_probe,purge_old_sessions

archive_old_sessions was removed because the product declared it and then stopped. handmade_probe was left alone because the product never owned it. That distinction is the whole reason drop-by-absence is safe to turn on: SchemaSmith removes what it put there, not everything it finds. A tool that cleaned up “anything not in the package” would eat that 2am fix on its first run in a database it didn’t create.

The question to take with you

Both recipes have been the same shape aimed at different objects: a guarded CREATE isn’t a declaration, it’s an instruction with an expiry date, and the expiry is silent. Domain types on PostgreSQL, scheduled events on MySQL and MariaDB — different engines, different objects, one failure.

So the useful habit isn’t memorising which object types you can declare. It’s the audit you can run against your own repo this afternoon: find every IF NOT EXISTS guard in your migration scripts, and for each one ask when did this last do anything? The guard that makes a script safe to re-run is the same guard that makes it stop listening. Some of those files have been shouting into a closed door for years.

Check yourself: You enable DropEventsRemovedFromProduct, delete an event's JSON, deploy — and the event is still there. Name two different reasons that could happen, and how you would tell them apart.

First: the flag isn’t where you think it is. DropEventsRemovedFromProduct is environment level only — put it under a product or a template and it is silently not read, and the deploy proceeds as though you never set it. Check the settings dump at the top of the deploy log; it prints the value the run actually resolved.

Second: SchemaSmith doesn’t own the event. Removal reaches only events SchemaSmith created. If that event was originally made by hand — or by a guarded CREATE EVENT script, which is the same thing as far as ownership goes — deleting the declaration removes nothing, because there was never a declaration behind it. Telling them apart is quick: the log confirms the flag, and an event you can’t drop by absence is one you’ll need to drop by hand once, after which the declaration owns it.

Two recipes, two engine families, one lesson: the file only means something if something reads it. Everything else in this course has been about putting facts where they can be read — on the table, in Extensions, in the model. This pair is about noticing the places where you thought you’d done that and hadn’t, because a guard was quietly eating your edits and reporting success.

Got a nightly job still running on a policy you changed months ago? Email me at forgebarrett@schemasmith.com — that one’s more common than anyone admits. More’s coming from the forge.

Until then, may your schedules run on the window you actually chose.

— Forge

Mark this lesson complete