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.

Consequently, the source and replica can use different foreign key implementations for statement-based replication, although both implementations produce the same referential-integrity result.

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.

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.

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.