Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

FirebirdSQL Connection Pooling (ADO.NET) is not Working [DNET585] #554

Closed
firebird-automations opened this issue Jan 11, 2015 · 19 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: @luronumen

Is duplicated by DNET667
Is related to DNET668

Attachments:
ADONETConnectionPoolingERROR.png

Actual result:
Engine Code: 335544726
Engine Message: Error reading data from the connection.
See: ADONETConnectionPoolingERROR.png

Expected Result:
The Connection Pooling should be automatically restored when any disconnection occurs between requests;

Steps to reproduce the issue:
1- Open a FirebirdSQL Connection Pooling (http://ADO.NET) using the http://ADO.NET Provider v.4.6.0.0 (Reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx) and do a Data request: The data is received without any issue;
2- Interupt the FirebirdSQL Server (net stop FirebirdServerDefaultInstance) and restart it (net start FirebirdServerDefaultInstance);
3- Try to request any other data from the Database using the same FirebirdSQL Connection Pooling open before.

Additional Information:
This issue did not happen when using the previous http://ADO.NET Provider v.4.5.2.0.

@firebird-automations
Copy link
Author

Modified by: @luronumen

Attachment: ADONETConnectionPoolingERROR.png [ 12661 ]

@firebird-automations
Copy link
Author

Modified by: @luronumen

description: Actual result:
Engine Code: 335544726
Engine Message: Error reading data from the connection.
See: ADONETConnectionPoolingERROR.png

Expected Result:
The Connection Pooling should be automatically restored when any disconnection occurs between requests;

Steps to reproduce the issue:
1- Open a FirebirdSQL Connection Pooling (http://ADO.NET) using the http://ADO.NET Provider v.4.6.0.0 (Reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx);
2- Interupt the FirebirdSQL Server (net stop FirebirdServerDefaultInstance) and restart it (net start FirebirdServerDefaultInstance);
3- Try to access the Database using the same FirebirdSQL Connection Pooling open before.

Additional Information:
This issue did not happen when using the previous http://ADO.NET Provider v.4.5.2.0.

=>

Actual result:
Engine Code: 335544726
Engine Message: Error reading data from the connection.
See: ADONETConnectionPoolingERROR.png

Expected Result:
The Connection Pooling should be automatically restored when any disconnection occurs between requests;

Steps to reproduce the issue:
1- Open a FirebirdSQL Connection Pooling (http://ADO.NET) using the http://ADO.NET Provider v.4.6.0.0 (Reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx) and do and Data request: The data is received without any issue;
2- Interupt the FirebirdSQL Server (net stop FirebirdServerDefaultInstance) and restart it (net start FirebirdServerDefaultInstance);
3- Try to request any other data from the Database using the same FirebirdSQL Connection Pooling open before.

Additional Information:
This issue did not happen when using the previous http://ADO.NET Provider v.4.5.2.0.

@firebird-automations
Copy link
Author

Modified by: @luronumen

description: Actual result:
Engine Code: 335544726
Engine Message: Error reading data from the connection.
See: ADONETConnectionPoolingERROR.png

Expected Result:
The Connection Pooling should be automatically restored when any disconnection occurs between requests;

Steps to reproduce the issue:
1- Open a FirebirdSQL Connection Pooling (http://ADO.NET) using the http://ADO.NET Provider v.4.6.0.0 (Reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx) and do and Data request: The data is received without any issue;
2- Interupt the FirebirdSQL Server (net stop FirebirdServerDefaultInstance) and restart it (net start FirebirdServerDefaultInstance);
3- Try to request any other data from the Database using the same FirebirdSQL Connection Pooling open before.

Additional Information:
This issue did not happen when using the previous http://ADO.NET Provider v.4.5.2.0.

=>

Actual result:
Engine Code: 335544726
Engine Message: Error reading data from the connection.
See: ADONETConnectionPoolingERROR.png

Expected Result:
The Connection Pooling should be automatically restored when any disconnection occurs between requests;

Steps to reproduce the issue:
1- Open a FirebirdSQL Connection Pooling (http://ADO.NET) using the http://ADO.NET Provider v.4.6.0.0 (Reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx) and do a Data request: The data is received without any issue;
2- Interupt the FirebirdSQL Server (net stop FirebirdServerDefaultInstance) and restart it (net start FirebirdServerDefaultInstance);
3- Try to request any other data from the Database using the same FirebirdSQL Connection Pooling open before.

Additional Information:
This issue did not happen when using the previous http://ADO.NET Provider v.4.5.2.0.

@firebird-automations
Copy link
Author

Commented by: @luronumen

This issue is still reproducible on http://ADO.NET Provider v.4.6.1.0

@firebird-automations
Copy link
Author

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Commented by: @luronumen

Hi Jiri

It seems that I am not the only person that are facing this king of the issue since the version v.4.6.0.0.

This is NOT the expected behavior because this is NOT what happen when I am using the SQL server instead of the Firebird.
Please see the SQL Server Connection Pooling (http://ADO.NET) documentation available at the link https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx

I am adding below the source code that is using the Connection Pooling [connectionString()] based in the http://ADO.NET documentation.

===============
using FirebirdSql.Data.FirebirdClient;

public static bool ExecuteCommand(string DML)
{
using (FbConnection fbConnection = new FbConnection(connectionString()))
using (FbCommand fbCommand = new FbCommand(DML, fbConnection))
try
{
Cursor.Current = Cursors.WaitCursor;
fbCommand.Connection.Open();
fbCommand.ExecuteNonQuery();
return true;
}
catch (FbException ex)
{
return false;
}
finally
{
Cursor.Current = Cursors.Default;
}
}

@firebird-automations
Copy link
Author

Commented by: alex (alexedu)

Hey all!
I have the same issue...
It seems that something changed since 4.5.2 version.

I see that ticket is closed, but what is the solution?

Should we add now some additional connection checking?

1) Override provider method IsConnectionBroken(Exception e) ?
2) Flush pool FbConnection.ClearPool ?

Or something else?

P.S. I have this issue with http://ASP.NET MVC + Entity Framework and also with desktop apps

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET667 [ DNET667 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is related to DNET668 [ DNET668 ]

@firebird-automations
Copy link
Author

Commented by: @luronumen

This issue is still reproducible on http://ADO.NET Provider v.5.8.0.0 (http://sourceforge.net/projects/firebird/files/firebird-net-provider/5.8.0.0/)

@firebird-automations
Copy link
Author

Commented by: franki (franki1986)

Why is this still an expected behaviour?

@firebird-automations
Copy link
Author

Commented by: @luronumen

Finally this issue was fixed in the http://ADO.NET Provider v.6.6.0 (https://www.nuget.org/packages/FirebirdSql.Data.FirebirdClient/6.6.0)
Reactivating Connection Pooling in my projects...
Thank you very much!

@firebird-automations
Copy link
Author

Commented by: @luronumen

Sorry but this issue is still reproducible on http://ADO.NET Provider v.6.6.0 (https://www.nuget.org/packages/FirebirdSql.Data.FirebirdClient/6.6.0)

:(

@firebird-automations
Copy link
Author

Commented by: bluish (bluish)

If the problem is still there, this issue should be reopened, please.

@firebird-automations
Copy link
Author

Commented by: @luronumen

Hi bluish

I have retested this issue on the most recent http://ADO.NET Provider v.7.0.0 (https://www.nuget.org/packages/FirebirdSql.Data.FirebirdClient/7.0.0) and now the error code that is generate is:

335544721 - Unable to complete network request to host "@1"

instead of the original one:

335544726 - Error reading data from the connection

Not sure If a open a new issue for this will be fixed as you can see the comments of this bug (even saying that it is not reproducible on http://ADO.NET Provider v.4.5.2.0). It's been a while since I've given up Connection Pooling on my systems and I suggest you do the same on yours. :(
FbConnectionStringBuilder {Pooling = false}

@firebird-automations
Copy link
Author

Commented by: bluish (bluish)

Thank you, Luciano!

@firebird-automations
Copy link
Author

Commented by: Jeriel Lopez (jerielelopez)

Hello Luciano,

I do not understand why you said that it was "NOT the expected behavior". The link you gave says that this is the expected behavior.

https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx

"If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an ***exception is thrown.***"

The previous version was adding overhead by checking if the connection was broken, and this was not the expected behavior.

Anyway, even if this connection is discarded, your other connections in the pool might be broken pipes as well, so it might be best for you to clear the pool if this is detected. You would have to retry the query and let the driver generate new connections. If you use 4.6+, you will see improvements in performance, but you have to take this into account and code for it.

Hope that helps someone.

My source is from a great talk by Jiri Cincura at the 2019 Firebird Conference.

P.s. Off topic: Not sure if you have to take this into account when using Entity Framework. I'll try to find out.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants