Sqlclient: sqlclient does not close connections in Docker(debian)

[paalmoest]

We are seeing behaviour form the System.Data.Sqlclient is not closing the connections when its running on the official mircosoft/dotnet docker image for linux. The connections are accumulating to over 400 connections in a couple of days on a service with little traffic. We have the same code running on a windows platform and there we do not see this behaviour.
We are running dotnet 1.1.0.

[danmosemsft]

@saurabh500

[saurabh500]

I think this is a duplicate of https://github.com/dotnet/corefx/issues/13422

[jamesgibbs100]

Hi there - seeing this problem as well - only the traffic is much higher and is resulting in 1000's of open connections. This is also a show stopped for me too

[saurabh500]

We have a package uploaded in our nightly feed, with the fix. https://dotnet.myget.org/feed/dotnet-core/package/nuget/System.Data.SqlClient/4.3.1-servicing-25210-01
The fix is targeted for a service release.

In case you want, you could try out the package mentioned above.

Disclaimer: Use at your own risk.

[jamesgibbs100]

@saurabh500 et al,

Thanks for the quick response - I seem to remember that trait from the MultiSubnetFailover issue we worked through earlier in the year....

Just want to update you (all).

I took the package above and dropped it into my test rig - worked straight away and the connections closed as expected.

I proceeded "at my own risk" to drop this into our UAT environment and did NOT see anything unexpected (read as LGTM). Tomorrow, this will head further towards production.

[Understand this is port of complex app (financial) which tends to push the boundaries somewhat]

Without this fix - there is simply nothing you can do to kill the connection gracefully - other than kill the process. You can control the rampant connection creation marginally by using pooling but if that is off then expect uncontrolled connection growth.

With this fix, it works completely as expected. My previously 7000 database connections across 40 tenant databases in UAT collapsed to a "working set" of 185 or there abouts......

I haven't detected anything adverse from using the package (although we were all a little nervous when we saw it had been downloaded only 3 times - of which I was one !! ;-))

Fixed it for me :-)

Question : Will this be out around the 5/10 along with 2.0.0 ZBB ??

Thank you all.
James

PS : Is it possible to improve the testing support on Linux so this (and things like multisubnetfailover) are found earlier ? I don't mean to sound negative (far from it) but this kind of thing is easy to spot and given the implementations are fundamentally different it probably deserves some extra "love"....

PPS : I should add that this is running on Linux on Docker and I'm loving it

[ben-foster-cko]

Did fix make it into the final 4.3.1 package as I'm still seeing open connections when running in Docker?

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[divega]

@saurabh500 are you confident that this issue is fixed or something the SqlClient team should investigate further?

I see customers reporting that it is fixed but also one customer saying it wasn't for him, at https://github.com/dotnet/SqlClient/issues/104#issuecomment-326533008 and also https://github.com/dotnet/corefx/issues/13422#issuecomment-326532463.

[David-Engel]

Assigning to @saurabh500 to confirm if the fix is already in.

[dbeaulieu]

I'm experiencing this issue as well for an app being migrated to docker+kubernetes. The app typically has ~30 connections open but that number surges to ~200 at times. The app is behind a load balancer and the windows version serving the same traffic uses < 10 connections.

Environment info:
<PackageReference Include="System.Data.SqlClient" Version="4.6.1" />
FROM mcr.microsoft.com/dotnet/core/runtime:2.2-alpine3.9

OS-IMAGE KERNEL-VERSION CONTAINER-RUNTIME
Debian GNU/Linux 9 (stretch) 4.9.0-9-amd64 docker://18.6.3

[cheenamalhotra]

@paalmoest @dbeaulieu

Although this is an old issue, I'm trying to regain the context. Could you confirm if the issue still exists with Microsoft.Data.SqlClient and if yes, is it related to #129 ?

[cheenamalhotra]

@saurabh500

Could you confirm if the fix you mentioned above in comment https://github.com/dotnet/SqlClient/issues/104#issuecomment-299057506 made it to the driver? Or it was only added to servicing branch? Please point us to the PR that was merged to address this issue.

Curious as @dbeaulieu reported this issue with S.D.S v4.6.1. We should consider moving it over to S.D.S and M.D.S if it didn't get merged in corefx - 'master' branch.

[saurabh500]

https://github.com/dotnet/corefx/commit/2e7be0c790fb89411939817ab279f287dbc29eda

According to the process, this fix was first made to master and then ported to servicing branch.

[saurabh500]

I also see the fix made for the issue in dotnet/SqlClient repo
https://github.com/dotnet/SqlClient/blob/master/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNITcpHandle.cs#L62-L66

[saurabh500]

@cheenamalhotra do we have a repro at our end for this bug ?

[saurabh500]

I cannot reproduce this issue with Sync Connection.Open(), however with SqlConnection.OpenAsync executed 300 times on different tasks, makes some of the connections to leak through. I have a repro posted at https://github.com/saurabh500/sqlclientrepros/tree/master/connectionleak

The async connection paths need to be investigated here.

Update:

This need not be run inside Docker. I could repro this on Ubuntu 16.04 as well as Alpine Docker image.

[saurabh500]

Update, I see TCP connections lingering around and they are being reused for subsequent connections and operations. The connection pool has a logic to clear out the connections that have been returned to the pool via SqlConnection.Close()/Dispose() after a time period of 3-5 minutes.

If I wait for 5 minutes these connections are killed.

Since there is no concrete repro for this issue, I am going to close. Please comment with a repro that can be used to show the problem.

As far as the original issue was concerned, the TcpStream was not disposed by SqlClient making the connections linger for a really long time. That issue was fixed quite some time ago as I have mention above.