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

Cannot detect Azure Sql Version

XMLWordPrintable

    • 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

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

        1. image-2020-07-22-11-10-52-183.png
          image-2020-07-22-11-10-52-183.png
          95 kB
        2. image-2020-07-22-11-14-08-255.png
          image-2020-07-22-11-14-08-255.png
          7 kB
        3. image-2020-07-22-12-03-49-484.png
          image-2020-07-22-12-03-49-484.png
          48 kB
        4. image-2020-07-22-13-13-13-292.png
          image-2020-07-22-13-13-13-292.png
          37 kB
        5. image-2020-07-22-14-18-16-218.png
          image-2020-07-22-14-18-16-218.png
          47 kB
        6. image-2020-07-23-11-47-24-000.png
          image-2020-07-23-11-47-24-000.png
          48 kB
        7. image-2020-07-23-11-51-48-137.png
          image-2020-07-23-11-51-48-137.png
          19 kB

            [DBZ-2373] Cannot detect Azure Sql Version

            Jiri Pechanec added a comment -

            Released

            Jiri Pechanec added a comment - Released

            Gunnar Morling added a comment -

            Thanks for testing, will mark this as "Resolved", the image update shouldn't impact the functionality itself.

            Gunnar Morling added a comment - Thanks for testing, will mark this as "Resolved", the image update shouldn't impact the functionality itself.

            Anthony Sumner (Inactive) added a comment -

            gunnar.morling jpechane Hi Guys, the fix works, I can now connect and test the SQL Azure managed instance. I can see its connected and generated topics so now I can test that it does what Debezium says. Thanks for your help.

            Once I pull a fixed image directly from your Dockerhub source I will test again and confirm your published image fixes it. (I tried setting the image debezium/connect:nightly but I'm not sure why it didn't work should it have the fix?, pod wouldn't start, so I built an image just to get testing)

            Feel free to mark this issue as waiting confirmation by me and chase.

            Anthony

            Anthony Sumner (Inactive) added a comment - gunnar.morling jpechane Hi Guys, the fix works, I can now connect and test the SQL Azure managed instance. I can see its connected and generated topics so now I can test that it does what Debezium says. Thanks for your help. Once I pull a fixed image directly from your Dockerhub source I will test again and confirm your published image fixes it. (I tried setting the image debezium/connect:nightly but I'm not sure why it didn't work should it have the fix?, pod wouldn't start, so I built an image just to get testing) Feel free to mark this issue as waiting confirmation by me and chase. Anthony

            Anthony Sumner (Inactive) added a comment -

            gunnar.morling jpechane Thanks assuming it appears in docker hub I'll grab the nightly build and test straight away

            Anthony Sumner (Inactive) added a comment - gunnar.morling jpechane  Thanks assuming it appears in docker hub I'll grab the nightly build and test straight away

            Gunnar Morling added a comment -

            Hey asumner2020, so we already fixed the version string parsing logic (see PR 1711 which I just merged). This will be part of the next nightly build, perhaps you could give this a try tomorrow or next week?

            jpechane, is this all that's needed? Or is there still a loose in regards to that timezone handling?

            Gunnar Morling added a comment - Hey asumner2020 , so we already fixed the version string parsing logic (see PR 1711 which I just merged). This will be part of the next nightly build , perhaps you could give this a try tomorrow or next week? jpechane , is this all that's needed? Or is there still a loose in regards to that timezone handling?

            Anthony Sumner (Inactive) added a comment -

            gunnar.morling jpechane I've implemented the Edition check and will attempt to sumbmit a pull request, I can't check java compiles though, I'm not setup but its close enough to c#

            I hope this helps.

            Anthony Sumner (Inactive) added a comment - gunnar.morling jpechane I've implemented the Edition check and will attempt to sumbmit a pull request, I can't check java compiles though, I'm not setup but its close enough to c# I hope this helps.

            Anthony Sumner (Inactive) added a comment - - edited

            For the benefit of others and reassurance...

            See https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview

            Anthony Sumner (Inactive) added a comment - - edited For the benefit of others and reassurance... See  https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview

            Anthony Sumner (Inactive) added a comment - - edited

            gunnar.morling jpechane Hi, I'm more than happy to test, this is the easiest route to move my legacy db to Kafka. Azure SQL Databases cannot support CDC so I had to Move our production database to a managed Instance of the Full Server product in order to enable CDC. So yes as Jiri says "If Select ServerProperty("Edition") == "SQL Azure" return an Int greater than 2016 to satisfy "return getSqlServerVersion() > 2016;" Else retrieve the version string as it is today.

            Not wishing to slow my fix down   but the ideal (later) would be to clone this connector and release the Azure SQL Server version just in case the product introduces newer capabilities. One has to be careful to note that this a Debezium "Server" connector, not a database connector, as the Azure Databases sit on top of a SQL cluster shared with others so no server level features can be used. In fact today the "Azure SQL Server" is merely a logical container and doesn't contain server level features.

             

            Since this is a complete blocker I'm very keen to test the nightly build as soon as possible!

             

            Anthony Sumner (Inactive) added a comment - - edited gunnar.morling jpechane Hi, I'm more than happy to test, this is the easiest route to move my legacy db to Kafka. Azure SQL Databases cannot support CDC so I had to Move our production database to a managed Instance of the Full Server product in order to enable CDC. So yes as Jiri says "If Select ServerProperty("Edition") == "SQL Azure" return an Int greater than 2016 to satisfy "return getSqlServerVersion() > 2016;" Else retrieve the version string as it is today. Not wishing to slow my fix down   but the ideal (later) would be to clone this connector and release the Azure SQL Server version just in case the product introduces newer capabilities. One has to be careful to note that this a Debezium "Server" connector, not a database connector, as the Azure Databases sit on top of a SQL cluster shared with others so no server level features can be used. In fact today the "Azure SQL Server" is merely a logical container and doesn't contain server level features.   Since this is a complete blocker I'm very keen to test the nightly build as soon as possible!  

            Gunnar Morling added a comment -

            So we'll have this fixed hopefully soon, perhaps you could help out with testing, asumner2020? If so, how do you run Debezium, would you be able to take the latest nightly build (ZIP archive) once the fix is available?

            Gunnar Morling added a comment - So we'll have this fixed hopefully soon, perhaps you could help out with testing, asumner2020 ? If so, how do you run Debezium, would you be able to take the latest nightly build (ZIP archive) once the fix is available?

            Gunnar Morling added a comment -

            Once we got confirmation that this will do the trick, we should remove the note on Azure SQL Server in the docs.

            Gunnar Morling added a comment - Once we got confirmation that this will do the trick, we should remove the note on Azure SQL Server in the docs.

              Unassigned Unassigned
              asumner2020 Anthony Sumner (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: