Connecting to remote sql server from dot net core from linux sometimes fails, then starts working ra-CodePudding

I have a C# dotnet core 3.1 Asp.net MVC Application running on Ubuntu 18.04. It connects to a remote Sql Server running on Windows Server 2016 using Entity Framework. Most of the time, the connection and queries work fine. However, once in a while, especially if the connection application has not received any requests to query the database, I get this timeout error:

fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred in the database while iterating the results of a query for context type 'OptionsAPI.Services.OptionsDbContext'.
      System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
       ---> System.ComponentModel.Win32Exception (258): Unknown error 258
         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at System.Data.SqlClient.SqlDataReader.get_MetaData()
         at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
      ClientConnectionId:a5e255ac-8539-4e14-96fe-71a7d565cc88
      Error Number:-2,State:0,Class:11
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
ClientConnectionId:a5e255ac-8539-4e14-96fe-71a7d565cc88
Error Number:-2,State:0,Class:11

I am not sure why this is happening. I realize this is a bit vague, but I have search the internet for similar problems and I don't know what to do next but ask a vague question.

CodePudding user response：

Did you notice how the error message says "timeout or disconnected"? You might be tasking the remote mssql with a long-running operation, while your client is set to not wait that long. There is a way to set longer or indefinite timeout on a connection, at the client. You can do this via connection string, or via the Timeout property on the SqlConnection/SqlCommand instance. If timeout is indeed the problem, you should consider investigating how to optimize that db work, cause keeping the connection open for 10 minutes (10 is the default, I think) for anything doesn't sound good.

Also investigate that you didn't somehow reference some .Net Framework package related to your sqlclient (instead of dotnetcore) - I find it weird that Win32 is mentioned in the stack trace of that error, which seems to be all related to the process that occurs on the client. Perhaps something isn't fully compatible. See if there's an update for EF core package. I know they had bugs last time I worked with it, so it wouldn't surprise me.

Or, inspect your usage of OptionsDbContext instance, and make sure that you're closing/disposing/destroying it after your db work is done. The EF's DbContext is designed to be used just for a single unit of work, and it should not be kept around for usage within other threads or future processes/requests. If you're using DI, make sure that that instance and anything else using it aren't registered as singletons. Your error suggests that it might be a result of a forgotten (unclosed) connection, which eventually drops (by default, by the client, after 10 min), raising that exception as a side effect.