"That's probably the best answer to any forum question I've ever posted and/or seen" is what someone said in response to my reply on the Adobe ColdFusion forums.
I always enjoy helping other with ColdFusion questions whenever I have time (a 2 year old child doesn't leave much room for extra time!), but reactions like this really make it feel that the help is worth my effort. That's what being an Adobe Community Professional is all about. For what its worth, here's what I had to say about using Validation Queries to eliminate surprises when using database connection pooling in ColdFusion:
The Maintain Connections setting means that after a db connection is created for a given database, that connection will be used for the current query and then kept open in a connection pool so that they can be reused for later queries. The reason is that opening a connection is an expensive, time consuming operation, and its more efficient to only have to authenticate once. When this setting is disabled, for every page request accessing a given database, a new database connection will be created, the db authentication will occur, the query/queries on the request will happen, then the db connection will be closed.
When you maintain connections you have a pool of db connections that exist for an extended period, being frequently reused with additional requests. If the connections are idle for a period greater than the Inactive Timeout setting in the datasource definition, then those connections are closed and the pool size is reduced. Also, if a request checks out a connection from the pool, attempts to use it for a query for some request, then if that db connection produces a db error then that is another situation where the db connection will be closed and removed from the pool.
It has been known to happen that when you are pooling datasource connections like this that its possible that the TCP connection to the database has been interrupted for some reason, and when the connection is checked out for use on a page request you will get some type of "communication" error. The actual error message will vary depending on the database.
If unchecking Maintain Connections resolves this MySQL Communication Link Failure issue for you, then you are better off re-checking it AND adding a validation query. ColdFusion 8.0 introduced a field in the dsn definition for Validation Query. It works this way: When a database connection is first created AND every subsequent time that connection is checked back out from the pool, the validation query will run BEFORE any queries for the page request. If the validation query fails, your page request will never see the error because ColdFusion will throw away that db connection and get another connection from the db connection pool. It will then run the validation query for that connection too. If that one errors, ColdFusion will continue closing the bad connections and checking out other connections until there are no connections left in the connection pool. If it actually got that far (meaning every connection in the pool turned out to be bad) then ColdFusion will then create a NEW db connection and use that one, and it will run the validation query on that too. All of this happens before your request runs to guarantee that your request gets a *good* db connection from the start.
A good validation query is something is that is highly efficient so that db isn't really taxed by having to run it. For MySQL you could use: Select 1 That's it. Enter that into the validation query field for the datasource and keep Maintain Connections checked to improve efficiency with connection pooling.
|
