Suspition of Log event lost from Oracle 19c with Debezium 3.1.3

[F BREGIER]

Hi,

Our Debezium in 3.1.3 version is using Kafka 4.0 and as source Oracle 19c.

We observe, without any clue to find out the root cause, an issue that is arriving every 5 to 10 minutes. To summarize :

- Initally our database was snapshoted using Incremental method (snapshot + log) and our Debezium is still in this mode while all snapshots are over (so only Log for now)

- About 5 K events /s on Log CDC

- About 1 or 2 events every 5 to 10 minutes occuring with a warning on our Sink (see after), so not so often but reccurrent

- We check both databases (source Oracle and target PostgreSql) on every tables : same number of rows, and in partial test checking values (more than 100 Milions of rows on several tables)

- Next step will be to go to "log" only (no snapshot)

The issue :

Wen we get an UPDATE or DELETE (where condition is on PK), the Sink complains that row does not exist. Our Sink fix the issue by retrying (Update Or Insert so the missing row goes to Insert, Delete while ignoring if no row exists)

But as far as we checked, it seems full snapshot is completed, and therefore we should have all rows. The snapshot was finished a month ago, so we are on Log only events.

The only thing we think about could be (but rarely possible in my opinion) that Debezium could do the following :

- In the same "log" slot, at least 2 events (1 INSERT and 1 UPDATE, or 1 INSERT and 1 DELETE) occurs on the very same row (same PK) (and seems possible in the source application adressing Oracle)

- Debezium could send only one of the event (the last one) by a deduplication operation, therefore leading to only 1 UPDATE ou 1 DELETE, 

- This could explain the time to time warning 

Could it be possible while Debezium is in "incremental snapshot" mode while snapshots are fully over and logs are now on the flow (no delay except standard delay in CDC) ?

If so (rarely possible for me but we try to understand the root issue), could it be corrected in more recent versions ?

Best regards,

Frederic

[Chris Cranford]

Hi -

So there are a few things at play here, so let me clarify as many as possible.

One of the most common reasons for sink errors and retries starts when the sink sets `tasks.max` greater than 1. When you have a flood of changes for a given table, if a batch creates too many row-level locks on a single table, the database will escalate these to page- or table- level locks. That means if more than one task writes to the same target table, the additional tasks may fail due to the lock, and will retry. This is normal when using multiple tasks because each task is trying to write to a shared resource that is locked.

The second most common reason is when `batch.size` is greater than `1`, which is the default. While some databases support handling `MERGE` statements with duplicate events well, there is one corner case where the batch may contain an `INSERT` followed by an `UPDATE` or `DELETE`. More often than not, databases suffer with such a batch because the `MERGE` (upsert) only reconciles the "Do I insert, update, or delete" logic once per PK at the start of the SQL statement. So for the same PK that doesn't exist when the `MERGE` is started with an `INSERT` followed by `UPDATE`, it thinks both should be inserted rather than applying an insert followed by an update. There are two ways to deal with this:

1. Set `batch.size` to `1`. While guarantees that at no point will the upsert operation operate on two or more rows with the same PK. The downside is that this has a significant impact on performance, so we only recommend doing this in the absolute worst case where you explicitly need to replicate each event from source as-is without deduplication.
2. The most efficient way is to enable the reduction buffer on the sink. What this does is guarantees that within a single batch, if there are changes for the same PK, the sink will reconcile and only write as part of the upsert statement the last event we see for the PK. So for the scenario of an `INSERT` followed by an `UPDATE`, the upsert will properly reconcile "Does this PK exist" and apply the insert or update semantics properly and you won't see issues because the `MERGE` operates only on one row in the data set with that PK. The downside is that you get partial application of events at the target, last one wins in a batch. For most applications this is often fine, but certain audit or financial systems require all events, and using the reduction buffer isn't possible. 

For PG specifically, this use case is often more clear "ON CONFLICT DO UPDATE command cannot affect a row a second time", which isn't the case for other vendors. 

What would help narrow this down would be if you can supply your Oracle source PG sink connector configurations, so we can make some recommendations, along with the explicit logged errors or complaints you see at the sink.

Thanks,
-cc

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/3ab69bd0-e879-4825-8292-e3b608dff5a3n%40googlegroups.com.

[F BREGIER]

Hi,

Thanks Chris,

I understand. tasks.max is 1, while batch.size is 1000 (for speed). So what we imagoned was correct, even if we had no clue: a deduplication occurs in Debezium while reading 1000 logs.

Our think is idempotent, so no issue therefore. Just the warnings we've got from it were suspicious. 

As I wrote, it tries first "respecting" order (UPDATE), and then goes to upsert mode. 

In case of DELETE, the first warning is followed by a retry but with no missing row warning.

It is not therefore an issue but the usual way.

Best regards,