Repository navigation
SqlConnection.State value is not updated when connection is broken #1874
Description
Describe the bug
Note, this may be happening in other scenarios as well. But this is a clear example of behavior I wasn't expected so figured we'd start from there.
The root issue is that the State property doesn't match what I'd expect when the connection to the server is broken.
To reproduce
- Create sample app that continuously executes a query against a server, e.g.
using SqlConnection conn = new SqlConnection("SERVER INFO HERE");
conn.Open();
while(true)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT 1";
try
{
var result = cmd.ExecuteNonQuery();
Console.WriteLine("Executed successfully");
} catch (Exception e)
{
Console.WriteLine($"State={conn.State} {e.Message}");
}
Thread.Sleep(1000);
}- Start the program, it should start printing out "Executed successfully"
- Shut off the server
Expected behavior
I expect an exception to be thrown (this happens) and the connection state to be Closed/Broken (this does not happen).
Instead I get the failures but the connection State stays as open
Further technical details
Microsoft.Data.SqlClient version: 5.1.0-preview2.22314.2
.NET target: .NET 6.0
SQL Server version: SQL 2022
Operating system: Docker container
Metadata
Metadata
Assignees
Labels
No labels
Activity
lcheunglci commentedon Jan 4, 2023
Hi @Charles-Gagnon , thanks for bringing this to our attention and thanks for providing the repro sample. We'll take a look after the release.
lcheunglci commentedon Jan 10, 2023
This behavior is by design. Connection State does not change outside of client server communication. The only things that change State are the application closing the connection or some kind of failure of an active call between the client and the server. There are no performant opportunities for the driver to change the connection state outside of those events.
Charles-Gagnon commentedon Jan 10, 2023
Why is the above example not considered a "failure of an active call"? I try to execute a query and it fails with a connection error.
Charles-Gagnon commentedon Jan 10, 2023
The documentation for State specifically says :
Indicates the state of the SqlConnection during the most recent network operation performed on the connection.which in the above example is false - I tried to execute a network operation but it was broken and so failed.
lcheunglci commentedon Jan 11, 2023
Thanks for your response. I'll do some investigation after our release.
cheenamalhotra commentedon Jan 20, 2023
I've talked to @Charles-Gagnon offline on this, and I had few points to add:
Error with Class 20 is not retriable, as in this case, so application must not retry (as per guidelines), for now I would recommend that to be handled.
For SqlClient, since the error class is 20 and this is infact a login failure, the driver should be able to update the connection state to 'Closed' and make it ready to be re-opened. The parser is also disconnected here so it doesn't change the fact that this connection will never be reusable in this state. It has to be re-opened by applications if this error occurs.
Doing so is presumably safe for driver since closing an already closed connection doesn't lead to exception (for applications capturing this error with class 20 and closing connection).
JRahnama commentedon Apr 3, 2023
Closing the issue. PR #1953 is merged with the fix for this issue.