Missing Delete Events (op: d) after upgrading Oracle Connector from 3.0.0 to 3.3.1
hyojin lee
Hi, debezium.
I recently upgraded my Debezium Oracle source connector from version 3.0.0 to 3.3.1. In the previous version, whenever a DELETE operation occurred in the Oracle database, two messages were produced to the Kafka topic as expected:
A record with op: d (due to ExtractNewRecordState SMT with rewrite mode).
A Tombstone record (null value).
However, after upgrading to 3.3.1, the record with op: d is no longer being produced, and only the tombstone record (or nothing at all) appears.
Configuration ChangesI have maintained almost the same configuration, but noticed a few behavior changes in the new version. My SMT settings for unwrap are as follows:
"transforms": "unwrap",
"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
"transforms.unwrap.drop.tombstones": "false",
"transforms.unwrap.delete.handling.mode": "rewrite",
"transforms.unwrap.add.fields": "op,source.ts_ms",
"tombstones.on.delete": "true"
Key Observations & Questions
Change in log.mining.strategy: I changed the strategy from online_catalog to hybrid. Could this impact how LogMiner captures or formats delete operations?
SMT Behavior in 3.x: Has there been a breaking change in ExtractNewRecordState (unwrap) between 3.0.0 and 3.3.1 regarding how delete.handling.mode: rewrite interacts with tombstones.on.delete: true?
Missing Record: Even though drop.tombstones is set to false, the actual delete event containing the before state (mapped to the fields via rewrite) is missing from the topic.
Comparison of Configurations
Version 3.0.0: Used online_catalog, snapshot.mode: schema_only, and log.mining.continuous.mine: false. (Delete events worked fine).
Version 3.3.1: Using hybrid, snapshot.mode: no_data, and log.mining.continuous.mine: true.
Why are the op: d events no longer appearing in the Kafka topic after the upgrade?
topic KEY
{
"schema": {
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "COMPANY_CD"
},
{
"type": "string",
"optional": false,
"field": "PC_CD"
},
{
"type": "string",
"optional": false,
"field": "DOCU_NO"
}
],
"optional": false,
"name": "dev-erp10.COMET.FI_DOCU_MST.Key"
},
"payload": {
"COMPANY_CD": "TEST",
"PC_CD": "TEST",
"DOCU_NO": "FI202306010002"
}
}
topic value
{
"schema": {
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "COMPANY_CD"
},
{
"type": "string",
"optional": false,
"field": "PC_CD"
},
{
"type": "string",
"optional": false,
"field": "DOCU_NO"
},
{
"type": "string",
"optional": true,
"field": "ABDOCU_NO"
},
{
"type": "string",
"optional": false,
"field": "WRT_DT"
},
{
"type": "string",
"optional": false,
"field": "ACTG_NO"
},
{
"type": "string",
"optional": false,
"field": "DOCU_CD"
},
{
"type": "string",
"optional": true,
"field": "CNSUL_DC"
},
{
"type": "string",
"optional": false,
"field": "WRT_DEPT_CD"
},
{
"type": "string",
"optional": false,
"field": "WRT_EMP_NO"
},
{
"type": "string",
"optional": false,
"field": "ACTG_DT"
},
{
"type": "string",
"optional": false,
"default": "00",
"field": "GAAP_CD"
},
{
"type": "string",
"optional": false,
"field": "DOCU_ST_CD"
},
{
"type": "string",
"optional": true,
"field": "EXCH_CD"
},
{
"type": "string",
"optional": true,
"field": "GWAPRVLST_CD"
},
{
"type": "string",
"optional": true,
"field": "INSR_FG_CD"
},
{
"type": "string",
"optional": true,
"field": "DOCU_FG_CD"
},
{
"type": "string",
"optional": true,
"field": "APRVL_EMP_NO"
},
{
"type": "string",
"optional": true,
"field": "MENU_CD"
},
{
"type": "string",
"optional": true,
"field": "MODULE_MNG_NO"
},
{
"type": "string",
"optional": true,
"field": "ABDOCU_FG_CD"
},
{
"type": "string",
"optional": true,
"default": "N",
"field": "GW_YN"
},
{
"type": "string",
"optional": true,
"field": "AUDOFIR_ID"
},
{
"type": "string",
"optional": true,
"field": "ATCH_CHNLNG_VER_VR"
},
{
"type": "string",
"optional": true,
"field": "APRVL_DT"
},
{
"type": "string",
"optional": true,
"field": "REVJRNZ_FG_CD"
},
{
"type": "int16",
"optional": false,
"field": "ACCSEQ_SQ"
},
{
"type": "string",
"optional": true,
"field": "FILE_ATCH_DC"
},
{
"type": "string",
"optional": true,
"field": "ATCH_FILE_NM"
},
{
"type": "string",
"optional": true,
"field": "INSERT_ID"
},
{
"type": "string",
"optional": true,
"field": "INSERT_IP"
},
{
"type": "string",
"optional": true,
"field": "INSERT_MCADDR_NM"
},
{
"type": "int64",
"optional": true,
"name": "org.apache.kafka.connect.data.Timestamp",
"version": 1,
"field": "INSERT_DTS"
},
{
"type": "string",
"optional": true,
"field": "UPDATE_ID"
},
{
"type": "string",
"optional": true,
"field": "UPDATE_IP"
},
{
"type": "string",
"optional": true,
"field": "UPDATE_MCADDR_NM"
},
{
"type": "int64",
"optional": true,
"name": "org.apache.kafka.connect.data.Timestamp",
"version": 1,
"field": "UPDATE_DTS"
},
{
"type": "string",
"optional": true,
"field": "ATHZ_RPTS_CD"
},
{
"type": "string",
"optional": true,
"field": "GWDOCU_NO"
},
{
"type": "string",
"optional": true,
"field": "RMENU_CD"
},
{
"type": "string",
"optional": true,
"field": "CONN_KEY_VR"
}
],
"optional": true,
"name": "dev-erp10.COMET.FI_DOCU_MST.Value"
},
"payload": null
}
Symptom 1: Missing op: d and Null Payloads
In the Kafka topic, I see the key correctly, but the Value Payload is null. This indicates that only the Tombstone record is being generated, and the actual Delete record (op: d)—which should have been generated by the ExtractNewRecordState SMT with delete.handling.mode: rewrite—is missing.
Example Topic Key:
Example Topic Value:
Symptom 2: JDBC Sink Connector DLQ Failure
Because the payload is null (Tombstone), the Confluent JDBC Sink Connector fails to process the record and sends it to the Dead Letter Queue (DLQ). The error in the DLQ header is as follows:
Exception: io.confluent.connect.jdbc.sink.TableAlterOrCreateException Message: Cannot ALTER TABLE "sjsrm"."fi_docu_mst" to add missing field SinkRecordField{schema=Schema{STRING}, name='COMPANY_CD', isPrimaryKey=false}, as the field is not optional and does not have a default value
Analysis & Questions
Compatibility Change: This setup worked perfectly on Debezium 3.0.0. After upgrading to 3.3.1, the op: d record (the "rewrite" event) seems to be skipped, leaving only the Tombstone.
Sink Conflict: The JDBC Sink Connector expects the fields (like COMPANY_CD) to be present to perform the deletion/update, but since it receives a null payload (Tombstone), it interprets the missing fields as a schema mismatch and attempts an ALTER TABLE, which fails because the columns are NOT NULL.
Source or Sink?: Given that this only occurs with the Oracle Source Connector after the upgrade, is there a specific change in how Debezium 3.3.1 handles LogMiner delete events or how the ExtractNewRecordState SMT interacts with Oracle?
Is this a known issue in Debezium 3.3.1, and why is the op: d record no longer being produced before the Tombstone?
Nathan Smit
hyojin lee
Thank you for the quick response. I missed the updated SMT documentation you shared, and that was exactly the issue.
The problem was that I was using the old configuration:
As-is: transforms.unwrap.delete.handling.mode: rewrite
After checking the documentation, I updated it to use the newer property:
To-be: transforms.unwrap.delete.tombstone.handling.mode: rewrite-with-tombstone
This change successfully restored both the delete events with the "before" data and the tombstones. My configuration now looks like this:
Everything is working perfectly now. I really appreciate your help!