MySQL 9.6: Foreign Key Cascades Now Appear in the Binlog

For years, MySQL users working with Change Data Capture (CDC), and replication environments have dealt with a fundamental architectural limitation: foreign keys were handled by the Storage Engine (InnoDB) and cascading operations were invisible to the binary log. MySQL 9.6, released January 20, 2026, addresses this long-standing issue by moving foreign key enforcement from the InnoDB storage engine to the SQL layer.

This post examines the technical details of this change, its implications for replication and CDC pipelines such as Readyset, the backward compatibility considerations, and who stands to benefit most from this architectural shift.

The Problem: Storage Engine-Level FK Enforcement

To understand why this change matters, we need to examine how MySQL has historically handled foreign key constraints.

When InnoDB implemented foreign key support, it did so entirely within the storage engine layer. The SQL engine would issue a DELETE or UPDATE statement against a parent table, and InnoDB would internally handle any cascading operations defined by ON DELETE CASCADE or ON UPDATE CASCADE clauses. This design had a critical consequence: the SQL layer never saw these cascaded changes.

Consider this schema:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

With data:

REPLACE INTO orders VALUES (1, 100, 250.00);
REPLACE INTO order_items VALUES (1, 1, 'Widget', 5);
REPLACE INTO order_items VALUES (2, 1, 'Gadget', 3);

When you execute :

DELETE FROM orders WHERE id = 1;

The SQL layer sends this single DELETE to InnoDB. InnoDB then internally deletes both rows from order_items as part of the cascade operation, but these child table deletions happen entirely within the storage engine. The SQL layer—and by extension, the binary log—only records the parent DELETE.

Examining SHOW BINLOG EVENTS would reveal only:

| binlog.000003 | 354 | Table_map      |         1 |         410 | table_id: 90 (test.orders)                                         |
| binlog.000003 | 410 | Delete_rows    |         1 |         459 | table_id: 90 flags: STMT_END_F                                     |
| binlog.000003 | 459 | Xid            |         1 |         490 | COMMIT /* xid=14 */                                                |

The two order_items deletions are nowhere to be found.

Binary Log Implications Under Row-Based Replication

This behavior exists regardless of whether you use statement-based (SBR) or row-based replication (RBR). Bug #32506, filed back in November 2007, documented this limitation explicitly. The response from the MySQL team at the time was clear: “The cascading deletes are internal to the InnoDB engine, and as such there is no way that the server can be informed about the fact that additional rows were affected internally.”

The workaround documented in the MySQL Reference Manual for versions through 8.4 was essentially: ensure your replica has identical InnoDB tables with identical foreign key definitions, and InnoDB will perform the cascades locally on the replica. This works for InnoDB-to-InnoDB replication but breaks down in several scenarios:

Different storage engines: If your replica uses a different storage engine—whether MyISAM, RocksDB, or any engine that doesn’t support foreign keys—the cascaded deletes simply don’t happen. The parent row gets deleted on the replica, but child rows remain orphaned.

CDC pipelines: Tools like Readyset, Debezium, and other CDC solutions read the binary log to capture data changes. Since cascaded operations never appeared in the binary log, CDC consumers would miss these events entirely. The Debezium FAQ explicitly notes this: “This may be caused by the usage of CASCADE DELETE statements. In this case the deletion events generated by the database are not part of the binlog and thus cannot be captured by Debezium.”

Audit requirements: Any system relying on the binary log for complete audit trails of data modifications would have blind spots around cascaded changes.

The MySQL 9.6 Solution: SQL Engine Foreign Key Enforcement

MySQL 9.6 fundamentally restructures foreign key handling by moving enforcement and cascade execution to the SQL engine. When you issue a DELETE or UPDATE against a parent table, the SQL engine now:

1. Evaluates the foreign key constraints defined on child tables
2. Executes the necessary cascading operations as discrete DML statements
3. Logs all operations—both parent and child—to the binary log

This architectural change means the binary log now contains complete change history. That same DELETE FROM orders WHERE id = 1 now produces binary log events for both the parent deletion and both child deletions:

| binlog.000003 | 354 | Table_map      |         1 |         410 | table_id: 90 (test.orders)                                         |
| binlog.000003 | 410 | Table_map      |         1 |         477 | table_id: 92 (test.order_items)                                    |
| binlog.000003 | 477 | Delete_rows    |         1 |         554 | table_id: 92 flags: NO_FOREIGN_KEY_CHECKS_F USE_SQL_FOREIGN_KEY_F  |
| binlog.000003 | 554 | Delete_rows    |         1 |         603 | table_id: 90 flags: STMT_END_F USE_SQL_FOREIGN_KEY_F               |
| binlog.000003 | 603 | Xid            |         1 |         634 | COMMIT /* xid=16 */                                                |

Every downstream consumer—replicas, CDC tools, audit systems—now receives complete visibility into all data modifications.

The innodb_native_foreign_keys Variable

To facilitate controlled migration, MySQL 9.6 introduces a read-only startup variable: innodb_native_foreign_keys. The default value is FALSE, meaning SQL engine-based foreign key enforcement is enabled by default.

Setting this variable to TRUE at server startup reverts to the legacy InnoDB-native foreign key handling:

[mysqld]
innodb_native_foreign_keys=TRUE

This fallback exists specifically for migration scenarios where you need to validate behavior before fully committing to the new implementation. Oracle has stated this variable will be removed in a future release once the community has fully adopted SQL engine-based foreign keys.

Performance Characteristics

A natural concern with any architectural change of this magnitude is performance impact. Oracle’s benchmarks indicate that SQL engine-based foreign key enforcement performs nearly identically to the InnoDB approach. The overhead of checking constraints and executing cascades remains effectively unchanged.

This makes sense when you consider what the change actually does: the same constraint checks and row modifications happen, just at a different layer in the stack. The work is equivalent; only the execution point has shifted.

For high-throughput OLTP workloads, the performance parity means this change can be adopted without regression concerns. The logging overhead for cascaded operations is minimal compared to the I/O cost of the actual row modifications.

Looking Forward

Oracle has indicated the roadmap includes broader support for triggers on cascaded changes and foreign key enforcement for additional storage engines. The SQL engine-based architecture provides a foundation for these enhancements that wouldn’t have been possible with storage engine-level enforcement.

For MySQL users who have worked around the cascade visibility limitation for years—or worse, discovered it only after data inconsistencies appeared in downstream systems—MySQL 9.6 closes a significant architectural gap. The binary log finally tells the complete story of what happened to your data.

---

References: