19.5.1.37 Replication and Foreign Keys

For InnoDB tables, foreign keys can be handled by the server runtime or by the native InnoDB implementation, depending on the innodb_native_foreign_keys system variable. Replication preserves the source's foreign keys as described in this section.

Statement-Based Replication

With statement-based replication, a statement that modifies a parent table is written to the binary log and reexecuted on the replica. The replica performs foreign key checks and cascading actions using its local innodb_native_foreign_keys setting.

The source session's enable_cascade_triggers value is also written to the binary log. The replication applier uses the source value rather than the replica session's local value:

• If enable_cascade_triggers=OFF on the source, cascade-induced child-table triggers do not execute on either the source or the replica.

• If enable_cascade_triggers=ON and both servers use SQL-layer foreign key handling, cascade-induced triggers execute on both servers, regardless of the replica's local enable_cascade_triggers setting.

• If the source uses InnoDB foreign key handling, child-table triggers do not execute for cascades. For replication to a server using SQL-layer handling, enable_cascade_triggers is replicated as OFF.

• If the source uses SQL-layer handling with enable_cascade_triggers=ON but the replica uses native InnoDB handling, the triggers execute on the source but not on the replica. This configuration is not recommended because trigger side effects can differ between the servers.

Row-Based Replication

With row-based replication, the source's innodb_native_foreign_keys setting is recorded in the row events. The replica uses the source's foreign key implementation for those events, overriding its local setting.

The source's enable_cascade_triggers setting controls trigger execution on the source only. When the source uses SQL-layer foreign key handling with enable_cascade_triggers=ON, cascade-induced triggers execute on the source and all row changes made by their trigger bodies are written to the binary log. Replica worker threads suppress cascade-induced trigger execution while applying row events, so the triggers are not executed a second time on the replica.

When SQL-layer foreign key handling is used on the source, cascading updates and deletes generate row events for affected child tables. These child-table events are written before the event for the parent-table operation. The child-table events cause the replica to skip foreign key checks and further cascading while applying those changes. This prevents a foreign key violation caused by applying a child update before the corresponding parent update and prevents cascades from being applied twice.

The parent-table event does not skip foreign key checks. This is necessary when the affected parent table is also a child in another foreign key relationship.

When InnoDB foreign key handling is used on the source, cascaded child-table changes do not generate separate row events. The replica applies the parent-table event using native InnoDB handling and performs the corresponding cascades locally.

Replication Upgrades

For row-based replication from a source running a version earlier than MySQL 9.6, the binary log does not identify SQL-layer foreign key handling. The replica therefore uses native InnoDB handling and performs cascading actions locally.

For statement-based replication from an older source, the replica uses its local innodb_native_foreign_keys setting. Replication from a source that predates cascade-trigger support to a newer replica is safe because enable_cascade_triggers defaults to OFF on the newer server.

Reverting a Replication Topology to InnoDB Handling

Changing innodb_native_foreign_keys requires a server restart. To change a replication or high-availability topology from SQL-layer handling to InnoDB:

1. Shut down each replica, configure innodb_native_foreign_keys=ON in its option file, and restart it.

2. When all replicas are configured to use InnoDB handling, shut down the source, configure innodb_native_foreign_keys=ON, and restart it

Changing the replicas before the source maintains compatible foreign key processing throughout the transition.