Understanding MSSQL CDC Offset and Debezium Snapshotting

Summary

The user is seeking clarification on the behavior of MSSQL CDC offset values and Debezium snapshotting in Airbyte connectors. They are experiencing issues with offset values not updating after a full refresh and encountering errors related to snapshotting in the logs.


Question

this is the offset info from the connection state :
"mssql_cdc_offset": {
"[\"ZoomSTG\",{\"server\":\"ZoomSTG\",\"database\":\"ZOOMSTG\"}]": "{\"transaction_id\":null,\"event_serial_no\":1,\"commit_lsn\":\"0017c0b6:0002465a:0013\",\"change_lsn\":\"0017c0b6:0002465a:0010\"}",
"[\"ZoomSTG\",{\"server\":\"ZoomSTG\",\"database\":\"ZoomSTG\"}]": "{\"commit_lsn\":\"0017c0b3:0009d2f1:0001\",\"snapshot\":true,\"snapshot_completed\":true}"
},

The snapshot commit_lsn 0017c0b3:0009d2f1:0001 is the value noted from a full refresh. This value doesn’t change until I hit the 10 day cdc retention mark in sql server and then it fails with the error that the saved offset is no longer present on the server. The other commit_lsn from incremental changes is updated and data is pulled accurately for 10 days. Is this correct behaviour?

I also noticed this error in the logs, even when the sync succeeds and pulls incremental data successfully
source > ERROR debezium-sqlserverconnector-change-event-source-coordinator i.d.p.ErrorHandler(setProducerThrowable):52 Producer failure io.debezium.DebeziumException: java.util.concurrent.ExecutionException: java.lang.InterruptedException: Interrupted while snapshotting table student The logs also show that the debezium engine was stopped for some reason before hitting this error.

The ``snapshot_completed":true}` is true in the offset file. Does it mean that the snapshot was completed successfully after failing initially. Or the snapshot offset not changing is a result of the above error that the snapshotting was interrupted?

Any pointers in this regard are appreciated. Thanks! <@U01MMSDJGC9> <@U01AB6V6NMQ>



This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["mssql-cdc-offset", "debezium-snapshotting", "incremental-changes", "offset-value", "sync-error", "snapshot-completed"]