MySQL 5 and ColdFusion 8 Connection Issues

I have been getting many errors from the blog so you may have been getting errors when clicking links when DB requests are made.

I have talked with Host My Site (my hosting provider) and apparently ColdFusion 8 and MySQL 5 do not always play nice together. The connection gets randomly dropped between ColdFusion and MySQL 5. Host My Site development teams are looking into this issue, but no ETA was given on when this will be resolved.

So what is the solution for now? If you are a HMS customer and you are having this issue here is how I resolved it. Call Host My Site and have them login to the ColdFusion 8 Admin and open your DSN connection info up. There is an option called "Maintain connections across client requests". Make sure this is unchecked.

What is this setting: "ColdFusion establishes a connection to a data source for every operation that requires one. Select this option to improve performance by caching the data source connection."

What I think was happening from the error messages I was getting, was that the connection is getting dropped, I assume from the MySQL side. Because ColdFusion had this connection cached, it does not know the connection has dropped, tried to run a query, and you get this error.

"No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error"

This is only a workaround!!
Doing the above will give you a performance hit, but I choose performance hit over errors for the moment. As soon as the original issue is resolved, you should turn this setting back on to take advantage of the performance you get.