Coldfusion 9 MySQL communications link failure

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.

Make sense?