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

Oracle connector parses NUMBER(*,0) as NUMBER(0,0) in DDL

    XMLWordPrintable

Details

    • Hide
      • Setup an Oracle connector with only table X in `table.include.list` (can be any table)
      • Create table Y with columns with unspecified precision (e.g. `CREATE TABLE tblname (col1 INT, col2 INT);`)
      • Update the configuration while in streaming mode such that table Y is now also replicated by adding it to `table.include.list`
      • Insert some data into table Y (e.g. `INSERT INTO tblname VALUES (200, 201);`)
      Show
      Setup an Oracle connector with only table X in `table.include.list` (can be any table) Create table Y with columns with unspecified precision (e.g. `CREATE TABLE tblname (col1 INT, col2 INT);`) Update the configuration while in streaming mode such that table Y is now also replicated by adding it to `table.include.list` Insert some data into table Y (e.g. `INSERT INTO tblname VALUES (200, 201);`)

    Description

      Hi, I have run into an issue with the Oracle connector. When updating the configuration of the connector to include a new table while the connector is in streaming mode, it seems to wrongly parse the column definition in the DDL of the newly added table.

      NUMBER(*,0) is interpreted as NUMBER(0,0) instead of NUMBER(38,0). This can be seen in the following output from the logs:

       

      2021-11-04 13:17:13,429 DEBUG  Oracle|repl-tmfyfz--1|streaming  Applying schema change event SchemaChangeEvent [database=ORCLPDB1, schema=DEBEZIUM, ddl=CREATE TABLE "DEBEZIUM"."TEST_TABLE2" 
         (    "COL1" NUMBER(*,0), 
          "COL2" NUMBER(*,0), 
           PRIMARY KEY ("COL1")
        USING INDEX  ENABLE, 
           SUPPLEMENTAL LOG DATA (ALL) COLUMNS
         ) ;, tables=[columns: {
        COL1 NUMBER(0, 0) DEFAULT VALUE NULL
        COL2 NUMBER(0, 0) DEFAULT VALUE NULL
      }                                                                                     
      primary key: [COL1]                                                                                                                                              
      default charset: null         
      ], type=CREATE]   [io.debezium.connector.oracle.OracleDatabaseSchema]
      

       

      Oracle returns column types like this when the precision is unspecified when the table was created (e.g. `CREATE TABLE TEST_TABLE2 (col1 INT, col2 INT);`).

      Once records are inserted into this table, this results in an exception which stops the connector:

       

      2021-11-04 13:17:57,016 ERROR  Oracle|repl-tmfyfz--1|streaming  Mining session stopped due to the {}   [io.debezium.connector.oracle.logminer.LogMinerHelper]
      org.apache.kafka.connect.errors.ConnectException: Error while processing event at offset {commit_scn=6202564, transaction_id=null, scn=6202563}
          at io.debezium.pipeline.EventDispatcher.dispatchDataChangeEvent(EventDispatcher.java:255)
          at io.debezium.connector.oracle.logminer.processor.memory.MemoryLogMinerEventProcessor.handleCommit(MemoryLogMinerEventProcessor.java:263)
          at io.debezium.connector.oracle.logminer.processor.AbstractLogMinerEventProcessor.processRow(AbstractLogMinerEventProcessor.java:183)
          at io.debezium.connector.oracle.logminer.processor.AbstractLogMinerEventProcessor.processResults(AbstractLogMinerEventProcessor.java:161)
          at io.debezium.connector.oracle.logminer.processor.memory.MemoryLogMinerEventProcessor.process(MemoryLogMinerEventProcessor.java:116)
          at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:162)
          at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:54)
          at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:166)
          at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:127)
          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: java.lang.NumberFormatException: Value out of range. Value:"199" Radix:10
          at java.base/java.lang.Byte.parseByte(Byte.java:154)
          at java.base/java.lang.Byte.parseByte(Byte.java:178)
          at io.debezium.connector.oracle.OracleValueConverters.lambda$convertTinyInt$14(OracleValueConverters.java:512)
          at io.debezium.jdbc.JdbcValueConverters.convertValue(JdbcValueConverters.java:1297)
          at io.debezium.connector.oracle.OracleValueConverters.convertTinyInt(OracleValueConverters.java:500)
          at io.debezium.connector.oracle.OracleValueConverters.convertNumericAsTinyInt(OracleValueConverters.java:428)
          at io.debezium.connector.oracle.OracleValueConverters.lambda$getNumericConverter$8(OracleValueConverters.java:228)
          at io.debezium.relational.TableSchemaBuilder.lambda$createKeyGenerator$3(TableSchemaBuilder.java:191)
          at io.debezium.relational.TableSchema.keyFromColumnData(TableSchema.java:130)
          at io.debezium.relational.RelationalChangeRecordEmitter.emitCreateRecord(RelationalChangeRecordEmitter.java:69)
          at io.debezium.relational.RelationalChangeRecordEmitter.emitChangeRecords(RelationalChangeRecordEmitter.java:46)
          at io.debezium.pipeline.EventDispatcher.dispatchDataChangeEvent(EventDispatcher.java:218)
          ... 13 more
      

       

       

      Oracle documentation about the asterisk in the precision field of NUMBER can be found here: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

      The full (quite verbose) logs have been added as an attachment. For easier reference: the first snippet was taken from line 30898, the second snippet from line 45892.

      This issue only occurs when the configuration is updated while the connector is in streaming mode. For example: the table TEST_TABLE1, used in the initial configuration of the connector, has exactly the same definition (aside from the name) but does not cause any issues. From the logs it can be seen that the DDL is parsed correctly:

      2021-11-04 13:16:00,289 DEBUG  Oracle|repl-tmfyfz--1|snapshot  Applying schema change event SchemaChangeEvent [database=ORCLPDB1, schema=DEBEZIUM, ddl=
        CREATE TABLE "DEBEZIUM"."TEST_TABLE1" 
         (    "COL1" NUMBER(*,0), 
          "COL2" NUMBER(*,0),               
           PRIMARY KEY ("COL1")
        USING INDEX  ENABLE,                       
           SUPPLEMENTAL LOG DATA (ALL) COLUMNS
         ) ;, tables=[columns: {                   
        COL1 NUMBER(38, 0) NOT NULL
        COL2 NUMBER(38, 0) DEFAULT VALUE NULL         
      }
      primary key: [COL1]                       
      default charset: null
      ], type=CREATE]   [io.debezium.connector.oracle.OracleDatabaseSchema]
      

       

      The configuration of the connector after adding the second table:

      {
        "name": "repl-tmfyfz--1-source",
        "config": {
          "connector.class": "io.debezium.connector.oracle.OracleConnector",
          "database.dbname": "ORCLCDB",
          "database.user": "c##dbzuser",
          "database.connection.adapter": "logminer",
          "database.pdb.name": "ORCLPDB1",
          "tasks.max": "1",
          "database.history.kafka.bootstrap.servers": "kafka-1:9092,kafka-2:9092,kafka-3:9092",
          "database.history.kafka.topic": "repl-tmfyfz--1.schema-changes",
          "database.server.name": "repl-tmfyfz--1",
          "database.port": "1521",
          "database.hostname": "172.21.0.3",
          "database.password": "dbz",
          "name": "repl-tmfyfz--1-source",
          "lob.enabled": "true",
          "database.out.server.name": "dbzxout",
          "table.include.list": "DEBEZIUM.TEST_TABLE1,DEBEZIUM.TEST_TABLE2"
        },
        "tasks": [
          {
            "connector": "repl-tmfyfz--1-source",
            "task": 0
          }
        ],
        "type": "source"
      }
      

       

      Attachments

        Activity

          People

            ccranfor@redhat.com Chris Cranford
            robin-vanderstraeten-klarrio Robin Vanderstraeten
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: