PeteComcar wrote: I didn't think you were being malicious, I just don't know what packet sniffing is... that's all! Anyway, before reading this post I actually UNCHECKED maintain connections. The reason being I got a different error at 11am yesterday "The server encountered an internal error and was unable to complete your request. Application server is busy. Either there are too many concurrent requests or the server is still starting up" I checked the error log and it was a JRun error, did some Googling and found some suggestions to UNCHECK maintain connections, so I did that, and it fixed both errors - so it was indeed to do with the CF9 settings. So sorry to contradict you BKBK but I'm leaving maintain connections unchecked. |
I can understand. I too have now googled up on the matter. It appears this is indeed a bug in ColdFusion 9.
Not surprising. Seach for MySQL in the ColdFusion Bugbase, and you will find that there have been connection issues between ColdFusion 9 and versions of MySQL newer than 5.0.x right from the beginning.
It is therefore not a coincidence that unchecking 'Maintain Connections' seems to work. It confirms there is a connection issue.
That setting has to do with connection pooling. When you check it, ColdFusion reuses a connection from a pool of connection objects for numerous queries to the database server. This reuse of course means sparing CPU resources, hence efficiency. Unchecking the setting compels ColdFusion to create a new connection for each query. You wouldn't want that, if your application is data-intensive.
I have gathered from the reactions on the web that the driver that ships with ColdFusion 8 appears to work well with the setting "Maintain Connections". That driver is mysql-connector-java-commercial-5.0.5-bin.jar. The newer driver that ships with ColdFusion 9 is mysql-connector-java-commercial-5.1.11-bin.jar. This brings me to my suggestion for a test. It goes without saying, you should do the test on a test server, not in production!
Stop ColdFusion 9. Copy mysql-connector-java-commercial-5.0.5-bin.jar to the lib directory (if you don't already have it there). Remove all other drivers of type mysql-connector-java-commercial-x.x.x-bin.jar from the lib directory, and store them in a back-up location out of the lib directory.
Restart ColdFusion 9. Choose MySQL 4/5 and check the setting to Maintain Connections. Any more communication 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?
AdChoices