[source-mssql] sync fails with CDC and no changes

[Trunken]

Connector Name

source-mssql

Connector Version

4.0.9

What step the error happened?

During the sync

Relevant information

Hello,

I have a problem with the MSSQL Source connector using CDC which happens after the retention period of the CDC entries expire and no data (0 rows) have been synced.

Setup:

• Airbyte (0.57.2) HELM
• Source MSSQL, Dest MSSQL

Some details:

• The MS SQL Server executes the cleanup job every night at 2AM. The transport happens shortly after.
• CDC is only activated for tables which are synced.
• Currently our retention period is 3 days and the transport consistently fails in the 4th night.
• MS SQL Server has a procedure which creates every 5 minutes an empty dummy lsn entry in the lsn_time_mapping table. This was created by Microsoft when we enabled CDC. This constantly increases the max lsn.

Steps to reproduce:

• MS SQL Server 2017 with CDC (working) enabled
• Configure retention period to X days and enable cleanup job
• No rows transported for X + 1 days
• Transport will fail with "Saved offset no longer present on the server. Please reset the connection, and then increase binlog retention and/or increase sync frequency."

I did some debugging and think the problem could be this: When no rows are transported the "commit_lsn" won`t be updated. This leads to the "commit_lsn" beeing out of range after the third day.

Code refs:

• https://github.com/airbytehq/airbyte/blob/e27fda281de63e3778436944ac2f4965b1a78166/airbyte-integrations/connectors/source-mssql/src/main/java/io/airbyte/integrations/source/mssql/initialsync/MssqlInitialReadUtil.java#L114
• https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-mssql/src/main/java/io/airbyte/integrations/source/mssql/cdc/MssqlDebeziumStateUtil.java#L58

Thank you for your help.

Relevant log output

2024-04-18 00:04:00 source > INFO main i.a.i.s.m.c.MssqlDebeziumStateUtil(lambda$savedOffsetStillPresentOnServer$3):268 0000003a:0000541c:0001 lsn exists on server: [false]. (min server lsn: 0000003b:000001c8:0003 max server lsn: 0000003c:00001185:0001)
2024-04-18 00:04:00 source > ERROR main i.a.c.i.b.s.SshWrappedSource(read):87 Exception occurred while getting the delegate read iterator, closing SSH tunnel io.airbyte.commons.exceptions.ConfigErrorException: Saved offset no longer present on the server. Please reset the connection, and then increase binlog retention and/or increase sync frequency.

Contribute

• [ ] Yes, I want to contribute

[ritikanaidu-trakstar]

Hi @Trunken , I'm facing this same error with the mssql connector. Setting the retention period to 10 days, seeing this error on the 11th day.

I've got about 80 tables in the same sync. Does airbyte maintain the commit_lsn for each job or a single oldest value from all the tables? Were you able to find any resolution for this error?

[Trunken]

@ritikanaidu-trakstar We created an additional table and a job which increments a counter in this table each night to keep CDC alive. Unfortunately very hacky but workes for now.

[ritikanaidu-trakstar]

Thanks @Trunken We are facing this error even if the table has updates during the retention period. It doesn't seem right to me, that airbyte doesn't update the commit_lsn in partition offset even when cdc max_lsn is changing. Can you please let me know which version of airbyte and mssql source connector are you using?

[brian-kasen-trakstar]

@Trunken - any details you can share around your airbyte version and mssql connector version you're using currently?

[evantahler]

We will be updating to a newer version of debezium soon - we will re-release the connector then and we can re-test this case.

[niveditabaliga-payroc]

@evantahler was the updated version of the connector to resolve this issue released?