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

MSSQL wrong default values in db schema for varchar, nvarchar, char columns

XMLWordPrintable

    • False
    • None
    • False

      Hello, during testing we encountered a strange bug regarding default values for nullable varchar, nvarchar and char columns in sql server. The bug is due to wrong parsing of sql server default value from DDL. The bug is in SQLServerDefaultValueConverter for string types. The info from intelij debuger is in screenshots.
      I believe this is a bug as the behavior is very strange and not really logical.  Thank you very much for any information wether this can be fixed or if there is a workaround for this issue.
      Full report follows...

      Bug report

      MSSQL server connector registers wrong schema for db columns that are of types varchar, nvarchar, char if they contain default value of NULL.

      What Debezium connector do you use and what version?

      This bug is present atleast in version 2.2 2.4 and latest stable 2.5

      What is the connector configuration?

      Standard debezium source the whole config will be attached. File:<debezium-test-defaults-source-1.properties>

      What is the captured database version and mode of depoyment?

      Confluent KafkaConnect localy or strimzi in kubernetes on prem.

      What behaviour do you expect?

      Debezium parses wrong default value for nullable char, varchar and nvarchar columns. It is "UL" instead of null.

      What behaviour do you see?

      Debezium should properly parse (NULL) from default value in DDL of MSSQL Server. It works correctly for numeric types but it parses "UL"  as null default value for varchar, nvarchar and char.

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

      Happens in latest stable 2.5. Will try in unstable tomorrow and attach info. 

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

      (You might be asked later to provide DEBUG/TRACE level log)
      FILE:<debezium-test-defaults-source.log>

      How to reproduce the issue using our tutorial deployment?

      Create MSSQL table with nullable nvarchar, char or varchar column with default null the debezium connector has a bug in conversion mapper that parses (NULL) from column definition of default incorrectly. This happens just for 
      DefaultValueConverter registered for types varchar, nvarchar and char.
      DDL for test table attached FILE:<testtable3-DDL.sql>

      Implementation ideas (optional)

      This fix should change parsing in DefaultValueConverter for string types. 

      The converter is called in io/debezium/relational/TableSchemaBuilder.java:379

      Error can be seen in io/debezium/connector/sqlserver/SqlServerDefaultValueConverter.java:73 when calling mapper.parse() for string types (varchar, nvarchar, char)

      it is ok for numeric types

            Unassigned Unassigned
            peter.tuzinsky@alza.cz Peter Tužinský (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: