ColdFusion 10 - MySQL Communications link failure

[sdsinc_pmascari]

We are migrating some websites onto a cloud infrastructure running Windows 2008 virtual machines.  These websites all run on ColdFusion with MySQL databases.  They currently are running in our CoLo with no problems.  Additionally, they are running on our development network in our offices with no problems.

We are setting up our cloud to match as closely as possible the configuration we currently use which is, essentially, CF10 + IIS on one server and MySQL on a separate machine.  We are 99% finished and most things are running great.  However....

We have run into a couple, as in 2, places where we click a link/button and are greeted with:

Error Executing Database Query.

Communications link failure The last packet successfully received from the server was 0 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.

Scanning the stack-trace I also find:

`Caused by: java.net.SocketException: Connection reset`

The communications link error is ALWAYS: 0ms.

What's most puzzling is the Queries that seem to be causing this are *simple* queries that are used ALL OVER the sites with no problems.  Why they are failing at hese 2 particular places has us at wits end.

Our only clue is, looking at the CF Error description of what scripts are called, we can see the script where the query is failing is getting called twice?  For example, one of the occurences is in our Application file:

    >The error occurred in D:/Our_Web_Sites/oursite/Application.cfm: line 73

    >Called from D:/Our_Web_Sites/oursite/Application.cfm: line 17

    >Called from D:/Our_Web_Sites/oursite/Application.cfm: line 1

    >Called from D:/Our_Web_Sites/oursite/Application.cfm: line 73

    >Called from D:/Our_Web_Sites/oursite/Application.cfm: line 17

    >Called from D:/Our_Web_Sites/oursite/Application.cfm: line 1

We can find nothing in our CF code that would be causing the script to be called twice so our guess is the first call is failing on the Query so CF tries again...only to fail and error.

Googling this issue I've found lots of posts about changing the MySQL timeouts.  None of those worked and I didn't expect them to since what we're dealing with doesn't appear to be a timeout issue.  These pages fail each and every time.

The closest we've come to a solution came from this blog posting:

http://www.talkingtree.com/blog/index.cfm/2011/1/12/Validation-Query-for-MySQL-communications-link-f...

If we UNCHECK the "Maintain connections across client requests. " setting in CFAdmin then the error goes away.  The blog suggests leaving that checked, which is our preference, and using Connection Validation of "SELECT 1;".  Try that...same error.

We've also tried the JDBC AutoConnect=true option.  No effect.

Downloaded latest JDBC Connector and used it instead of standard CF10-MySQL connector.  No effect.

Again, 99% of the site works with the exception of these two links, both of which work just fine in all our other environments.  Any other ideas?

[BKBK]

I have some questions.

1) You talk of 2 calls. However, you show us 5 calls from Application.cfm. What do those line numbers tell us?

2) Shouldn't that be AutoReconnect=true rather than AutoConnect=true? In any case, it seems that the problem here is that ColdFusion attempts to use a stale connection to the database. It is then usually preferable to change wait_timeout instead. The MySQL documentation says, 'Alternatively[to AutoReconnect=true], as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours.'.

3) Shouldn't you already convert to Application.cfc?

[sdsinc_pmascari · Accepted solution]

Found the issue.  We are running our network on Savvis' cloud infrastructure.  The Windows server instances we were using from Savvis had Trend Micro Deep Security Agent installed.  This is an intrusion protection system and it was the problem.  Disabling the service cleared up all communication errors.  I have no clue why it was rejecting some queries that it had just accepted previously.  I am just glad to (finally) put this behind me!

[BKBK]

I am glad to hear you finally solved it. Thanks for sharing the solution with us.