RFC: Importing data into Vitess using VReplication for databases that have foreign key constraints

[rohit-nayak-ps]

Feature Description

This document discusses the changes required in VReplication workflows related to the proposed foreign key support in Vitess for foreign keys including cascade constraints in unsharded and shard-scoped Vitess clusters.

TL;DR

For importing, we will need to run the vreplication MoveTables workflow using a modified version of the copy phase. We
run a single copy phase cycle based on one consistent snapshot for all tables with foreign_key_checks=off, followed by the
replicate phase starting from the snapshot gtid. Currently we don't honor the foreign_key_checks settings on connections during the replicate phase: that will be fixed.

After the cutover, vtgate will be emulating fk constraints. As a result, once data is imported, we can run vreplication
workflows, including native onlineddl workflows, as they do today since cascade queries will make it to the binlog.

Proposed Design

Current MoveTables flow

VReplication workflows first run a copy phase to bulk copy the data from the source and then switch to the
replicate phase where it streams binlogs. The copy phase copies one table at a time. Large tables typically take
multiple cycles: by default each cycle is one hour long. In between cycles and tables we also stream from the binlog
(the catchup phase) where updates/inserts/deletes for tables and table ranges already copied are applied. The catchup
phase is needed so that we are tracking the source binlogs closely and are resilient to the normal binlog purging.
More details here.

During the copy phase, including catchup phases, we set foreign_key_checks=off. If we allow cascade constraints, we
will see failures during catchup because some tables are not yet present or are partially present.

Proposed flow

To get around this, we propose running a single copy phase which copies all tables using a single consistent snapshot.
Essentially no catchup phase. Once the copy is complete, the database is consistent as of the snapshot's gtid. As long
as the binlogs as of that gtid are still available, we can then stream from that gtid. We perform the binlog streaming
with the foreign_key_checks set by the user. MySQL will then cascade the constraints. Note that both copy phase and replicate phase queries are run directly on vttablet (and not via vtgate).

Proposed Import flow

• We create the target schema with the foreign key constraints in MySQL.
• Start a MoveTables workflow for all tables setting the foreign_key_mode.
• Run only one copy phase cycle. We take a single snapshot and stream all tables sequentially.
• The copy phase cycle will run with foreign_key_checks=off.
• The copy phase will end up with a consistent database at the time of the snapshot. All foreign key constraints are
  also valid as of that gtid.
• After the copy phase we run the replication phase starting from the gtid of the snapshot.
• The replicate phase will set or reset the foreign_key_checks based on the value found for the row event. Cascades will be done in MySQL (as on the source) since we run the query directly on MySQL and not via vtgate.

Post Cutover

• Cutover will happen when on SwitchWrites.
• vtgate will implement cascading by explicit queries.
• Binlogs will contain all queries including the cascade ones.
• Future vreplication workflows including OnlineDDL and Reshard continue to use existing vreplication workflows.

Limitations

• Any non-recoverable interruption during the copy phase (due to network/mysql connectivity failure or PRS)
  means a new copy phase has to be started.
• Binlogs cannot be purged by the user until we have reached a low replication lag.
• This will only work for unsharded and shard-scoped FK constraints.
• Only restrict and cascade foreign key constraints are supported.

Notes

• No changes are required for vdiff.
• The cascade constraints persist in the target schema after the import phase. This is because vtgate is going to use the FK
  definitions in the mysql database as the source of truth for the schema and also use it for restrict checks: queries will run with foreign_key_checks=on. Note that mysql will also try to execute any cascade constraints but they will result in noops since vtgate has already explicitly run the cascde queries.

Use Case(s)

Databases with tables which use foreign keys with restrict and cascade modes

[rohit-nayak-ps]

Closed via #13137 and #14013