MySQL just fixed a 20-year-old bug where cascade deletes silently skipped triggers

A MySQL bug filed in June 2005 just got fixed, roughly 20 years and one console generation later. Bug #11472 had a simple, nasty shape: triggers never fired when a foreign key cascade changed your data.

Here’s the trap. You set up a parent table and a child table with a foreign key marked ON DELETE CASCADE or ON DELETE SET NULL. You delete a parent row. MySQL dutifully deletes or nulls the matching child rows. But the AFTER DELETE or AFTER UPDATE trigger you wrote on that child table, the one feeding your audit log or invalidating a cache, never runs. Do the exact same change with a direct DELETE statement and the trigger fires every time. The behavior depended entirely on how the row got touched, and nothing in the docs made that loud enough.

Why this was worse than it sounds

Triggers aren’t a toy feature. Teams lean on them for the unglamorous plumbing that has to be correct: append-only audit trails for compliance, cache or search-index invalidation, denormalized counters, derived tables that have to stay in sync. The common thread is that all of it is supposed to be automatic and complete. A trigger that fires “usually” is arguably worse than no trigger at all, because you build the rest of the system trusting it.

So picture the failure mode. Your audit table faithfully records every direct delete. Then a cascade quietly removes a thousand child rows and logs none of them. Months later an auditor or an incident responder asks “what happened to these records,” and the honest answer is “we have no idea, the database ate them without a receipt.” The bug didn’t throw an error. It didn’t corrupt anything. It just left holes in exactly the records you keep specifically so you’ll never have holes.

The MySQL bug report classified it as severity S2, “serious,” and it earned the label. The original description put it plainly: when rows are changed indirectly through a foreign key definition, “triggers on that table are not executed as required.” That sentence has been sitting in the tracker since Omer Barnir filed it in 2005. It collected comments, duplicates, and “still broken in 5.5, 5.6, 5.7, 8.0” confirmations for the better part of two decades.

Why it took two decades

This wasn’t pure neglect, even if 20 years makes it look that way. The bug lives at an architectural seam. Foreign key cascades are enforced down inside InnoDB, the storage engine, while triggers are a concept that belongs to the SQL layer above it. When InnoDB cascades a delete, it’s working below the level where the server knows or cares about triggers. Wiring the two together means teaching the storage engine to call back up into server-layer machinery for every cascaded row, which is invasive, performance-sensitive, and easy to get subtly wrong.

You can tell it’s a hard problem because MySQL’s main fork has the identical gap. MariaDB tracks it under MDEV-19402 and MDEV-12302, and it’s still open there. PostgreSQL, by contrast, fires triggers on cascade actions and has for years, which is why the bug became a recurring “this is why I switched to Postgres” anecdote. Two decades of that as a competitive talking point apparently helped move it up the list.

What actually shipped

The fix landed in MySQL 9.7 as worklog WL#17024, titled “Activate triggers on referencing tables during foreign key CASCADE.” Oracle’s announcement put the change in human terms: “MySQL 9.7 Lets Child Triggers Speak Up.” Cascaded updates and deletes now invoke the triggers on the affected child tables, closing the seam between InnoDB and the SQL layer that kept them quiet for 20 years.

One caution worth stating clearly: this is a behavior change, not just a bug fix, and it cuts both ways. Code written in the last 20 years was written against the broken behavior. Some of it may quietly depend on cascade actions not firing triggers, a recursive trigger that would now loop, a counter that would now double-count, a trigger with side effects you didn’t want on bulk cascades. Turning on correct behavior can surface latent assumptions. Read the upgrade notes, test on a copy, and watch for triggers that suddenly start doing work they never did before.

Why you’re hearing about this now

The story caught fire on developer forums because it’s the perfect shape for it: a famous, ancient, “surely they’ll never fix it” bug that someone finally fixed. There’s genuine relief in the reaction and a fair amount of dark humor about what else is lurking in 30-year-old database codebases.

But the useful takeaway isn’t schadenfreude. It’s that the bug survived 20 years partly because the failure was silent. Nobody got paged. No query returned an error. The data just wasn’t there, in a table most people only read during an audit. Silent correctness bugs are the ones that rot the longest, because the system keeps insisting everything is fine.

It’s also a reminder of how much load-bearing infrastructure runs on software older than the engineers maintaining it. MySQL turned 30 this decade, and the foreign key plus trigger combination has been in production at banks, governments, and half the web since before some of today’s senior developers wrote their first query. A bug at that scale isn’t an edge case. It’s a default that millions of schemas inherited without anyone choosing it. The fix is welcome, but the more sobering thought is the obvious one: if a serious, well-documented, widely-hit bug can sit untouched for 20 years, the count of similar ones still open is not zero. It’s just unmeasured.

What this means for you

If you run MySQL and use foreign key cascades with triggers, this matters today, before you upgrade. Assume your audit logs, cache invalidations, and derived tables have gaps wherever a cascade touched a child row. Go check the tables that have to be complete. If you find holes, you’ve found the bug, and you’ll want to reconcile that history rather than discover it under pressure later.

When you do move to MySQL 9.7, treat the new trigger behavior as a migration, not a free upgrade. Spin up a copy, run your real workload against it, and confirm no trigger starts firing in a way that loops, double-counts, or fires side effects on bulk cascades. If you’re on MariaDB, the gap is still open under MDEV-19402, so the old workaround still applies: handle deletes in application code, or don’t rely on triggers for anything a cascade can reach. The lesson that outlasts this particular bug is plain. A feature that works “almost always” is a feature you can’t actually trust, and databases are the worst place to learn that twice.