Reverse workflows fail in some MoveTables workflows in FKManaged clusters

[rohit-nayak-ps]

Overview of the Issue

In MoveTables imports for databases with foreign keys, after switching traffic, the reverse workflow breaks in some setups. One such is:

• The source database has foreign key constraints
• We do a MoveTables import into a Vitess cluster
• The user decides then to drop some of the foreign key constraints (because, say, the related tables get sharded differently, or the app requirement changes)
• The target keyspace is still running in the FKManagedmode and @@global.foreign_key_checks are ON
• User does a SwitchTraffic. Now DMLs are running on the target and replicated on the source using the reverse workflow.
• The user still has the constraints setup on the source, but they are setting @@global.foreign_key_checks to OFF expecting this to ignore the constraints. (They cannot drop the constraints since running alters could cause outages)
• A DML, now running on the target, is one that would have failed earlier due to the constraints, but since the constraints are dropped it succeeds.
• The related RowEvent has the foreign_key_checks ON because of the global setting.
• When the RowEvent is applied on the source keyspace, in the reverse workflow, @@foreign_key_checks is set to ON on the session (thus overriding the global setting). This results in the query failing because the constraints still exist on the source with a foreign key constraints failed error.
• The workflow cannot recover from this

[shlomi-noach]

I'm not sure I follow the explanation:

The user decides then to drop some of the foreign key constraints

On the source or on the target? In my understanding this is on the target.

The related RowEvent has the foreign_key_checks ON because of the global setting.

Didn't the user just change the global setting to OFF? So why would the even have FK checks ON?

I feel like the problem here is that the user creates an inconsistency between target (where they dropped the constraints) and the source (where constraints still exist). Why would we expect revert replication to work in this scenario?