Unhandled Exception - System.InvalidOperationException: Connection pool is full.

[Luciano Rodrigues Nunes Mendes]

Hi everyone,

I have software written in C# that uses the ADO.NET data provider library and sometimes the following exception happens when trying to run a query or update or insert:

See the end of this message for details on invoking

just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************

System.InvalidOperationException: Connection pool is full.

at

FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetOrCreateConnectionImpl(Boolean&

createdNew)

at

FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetConnection(FbConnection

owner)

at

FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Get(ConnectionString

connectionString, FbConnection owner)

at FirebirdSql.Data.FirebirdClient.FbConnection.Open()

at CHRONOS.Models.Databases.FirebirdSQLDatabase.ExecuteProcedure(String

procedureName, String[] inputParameters, String[] outputParameters, Boolean

poolingStatus)

I've tried to catch this exception with a try/catch(FbException ex) block in the CHRONOS.Models.Databases.FirebirdSQLDatabase.ExecuteProcedure method but it never catches this FbException.

Should the ADO.NET data provider library catch this exception? Should I open a bug to fix the ADO.NET data provider library?

Thanks in advance,

Luciano

[Jiří Činčura]

It's not a FbException. It's a regular InvalidOperationException.

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

[Luciano Rodrigues Nunes Mendes]

Hi Jiří

Thank you very much for your prompt reply!

What can I do to avoid this kind of issue?

Thanks in advance,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/d0e6bdc7-f9cf-4cc7-b11b-c6d93eb6f7f2%40www.fastmail.com.

[Jiří Činčura]

Return connections back to the pool as soon as you don't need them anymore. Best practice is to use `using` block.

[Luciano Rodrigues Nunes Mendes]

Hi Jiří

How can I return connections back to the pool?

Here is the method that the exception happened. Could you please help me to improve it?

======================

        private static bool ExecuteProcedure(string procedureName, string[] inputParameters, string[] outputParameters, bool poolingStatus = true)

        {

            using (var fbConnection = new FbConnection(ConnectionString()))

            using (var fbCommand = new FbCommand(procedureName, fbConnection))

                try

                {

                    Cursor.Current = Cursors.WaitCursor;

                    fbCommand.CommandType = CommandType.StoredProcedure;

                    //INPUT PARAMETERS

                    for (var i = 0; i < inputParameters.Length; i++)

                        fbCommand.Parameters.Add("@" + i, inputParameters[i]).Direction = ParameterDirection.Input;

                    //OUTPUT PARAMETERS

                    for (var i = inputParameters.Length; i < inputParameters.Length + outputParameters.Length; i++)

                        fbCommand.Parameters.Add("@" + i, FbDbType.VarChar).Direction = ParameterDirection.Output;

                    //OPEN CONNECTION

                    fbConnection.Open();

                    //EXECUTE PROCEDURE

                    _ = fbCommand.ExecuteNonQuery();

                    //GET OUTPUT PARAMETERS

                    for (var i = inputParameters.Length; i < inputParameters.Length + outputParameters.Length; i++)

                        outputParameters[i - inputParameters.Length] = fbCommand.Parameters["@" + i].Value.ToString();

                    NotifyReconnection();

                    Utility.Log("EP", procedureName);

                    return true;

                }

                catch (FbException ex)

                {

                    if (poolingStatus)

                        return ExecuteProcedure(procedureName, inputParameters, outputParameters, false);

                    else NotifyException(ex);

                    return false;

                }

                finally

                {

                    Cursor.Current = Cursors.Default;

                }

        }

======================

Thanks in advance,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/c52adff9-4d93-4ac5-95e0-b7c0fe3c1c46%40www.fastmail.com.

[Jiří Činčura]

From a connection pooling perspective, this method looks OK.

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

> > To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com <mailto:firebird-net-provider%2Bunsu...@googlegroups.com>.

> > To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/c52adff9-4d93-4ac5-95e0-b7c0fe3c1c46%40www.fastmail.com.
>
> --
> You received this message because you are subscribed to the Google
> Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWCR%2BK4Xfn9qPLrp%3DNPx8qTmf__HuUcGKAK2s4Re7fc_KA%40mail.gmail.com <https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWCR%2BK4Xfn9qPLrp%3DNPx8qTmf__HuUcGKAK2s4Re7fc_KA%40mail.gmail.com?utm_medium=email&utm_source=footer>.

[Luciano Rodrigues Nunes Mendes]

Hi Jiří

Thank you very much for your review!

I saw another person on this site facing a similar issue: Firebird Client пул соединений работает неправильно - PullRequest

Would it be the case that we investigate the possibility of an issue in the ADO.NET library pooling?

Best Regards,

Luciano

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/3612621f-ba6b-4f72-9058-1ed28424bdc3%40www.fastmail.com.

[Jiří Činčura]

This has been resolved in 8.0.0, IIRC.

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

On Tue, Feb 23, 2021, at 21:29, Luciano Rodrigues Nunes Mendes wrote:
> Hi Jiří
>

> Thank you very much for your review!
>
> I saw another person on this site facing a similar issue: Firebird
> Client пул соединений работает неправильно - PullRequest

> <http://pullrequest.ru/12759244/firebird-client-pul-soedinenii-rabotaet-nepravilno>

> Would it be the case that we investigate the possibility of an issue in

> the ADO.NET <http://ado.net/> library pooling?

> > > > To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com <mailto:firebird-net-provider%2Bunsu...@googlegroups.com> <mailto:firebird-net-provider%2Bunsu...@googlegroups.com <mailto:firebird-net-provider%252Buns...@googlegroups.com>>.

> > > > To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/c52adff9-4d93-4ac5-95e0-b7c0fe3c1c46%40www.fastmail.com.
> > >
> > > --
> > > You received this message because you are subscribed to the Google
> > > Groups "firebird-net-provider" group.
> > > To unsubscribe from this group and stop receiving emails from it, send
> > > an email to firebird-net-pro...@googlegroups.com <mailto:firebird-net-provider%2Bunsu...@googlegroups.com>.
> > > To view this discussion on the web visit

> > > https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWCR%2BK4Xfn9qPLrp%3DNPx8qTmf__HuUcGKAK2s4Re7fc_KA%40mail.gmail.com <https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWCR%2BK4Xfn9qPLrp%3DNPx8qTmf__HuUcGKAK2s4Re7fc_KA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> >
> > --
> > You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.

> > To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com <mailto:firebird-net-provider%2Bunsu...@googlegroups.com>.

> > To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/3612621f-ba6b-4f72-9058-1ed28424bdc3%40www.fastmail.com.

>
> --
> You received this message because you are subscribed to the Google
> Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWC8ohhWZbahDsRxYqiQjmX2S3jaW2x4-v4k_DU4RVCoAw%40mail.gmail.com <https://groups.google.com/d/msgid/firebird-net-provider/CAK1TzWC8ohhWZbahDsRxYqiQjmX2S3jaW2x4-v4k_DU4RVCoAw%40mail.gmail.com?utm_medium=email&utm_source=footer>.

[Luciano Rodrigues Nunes Mendes]

Hi Jiří,

Thank you very much for confirming that the issue that I am facing in my system is already fixed in the next ADO.NET provider (v8.0.0).

Best Regards,

Luciano

You received this message because you are subscribed to a topic in the Google Groups "firebird-net-provider" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-net-provider/tZM58llPTa8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/910eb1a8-976a-4e9b-9a1d-38a1430b2d95%40www.fastmail.com.