Query execution fails after waiting 30min

[kevcunnane]

• SQL Operations Studio Version: Feb release & latest from master

Steps to Reproduce:

• Use a macOS machine, such as the one owned by @twright-msft. Not all machines will work (mine is super-reliable for instance)
• Run a query against any DB
• Walk away for 30min
• Re-run the query

Expected:

• Query executes 2nd time around

Actual:

• Executing... spins for ~1minute, then we get an error message in the batch stating
  • Note that even if we could just get a quick failure in a way where we could then close & reopen the connection that'd be OK. It's the lack of reliability plus a long wait that's making this quite bad.

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 35 - An internal exception was caught)

• If you are running 2 batches, you may see another message:

Query failed: BeginExecuteReader requires an open and available Connection. The connection's current state is open.

Note that we're actually checking if the connection isn't open but this isn't reliable when it comes to TCP sockets closing. This may well be a driver-side issue, but any fix/workaround we can apply would be good.

Here's our code to close & reopen on issues:

        private void VerifyConnectionOpen(DbConnection connection)
        {
            if (connection == null)
            {
                // Ignore this connection
                return;
            }
 
            if (connection.State != ConnectionState.Open)
            {
                // Note: this will fail and throw to the caller if something goes wrong.
                // This seems the right thing to do but if this causes serviceability issues where stack trace
                // is unexpected, might consider catching and allowing later code to fail. But given we want to get
                // an opened connection for any action using this, it seems OK to handle in this manner
                ClearPool(connection);
                connection.Open();
            }
        }

We can add connection.Close() in this case which might fix issues where connection is in a broken state, but definitely doesn't fix the error all up.

[saurabh500]

@kevcunnane Do you know if @twright-msft was connecting to Sql Server on linux or windows?

[kevcunnane]

I believe we tried against both, but I'm not sure.

[kburtram]

Closing out older issues. Please reactive if this still repros. Thanks!