"Error writing data to the connection. Error reading data from the connection." exception after deleting the connection

[luronumen]

Actual Result

• "Error writing data to the connection. Error reading data from the connection." exception after deleting the connection

Expected Result

• The connection pool should be re-established in the second attempt without any exceptions

Steps to Reproduce the Issue

1. Create the following ConnectionString:

   private static string ConnectionString() => new FbConnectionStringBuilder { Pooling = true, ServerType = FbServerType.Default, DataSource = "LOCALHOST", Database = "BOTSERVICE_CIN", Charset = "UTF8", Role = "RDB$ADMIN" }.ConnectionString;

2. Create the following SelectProcedure method:

   private static bool SelectProcedure(string procedureName, string[] inputParameters, out DataTable dataTable, bool poolingStatus = true) { dataTable = new DataTable(); try { using var fbConnection = new FbConnection(ConnectionString()); using var fbCommand = new FbCommand(procedureName, fbConnection); fbCommand.CommandType = CommandType.StoredProcedure; //INPUT PARAMETERS for (var i = 0; i < inputParameters.Length; i++) fbCommand.Parameters.Add("@" + i, inputParameters[i]?.Normalize()).Direction = ParameterDirection.Input; //OPEN CONNECTION fbConnection.Open(); //SELECT PROCEDURE using (var fbDataReader = fbCommand.ExecuteReader()) dataTable.Load(fbDataReader); return true; } catch (FbException fbException) { if (poolingStatus) return SelectProcedure(procedureName, inputParameters, out dataTable, false); Console.WriteLine($"\"Database - Select Procedure\" exception: {fbException.Message}"); return false; } }

3. Run the SelectProcedure method: Verify that no issue happen

   SelectProcedure("B_CURRENCYEXCHANGES_API", Array.Empty(), out dataTable)

4. While the application is running open BOTSERVICE_CIN database using the FlameRobin and run the following query:

   DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENTS.MON$SYSTEM_FLAG=0 AND MON$ATTACHMENT_ID<>CURRENT_CONNECTION;

5. Run the SelectProcedure method again: Verify that the issue happen

   SelectProcedure("B_CURRENCYEXCHANGES_API", Array.Empty(), out dataTable)

Important Notes

• This issue does not happen if Stop/Start FirebirdSQL service;
• This issue does not happen if the FirebirdSQL service is in other PC and the client PC lost the database connection;

Environment Setup

• .NET 7.0
• Firebird SQL 3.0.10
• FirebirdSql.Data.FirebirdClient 9.1.1

[cincuranet]

The connection pool should be re-established in the second attempt without any exceptions

That's exactly what's not 100% reliably possible. Even if I check that the connection is OK, the second I give it to you, the connection might become broken. Hence you'd still need to have some recovery logic in place (in fact this was even described in ADO.NET/SqlClient documentation).

[luronumen]

Hi @cincuranet

Many thanks for your quick response!

It wasn't clear to me from reading your comment whether you considered this to be a valid issue.

Note that when calling the SelectProcedure method after deleting the connection pool on the server side, an FbException is expected to occur in the try block of the method that is handled in the catch block. Note that in the catch block the method is called again, for the second time, and it is precisely here in this second call where a new connection pool should be opened and no more exceptions should happen: This is the issue!

The implementation of this method has worked very well in other situations when for example the FirebirdSQL service is restarted or when the database connection is lost for some network reason.

Another important piece of information is that the error message has changed since version 8.0.0:

Version 7.10.1 - Friday, December 4, 2020 (12/4/2020): Unable to complete network request to host " No message for error code 335544721 found.

Version 8.0.0 - Thursday, April 1, 2021 (4/1/2021): Error writing data to the connection. Error reading data from the connection.

Best Regards, Luciano

[Ionut27]

The message is the same as in this unresolved issue Behind could be another one that could help on solving it

[cincuranet]

@luronumen Can you also upload database (or at least structure to run your example)?

[luronumen]

Hi @cincuranet

I have attached the NETProvider_1095.zip .NET 7.0 console application containing:

• FB30_DDL.SQL: Script to create the Database;
• BOTSERVICE_CIN.FDB: The created database (FirebirdSQL 3.0)
• Program.cs: .NET console application to reproduce the issue

Please let me know if you also able to reproduce the issue.

Best Regards, Luciano

[cincuranet]

OK, the problem is that in this case the connection is "closed gracefully" from server-side ("connection shutdown" response) and because that's not broken socket (yet), the connection goes back to pool. Retrying again would solve it itself. But it should be handled nevertheless.

[luronumen]

Hi @cincuranet

Many thanks for debugging this issue!

As you can see in the Program.cs class, the SelectProcedure method automatically makes a second attempt (line 26) when the first attempt fails (line 20) but the connection is not reestablished: This is the bug!

I have a service running on Windows and in some cases this reconnection only happens after several minutes as you can see in the log below:

Please let me know when a fix for this bug is available so I can help you retest it.

Best Regards, Luciano

[cincuranet]

As you can see in the Program.cs class, the SelectProcedure method automatically makes a second attempt (line 26) when the first attempt fails (line 20) but the connection is not reestablished: This is the bug!

You'd need to retry for 3rd time.

[luronumen]

Hi @cincuranet

Is trying a third time a temporary workaround or is the current behavior not considered a bug?

[cincuranet]

In your case the 3rd retry is workaround. Regarding bug, yes, it's a bug.

[cincuranet]

Closing in favor if specific #1097.

[luronumen]

Hi @cincuranet

Thank you very much for the confirmation! Please let me know when you have the fix so I can help you validate it.

Meanwhile I will use the workaround replacing the line 25:

if (poolingStatus)

by

if (poolingStatus || fbException.ErrorCode == 335544727) //See https://github.com/FirebirdSQL/NETProvider/issues/1095

Best Regards, Luciano

[cincuranet]

Please let me know when you have the fix so I can help you validate it.

Keep watching #1097 and you'll catch when it's done.