While this is still WIP, I'm happy to get eyes on the proposed solution. For your convenience, this is the outline:

proto

• vreplication's filter/rule has 2 new fields:
  • source_unique_key (mapped to SourceUniqueKey variable)
  • target_unique_key (mapped to TargetUniqueKey variable)
  • These are optional, and are only populated by Online DDL. Normally both will have the string value PRIMARY
  • But it's possible one or both will have the name of another unique key. The two names don't have to be identical, but Online DDL ensures that both will cover the exact same columns in same order (though a column is also allowed to be renamed, but that's just a nuance)

Online DDL

• In vrepl.go, the part of Online DDL that analyzes a VReplication-based migration, we analyze both source and target table for all existing unique keys. We rank unique keys from best to worst. PRIMARY is always "best" because it's the clustered index hence by definition most performant key. Then, we prefer keys that use integers, over keys that use strings, etc.
• vrepl.go then intersects unique keys from source table and unique keys from target table and determines which keys are shared. It's possible that a UK on the source table and a UK on the target table have different names. That's fine, as long as they cover same columns in same order.
• Based on ranking, and based on shared keys, vrepl.go determines the chosen UK on source table, and chosen UK on target table. This is what gets to aforementioned proto/rule.

Streamer

• rowstreamer receives the streaming query. The Unique Key name for source table is encoded as a comment directive /*vt+ ukName="name_of_the_unqiue_key" */
• If the unique key is not specificed, or is PRIMARY, we fallback to standard behavior.
• Otherwise, rowstreamer investigates the unique key by querying information_schema, and gets the orderd list of columns in that unique key.
• rowstreamer from there on assumes those columns to be the PK columns. It still calls them pkColumns (I wanted to minimize code changes)
• From there on, everything happens implicitly: SendQuery is calculated by pkColumns which means rowstreamer reads the table by UK order, not PK order, and also sends the UK columns as Fields, and also calculates lastPK by UK columns (again, the name remains lastPK but reall ythis could be any UK).

VReplicator / table plan builder

• Detects SourceUniqueKey in Rule, generates a comment directive /*vt+ ukName="name_of_the_unqiue_key" */ which rowstreamer will later consume
• Detects TargetUniqueKey in Rule
• if TargetUniqueKey is trivial (ie PRIMARY) or unspecified, then nothing happens and we fallback to standard behavior (tables ar ealready analyzed with PK info)
• otherwise, for each stream/rule:
  • table plan builder uses vreplicator's recalculatePKColsInfo function to:
    • read list of columns in specified unique key
    • re-evaluate colsInfo: make a copy of colsInfo, reorder columns by UK order, reassign IsPK
  • using the modified colsInfo, run analyzePK to evaluate pkCols. They're still called pkCols, but they may now identify the columns of any UK.
  • pkCols are then used, as usual and without change, to generate queries.

Notes

• I believe I have modified the minimal set of code required for supporting non-PK columns
• The use of comment directives is temporary and is my easiest way to communicate hints from vreplication to streamer. We can change that later.
• I've added some tests from gh-ost test suite, which pass. I've added tests to vrepl_stress endtoend test, which pass. But I'm still unsure if I've generate da strong enough test case.
• All thoughts welcome. I certainly need this logic reviewed.

cc @rohit-nayak-ps @sougou @deepthi

Question: as mentioned above:

vreplication's filter/rule has 2 new fields:
source_unique_key (mapped to SourceUniqueKey variable)
target_unique_key (mapped to TargetUniqueKey variable)

• when source_unique_key is non empty and non-PRIMARY, that means rowstreamer needs to read information_schema.columns to see what columns are covered by the unique key and in what order.
• similarly, when target_unique_key is non empty and non-PRIMARY, that means vreplicator needs to read information_schema.columns to see what columns are covered by the unique key and in what order.

The module that populates these two values is onlineddl/vrepl, which already knows the column identities and order for both keys. I wonder if instead of source_unique_key (string) and target_unique_key (string) we should instead have source_unique_key_columns (string array) and target_unique_key_columns (string array).

This way the one who own the decision to use a specific unique key can already dictate the columns; the vreplication components don't need to read information_schema. Downside is that vreplication will need to trust that the given columns are, indeed, covered by a unique key. Also, it loses context to the name/identity of the key it actually uses.

Extra information is that I believe I will want to anyway add the names of the columns in the source key (possibly renamed if there's a ALTER TABLE CHANGE col new_name) because reasons, something I'm thinking about (will do writeup elsewhere).

The more I think of it, the more I believe we should just write the names of columns rather than name of index.

Thoughts?

EDIT: this change is implemented in #8420

Merged #8420 into this PR. From #8420's main comment:

Description

A take/twist on #8364, per comment

Mostly exactly the same as #8364 , except:

• Rule's proto has source_unique_key_columns instead of source_unique_key, and target_unique_key_columns instead of target_unique_key.
• rowstreamer doesn't need to inspect information_schema.columns, since it relies on source_unique_key_columns
• vreplicator likewise doesn't need to inspect information_schema.columns, since it relies on target_unique_key_columns

Just observing here that this feature is only implemented for Online DDL, though internally vreplication supports it via this PR. If we want to extend this to vreplication workflows (movetables/reshard/...) we will need to generate the filter hints that Online DDL generates while creating those workflows.

Just observing here that this feature is only implemented for Online DDL, though internally vreplication supports it via this PR.

Correct. There has to be some logic above VReplication to set those fields.