[Oracle] Data loss issue when using lob.enabled=true with batch updates on Oracle

[Apollo Elon]

Hi everyone,

I’ve encountered a problem while using Debezium to capture Oracle data, and I’d appreciate your help.

Environment

• Debezium version: 1.9.8.Final

• Oracle version: 19c

• JDK: OpenJDK 17

Connector Configuration (excerpt)

"decimal.handling.mode" = "string"
"include-comment-changes" = "true"
"include-schema-changes" = "true"
"message.key.columns" = "ZZZ.OGG1:ID"
"lob.enabled" = "true"
"log.mining.strategy" = "online_catalog"
"database.pdb.name" = "ORCLPDB1"

Table Setup

create table ZZZ.OGG1 (
    ID     NUMBER(6),
    NAME   VARCHAR2(30),
    EMAIL  VARCHAR2(100),
    EMAIL1 VARCHAR2(100)
);
INSERT INTO ZZZ.OGG1 (ID, NAME) VALUES (9, '5999');
INSERT INTO ZZZ.OGG1 (ID, NAME) VALUES (10, '51010');
INSERT INTO ZZZ.OGG1 (ID, NAME) VALUES (111, '5111111111');
INSERT INTO ZZZ.OGG1 (ID, NAME) VALUES (115, '5115115115');

Problem Description

When updating rows individually, Debezium captures the changes correctly, e.g.:

-D {ID=9, NAME=599}  
+I {ID=9, NAME=5999, __event_time__=..., __ingestion_time__=...}  

-D {ID=111, NAME=5111111}  
+I {ID=111, NAME=5111111111, __event_time__=..., __ingestion_time__=...}  

... (and so on)

However, when performing a batch update, some intermediate events are lost. For example:

update OGG1
set NAME = concat(NAME, ID)
where ID in (9, 10, 111, 115);

Produces only:

-D {ID=9, NAME=5999}  
+I {ID=115, NAME=5115115115115, __event_time__=..., __ingestion_time__=...}  

It looks like all intermediate events (for IDs 9 and 115) were merged or dropped.

Question

Is this a known limitation/bug when using lob.enabled=true with batch updates in Oracle?
If I want to keep the current Debezium version (1.9.8.Final), is there any configuration workaround or code patch I could apply to avoid event merging in this scenario?

Thanks a lot for your help!

Best regards,

[Elon]

[Chris Cranford]

Hi Elon -

This is a bug in Debezium 1.9 that was fixed through a combination of DBZ-6107 [1] and DBZ-6963 [2]. I would highly recommend since you're on JDK 17 to update to Debezium 3.2.1.Final if possible. There are so many improvements, not just specific to this problem, but also to log gathering and consistency to avoid missed events that we've fixed over the years that Debezium 1.x simply does not have.

Hope that helps.
-cc

[1]: https://issues.redhat.com/browse/DBZ-6107
[2]: https://issues.redhat.com/browse/DBZ-6963

--
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/c82d5acb-8eee-42f6-bd28-44de4804f9f2n%40googlegroups.com.