-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
None
-
False
-
None
-
False
What Debezium connector do you use and what version?
debezium-connector-sqlserver-2.3.3
What is the connector configuration?
Configuration works in all other aspects, no problems. Signalling enabled like default.
What is the captured database version and mode of deployment?
SQL Server 2019 on a VM.
What behaviour do you expect?
When sending a incremental snapshot signal with additional-conditions that would filter the resulting SELECT down to few rows, it would send those filtered rows as snapshots. E.g. in my case I am trying to snapshot table DataField with additional-condition Organization_Id=1738. I would expect it to send all resulting rows, those are around 700 rows.
I would also expect that signal snapshot ignores any previous watermark and would attempt snapshot with the query as it would be the first snapshot. It works like this if I don't specify any additional-conditions.
What behaviour do you see?
Looking at the SQL profiler, the resulting query that Debezium sends between the initial snapshot-window-open and snapshot-window-close looks like this:
exec sp_executesql N'SELECT TOP 1024 * FROM [company_devdb].[dbo].[DataField] WHERE ([Id] > @P0 ) AND NOT ([Id] > @P1 ) AND Organization_Id=1738 ORDER BY [Id]',N'@P0 int,@P1 int',2386241,2386241
That number it feeds in as parameters 2386241 is the MAX(Id) of the the select statement filtered with the condition. So it is trying to filter records WHERE Id > MAX(Id) AND NOT Id > MAX(Id), so of course I cannot find anything because it tries to filter Id's larger than the max id...
I have previously snapshotted the same table without any additional-condition with signals, that worked fine. Also did it multiple times and always it re-read the whole table.
Do you see the same behaviour using the latest released Debezium version?
I tried to look any changes in recent PR's that would touch the snapshot files, couldn't see any.
Do you have the connector logs, ideally from start till finish?
[2023-10-02 16:30:59,230] INFO [company-sql-connector-02|task-0] Requested 'INCREMENTAL' snapshot of data collections '[company_devdb.dbo.DataField]' with additional condition 'Organization_Id=1738' and surrogate key 'PK of table will be used' (io.debezium.pipeline.signal.actions.snapshotting.ExecuteSnapshot:53) [2023-10-02 16:30:59,257] INFO [company-sql-connector-02|task-0] Incremental snapshot for table 'company_devdb.dbo.DataField' will end at position [2386241] (io.debezium.pipeline.source.snapshot.incremental.AbstractIncrementalSnapshotChangeEventSource:379) [2023-10-02 16:30:59,336] INFO [company-sql-connector-02|task-0] 52129 records sent during previous 00:48:08.216, last recorded offset of {server=company, database=company_devdb} partition is {transaction_id=null, incremental_snapshot_correlation_id=ad-hoc-12, event_serial_no=1, incremental_snapshot_maximum_key=aced0005757200135b4c6a6176612e6c616e672e4f626a6563743b90ce589f1073296c020000787000000001737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000246941, commit_lsn=00000bfd:00019250:0003, change_lsn=00000bfd:00019250:0002, incremental_snapshot_collections=[{"incremental_snapshot_collections_id":"company_devdb.dbo.DataField","incremental_snapshot_collections_additional_condition":"Organization_Id=1738","incremental_snapshot_collections_surrogate_key":null}], incremental_snapshot_primary_key=aced000570} (io.debezium.connector.common.BaseSourceTask:211) [2023-10-02 16:31:04,218] INFO [company-sql-connector-02|task-0] No data returned by the query, incremental snapshotting of table 'company_devdb.dbo.DataField' finished (io.debezium.pipeline.source.snapshot.incremental.AbstractIncrementalSnapshotChangeEventSource:386)
