Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
emp51302
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Post by emp51302 » Thu 23 May 2013 14:43

Hi Devart Admin's - I am currently using the dotConnect version 7.6.226 and it was working fine until yesterday suddenly at around 6.45pm EST I started getting Timeout errors. I checked my code and nothings major changed. I using C# using {...} statements which should close connections and dispose connections too. The only way to fix it was login to my 3 servers which runs on load balancer and recycle the IIS App Pool. Why do I need to do that? Its just bizzare that this would suddenly happen! I am a licensed user of your product, and didn't expect this to happen. Please help as this is in our production environment.

The exact error is as below... :shock:

Code: Select all

System.InvalidOperationException
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> Devart.Data.Linq.LinqCommandExecutionException: Error on opening DbConnection. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at Devart.Data.MySql.MySqlConnection.Open()
   at Devart.Data.Linq.Engine.cm.g()
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.Engine.cm.g()
   at Devart.Data.Linq.Engine.cm.b(IConnectionUser A_0)
   at Devart.Data.Linq.DataProvider.a(c1 A_0, Object[] A_1)
   at Devart.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at Scribe.Articles.autosave.Page_Init(Object sender, EventArgs e)
   at System.Web.UI.Page.OnInit(EventArgs e)
   at System.Web.UI.Control.InitRecursive(Control namingContainer)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Post by MariiaI » Mon 27 May 2013 07:09

This error states that you have run out of connections available. To solve the problem increase Max Pool Size connection string parameter value. The default value is 100. Another way to get extra connections is to clear one or all of the pools.
Also, you could try validating these connections before taking them from pool or disable pooling.
To do it, open Database Explorer, right-click on Database Connection->Edit Connection Properties->Advanced. Change one of the following parameters in the opened dialog window and save changes:
- set the "Validate Connection" parameter to "True";
or
- set the "Pooling" parameter to "False".

Please tell us if this helps.

emp51302
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Re: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Post by emp51302 » Thu 30 May 2013 14:11

Thank you Mariial. Appreciate it. I have increased the Max Pool size and that has seems to do the trick. Btw, how is Validate Connection different from Pooling = False?

If pooling is turned off (False), won't that affect the application? What exactly does Validation Connection do?

Thank you and would appreciate your response on the same.

:)

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Post by MariiaI » Fri 31 May 2013 08:56

dotConnect for MySQL uses connection pooling. If Pooling=true (the default value), the connection is not deleted after closing it, it is placed to the pool instead. When a new connection with the same connection string is opened, it is taken from the pool (if there are free connections) instead of the creating a new one. This provides significant performance improvements.
In case Pooling=false, a connection will be deleted from memory and free the session. However this may lead to performance loss.

"Validate Connection" parameter indicates whether to validate connections that are being taken from the pool. If true, each time when a connection is taken from the pool, it is checked for validity and if the connection is not valid, it is destroyed and a new one is created.
If this parameter is true, the connection pool manager background validation is disabled because it is not needed. In such case we always have a valid connection, however the performance is not optimal.

Note: This behaviour may be useful when the physical connection is unstable and connection can be lost in a short period of time. Pool manager validates pool connections every 30 seconds, so it is possible that connection will become invalid during this interval and the application will take it from the pool before the validation. If the physical connection is stable, pool manager validation is enough to ensure that the connection is valid, so it is better not to use this property. Use this property only if necessary because it causes a round-trip to the database to validate each connection immediately before it is provided to the application.

Thus, if the increasing of the Max Pool size has helped you in solving the issue with the "Timeout expired" exception, it's better not to disable pooling and not to use connection validation in order to prevent performance loss.

emp51302
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Re: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool!

Post by emp51302 » Fri 31 May 2013 12:55

Thanks Mariial - Appreciate it. Yes I will just keep the Max Pool Connections and not use the other two.

Post Reply