Add append_dedupe feature for the MySQL destination

[persunde]

Environment

• Airbyte version: 0.39.37-alpha
• OS Version / Instance: Ubuntu 22.04, AWS EC2
• Deployment: Docker (docker-compose up)
• Source Connector and version: Postgres 0.4.31 or MySQL 0.5.15
• Destination Connector and version: Postgres 0.3.20 or MySQL 0.1.20
• Step where error happened: Setup new connection

Current Behavior

"Incremental Sync - Deduped History" does NOT show up in the "Set up connection" page if I try to setup a connection

• Postgres to MySQL
• MySQL to MySQL

Where is "Incremental Sync - Deduped History"?

But "Incremental Sync - Deduped History" is selectable if you do Postgres to Postgres or MySQL to Postgres.

Expected Behavior

Expected "Incremental Sync - Deduped History" to be one of the "Sync Mode" options in the dropdown menus for each table I want to sync.

Logs

I can maybe give logs later.

Steps to Reproduce

1. Create Postgres Source
2. Create MySQL Destination
3. Create a new Connection with Postgres as source, and MySQL as destination
4. Try to change the "Sync mode" for the tables. "Incremental Sync - Deduped History" does NOT show up as a possible option. Only "Full Refresh Overwrite", "Full Refresh Append" and "Incremental Append" shows up as possible options.

Are you willing to submit a PR?

Maybe for another issue in the future.

See here for Slack thread:
https://airbytehq.slack.com/archives/C021JANJ6TY/p1657876134958799

[octavia-squidington-iii]

cc @airbytehq/frontend

[natalyjazzviolin]

Hello @persunde, thanks for submitting this issue! The logs would definitely be helpful for debugging this, could you please add them?

[natalyjazzviolin]

@persunde , just got a note from @tealjulia: the documentation is wrong and unfortunately MySQL doesn't support the Incremental Sync - Deduped History sync mode.

[persunde]

@natalyjazzviolin is there any plans to add "Incremental Sync - Deduped History" sync mode to MySQL?
Or only overwrite rows where the ID is the same between source and destination.

We want to transfer data from source to destination, but the source will delete old data, but we want to keep the old data in the destination.
I am not sure what I ask for is covered here or not:

In the future
We will consider making other flavors of full refresh configurable as first-class citizens in Airbyte. e.g. On new data, copy old data to a new table with a timestamp, and then replace the original table with the new data. As always, we will focus on adding these options in such a way that the behavior of each connector is both well documented and predictable.

https://docs.airbyte.com/understanding-airbyte/connections/full-refresh-overwrite#in-the-future