Coldfusion 9 losing connection to MySQL 5 database server a couple of weeks after the server is started

Question

We get the following Coldfusion error message after our server have been running for a couple of weeks:

Error Executing Database Query.Could not create connection to database server. Attempted reconnect 3 times

We run Coldfusion Enterprise 9 on a one year old XServer with Snow Leopard and MySQL 5 The server has about ten DSN set up in the Coldfusion Administrator All local, with default advanced settings, and host set to "localhost" The server is not under heavy load.

The strange thing is that after a restart of the server, everything works fine. Then after a week or so, some databases will stop working, in the sense that Coldfusion cannot create a connection to them. If I then go to the Coldfusion Administrator and click "Verify all datasources", I will get that only 2 or 3 got verified, the other ones failed, and it is always the same datasources that can't be verified when the server starts to behave like this if I try to verify again, BUT NOT neccessary the same datasources that couldn't be verified the last time the server behaved like this.

I know about the setting "max_connections" and we have included a line for that setting in the MySQL config file and set it to 2000, and when we read it by a query it says "2000", so that can't be the problem.

Anyone?

Answer 1

We entered a validation query of "SELECT 1" for all our DSNs in the CF Admin, and the server has been running now for over a month without the issue.

So problem solved in a way, but it would be nice to now why CF is loosing its connection.

Thanks for your help!

Answer 2

I would suggest checking the JDBC drivers on the server and making sure that they are suitably up to date. This sounds like a problem I used to have in the early 2000 when using JDBC rather than odbc and when we moved to CFMX (version 6 - the first java version of Adobe ColdFusion Server)

You might also be suffering from communication link failures. Depending on your traffic it might be worth looking at using a verification query.

http://goo.gl/E1mRu has loads of information that might be useful and helpful.

You should also check the updates and hotfixes for ColdFusion 9.

Also check the server jre that you have installed on your Apple Mac. It may need replacing with an updated version not supplied by apple.