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

column.include.list does work in SQL Server

    XMLWordPrintable

Details

    • Task
    • Resolution: Unresolved
    • Major
    • Backlog
    • None
    • sqlserver-connector
    • None
    • False
    • None
    • False

    Description

      I would like to be able to use the 'column.include.list' parameter, unfortunately i can't set it. after chatting with the community it was suggested that i create an issue. ccranfor@redhat.com 

      I tested on releases 2.1.2.Final and 2.5.0.Final with the same results.
      Here is my configuration:

      Variant 1

      name=sage-connector
connector.class=io.debezium.connector.sqlserver.SqlServerConnector
tasks.max=1
database.hostname=$DB_HOSTNAME
database.port=$DB_PORT
database.user=$DB_USER
database.password=$DB_PASSWORD
database.names=$DB_DBNAME
database.instance=$DB_INSTANCE_NAME
database.encrypt=false
topic.prefix=_cdc
table.include.list=\
$DB_SCHEMA_NAME.WORKSTATIO,\
$DB_SCHEMA_NAME.TABUNIT,\
$DB_SCHEMA_NAME.SALESREP,\
$DB_SCHEMA_NAME.ITMMASTER,\
$DB_SCHEMA_NAME.ITMFACILIT,\
$DB_SCHEMA_NAME.ITMCATEG,\
$DB_SCHEMA_NAME.ITMBOM,\
$DB_SCHEMA_NAME.FACILITY,\
$DB_SCHEMA_NAME.CONTACTCRM,\
$DB_SCHEMA_NAME.CONTACT,\
$DB_SCHEMA_NAME.COMPANY,\
$DB_SCHEMA_NAME.BPSUPPLIER,\
$DB_SCHEMA_NAME.BPSCATEG,\
$DB_SCHEMA_NAME.BPDLVCUST,\
$DB_SCHEMA_NAME.BPCUSTOMER,\
$DB_SCHEMA_NAME.BPARTNER,\
$DB_SCHEMA_NAME.BPADDRESS,\
$DB_SCHEMA_NAME.BOMD,\
$DB_SCHEMA_NAME.BOM,\
$DB_SCHEMA_NAME.ATABDIV,\
$DB_SCHEMA_NAME.ZPLNHEA,\
$DB_SCHEMA_NAME.ZSHIDAY,\
$DB_SCHEMA_NAME.ZPLNAGR,\
$DB_SCHEMA_NAME.ZOBJHEA,\
$DB_SCHEMA_NAME.SRETURN,\
$DB_SCHEMA_NAME.SRETURND,\
$DB_SCHEMA_NAME.PORDER,\
$DB_SCHEMA_NAME.PORDERC,\
$DB_SCHEMA_NAME.PORDERP,\
$DB_SCHEMA_NAME.PORDERQ,\
$DB_SCHEMA_NAME.SPRICLIST,\
$DB_SCHEMA_NAME.PRICSTRUCT,\
$DB_SCHEMA_NAME.SPRICCONF,\
$DB_SCHEMA_NAME.SINVOICE,\
$DB_SCHEMA_NAME.SINVOICEV,\
$DB_SCHEMA_NAME.SINVOICED,\
$DB_SCHEMA_NAME.SDELIVERY,\
$DB_SCHEMA_NAME.SDELIVERYD,\
$DB_SCHEMA_NAME.ATEXTRA,\
$DB_SCHEMA_NAME.SERREQUEST,\
$DB_SCHEMA_NAME.ZSUBOBJ,\
$DB_SCHEMA_NAME.ZSUBOBJDET1,\
$DB_SCHEMA_NAME.ZSUBOFF,\
$DB_SCHEMA_NAME.AUTILIS,\
$DB_SCHEMA_NAME.ZFCYPLZ,\
$DB_SCHEMA_NAME.POSCOD,\
$DB_SCHEMA_NAME.ACLOB,\
$DB_SCHEMA_NAME.ZDIRECTDELIV,\
$DB_SCHEMA_NAME.ZADDLIST,\
$DB_SCHEMA_NAME.ZTRACE,\
$DB_SCHEMA_NAME.STOCK,\
$DB_SCHEMA_NAME.STOALL,\
$DB_SCHEMA_NAME.BAPPOINT
column.include.list=\
$DB_SCHEMA_NAME.SINVOICED.NUM_0,\
$DB_SCHEMA_NAME.SINVOICED.CPY_0,\
$DB_SCHEMA_NAME.SINVOICED.SDDLIN_0,\
$DB_SCHEMA_NAME.SINVOICED.SIDLIN_0,\
$DB_SCHEMA_NAME.SINVOICED.ITMREF_0,\
$DB_SCHEMA_NAME.SINVOICED.ITMDES1_0,\
$DB_SCHEMA_NAME.SINVOICED.ITMDES2_0,\
$DB_SCHEMA_NAME.SINVOICED.ITMDES3_0,\
$DB_SCHEMA_NAME.SINVOICED.QTY_0,\
$DB_SCHEMA_NAME.SINVOICED.SAU_0,\
$DB_SCHEMA_NAME.SINVOICED.ZDSPLINWEI_0,\
$DB_SCHEMA_NAME.SINVOICED.ZDSPWEU_0,\
$DB_SCHEMA_NAME.SINVOICED.ZCUSORDQTY_0,\
$DB_SCHEMA_NAME.SINVOICED.ZCUSORDUOM_0,\
$DB_SCHEMA_NAME.SINVOICED.GROPRI_0,\
$DB_SCHEMA_NAME.SINVOICED.NETPRI_0
schema.history.internal.kafka.bootstrap.servers=$KAFKA_BROKERS
schema.history.internal.kafka.topic=_erp.cdc.debezium.sage.schemahistory
transforms=Reroute
transforms.Reroute.type=io.debezium.transforms.ByLogicalTableRouter
transforms.Reroute.topic.regex=([A-Za-z\-\_]+\.)([A-Za-z]+\.)([A-Za-z]+\.)(.*)
transforms.Reroute.topic.replacement=_erp.cdc.sage.$4
decimal.handling.mode=string
include.schema.changes=false
max.iteration.transactions=$MAX_ITERATION_TRANSACTION

       

      if in the parameter 'column.include.list' I declare only the columns of one table ignoring the other tables, I get the following error:

      dkm-debezium-debezium-dkm-1  | io.debezium.DebeziumException: org.apache.kafka.connect.errors.ConnectException: Data row is smaller than a column index, internal schema representation is probably out of sync with real database schema
dkm-debezium-debezium-dkm-1  |  at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)
dkm-debezium-debezium-dkm-1  |  at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155)
dkm-debezium-debezium-dkm-1  |  at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:71)
dkm-debezium-debezium-dkm-1  |  at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109)
dkm-debezium-debezium-dkm-1  |  at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
dkm-debezium-debezium-dkm-1  |  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
dkm-debezium-debezium-dkm-1  |  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
dkm-debezium-debezium-dkm-1  |  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
dkm-debezium-debezium-dkm-1  |  at java.base/java.lang.Thread.run(Thread.java:829)

      Messages are created only for some tables (not for SINVOICED) but all messages are without any columns.

      Variant 2
      if in the 'column.include.list' parameter I declare all tables and especially configure the columns of the SINVOICED table, then I receive no errors, but the configuration related to the SINVOICED table is ignored, all columns are contained in the SINVOICED message.

      name=sage-connector
connector.class=io.debezium.connector.sqlserver.SqlServerConnector
tasks.max=1
database.hostname=$DB_HOSTNAME
database.port=$DB_PORT
database.user=$DB_USER
database.password=$DB_PASSWORD
database.names=$DB_DBNAME
database.instance=$DB_INSTANCE_NAME
database.encrypt=false
topic.prefix=_cdc
table.include.list=\
    $DB_SCHEMA_NAME.WORKSTATIO,\
    $DB_SCHEMA_NAME.TABUNIT,\
    $DB_SCHEMA_NAME.SALESREP,\
    $DB_SCHEMA_NAME.ITMMASTER,\
    $DB_SCHEMA_NAME.ITMFACILIT,\
    $DB_SCHEMA_NAME.ITMCATEG,\
    $DB_SCHEMA_NAME.ITMBOM,\
    $DB_SCHEMA_NAME.FACILITY,\
    $DB_SCHEMA_NAME.CONTACTCRM,\
    $DB_SCHEMA_NAME.CONTACT,\
    $DB_SCHEMA_NAME.COMPANY,\
    $DB_SCHEMA_NAME.BPSUPPLIER,\
    $DB_SCHEMA_NAME.BPSCATEG,\
    $DB_SCHEMA_NAME.BPDLVCUST,\
    $DB_SCHEMA_NAME.BPCUSTOMER,\
    $DB_SCHEMA_NAME.BPARTNER,\
    $DB_SCHEMA_NAME.BPADDRESS,\
    $DB_SCHEMA_NAME.BOMD,\
    $DB_SCHEMA_NAME.BOM,\
    $DB_SCHEMA_NAME.ATABDIV,\
    $DB_SCHEMA_NAME.ZPLNHEA,\
    $DB_SCHEMA_NAME.ZSHIDAY,\
    $DB_SCHEMA_NAME.ZPLNAGR,\
    $DB_SCHEMA_NAME.ZOBJHEA,\
    $DB_SCHEMA_NAME.SRETURN,\
    $DB_SCHEMA_NAME.SRETURND,\
    $DB_SCHEMA_NAME.PORDER,\
    $DB_SCHEMA_NAME.PORDERC,\
    $DB_SCHEMA_NAME.PORDERP,\
    $DB_SCHEMA_NAME.PORDERQ,\
    $DB_SCHEMA_NAME.SPRICLIST,\
    $DB_SCHEMA_NAME.PRICSTRUCT,\
    $DB_SCHEMA_NAME.SPRICCONF,\
    $DB_SCHEMA_NAME.SINVOICE,\
    $DB_SCHEMA_NAME.SINVOICEV,\
    $DB_SCHEMA_NAME.SINVOICED,\
    $DB_SCHEMA_NAME.SDELIVERY,\
    $DB_SCHEMA_NAME.SDELIVERYD,\
    $DB_SCHEMA_NAME.ATEXTRA,\
    $DB_SCHEMA_NAME.SERREQUEST,\
    $DB_SCHEMA_NAME.ZSUBOBJ,\
    $DB_SCHEMA_NAME.ZSUBOBJDET1,\
    $DB_SCHEMA_NAME.ZSUBOFF,\
    $DB_SCHEMA_NAME.AUTILIS,\
    $DB_SCHEMA_NAME.ZFCYPLZ,\
    $DB_SCHEMA_NAME.POSCOD,\
    $DB_SCHEMA_NAME.ACLOB,\
    $DB_SCHEMA_NAME.ZDIRECTDELIV,\
    $DB_SCHEMA_NAME.ZADDLIST,\
    $DB_SCHEMA_NAME.ZTRACE,\
    $DB_SCHEMA_NAME.STOCK,\
    $DB_SCHEMA_NAME.STOALL,\
    $DB_SCHEMA_NAME.BAPPOINT
column.include.list=\
    $DB_SCHEMA_NAME.WORKSTATIO.*,\
    $DB_SCHEMA_NAME.TABUNIT.*,\
    $DB_SCHEMA_NAME.SALESREP.*,\
    $DB_SCHEMA_NAME.ITMMASTER.*,\
    $DB_SCHEMA_NAME.ITMFACILIT.*,\
    $DB_SCHEMA_NAME.ITMCATEG.*,\
    $DB_SCHEMA_NAME.ITMBOM.*,\
    $DB_SCHEMA_NAME.FACILITY.*,\
    $DB_SCHEMA_NAME.CONTACTCRM.*,\
    $DB_SCHEMA_NAME.CONTACT.*,\
    $DB_SCHEMA_NAME.COMPANY.*,\
    $DB_SCHEMA_NAME.BPSUPPLIER.*,\
    $DB_SCHEMA_NAME.BPSCATEG.*,\
    $DB_SCHEMA_NAME.BPDLVCUST.*,\
    $DB_SCHEMA_NAME.BPCUSTOMER.*,\
    $DB_SCHEMA_NAME.BPARTNER.*,\
    $DB_SCHEMA_NAME.BPADDRESS.*,\
    $DB_SCHEMA_NAME.BOMD.*,\
    $DB_SCHEMA_NAME.BOM.*,\
    $DB_SCHEMA_NAME.ATABDIV.*,\
    $DB_SCHEMA_NAME.ZPLNHEA.*,\
    $DB_SCHEMA_NAME.ZSHIDAY.*,\
    $DB_SCHEMA_NAME.ZPLNAGR.*,\
    $DB_SCHEMA_NAME.ZOBJHEA.*,\
    $DB_SCHEMA_NAME.SRETURN.*,\
    $DB_SCHEMA_NAME.SRETURND.*,\
    $DB_SCHEMA_NAME.PORDER.*,\
    $DB_SCHEMA_NAME.PORDERC.*,\
    $DB_SCHEMA_NAME.PORDERP.*,\
    $DB_SCHEMA_NAME.PORDERQ.*,\
    $DB_SCHEMA_NAME.SPRICLIST.*,\
    $DB_SCHEMA_NAME.PRICSTRUCT.*,\
    $DB_SCHEMA_NAME.SPRICCONF.*,\
    $DB_SCHEMA_NAME.SINVOICE.*,\
    $DB_SCHEMA_NAME.SINVOICEV.*,\
    $DB_SCHEMA_NAME.SINVOICED.NUM_0,\
    $DB_SCHEMA_NAME.SINVOICED.CPY_0,\
    $DB_SCHEMA_NAME.SINVOICED.SDDLIN_0,\
    $DB_SCHEMA_NAME.SINVOICED.SIDLIN_0,\
    $DB_SCHEMA_NAME.SINVOICED.ITMREF_0,\
    $DB_SCHEMA_NAME.SINVOICED.ITMDES1_0,\
    $DB_SCHEMA_NAME.SINVOICED.ITMDES2_0,\
    $DB_SCHEMA_NAME.SINVOICED.ITMDES3_0,\
    $DB_SCHEMA_NAME.SINVOICED.QTY_0,\
    $DB_SCHEMA_NAME.SINVOICED.SAU_0,\
    $DB_SCHEMA_NAME.SINVOICED.ZDSPLINWEI_0,\
    $DB_SCHEMA_NAME.SINVOICED.ZDSPWEU_0,\
    $DB_SCHEMA_NAME.SINVOICED.ZCUSORDQTY_0,\
    $DB_SCHEMA_NAME.SINVOICED.ZCUSORDUOM_0,\
    $DB_SCHEMA_NAME.SINVOICED.GROPRI_0,\
    $DB_SCHEMA_NAME.SINVOICED.NETPRI_0,\
    $DB_SCHEMA_NAME.SDELIVERY.*,\
    $DB_SCHEMA_NAME.SDELIVERYD.*,\
    $DB_SCHEMA_NAME.ATEXTRA.*,\
    $DB_SCHEMA_NAME.SERREQUEST.*,\
    $DB_SCHEMA_NAME.ZSUBOBJ.*,\
    $DB_SCHEMA_NAME.ZSUBOBJDET1.*,\
    $DB_SCHEMA_NAME.ZSUBOFF.*,\
    $DB_SCHEMA_NAME.AUTILIS.*,\
    $DB_SCHEMA_NAME.ZFCYPLZ.*,\
    $DB_SCHEMA_NAME.POSCOD.*,\
    $DB_SCHEMA_NAME.ACLOB.*,\
    $DB_SCHEMA_NAME.ZDIRECTDELIV.*,\
    $DB_SCHEMA_NAME.ZADDLIST.*,\
    $DB_SCHEMA_NAME.ZTRACE.*,\
    $DB_SCHEMA_NAME.STOCK.*,\
    $DB_SCHEMA_NAME.STOALL.*,\
    $DB_SCHEMA_NAME.BAPPOINT.*
schema.history.internal.kafka.bootstrap.servers=$KAFKA_BROKERS
schema.history.internal.kafka.topic=_erp.cdc.debezium.sage.schemahistory
transforms=Reroute
transforms.Reroute.type=io.debezium.transforms.ByLogicalTableRouter
transforms.Reroute.topic.regex=([A-Za-z\-\_]+\.)([A-Za-z]+\.)([A-Za-z]+\.)(.*)
transforms.Reroute.topic.replacement=_erp.cdc.sage.$4
decimal.handling.mode=string
include.schema.changes=false
max.iteration.transactions=$MAX_ITERATION_TRANSACTION

       

      By the way, column.exclude.list works.

      Attachments

        Activity

          People

            Unassigned Unassigned
            damiano01 Damiano Pisconti (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: