Oracle DB captured row updates destroys values in certain rows

[Chris Ridgway]

Apache Kafka Connect version:

2.7.1

Kafka version:

source  = "Mongey/kafka"
version = "0.5.4"

What we see in source vs KafDrop:

Testing outcome:
- Create events are fine

- Updates are not

- Updating a column which already comes through will be reflected, but the other columns affected will remain wrong.

- Cloning the affected row will also have the correct create values.

- Mutating the cloned row will reflect the wrong values.

Thank you.

[Chris Cranford]

Hi -

Does the table have full supplemental logging enabled?

    ALTER TABLE <table> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

If full supplemental logging isn't enabled, you should see a WARN message for this table when streaming begins, and updates will not reflect values for columns that are not changed as part of the update.

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/26bdd92e-8a92-4837-a69d-03c2f4bb01d5n%40googlegroups.com.

[Chris Ridgway]

Hi Chris,

Yes, supplemental logging is enabled, as seen below:
`ALTER TABLE TRANS_MANAGER.BUS_HRS_T ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;`

Please advise. Thank you.

[Chris Cranford]

Hi Chris,

There is an open Jira issue where your situation sounds very similar to the issue; however what we've been unable to identify thus far has been "the why" and perhaps you hold that key.

Would it be possible to use my LogMiner extract tool [1], and to provide an SCN range of where this happens in your logs and extract that entire transaction? If you can, the first thing we need to identify is the SCN and transaction of the event. If this event is still in your Kafka topic, you should be able to pull at least the SCN and perhaps the transaction id from the source information block of the event. If all you have is the SCN, that's good enough.

With the SCN, lets go -10/+10 around that SCN and get that data first. To do that we'll use the `list-changes` command, instructions found here [2].  I would suggest using --output so that it extracts the data to a file that you can then search. Just be sure to provide the `--start-scn` and `--end-scn` values as -10 and +10 of your event's SCN. Once you find that event, can you check to see if the UPDATE SQL that is produced has the same wrong values as your event. If it does, save that output file to the side. Now look in the file in that -10/+10 range, and for your event's XID (transaction id), do you also observe a START and COMMIT events? If you do, then the file is all I need.

If you do not, we need to widen the SCN range until we do. Continue to subtract from `--start-scn` and increase the `--end-scn` until you find the transaction's START and COMMIT for that transaction in the output.  I would suggest for this, given the file can become quite noisy with all the other changes not in that transaction, you can append "--transaction <your-transaction-id>` as an optional argument and the output will only provide things relevant for that transaction only. Once you observe the START and the COMMIT, then I would suggest double checking two things:

    - With `--transaction` and the full transaction, does the UPDATE have the same bad values?
    - Without the `--transaction` argument, does the UPDATE have the same bad values?

If you could then share all 3 files, perhaps there is something that we can deduce that may have caused this.

Please feel free to get in touch w/me on Zulip if you need help with the query tool.

Thanks,
-cc

[1]: https://github.com/Naros/debezium-oracle-query-tool
[2]: https://github.com/Naros/debezium-oracle-query-tool?tab=readme-ov-file#list-changes---performing-a-mining-step-and-outputs-all-changes

To view this discussion visit https://groups.google.com/d/msgid/debezium/d1a7763b-d1e6-42a2-bc53-750873ed551an%40googlegroups.com.