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

by

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.

There’s no useful info in the mysql_service.log
We have also tried flushing the hosts, as I read about in one forum, to check if the MySQL server has “banned” our user, without result.

Anyone?

Answer

if you type “netstat -an | grep 3306” on the mysql server do you see many many open socket (“ESTABLISHED”), if this is more than the number of sockets mysql is configured to allow open, then something is locking up and holding on to all the database pool handles.

it could be a problem in mysql, but is more likely to be an application problem not releasing handles back to a pool, similar to a memory leak.

it’s rather hard to diagnose the problem without more information, but that’s where I’d start.

Attribution
Source : Link , Question Author : user1503757 , Answer Author : Paul M

Leave a Comment