January 16, 2019
Coldfusion 2016 JDBC Connection To Oracle
Comments
(7)
January 16, 2019
Coldfusion 2016 JDBC Connection To Oracle
Newbie 1 posts
Followers: 0 people
(7)

I’m setting up a new coldfusion 2016 standard server on windows server 2016.  I have successfully created a datasource that points to an oracle database using the oracle jdbc driver.  The datasource validates, and a coldfusion application that uses that datasource works most of the time.  The problem arises after many hours or non-use (this is a server that is not in production yet).  Usually in the morning i access the coldfusion application and i get an error that the connection to the database has timed out.  If I remote into the server where coldfusion is installed, the application immediately starts working again, using the datasource without any issues.  When I say I remote into the server, I am doing nothing else.  I simply log into the server.  I am not accessing coldfusion administrator or anything else on the server.  Any ideas as to what might be going on here?

Thanks.

7 Comments
2019-01-17 00:58:08
2019-01-17 00:58:08

We had similar experience with little used websites with infrequent database connections. The first action on the website in the morning that tries to use the database connection often failed with a timeout error.

I am willing to bet that it’s not the act of remoting into the server that resolves the problem, but merely trying again the same action on the website.

We were having the problem with ColdFusion 10. After we investigated it, we discovered that contrary to all the documentation and explanations out there, and despite the correct CF administrator settings for the particular datasource, the CF server was NOT timing out unused database connections as it should. So what would happen is that the CF server held on to database connections overnight, but of course the database server closed them on its side. Come the morning, CF server thought the connection was valid, when in fact it wasn’t.

In the end we worked around the problem by enabling connection validation for the datasource in question, and putting ‘select 1’ as the validation query. (You can make these changes in the datasource’s settings.)

Granted this was with CF 10 and not 2016, but it’s possible that this issue still exists, so give the validation query a try.

Like
(4)
>
Legorol
's comment
2019-01-17 02:27:38
2019-01-17 02:27:38
>
Legorol
's comment

Yep, that could be it. And the validation query feature still exists, and this is one of its main uses. But I will point out also (for both of you) that perhaps the problem could be addressed by finding what the Oracle connection time is (on the Oracle side), and then ensure that the CF Admin’s DSN connection timeout is close to that.

Like
>
Charlie Arehart
's comment
2019-01-17 11:41:23
2019-01-17 11:41:23
>
Charlie Arehart
's comment

Charlie, when we had this issue, our problem was that the connection timeout set for the datasource in the CF admin was simply ignored by the CF server. The timeout set on the db server was not relevant. (For full clarity, we were using MySQL and not Oracle).

One experiment we did was to set the timeout on the db server to a much higher value than the timeout set in CF admin. We then used a 3rd party tool to monitor the connection between CF and the DB. The datasource timeout came and went, and the connection was not closed. However, once the timeout set in the db server was hit, the connection was duly closed by the db server.

Like
>
Legorol
's comment
2019-01-17 13:36:24
2019-01-17 13:36:24
>
Legorol
's comment

So are you saying problem solved (with your workaround)?

As for the cf admin dsn connection timeout, I wouldn’t think it would be being “ignored”. I’d think instead its purpose is different than you’re expecting (even with the monitoring, which of the db’s connections, not cf’s db connection pool, which may be significant here).

But I’ll assume there’s no need to go further into that if your workaround is sufficient, right?

Like
>
Charlie Arehart
's comment
2019-01-17 15:48:44
2019-01-17 15:48:44
>
Charlie Arehart
's comment

Yes having a validation query of ‘select 1’ resolved the issue for us.

You are right that there are subtleties involved (connection pooling etc.), I didn’t go into all the details. I am pretty sure that at the time we investigated this, we were careful about all the nuances. In the end we weren’t seeing the behaviour we were expecting (based on documentation, help texts in CF admin, other sources and what would be “logical”), so we chalked it up to a bug in either CF or the JDBC driver.

Like
2019-01-17 00:54:40
2019-01-17 00:54:40

[I edited this comment to remove content, because this website eats up newlines when editing a comment]

Like
2019-01-16 23:53:23
2019-01-16 23:53:23

It does not make sense, on the surface, that simply remoting into the server should make a difference in how the app works, no.

So first, are you really saying that the URL that you use to access the app OFF and then ON the server (by remoting in) is identical?

If so, let’s make sure both requests really are going to the same server. If you go to the command line and do a ping of the domain name in the server, does that resolve to the same IP address? (If you cannot do a “ping”, because it’s not installed by default in Windows, try the nslookup command, whose output is a bit more confusing if you’ve never seen it before, but it should be available at the Windows command line.)

Like
Add Comment