JDBC Connection terminated error

Here's what I'd suggest.

1. Try disabling JDBC pooling entirely. You can do this in the CF Admin by unchecking "Maintain Connections". This will keep you from having any JDBC pooled connections at all, and will almost certainly fix your immediate problem. On the downside - and there's always a downside right? - your overall database interactivity will be slower, perhaps much slower. But this will fix you in production.

2. Play around with the "limit connections" and "timeout" settings. I've had very good results by limiting database connections to a relatively small pool, say 20 or 40 (note that without more detailed metrics I can't tell you whether those numbers will be the right ones for you). This will kill older connections when you exceed this number, and create new ones in their place. Some databases (mostly Oracle in my experience but occasionally SQL Server) will benefit from a smaller pool. Note that the timeout on your local side may cause problems with the corresponding timeout on the other side, and this'll cause your pooled connection to fail. So, you might reduce the timeout and set a lower connection amount. Or you might increase the timeout and still set a lower connection amount, or not even change the timeout at all. Note that if there's no value in the "limit connections" field, CF will keep creating new connections without killing old ones.

Dave Watts, Eidolon LLC

Hi Shannon,

Do the errors simply stop and application just works again or do you perform an action to rectify issue?

Could issue be a network problem - how about run "ping -t" to see if traffic terminates when JDBC fails?

Regards, Carl.

Shannon, you will perhaps have found the many, many instances of people on the web having reported this error, and all kinds of ideas floated about (many from a long time ago). I'm not finding any of the top several that show a good answer (of why it happens, or how to fix it).

I will say that in my daily troubleshooting the past 14 years, I have not seen it, so there would seem something rather unique. 

But I will point out that one made a suggestion that is at least worth considering: did you know that for many years, there has been the option in the CF Admin, in the "advance settings" for a datasource, to specify a "validation query"? Some never use it, some always use it. It's purpose is not well-documented, but bottom line it has helped with some problems. For SQL Server (and many databases), it can be as simple as "select 1".

And yes, it's natural to fear "that's got to add a lot of overhead". No, it does not. It's used when the connection is opened, which is not "every query". And even if it were on "every request" that does some queries, well you're going to open the connection anyway, which will need to talk to the db. Having it do this one trivial query will not add burden. And if it solves your problem, that may be what matters. (With the right tools, you can also confirm that it adds negligble overhead, if someone presses that point.)

One last quick question: are you just finding these errors in the CF logs, or are real users getting the error? I ask because if it only in the logs, note that the errors could be happening only with some unique requests, which may be as much about the clients making the request, or the nature of the SQL generated for their request, etc.

Let us know how it goes.

Perhaps caused by problems with indexes in SQL Server. If so, then it would help if you rebuilt the indexes.