Problem trying to reconnect database after a network disconnect

[Colin]

Hi, posted a question on Stack Overflow and was encouraged to post here.

I have constructed a small app, as suggested there, and have posted the code below. Note, I am not a fluent C# developer, so forgive me if I have done things wrong or not in the best way...

I am running on Linux and the server is on Linux (Firebird 5).

After I have detected a failure (i.e. catch the Timeout exception), I wan't to reestablish the connection to the DB (the network is back up by now), but don't know how to. On another Stack Overflow post, I saw mention of ClearAllPools, but basically the app hangs at the call to FbConnection.ClearAllPools as shown below (basically it hangs on any database call I have tried after the network is proven to be back up).

In terms of the network connection used in this test, I am connecting over a VPN. Basically I bring the VPN down, and then back up again, and ensure other things over the VPN start working again, I want the app to do the same.

Here is the project XML to show the dependencies:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>
    <ItemGroup>
      <PackageReference Include="EntityFramework.Firebird" Version="10.1.0" />
      <PackageReference Include="FirebirdSql.Data.FirebirdClient" Version="10.3.3" />
    </ItemGroup>
</Project>

And here is the Program.cs source:

using System.Data.Common;
using EntityFramework.Firebird;
using FirebirdSql.Data.FirebirdClient;

const string databaseHost = "thehost";
const string databaseName = "database.fdb";
const string databaseRole = "TheRole";
const string databaseUsername = "TheUser";
const string databasePassword = "ThePassword";

DbProviderFactories.RegisterFactory(FbProviderServices.ProviderInvariantName, FirebirdClientFactory.Instance);
var dbFactory = DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient");
var connection = dbFactory.CreateConnection();
if (connection == null) {
    throw new Exception("Could not create a new DB Connection. dbFactory.CreateConnection returned null");
} else {
    connection.ConnectionString = $"Host={databaseHost};Database={databaseName};Username={databaseUsername};Password={databasePassword};Role={databaseRole};Pooling=true";

    Console.WriteLine("About to open connection");
    connection.Open(); // Make sure we are good to go
    Console.WriteLine("Connection opened");
   
    await ExecuteCommandAsync("1");
   
    Console.WriteLine($"Disconnect and Reconnect network now, hit enter when ready");
    Console.ReadLine();
   
    await ExecuteCommandAsync("2 - after disconnect");

    await ExecuteCommandAsync("3 - after reconnect");
   
    Console.WriteLine("Done");
}

async Task ExecuteCommandAsync(string messageSuffix) {
    try {
        await Task.Run(() => {
            Console.WriteLine("-----------------");
            Console.WriteLine("Executing command " + messageSuffix);
            using var cmd = connection.CreateCommand();
            cmd.CommandTimeout = 5; // Does not seem to have any effect
            cmd.CommandText = $"select count(*) from RDB$DATABASE";
            var reader = cmd.ExecuteReader();
            reader.Read();
            Console.WriteLine($"Finished executing command. Count is {reader.GetInt32(0)}");
        }).WaitAsync(TimeSpan.FromSeconds(10));
    } catch (TimeoutException e) {
        Console.WriteLine("Got a timeout, attempting to reconnect...");
        connection.Close();
        Console.WriteLine("After Close, before ClearAllPools...");
        FbConnection.ClearAllPools();

        // We don't get here, at least not after a few minutes 

        Console.WriteLine("After ClearAllPools...");
        connection.Open();
        Console.WriteLine("After Open");
    }
}

[Jiří Činčura]

Take minidump when the code is "stuck" and share it.

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

[Colin]

Assume I did it correctly... Zipped Minidump attached.

Regards

Colin

[Colin]

Just an update... 

I have been into the office and found that disconnecting and reconnecting the network cable does not cause the same issue. Nor does shutting down and restarting the DB.

The problem occurs when disconnecting and reconnecting when connected via VPN... other things like remote desktop picks up and continues, but the Firebird app does not.

My main concern was the restarting of the DB and possibly some short term re-connections, and this does not seem to have the same problem as mentioned.

In other words, I imagine it will be harder to replicate and debug this issue... it seems specific to a VPN re-connection rather than "a normal" network reconnect, and I suspect it will be less of a problem for us too.

Just thought I better provide this update...

Colin

[Jiří Činčura]

The `ClearAllPools` issue is https://github.com/FirebirdSQL/NETProvider/pull/1076.

What VPN are you using? It looks something gets stuck there and not timing out/throwing exception.

[Colin]

Hi, it is a Fortigate VPN.

Regards

Colin