Connection closed after a few seconds

[JDBC-0]

Driver version

12.6.3

SQL Server version

Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) Mar 19 2024 00:23:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10

JAVA/JVM version

17.0.8 LTS

Table schema

Provide the table schema to repro the issue.

Problem description

Connection gets lost.

Expected behavior

Connection stays alive until closed.

Actual behavior

After a few seconds, some SQL statements fail with Die Verbindung wurde geschlossen. errorCode=0, message=Die Verbindung wurde geschlossen., SQLState=08S01, cause=null

Error message/stack trace

com.microsoft.sqlserver.jdbc.SQLServerException: Die Verbindung wurde geschlossen.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:233)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1848)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1146)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(SQLServerPreparedStatement.java:1723)
    at de.icomps.types.iCJDBCStatement.setNull(iCJDBCStatement.java:635)

Any other details that can be helpful

Occurs after a few seconds for some connections. Others work. All connections are build from the exact same JDBC URL:

jdbc:sqlserver://{DBSERVERADR};databaseName=amc;applicationName=MyApp;workstationID={HOSTID};integratedSecurity=true;sendStringParametersAsUnicode=false;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=false;connectRetryCount=30;connectRetryInterval=10;encrypt=false;loginTimeout=300;lockTimeout=3600000;queryTimeout=3600

JDBC trace logs

[Jeffery-Wasty]

Hi @JDBC-0,

I don't see anything from your connection string that would be a problem. Likely, this is a network issue, especially since this only happens intermittently as described above. We can take a look at the driver logs to confirm. Please supply us with logs by following the instructions in this link https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16. You may also be interested in our wiki page on troubleshooting intermittent connection issues: https://github.com/microsoft/mssql-jdbc/wiki/Troubleshooting-Intermittent-Connection-Failures.

[JDBC-0]

The problem vanished after I removed all timeout parameters from the URL:

connectRetryCount=30;connectRetryInterval=10;loginTimeout=300;lockTimeout=3600000;queryTimeout=3600

these/most of them stay unchanged for all the previous driver versions, but this problem also occured at least since 12.6.2.

[lilgreenbird]

the SQLState 08S01 implies there was a communication link failure. Perhaps you were having network issues a the time? Otherwise please confirm what "at least since 12.6.2" means? what release did this work for you and what release did the issue occur in? As mentioned by Jeff, please include logs as well.

[JDBC-0]

We run a long time on 12.2.0 (and below) without these problems. But we noticed another very strange problem that a specific SQL statement sometimes didn't seem to be executed even though we reduced it to 1 single UPDATE statement and enclosed it with log outputs - no exception, no error, nothing. Since then we updated the driver again and again in hope to "fix" this strange problem. So, far it didn't occur anymore, so we hope that it was a driver problem. But now these connection closed errors occured with the newer driver versions.

We have no idea what's the problem, but removing all of these from the URL, it seems to work:

connectRetryCount=30;connectRetryInterval=10;loginTimeout=300;lockTimeout=3600000;queryTimeout=3600

I remember that we had timeout issues in the past so we tried to fiddle around with these parameters which seemed to help. Now we removed them to "fix" the connection closed problem .... perhaps we might now run in some timeout problems again.