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

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

      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>

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

              Created:
              Updated:
              Resolved: