How to Prevent "MySql has gone away" when using TIdHTTPServer

1
votes

I have written a web server using Delphi and the Indy TIdHttpServer component. I am managing a pool of TAdoConnection connections to a MySql database. When a request comes in I query my pool for available database connections. If one is not available then a new TAdoConnection is created and added to the pool.

Problems occur when a connection becomes "stale" (i.e. it has not been used in quite some time). I think in this instance the query results in the "MySql has gone away" error.

Does anyone have a method for getting around this? Or would I have manage it myself by one of the following:

  1. Writing a thread that will periodically "refresh" all connections.
  2. Keeping track of the last active query, and if too old pass up using the connection and instead free it.
1
mysqldelphiindy
Put a timestamp in your pool object when you connect to the db, throw away the pool object when it is getting to "old" (ie x minutes) and create a new connection... - whosrdaddy

1 Answers

1
votes

Two suggestions:

  • store a 'last used' time stamp with every pooled connection, and if a connection is requested check if the connection is too old - in this case, create a new one

  • add a validateObject() method which issues a no-op SQL query to detect if the connection is still healthy

  • a background thread which cleans up the pool in regular intervals: removing idle connections allows to reduce the pool size back to a minimum after peak usage

For some suggestions, see this article about the Apache Commons Pool Framework: http://www.javaworld.com/article/2071834/build-ci-sdlc/pool-resources-using-apache-s-commons-pool-framework.html