MySQL Bugs: #37159: Expected behaviour when connection is lost

Bug #37159	Expected behaviour when connection is lost
Submitted:	3 Jun 2008 12:28	Modified:	19 Mar 2009 16:18
Reporter:	Christos Pavlides	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.3.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
What is the expected behaviour when the connector is using connection pooling and the server restarts or the connections are dropped from the server (killing the connection threads)? Do we have to manually clear the connection pool or should the connector detect a disconnected connection and reconnect?

How to repeat:
Currently when the connector connects to a server and the connection is killed or the server is restarted the connector fails to reestablish the connection. The error received is:

MySql.Data.MySqlClient.MySqlException: error connecting: 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 MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at SubSonic.MySqlDataProvider.CreateConnection(String newConnectionString) 

To repeat this behaviour set the MaximumPoolSize to a small number (10-20), then connect and do something on the server. Kill the thread on the server. Continue this process MaximumPoolSize times. At the end the connector will not be able to connect any more and the exception provided above will be thrown on every attempt to use the connection.

Hi Christos and thanks for your report. We have something similar reported in Bug#29756 so I'll recheck reports together.

Hi Tonci,
do we have any news on this? I would really appreciate your help on this one as well... Too many issues from me lately :), I know but we are preparing to go live with MySql on a very high volume application and we have to make sure that everything (at least as much as we can test on) works as expected.

Thanks again for all your help these days.

Hi Tonci,
do we have an update on this?

Hi Tonci,
did you have a chance to look into this issue?
I would really like to resolve this as soon as possible.

Christos, sorry for the delay.

The connector's pool manager should detect that the connection is dead, close it, and attempt to get another one. Does this happen in your test case?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Christos

For the record, here's how the alternative pooling logic in http://bugs.mysql.com/file.php?id=12463 handles this:

 - pooled connections are pinged in the background on a user-defined interval
 - if a ping fails, the connection is removed from the pool
 - unless it was already acquired by an application (while pinging), in which case an exception is thrown from Connection.Open()

To avoid a massive flux in TCP connection attempts when a server is taken offline, the pool does not automatically recover crashed connections.

The last case, where an application grabs a connection during ping, might at first seem like an unlikely event.  It does happen slightly more often than you'd expect, because a ping to an offline server can take up to [Connection Timeout] seconds.  The extra time spent waiting for a ping response when the server is offline increases the probability that someone comes along and acquires the connection.

Hopefully, the algorithm results in a low probability that you'll get a failed connection while guaranteeing that you'll wait at most [Connection Timeout] for it in any case.

(I'm not sure how the official driver handles this, I haven't found it documented anywhere. Sorry!)