Debezium Sql Server connector stuck after connection idle-killed during schema history recovery

[100maygupta]

When schema history recovery on a connector with a large history topic exceeds an intermediate LB / firewall idle timeout, the JDBC connections are silently closed during the replay and the first SQL call after recovery throws SQLServerException: The connection is broken and recovery is not possible. The error is retriable, so BaseSourceTask enters its retry loop, but every retry re-runs schema history recovery on fresh-but-equally-doomed connections and never makes forward progress.

Bug report

What Debezium connector do you use and what version?

SQL Server. Reproduced on 3.2.x and 3.5.0. Vulnerable code path is also present on main.

---

What is the connector configuration?

{
  "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
  "database.hostname": "<lb-host>",
  "database.port": "1433",
  "database.user": "<user>",
  "database.password": "<pwd>",
  "database.names": "testDB",
  "topic.prefix": "server1",
  "schema.history.internal.kafka.bootstrap.servers": "broker:9092",
  "schema.history.internal.kafka.topic": "schema-changes.testdb",
  "snapshot.mode": "initial"
}

---

What is the captured database version and mode of deployment?

E.g. on-premises, with a specific cloud provider, etc.

SQL Server 2019, behind an HAProxy TCP proxy with timeout server shorter than the schema history recovery duration. Equivalent to any deployment with an LB / firewall on the JDBC path that has an application-level idle timeout.

---

What behavior do you expect?

If the JDBC connections are killed by an LB / firewall during schema history recovery, the connector should detect the broken connection on the first SQL call of executeIteration(), refresh it, and resume streaming.

---

What behavior do you see?

The connector enters a permanent retry loop. Sequence on every retry:

1. BaseSourceTask.start(config) opens fresh dataConnection and metadataConnection.
2. coordinator.start() runs schema.recover(previousOffsets) - reads the schema history topic from Kafka. JDBC connections sit idle the entire time.
3. The LB's application-level idle timer fires and closes the backend connection.
4. Recovery completes, snapshot is skipped (offset records prior completion), streaming starts.
5. First SQL call (commitTransaction() or getToLsn() at the top of executeIteration()) throws:

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is broken and recovery is not possible.
The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:231)
    at io.debezium.connector.sqlserver.SqlServerConnection.getNthTransactionLsnFromLast(SqlServerConnection.java:313)
    at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.getToLsn(...)
    at io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource.executeIteration(...)

6. SqlServerErrorHandler classifies it retriable and restarts the connector looping back to step 1.

Why the existing mechanisms don't recover this:

1. TCP keepalives are already on by default. The driver hardcodes TCP_KEEPIDLE=30s / TCP_KEEPINTERVAL=1s on every socket via Socket.setOption().
2. OS-level keepalive doesn't help The driver's per-socket setOption(TCP_KEEPIDLE, 30) overrides any net.ipv4.tcp_keepalive_* on the host, and there is no driver-level configuration to disable or tune the per-socket setting.
3. Connection Resiliency does not engage. (connectRetryCount / connectRetryInterval) - empirically the driver flags the session state as non-recoverable and skips its own retry.

Suggested fix:

At the top of executeIteration(), wrap commitTransaction() + endTransaction() + getToLsn() in a try / catch (SQLException). In the catch, probe both dataConnection and metadataConnection with isValid(). If either is invalid, refresh only the invalid side(s) (close() + connect() on the JdbcConnection - clears the prepared-statement cache and re-runs initial setup on the fresh connection), then re-read toLsn once. If both connections are valid, rethrow - the SQLException is a real SQL error, not a connection issue.

Recovery is intentionally placed at this code site because streamingExecutionContexts (per-partition state, e.g. lastProcessedPosition) is mutated later in the iteration. Recovering at the top is safe because no iteration state has been touched yet; mid-iteration recovery would need explicit retry plumbing to keep state consistent with committed offsets.

Approach was discussed here.

---

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

Ideally, also verify with the latest Alpha/Beta/CR version.

Yes — the unwrapped call site is on main. Tested with connector version 3.5.0 - seeing the same behaviour and trace.

---

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

You might be asked later to provide DEBUG/TRACE level log.

One full retry cycle (loop repeats):

[15:18:43,196] INFO  Started database schema history recovery
[15:21:58,713] INFO  Finished database schema history recovery of 7500500 change(s) in 195397 ms
[15:21:58,912] ERROR Producer failure
  com.microsoft.sqlserver.jdbc.SQLServerException: The connection is broken and recovery is not possible.
    The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
[15:21:58,920] WARN  Retry 1 of unlimited retries will be attempted
[15:21:59,256] WARN  Going to restart connector after 10 sec. after a retriable exception
[15:22:09,566] INFO  Successfully restarted task
[15:22:09,590] INFO  Started database schema history recovery
[15:25:21,262] INFO  Finished database schema history recovery of 7500500 change(s) in 191670 ms
[15:25:21,565] WARN  Going to restart connector after 10 sec. after a retriable exception
... loops indefinitely ...

---

How to reproduce the issue using our tutorial deployment?

1. Start SQL Server with CDC enabled on enough tables that schema history recovery takes longer than the LB idle timeout (in our test: 500 tables with the schema history topic duplicated 15,000× to ~7.5M entries, recovery time ~2 minutes).
2. Put HAProxy in TCP mode in front of SQL Server with timeout server of e.g. 60s.
3. Start the Debezium SQL Server connector pointing at the HAProxy frontend. Wait for the initial snapshot to complete and offset to flush.
4. Restart the connector. The new task instance enters the retry loop described above.

[100maygupta]

Working on the fix