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

Cannot detect Azure Sql Version

    Details

    • Steps to Reproduce:
      Hide

      1) In Azure create a SQL Server Managed Instance (not Azure SQL DB), the SQL MI allows CDC to be enabled.

      2) watch the logs on the connector instance the Kafka Connect image built with docker and containing the SQL Server plugin

      3) with no server.timezone in the registration property, register json configuration payload against the pod above using /connectors endpoint

      4) Observe the ERROR Couldn't obtain database server version; assuming 'AT TIME ZONE' is not supported.

      2020-07-21 18:30:39,850 WARN The 'server.timezone' option should be specified to avoid incorrect timestamp values in case of different timezones between the database server and this connector's JVM. (io.debezium.connector.sqlserver.SqlServerConnection) [task-thread-test-connector-0]
      2020-07-21 18:30:39,920 ERROR Couldn't obtain database server version; assuming 'AT TIME ZONE' is not supported. (io.debezium.connector.sqlserver.SqlServerConnection) [task-thread-test-connector-0]
      java.lang.RuntimeException: Couldn't obtain database server version
      at io.debezium.connector.sqlserver.SqlServerConnection.getSqlServerVersion(SqlServerConnection.java:480)
      at io.debezium.connector.sqlserver.SqlServerConnection.supportsAtTimeZone(SqlServerConnection.java:461)

      5) Root cause is to not put in the server.timezone in the registration property to trigger the bug when connecting to an Azure based SQL Server Managed Instance. (CDC is only supported on SQL Server instances, not Azure SQL Databases as there is no access to enable CDC at the server level. It appears it server.timezone is not a supported property?

      https://debezium.io/documentation/reference/0.10/connectors/sqlserver.html#connector-properties

      Show
      1) In Azure create a SQL Server Managed Instance (not Azure SQL DB), the SQL MI allows CDC to be enabled. 2) watch the logs on the connector instance the Kafka Connect image built with docker and containing the SQL Server plugin 3) with no server.timezone in the registration property, register json configuration payload against the pod above using /connectors endpoint 4) Observe the ERROR Couldn't obtain database server version; assuming 'AT TIME ZONE' is not supported. 2020-07-21 18:30:39,850 WARN The 'server.timezone' option should be specified to avoid incorrect timestamp values in case of different timezones between the database server and this connector's JVM. (io.debezium.connector.sqlserver.SqlServerConnection) [task-thread-test-connector-0] 2020-07-21 18:30:39,920 ERROR Couldn't obtain database server version; assuming 'AT TIME ZONE' is not supported. (io.debezium.connector.sqlserver.SqlServerConnection) [task-thread-test-connector-0] java.lang.RuntimeException: Couldn't obtain database server version at io.debezium.connector.sqlserver.SqlServerConnection.getSqlServerVersion(SqlServerConnection.java:480) at io.debezium.connector.sqlserver.SqlServerConnection.supportsAtTimeZone(SqlServerConnection.java:461) 5) Root cause is to not put in the server.timezone in the registration property to trigger the bug when connecting to an Azure based SQL Server Managed Instance. (CDC is only supported on SQL Server instances, not Azure SQL Databases as there is no access to enable CDC at the server level. It appears it server.timezone is not a supported property? https://debezium.io/documentation/reference/0.10/connectors/sqlserver.html#connector-properties

      Description

      (I Couldn't really work out what Epic this might apply to)

      When connecting to an Azure based SQL Server Managed Instance (a full SQL Server) the registration.json works, and connects to the server but fails to establish the version.

      in "io.debezium.connector.sqlserver.SqlServerConnector",
      This is because the "private int getSqlServerVersion()" function makes the following assumption...
      // Always beginning with 'Microsoft SQL Server NNNN'
      see the code here
      https://github.com/debezium/debezium/blob/04254e3fe749b32e172136b48715e3ac1ac5b906/debezium-connector-sqlserver/src/main/java/io/debezium/connector/sqlserver/SqlServerConnection.java#L469

      However, the Managed SQL Server instance returns a different version string name breaking the assumption above.
      "Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 18 2020 16:54:11 Copyright (C) 2019 Microsoft Corporation"

      However the version shown does not correspond to the actual SQL version, it is better to use the Product Version in TSQL.
      In Azure...  (I.e. Always the latest edition).
      Whilst SQL Azure returns v12, connection properties reveal the underlying engine version to confirm this.

      See https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver15

      So perhaps can we track... Edition and ProductMajorVersion?

      The use of substring is this bug waiting to happen

      return Integer.valueOf(version.substring(21, 25));

      If Edition <> "SQL Azure" && ProductMajorVersion >= 13 Then etc

      In the mean time I was hoping the logs suggestion to set server.timezone doesn't seem to be an option?

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                asumner2020 Anthony Sumner
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: