I have noticed that some records are present in CDC table but are not present in Kafka topic.
I found in my logs:
I queried the capture instance with lsn range where I noticed data loss:
And these are records from my Kafka topic:
As you can see from SQL Server query results commitLsn is monotonically increasing, while changeLsn is not (only within records with the same commitLsn)!
- 00040266:00018a21:000d 00040266:000188e4:0083 <- last recorded LSN before KConnect restart
- 00040266:00019015:0006 00040266:00018ae2:0120 <- first fetched record after KConnect restart
- 00040266:00019015:0006 00040266:0001866b:00b0 <- should have been fetched but 00040266:0001866b:00b0 is lower than 00040266:000188e4:0083. Row has been skipped (see logs).
- 00040266:00019015:0006 00040266:0001866b:00b6 <- should have been fetched but 00040266:0001866b:00b6 is lower than 00040266:000188e4:0083. Row has been skipped (see logs).
- The only records fetched for commitLsn 00040266:00019015:0006 are records with changeLsn greater than 00040266:000188e4:0083.
changeLsn is not enough to reliably track offset in capture instances. According to docs:
__$seqval binary(10) Sequence value used to order changes to a row within a transaction.