[source-MySQL] Can't select incremental sync in CDC-based connection

[mrmooon]

Connector Name

source-mysql

Connector Version

3.4.1

What step the error happened?

Configuring a new connector

Relevant information

We have a connection from MySQL to S3 using CDC as the update method. However, we can't select the incremental strategy for two particular streams. As I understand it, Airbyte should be able to retrieve the data incrementally based on the metadata of the binary logs, regardless of the structure of the table schema.

Relevant log output

No response

Contribute

• Yes, I want to contribute

[marcosmarxm]

@mrmooon it looks is affecting only 2 streams, with others are you able to select incremental?
Can you confirm both tables are configured to use CDC in MySQL?

[mrmooon]

@mrmooon it looks is affecting only 2 streams, with others are you able to select incremental? Can you confirm both tables are configured to use CDC in MySQL?

Hey, @marcosmarxm. Yes, we update data incrementally with the rest of the streams (more than 150 tables split across multiple connections).

AFAIK all the table updates are written to the binary logs. The only difference from other streams is that these two streams don't have an id column in the source DB.

[akashkulk]

Do the two particular streams have a PK defined? A PK is required for a stream to enable CDC

[mrmooon]

Hello @akashkulk! These tables don't have a PK. I understand this is a requirement forIncremental Sync - Append + Deduped strategy when the data in the destination is updated to maintain the state of the source system (type 1 SCD). But, does the incremental append require a PK in the source system to identify changed records? To my understanding, the binlog offset allows you identify which was the last record ingested. Sorry if this is a trivial question.

[akashkulk]

Your assessment is correct here but unfortunately this is not supported by the source at the moment :(

The long of it is that the Append vs De-dup is decided by the destination connector and selecting a stream with no PK with de-dup mode causes problems. As a result, the source defensively disables streams w/out PK for incremental syncing because it cannot determine the final destination.

[akashkulk]

https://github.com/airbytehq/airbyte-internal-issues/issues/8009 to track.

But I don't think we will prioritize a fix this in the near future :(