XML

Word

Printable

Type: Bug
Resolution: Done
Priority: Major
Fix Version/s: 2.6.0.CR1
Affects Version/s: 2.5.1.Final
Component/s: oracle-connector
Labels:
None

Blocked:
False
Blocked Reason:
None
Ready:
False
Git Pull Request:
https://github.com/debezium/debezium/pull/5399

SFDC Cases Counter:
SFDC Cases Open:
SFDC Cases Links:

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

relates to

DBZ-5295 Lob type data is inconsistent between source and sink, after modifying the primary key

Closed

links to

RHEA-2024:139598 Red Hat build of Debezium 2.5.4 release

Assignee:: Chris Cranford

Reporter:: Andrey Pustovetov (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Created:: 2024/02/07 2:17 PM

Updated:: 2024/10/09 12:21 PM

Resolved:: 2024/03/18 8:05 PM

Details

Description

Bug report

What Debezium connector do you use and what version?

What is the connector configuration?

What is the captured database version and mode of deployment?

What behaviour do you expect?

What behaviour do you see?

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

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

How to reproduce the issue using our tutorial deployment?

Feature request or enhancement

Attachments

Issue Links

Easy Agile Planning Poker

Activity

People

Dates