Reconnect after a long pause

[Bauradar - Weitblick am Bau]

Hi,

some of my customers keep the Desktop program open for hours and days. After some period of time the get it back in the front and want to start to work again.

In these situations the connection crashes with the message below and so does my program.

Is there a way to check if the connection is still valid? If not, how to recover it?

Its the latest 10x version of the .net provider.

Thanks

Niko

2024-01-24 06:54:38.6010|ERROR|1|Unerwarteter Fehler aufgetreten|System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> FirebirdSql.Data.FirebirdClient.FbException: Error writing data to the connection.
Error reading data from the connection. ---> FirebirdSql.Data.Common.IscException: Error writing data to the connection.
Error reading data from the connection. ---> System.IO.IOException: In die Übertragungsverbindung können keine Daten geschrieben werden: Eine vorhandene Verbindung wurde vom Remotehost geschlossen. ---> System.Net.Sockets.SocketException: Eine vorhandene Verbindung wurde vom Remotehost geschlossen
   bei System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   bei FirebirdSql.Data.Client.Managed.FirebirdNetworkHandlingWrapper.Flush()
   bei FirebirdSql.Data.Client.Managed.Version10.GdsTransaction.BeginTransaction(TransactionParameterBuffer tpb)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei FirebirdSql.Data.Client.Managed.Version10.GdsTransaction.BeginTransaction(TransactionParameterBuffer tpb)
   bei FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.BeginTransaction(TransactionParameterBuffer tpb)
   bei FirebirdSql.Data.FirebirdClient.FbTransaction.BeginTransaction()
   bei FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet)
   bei FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet)
   bei FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
   bei System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   bei System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   bei System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   bei System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   bei System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
   bei System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()

[Jiří Činčura]

> Is there a way to check if the connection is still valid? If not, how
> to recover it?

There's not a 100% solution to know the connection is valid. You can only be 100% sure when you try to send/receive some data.

With that. The recommended approach is to have retry/reconnect logic in place.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Bauradar - Weitblick am Bau]

Hello Jiri,

if there is no 100% way to detect, if the connection is valid or not - should I do a reconnection on every query etc.?

Niko

[Jiří Činčura]

That would be, first of all, wasteful, and also still not 100% correct - you can reopen the connection and right after you've done that, it will become broken and the subsequent operation would still fail.

Retrying transient errors is the way to go.

[Mark Rotteveel]

On 26/01/2024 07:26, 'Bauradar - Weitblick am Bau' via

firebird-net-provider wrote:
> if there is no 100% way to detect, if the connection is valid or not -
> should I do a reconnection on every query etc.?

Opening and closing connections for a unit of work would be advisable,
and rely on the connection pool.

That said, I work primarily in Java, and there you usually use
third-party connection pools with advanced features in testing broken
connections and replacing stale (i.e. idle too long) or old (i.e. open
for a long time) connections. I'm not sure how the internal connection
pool of the Firebird ADO.net provider fairs in comparison, so while my
advice might work well in the Java world, it might not apply here.

Mark
--
Mark Rotteveel

[Bauradar - Weitblick am Bau]

Hello,

this is my regular aproach to use a database connection:

 using (var fbCon = DatabaseHelper.CreateOpenFbConnection())
{
       Arbeitseinteilungen = (await fbCon.QueryAsync<CR148Mitarbeiter>(queryArbeitseinteilung, new { StartWoche = startwoche, StopWoche = stopwoche, Firma = firma })).ToList();
}

I assume this matches the unit-of-work pattern.

BUT: I have some old code with EF and a long living context (starts and dies with the ViewModel livetime = whole program livetime).

These long living EF contextes are getting replaced with an RESTapi or Dapper (and the unit of work pattern), which will take a lot of time.

Is there a way to check a EF connection if its valid or not?

Thanks

Niko

[Scott Morgan]

On 25/01/2024 17:44, 'Bauradar - Weitblick am Bau' via

firebird-net-provider wrote:
> Hi,
>
> some of my customers keep the Desktop program open for hours and days.
> After some period of time the get it back in the front and want to start
> to work again.
>

Would adjusting the 'DummyPacketInterval' setting help?

(these links look a little out of date, may be better checking on the
main FB support list first)
https://firebirdsql.org/rlsnotesh/config-fb-conf.html
https://www.ibphoenix.com/resources/documents/how_to/doc_104

Scott

[Jiří Činčura]

> Would adjusting the 'DummyPacketInterval' setting help?

Not in detection of broken connection.

[Jiří Činčura]

> Is there a way to check a EF connection if its valid or not?

Same as with pure FbConnection. Do something and have retry logic in place.