Is this bug? Streaming Issue.

[추거누]

oracle-ee-19c  

debezium 2.5.2 (oracle)

I created table like below.

=================================sql

create table t_cdc_multikey(

    id number,

    code char(2),

    c1 varchar2(50),

    constraint pk_cdc_multikey primary key (id, code)

);

 

==================================

there are some data in oracle.. and I updated a row for streaming test.

First, I update a row with only id not (id, code). oracle debezium made a message like this.

================================================ sql

update t_cdc_multikey set c1='who r u?' where id = 100001;

================================================= message

  "payload": {

    "before": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "Hello world!"

    },

    "after": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "who r u?"

    },

====================================

as you can see, There is no code value on the message and id value is 0 not 100001..

on the other hand, I update the row with (id,.code). it's same..

================================ sql

  "payload": {

    "before": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "who r u?"

    },

    "after": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "exhausted.."

    },

======================== 

And i deleted it. (id, code) value enter it normally.

===============================

delete from t_cdc_multikey where id=100001 and code='C';

==============================

  "payload": {

    "before": {

      "ID": {

        "scale": 0,

        "value": "AYah"

      },

      "CODE": "C ",

      "C1": "exhausted.."

    },

    "after": null,

=====================================

I am testing on more time.. insert & update

but this time i update with multi key.

=============================

insert into t_cdc_multikey values(100001, 'C', 'Hello world!')

=============================

  "payload": {

    "before": null,

    "after": {

      "ID": {

        "scale": 0,

        "value": "AYah"

      },

      "CODE": "C ",

      "C1": "Hello world!"

    },

==============================

update t_cdc_multikey set c1='one more time' where id = 100001 and code='C';

=============================

  "payload": {

    "before": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "Hello world!"

    },

    "after": {

      "ID": {

        "scale": 0,

        "value": "AA=="

      },

      "CODE": "",

      "C1": "one more time"

    },

======================================

insert message was made successfully.

but, update query didn't make (id, code) value...

i don't think it's intended. it's bug or something and quite critical.

Did you test older version similar with this?

If i downgrade a debezium version, is it not happened?

[Chris Cranford]

Hi -

The issue with `ID` being shown with scale of 0" and a value of "AA==" is because of how you've defined the ID column.  By default in Oracle, a NUMBER column is constructed as a NUMBER(38,0) column type. A number with 38 digits cannot fit into an INT64 data type in a Kafka topic, so Debezium needs to encode the value in a format that avoids data loss. Debezium does this by using a Kafka Connect Decimal data type for all numeric columns that are NUMBER([P],[S]) where P >= 19, where the payload gets encoded as a series of BYTES.  Please see Table 11 in the documentation [1].

Now you can configure the "decimal.handling.mode" with a value of "double" or "string" to have the values encoded as a FLOAT64 or STRING respectively, but this will be done for all number column types and may not be ideal given your situation. Another alternative is to create the table using "ID NUMBER(18,0)" so that it can be safely managed by an INT64 data type.

The second problem with "CODE" missing in your change events has to do with the fact you have not properly followed the documentation with how to set-up tables.  In addition, the connector likely logged a WARN message about missing configuration for your table as well.  If you do not enable full supplemental logging for a table, then Oracle does not record the values for columns that are not changed, which is why you only see the value for CODE when you mutate the column. 

Every captured table must be altered as follows [2]:

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

I'd suggest using the documentation as a reference as we cover a great deal about these nuances.

Thanks,
Chris

[1]: https://debezium.io/documentation/reference/stable/connectors/oracle.html#oracle-numeric-types
[2]: https://debezium.io/documentation/reference/stable/connectors/oracle.html#_preparing_the_database

--
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 on the web visit https://groups.google.com/d/msgid/debezium/732ac348-e368-4c6d-92f9-356afe4aa6fcn%40googlegroups.com.

[추거누]

My mistake. 

At first, I set up supplemental logging.. but, during test, I re-create whole db and forgot it..

Thank you! and appreciate for quick response. :)

2024년 3월 25일 월요일 오후 11시 9분 10초 UTC+9에 Chris Cranford님이 작성: