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
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 withforeign_key_checks=off
, followed by the replicate phase starting from the snapshot gtid. Currently we don't honor theforeign_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 thereplicate phase
where it streams binlogs. Thecopy 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 (thecatchup 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 duringcatchup
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 onvttablet
(and not viavtgate
).Proposed Import flow
MoveTables
workflow for all tables setting theforeign_key_mode
.foreign_key_checks=off
.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 viavtgate
.Post Cutover
SwitchWrites
.vtgate
will implement cascading by explicit queries.OnlineDDL
andReshard
continue to use existing vreplication workflows.Limitations
restrict
andcascade
foreign key constraints are supported.Notes
vdiff
.restrict
checks: queries will run withforeign_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
andcascade
modes