Trouble with virtual generated columns

[SpencerMalone]

👋 We have a virtual generated column, but it seems like ghostferry is trying to insert data into the virtual generated column when it should not, resulting in an error like...

during prepare query near paginationKey <redacted>: Error 3105: The value specified for generated column '<redacted>' in table '<redacted>' is not allowed.

Seems like we should just not insert any data into virtual generated columns

[shuhaowu]

You might be able to ignore the column in this function by removing the virtual columns from TableSchema.Table.Columns:

ghostferry/table_schema_cache.go

Line 205 in b3aaacf

for _, tableSchema := range tableSchemas {

[SpencerMalone]

That's a good thought, lemme pull on that thread locally and check back in!

[SpencerMalone]

Sorry, I'm still struggling with this a bit, but hopefully will have an update in a few weeks!

[SpencerMalone]

Ignoring in the table schema ended up being a bit of a dead end, it made the DML stuff pretty unhappy, in the end we settled with maintaining a ColumnsToSkip list in type TableSchema struct, and using that to rebuild ColumnsToSelect in the cursor, and simply skipping the rows in the DML events. Would y'all be interested in a PR?

[milanatshopify]

Would y'all be interested in a PR?

Always interested in PRs - did you have some work in progress?

[SpencerMalone]

I can try to untangle our change to get it into the upstream, we've had it in production for ~a year now, but our fork is so divergent it may not happen at this point D:

[proton-lisandro-pin]

FIY, i recently ran into this exact same issue and wrote a PR to handle generated columns within ghostferry.

See #400 for details.