Search code examples
sql-serverapache-kafkaapache-kafka-connectmssql-jdbcdebezium

SQL Server Connection Failure - Debezium - Kafka Connect

We're trying to set up a SQL-Server->Debezium->Kafka Connect->Kafka Pipeline setup for a few tables in an SQL-Server (mssql)

Upon initiating the setup, getting the following error: Logs:

{
  "name": "mssql-server-uat",
  "connector": {
    "state": "RUNNING",
    "worker_id": "172.17.0.3:8083"
  },
  "tasks": [
    {
      "id": 0,
      "state": "FAILED",
      "worker_id": "172.17.0.3:8083",
      "trace": <separately given below>
    }
  ],
  "type": "source"
}

trace:

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
    at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42)
    at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.execute(SqlServerStreamingChangeEventSource.java:267)
    at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:101)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection timed out (Read failed)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2892)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2031)
    at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6418)
    at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7579)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:590)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
    at io.debezium.jdbc.JdbcConnection.prepareQueryAndMap(JdbcConnection.java:657)
    at io.debezium.connector.sqlserver.SqlServerConnection.timestampOfLsn(SqlServerConnection.java:210)
    at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.lambda$execute$1(SqlServerStreamingChangeEventSource.java:243)
    at io.debezium.jdbc.JdbcConnection.prepareQuery(JdbcConnection.java:525)
    at io.debezium.connector.sqlserver.SqlServerConnection.getChangesForTables(SqlServerConnection.java:172)
    at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.execute(SqlServerStreamingChangeEventSource.java:162)
    ... 6 more
Caused by: java.net.SocketException: Connection timed out (Read failed)
    at java.base/java.net.SocketInputStream.socketRead0(Native Method)
    at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
    at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
    at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2023)
    ... 21 more

What could be the possible reason? Because the sql-server is up and running normally and this is strange

Comment on the problem for any specific information if required

EDIT: This error doesn't block the connection all the time. When started, the connection works, CDC events are streamed to Kafka, even read by the Kafka-Consumer we've written, but suddenly times out and the above error occurs.

Solution

  • Kafka Connect cannot connect to your SQL Server. 

    SQLServerException: Connection timed out

    Possible reasons could be:

    1. You've specified the wrong hostname in your connector configuration
    2. Your networking does not allow Kafka Connect to connect to SQL Server (for example, if you are using Docker and have not configured the networking correctly)
    3. A firewall is blocking inbound traffic to SQL Server

    edit:

    1. If the error is intermittent then that suggests that perhaps SQL Server itself is timing out, so I would speak to your DBAs and ask them to take a look at the instance and machine it is running on to see if there's an indication of why a connection would be timing out.