Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-7458

Oracle LOB requery on Primary Key change does not work for all column types

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 2.6.0.CR1
    • 2.5.1.Final
    • oracle-connector
    • None

    Description

      Bug report

      For bug reports, provide this information, please:

      What Debezium connector do you use and what version?

      debezium-connector-oracle version 2.5.1.Final

      What is the connector configuration?

      {
  "name": "source-test-connector",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",
    "database.hostname": "oracle",
    "database.port": "1521",
    "database.user": "c##dbzuser",
    "database.password": "dbz",
    "database.dbname": "orclcdb",
    "database.pdb.name": "orclpdb1",
    "database.connection.adapter": "logminer",
    "topic.prefix": "dbz",
    "lob.enabled": "true",
    "schema.name.adjustment.mode": "avro",
    "table.include.list": "C##DBZUSER.TEST_TABLE",
    "include.schema.changes": "false",
    "schema.history.internal.kafka.bootstrap.servers" : "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.test",
    "heartbeat.interval.ms": "60000",
    "log.mining.strategy": "online_catalog",
    "log.mining.query.filter.mode": "in",
    "custom.metric.tags": "connector=source-test-connector",
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "key.converter.schemas.enable": "true",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "true"
  }
}

      What is the captured database version and mode of deployment?

      Oracle Database 19, Docker

      What behaviour do you expect?

      Oracle connector reselects values by DATE/TIMESTAMP columns if they are specified in table primary key.

      What behaviour do you see?

      Oracle connector does not reselect values when DATE/TIMESTAMP columns are used in table primary key:

      Do you see the same behaviour using the latest relesead Debezium version?

      Yes

      Do you have the connector logs, ideally from start till finish?

      Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:937)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3855)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
at io.debezium.jdbc.JdbcConnection.prepareQuery(JdbcConnection.java:714)
at io.debezium.connector.oracle.BaseChangeRecordEmitter.emitUpdateAsPrimaryKeyChangeRecord(BaseChangeRecordEmitter.java:88) ... 22 more
Caused by: Error : 932, Position : 76, Sql = SELECT "DATA" FROM "C##DBZUSER"."TEST_TABLE" WHERE "ID"=:1 AND "CREATED_AT"=:2 , OriginalSql = SELECT "DATA" FROM "C##DBZUSER"."TEST_TABLE" WHERE "ID"=? AND "CREATED_AT"=?, Error Msg = ORA-00932: inconsistent datatypes: expected DATE got NUMBER
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637) ... 38 more

      Caused by: io.debezium.DebeziumException: Failed to re-select table with LOB columns due to primary key update
at io.debezium.connector.oracle.BaseChangeRecordEmitter.emitUpdateAsPrimaryKeyChangeRecord(BaseChangeRecordEmitter.java:101)
at io.debezium.relational.RelationalChangeRecordEmitter.emitUpdateRecord(RelationalChangeRecordEmitter.java:128)
at io.debezium.relational.RelationalChangeRecordEmitter.emitChangeRecords(RelationalChangeRecordEmitter.java:53)
at io.debezium.pipeline.EventDispatcher.dispatchDataChangeEvent(EventDispatcher.java:271) ... 19 more
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8548)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8030)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8805)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8780)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:218)
at io.debezium.connector.oracle.BaseChangeRecordEmitter.prepareReselectQueryStatement(BaseChangeRecordEmitter.java:167)
at io.debezium.connector.oracle.BaseChangeRecordEmitter.lambda$emitUpdateAsPrimaryKeyChangeRecord$0(BaseChangeRecordEmitter.java:89)
at io.debezium.jdbc.JdbcConnection.prepareQuery(JdbcConnection.java:713)
at io.debezium.connector.oracle.BaseChangeRecordEmitter.emitUpdateAsPrimaryKeyChangeRecord(BaseChangeRecordEmitter.java:88) ... 22 more

      How to reproduce the issue using our tutorial deployment?

      1. Create a new table:

      CREATE TABLE c##dbzuser.test_table (
  id         NUMBER(10) NOT NULL,
  created_at DATE,
  data       CLOB,
  PRIMARY KEY (id, created_at)
);

      2. Insert a new record into the table:

      INSERT INTO c##dbzuser.test_table (id, created_at, data)
VALUES (1, current_date, TO_CLOB('data'));

commit;

      3. Create a new connector:

      curl -X POST -H "Accept:application/json" -H "Content-Type:application/json" http://localhost:8083/connectors -d '
{
  "name": "source-test-connector",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",
    "database.hostname": "oracle",
    "database.port": "1521",
    "database.user": "c##dbzuser",
    "database.password": "dbz",
    "database.dbname": "orclcdb",
    "database.pdb.name": "orclpdb1",
    "database.connection.adapter": "logminer",
    "topic.prefix": "dbz",
    "lob.enabled": "true",
    "schema.name.adjustment.mode": "avro",
    "table.include.list": "C##DBZUSER.TEST_TABLE",
    "include.schema.changes": "false",
    "schema.history.internal.kafka.bootstrap.servers" : "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.test",
    "heartbeat.interval.ms": "60000",
    "log.mining.strategy": "online_catalog",
    "log.mining.query.filter.mode": "in",
    "custom.metric.tags": "connector=source-test-connector",
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "key.converter.schemas.enable": "true",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "true"
  }
}'

      4. Update an existing record in the table:

      UPDATE c##dbzuser.test_table
SET id = 2
WHERE id = 1;

commit;

      5. Check status of created connector:
      ER: connector is running
      AR: connector is failed with errors:

      • time.precision.mode=adaptive - java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
      • time.precision.mode=connect - java.sql.SQLException: Invalid column type

      Feature request or enhancement

      <Your answer>

      Attachments

        Issue Links

          relates to

          Bug - A problem that impairs or prevents the functions of the product. DBZ-5295 Lob type data is inconsistent between source and sink, after modifying the primary key

          • Critical - To be worked on immediately following BLOCKER issues.
          • Closed

          Activity

            People

              ccranfor@redhat.com Chris Cranford
              andrey.pustovetov@gmail.com Andrey Pustovetov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: