MySQL ON DELETE CASCADE isn't in the binlog: what that hides, and what to do

The short answer

In MySQL 8.4, when you delete a parent row and InnoDB cascades the delete to child rows, the child deletes are handled inside the storage engine, and, in the manual’s words, “none of the changes are logged”.¹ The binary log records the statement or row you deleted from the parent, not the rows the cascade removed. Anything that reads the binlog to learn what changed, a replica, a change-data-capture feed such as Debezium, an audit tool, doesn’t see the children go.¹

Newer MySQL versions give you a choice. In the 9.x manual, foreign keys can be handled by the server’s SQL layer instead of InnoDB, and then cascading updates and deletes do generate row events for the child tables.² On 8.4, if you need every deleted row on record, delete children explicitly instead of relying on cascade.

What the manual says

The 8.4 page on InnoDB and replication walks through an example. Table fc1 is the parent, fc2 the child with ON DELETE CASCADE. On the source, deleting one row from fc1 takes fc2 from 3 rows to 1. On the replica, the delete arrives for fc1, deletes nothing from fc2, and the replica’s fc2 keeps all its rows, “due to the fact that the cascading deletes are handled internally by the InnoDB storage engine, which means that none of the changes are logged.”¹

The page adds that cascades run on the replica only if the tables use InnoDB on both source and replica, and that this holds for statement-based and row-based replication alike.¹ With InnoDB on both sides, the replica’s own InnoDB re-runs the cascade when it applies the parent delete. That’s why replication usually looks fine. But the binlog itself still holds only the parent change.

Who is affected

The CDC row is the one that surprises people. Debezium’s MySQL connector reads the binlog and needs binlog_format=ROW and binlog_row_image=FULL.⁶ In MySQL 8.4 binary logging is on by default with row format.⁵ With all of that set correctly, a downstream system still receives a delete for one order and nothing for its 300 order lines. The warehouse keeps orphaned lines; a search index keeps documents for rows that no longer exist.

Triggers don’t fire on cascades either

A common workaround for “log every delete” is a BEFORE DELETE or AFTER DELETE trigger on the child table that writes to an audit table. In MySQL, that doesn’t help with cascades: “Cascaded foreign key actions do not activate triggers.”³ DROP TABLE and TRUNCATE TABLE don’t activate DELETE triggers either.³

You also can’t move the logic up to the parent table’s trigger and have it update the parent: MySQL refuses a trigger that updates the table it fires on, with error 1442.⁷ A trigger on the parent can, however, write audit rows about the children before the delete, by selecting them from the child table. That works, at the cost of one more thing to maintain.

A related detail: for InnoDB, NO ACTION is the same as RESTRICT, so a parent delete with existing children is rejected immediately, not at the end of the statement.⁴

MySQL 9.6 and later: a switch

The MySQL 9.7 manual describes two ways InnoDB foreign keys can be handled, chosen by innodb_native_foreign_keys, which needs a restart:

• By InnoDB itself: cascades don’t generate row events for the child tables, as in 8.4.
• By the SQL layer: cascading updates and deletes generate row events for the affected child tables.²

It also warns that a source older than 9.6 doesn’t mark SQL-layer handling in the binlog.² If you’re on 9.6 or later and depend on CDC, SQL-layer handling is the setting to evaluate; test it with your consumer before relying on it.

What to do on 8.4

1. Delete children explicitly. In the application or a stored procedure, delete child rows first, then the parent, in one transaction. Each delete is then a logged row event.
2. Keep ON DELETE RESTRICT as a safety net. A forgotten child makes the parent delete fail loudly instead of cascading silently.
3. Log before the delete, from the parent’s trigger or the application, if you must keep cascade.
4. Reconcile downstream. A periodic job that finds orphaned child rows in the warehouse catches what the feed missed.
5. Turn on binlog_rows_query_log_events so each row event carries the statement that caused it; Debezium surfaces it as source.query.⁶ It won’t add the missing children, but it tells you which statement to suspect.

Why this matters for AI agents

An agent asked to “remove the test customer” runs one DELETE on customers. It reports one row deleted. With cascades, orders, order lines and invoices may go with it, and on 8.4 none of those child deletes reach the binlog. The agent’s report and the binlog agree with each other, and both understate what happened. The Postgres version of this story, where the change log does show the cascade, is in ON DELETE CASCADE deleted more rows.

We read Debezium feeds to compare what an agent said with what MySQL did. In a recorded MySQL 8.4 scene, one agent UPDATE of an order produced a transaction of 302 events, 300 of them written by a trigger, each carrying the agent’s statement in source.query, and our check reported that it didn’t match.⁸ Triggers that write rows show up that way. Cascades on 8.4 don’t, because there are no events to read, so no feed-based check, ours included, can see them. Our comparison is also offline and doesn’t read every MySQL statement shape.⁸

For the full picture of auditing MySQL changes, see auditing MySQL changes made by an AI agent; for limiting what the agent can delete in the first place, a read-only MySQL user. The change data capture entry explains the feeds. To see how a check compares an agent’s report with the database on Postgres, try the playground; to grade what an agent can reach on your machine, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.