portal entry

select a category, or use search below
(searches all categories and all time range)

Coldfusion 2016 JDBC Connection To Oracle

| View in Portal
January 16, 2019 05:49:53 PM GMT
<p>Connection to oracle database times out until i remote into the server running coldfusion.</p>
<p>The post <a rel="nofollow" href="https://coldfusion.adobe.com/2019/01/coldfusion-2016-jdbc-connection-oracle/">Coldfusion 2016 JDBC Connection To Oracle</a> appeared first on <a rel="nofollow" href="https://coldfusion.adobe.com">ColdFusion</a>.</p>
Labels: Data Source, JDBC Connection, Question, 2016, data source, jdbc connection, Oracle, question


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.)
Comment by Charlie Arehart
1548 | January 16, 2019 11:53:23 PM GMT
[I edited this comment to remove content, because this website eats up newlines when editing a comment]
Comment by Legorol San
1550 | January 17, 2019 12:54:40 AM GMT
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.
Comment by Legorol San
1549 | January 17, 2019 12:58:08 AM GMT
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.
Comment by Charlie Arehart
1551 | January 17, 2019 02:27:38 AM GMT
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.
Comment by Legorol San
1553 | January 17, 2019 11:41:23 AM GMT
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?
Comment by Charlie Arehart
1554 | January 17, 2019 01:36:24 PM GMT
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.
Comment by Legorol San
1555 | January 17, 2019 03:48:44 PM GMT