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.

[Jeffery-Wasty]

This is how I understand your situation (correct me if I'm wrong). There was no "connection closed" issue on 12.2.0, however "connection closed" issue happens after upgrade to 12.6.3 AND with the timeout parameters specified above. If this is the case, then please try reproducing the error with logging enabled (https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16). It looks like the initial "connection closed" error is related to your network, however, it only showing up when timing-related settings are enabled is odd, and I would like to see what the driver is doing for this to happen.

[Jeffery-Wasty]

@JDBC-0 Is this still an issue? If so, can you please provide some clarification that I mentioned in my previous comment? If not, we'll assume the issue is resolved and move forward with closing the issue.

[JDBC-0]

Well, we had to get back to 12.4.1 because of #2475 ... we also removed all timout params to (try to) avoid these connection problems. As soon as #2475 will be fixed, we might be able to reproduce these connection problems again.

[Jeffery-Wasty]

Got it, thanks.

[Jeffery-Wasty]

Hi @JDBC-0,

With the previous issue (#2475) reaching completion, hotfix is expected to be rolled out Thursday, I want to re-examine this issue. Does this issue occur with 12.5.0, more specifically, with the custom 12.5.0 driver I sent you in the other thread? I would love it if we could include this in Thursday's release, but to do so we will need to get started on identifying the issue immediately.

[Jeffery-Wasty]

We will close this for inactivity soon if there is no further update.

[JDBC-0]

We had to revert to 12.4.1 due to #2475 ... with this older version, these errors vanished. We have to wait for post 12.8.0 driver when #2475 is fixed and see if the newest driver still causes such behaviour ...

[Jeffery-Wasty]

Latest release with the fix has been published. Please try it out and let us know if these connection issues arise again with the above timeout parameters specified.

[JDBC-0]

@Jeffery-Wasty : release notes of 12.8.1 does not include #2475 ... : https://github.com/microsoft/mssql-jdbc/releases

[Jeffery-Wasty]

Release notes include links to pull requests, not issues. #2475 was resolved by #2488. #2488 was merged into main, and backported to 12.8 in #2494. You can see #2494 as the final pull request mentioned under "Fixed issues".

[JDBC-0]

@Jeffery-Wasty : OK, 12.8.1 looks good, so far. But we need to keep it on test systems for a while after releasing it to production. The connection issues appeared only on production ...

[Jeffery-Wasty]

Sure, how long do you suspect it would take before potential issues would arise? 1 week? 2 weeks?

[JDBC-0]

As we have a sprint of 2 weeks, it will take at least 3-4 weeks until it did run some days on production.

[Jeffery-Wasty]

@JDBC-0 we'll close this issue for now, but if it resurfaces, let us know and we can reopen the issue.