Loading...

Type: Bug
Resolution: Done
Priority: Blocker
Fix Version/s: 1.3.0.Alpha1
Affects Version/s: 1.2.1.Final
Component/s: connect-service
Labels:
- easy-starter

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

SFDC Cases Counter:
SFDC Cases Open:
SFDC Cases Links:

(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));