Type: Bug
Resolution: Unresolved
Priority: Major
Fix Version/s: 2.4-backlog
Affects Version/s: 2.3.0.Final
Component/s: jdbc-connector
Labels:
None

Blocked:
False
Blocked Reason:
None
Ready:
False

SFDC Cases Counter:
SFDC Cases Links:

In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.

Bug report

For bug reports, provide this information, please:

What Debezium connector do you use and what version?

io.debezium.connector.jdbc.JdbcSinkConnector, version 2.3

What is the connector configuration?

source:


{
    "name": "inventory-source-connector",
    "config": {
        "connector.class" : "io.debezium.connector.oracle.OracleConnector",
        "tasks.max" : "1",
        "database.server.name" : "oracle-db-source",
        "database.hostname" : "oracle-db-source",
        "database.port" : "1521",
        "database.user" : "c##logminer",
        "database.password" : "dbz",
        "database.dbname" : "XE",
        "database.out.server.name":"dbzxout",
        "database.oracle.version": "11",
        "schema.history.internal.kafka.bootstrap.servers" : "kafka:9092",
        "schema.history.internal.kafka.topic": "schema-changes.inventory",
        "database.connection.adapter": "logminer",
        "table.include.list" : "INVENTORY.TEST_TAB",
        "database.schema": "inventory",
        "errors.log.enable": "true",
        "snapshot.lock.timeout.ms":"5000",
        "include.schema.changes": "true",
        "snapshot.mode":"always",
        "decimal.handling.mode": "precise",
        "topic.prefix":  "oracle-db-source",
        "schema.history.internal.store.only.captured.databases.ddl": "true",
        "schema.history.internal.store.only.captured.tables.ddl": "true",
        "lob.enabled": "true",
        "datatype.propagate.source.type": ".*"
    }
}

target:

{
    "name": "jdbc-sink-postgress",
    "config": {
        "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",
        "connection.url": "jdbc:postgresql://postgres:5432/inventory",
        "connection.username": "postgres",
        "connection.password": "postgres",
        "tasks.max": "1",
        "table.name.format": "TEST_TAB",
        "topics": "oracle-db-source.INVENTORY.TEST_TAB",
        "insert.mode": "upsert",
        "delete.enabled": "true",
        "primary.key.mode": "record_key",
        "primary.key.fields": "ID",
        "quote.identifiers":  "true",
        "schema.evolution": "basic"
    }
}

What is the captured database version and mode of depoyment?

(E.g. on-premises, with a specific cloud provider, etc.)

Source Database: Oracle Database 11g Express Edition (Docker image: oracleinanutshell/oracle-xe-11g:latest)
Target Database: PostgreSQL 15.3 (Docker image: postgres)
Deployment: Locally using Docker

What behaviour do you expect?

I created table in Oracle:

CREATE TABLE inventory.test_tab
(
id int,
val_1 float,
val_2 number,
val_3 number(22, -1),
PRIMARY KEY (id)
);

INSERT INTO inventory.test_tab (id, val_1, val_2, val_3) VALUES
(
1,
CAST('0.1234567890123456789012345' AS float),
CAST('0.1234567890123456789012345' AS NUMBER),
null
);

INSERT INTO inventory.test_tab (id, val_1, val_2, val_3) VALUES
(
2,
CAST('0.1234567890123456789012345' AS float),
CAST('0.1234567890123456789012345' AS NUMBER),
CAST('12345678901234567890123' AS number(22, -1))
);

I expect the data to be successfully uploaded to the target database without loss of precision.

What behaviour do you see?

A table with the following structure was created in the target database:

CREATE TABLE public."TEST_TAB" (
    "ID" numeric(38) NOT NULL,
    "VAL_1" float8 NULL,
    "VAL_2" float8 NULL,
    "VAL_3" numeric(22) NULL,
    CONSTRAINT "TEST_TAB_pkey" PRIMARY KEY ("ID")
);

only the first row was inserted into the target table with the following values (loss of precision in val_1 and val_2):

1 0.12345678901234568 0.12345678901234568

and error occurs:

2023-08-02 14:34:54,090 ERROR  ||  ERROR: numeric field overflow
  Detail: A field with precision 22, scale 0 must round to an absolute value less than 10^22.   [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
2023-08-02 14:34:54,094 ERROR  ||  Failed to process record: Failed to process a sink record   [io.debezium.connector.jdbc.JdbcSinkConnectorTask]
org.apache.kafka.connect.errors.ConnectException: Failed to process a sink record
    at io.debezium.connector.jdbc.JdbcChangeEventSink.execute(JdbcChangeEventSink.java:72)
    at io.debezium.connector.jdbc.JdbcSinkConnectorTask.put(JdbcSinkConnectorTask.java:89)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:583)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:336)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:237)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:206)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:202)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:257)
    at org.apache.kafka.connect.runtime.isolation.Plugins.lambda$withClassLoader$1(Plugins.java:177)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.DataException` to JPA `PersistenceException` : JDBC exception executing SQL [INSERT INTO "public"."TEST_TAB" ("ID","VAL_1","VAL_2","VAL_3") VALUES (?,?,?,?) ON CONFLICT ("ID") DO UPDATE SET "VAL_1"=EXCLUDED."VAL_1","VAL_2"=EXCLUDED."VAL_2","VAL_3"=EXCLUDED."VAL_3"]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:165)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:175)
    at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:654)
    at io.debezium.connector.jdbc.JdbcChangeEventSink.writeUpsert(JdbcChangeEventSink.java:258)
    at io.debezium.connector.jdbc.JdbcChangeEventSink.write(JdbcChangeEventSink.java:217)
    at io.debezium.connector.jdbc.JdbcChangeEventSink.execute(JdbcChangeEventSink.java:69)
    ... 13 more
Caused by: org.hibernate.exception.DataException: JDBC exception executing SQL [INSERT INTO "public"."TEST_TAB" ("ID","VAL_1","VAL_2","VAL_3") VALUES (?,?,?,?) ON CONFLICT ("ID") DO UPDATE SET "VAL_1"=EXCLUDED."VAL_1","VAL_2"=EXCLUDED."VAL_2","VAL_3"=EXCLUDED."VAL_3"]
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:101)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:97)
    at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:78)
    at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:820)
    at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:643)
    ... 16 more
Caused by: org.postgresql.util.PSQLException: ERROR: numeric field overflow
  Detail: A field with precision 22, scale 0 must round to an absolute value less than 10^22.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1502)
    at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:84)
    ... 19 more

Oracle float and number types should be mapped to the postgres numeric type to avoid loss of precision.

Oracle number(22, -1) type should be mapped to the postgres numeric(22, -1) type.

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

(Ideally, also verify with latest Alpha/Beta/CR version)

I only tested version 2.3 (Docker image: debezium/connect:2.3)

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

(You might be asked later to provide DEBUG/TRACE level log)

I can provide logs if necessary

How to reproduce the issue using our tutorial deployment?

relates to

DBZ-6836 Add precision and scale information in VariableScaleDecimal type

Open

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 depoyment?

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?

Attachments

Issue Links

Activity

People

Dates