July 30, 2019
Guide to configure JNDI datasource in ColdFusion
Comments
(14)
July 30, 2019
Guide to configure JNDI datasource in ColdFusion
I am working with ColdFusion Support team.
Staff 11 posts
Followers: 11 people
(14)

(Originally written in Jul 2019. Updated Jan 2021.)

Here’s a guide to show you how to configure JNDI datasource in ColdFusion (versions 10 and above).

1. Get the JDBC Driver

The JDBC Driver for your particular database must be placed in <coldfusion>/cfusion/lib folder.

2. context.xml configuration

Find the context.xml file in the <coldfusion>/cfusion/runtime/conf folder. Save a copy, then edit the file, adding a resource block before the closing tag </Context>, defining the database connection detail:

<Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource"
maxActive="50" maxIdle="30" maxWait="10000"
username="username" password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test"/>

For more information on the attributes please refer to https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html. For setting isolation level use the defaultTransactionIsolation attribute.

3. web.xml configuration

Find the web.xml file in <coldfusion>/cfusion/wwwroot/WEB-INF. Save a copy, then edit the file, adding the following just before the closing tag, </web-app>:

<resource-ref>
  <description>MySQL Datasource example</description>
  <res-ref-name>jdbc/test</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

4. Restart ColdFusion

After saving the configuration files restart the ColdFusion server.

If you proceed to the next step without restarting CF, you would get an error in the Administrator indicating, “Datasource java:comp/env/jdbc/test could not be found”.

If ColdFusion fails to start or you can’t access the Administrator, carefully review the changes you made, and if necessary revert the files to the backup you created.

5. Add ColdFusion DataSource through Administrator

Log into ColdFusion Administrator and navigate to Data & Services > Data Sources and add a new Data Source by selecting J2EE Data Source(JNDI) in the driver dropdown. For more details on this refer https://helpx.adobe.com/in/coldfusion/configuring-administering/data-source-management-for-coldfusion.html#ConnectingtoJNDIdatasources

The following content is copied from a related blog post here by Krishna. Blog link: https://coldfusion.adobe.com/2015/07/configuring-jndi-data-sources-in-coldfusion/

One common mistake generally done is: when the data source is configured from the ColdFusion administrator by providing irrelevant username/password. You may tend to provide Database’s username/password. This is NOT Database’s username/password. It is the application server’s username and password on which data the source is configured. It should be left blank when the credentials are not required by the server.

Prepending java:comp/env/ is required for the actual JNDI name that is registered in XML while registering the name in administrator.

It is not mandatory that you register the JNDI data source in ColdFusion administrator.

Again, this steps above can be followed in CF versions 10 and above.

Hope you found this useful.

14 Comments
2022-03-10 05:29:01
2022-03-10 05:29:01

Priyank, Mohit, or others, I want to follow up on comments you shared here back in 2019/2020. You’d both mentioned that usinf jndi datasources could help with hangup of requests due to jdbc connection pooling. That was so encouraging, and I’d asked for more info then, but neither of you responded.

I’ve since found in some testing that even in using jndi datasources, requests can still hang and stack tracing them finds the same checkin/checkout/fetch connection methods hanging up–and in the very same coldfusion jdbc pool classes (within the package coldfusion.server.j2ee.sql.pool) as appeared when using the cf dsns (which had been renamed in the cf admin, to make sure they were not used).

Can either of you please share more on anything else needed, if just using jndi datasources alone doesn’t do the trick? Mohit had mentioned hikari dbcp, but as I researched Jr, I didn’t see how traditional cfquery calls would leverage it. It seemed one would have to write Java code to run queries USING the hikari classes and methods. If there a way to have it enabled solely via configuration vs code? Thx

Like
2020-07-03 22:20:47
2020-07-03 22:20:47

I tried that but it did not work. Then I saw Charlies’ comment, went to his page where he talked about using configuration manager and enabling TCP/IP for your instance of SQL Server. Thank you, Charlie. I will go to your page and comment.

Like
2019-07-30 15:53:33
2019-07-30 15:53:33

Interesting, Priyank. Can you offer a sentence or two about what scenarios may lead one to want to do this? Is there some benefit to using a JNDI-defined datasource, where a CF-provided one (or the “other JDBC” option) won’t work for folks? I just ask because I’d hate to see some folks think this is something they SHOULD do, when the existing options may suffice.

Perhaps you had a user who had a real need for this. I’m just saying it would be helpful to hear the need, as well as this solution to it. 🙂 Thanks.

Like
(11)
>
Charlie Arehart
's comment
2019-07-30 16:29:24
2019-07-30 16:29:24
>
Charlie Arehart
's comment

Hi Charlie,

We have users who still would like to use JNDI data source who are either migrating from WebSphere environment to Standalone installation of CF. We offered them to use the JDBC option however they want to stick to JNDI data source.

I don’t see any benefit over “Other JDBC”. Maybe the way users have written their application calling the JNDI. I published this blog because the old blog was deleted during the migration.

Like
>
Priyank Shrivastava
's comment
2019-07-31 03:56:08
2019-07-31 03:56:08
>
Priyank Shrivastava
's comment

Great. That explains it reasonably. Thanks.

Like
>
Charlie Arehart
's comment
2019-11-21 16:52:31
2019-11-21 16:52:31
>
Charlie Arehart
's comment

Hi Charlie,

There is a performance benefits on switching  from CF datasource to jndi datasource. CF implementation to get a connection from pool and returning a connection is synchronized i.e. if one thread either taking the connection from connection pool or returning the connection then all the other thread have to wait and if for any reason establising the new connection is slow then with will slow down all the threads. Using JNDI we can switch to different datasource pool which have non blocking implementation.

Thanks

 

 

 

 

Like
>
Charlie Arehart
's comment
2019-11-21 17:00:39
2019-11-21 17:00:39
>
Charlie Arehart
's comment

Hi Charlie,

There is performance gain in switching from CF datasource implemenation. CF datasource implementation to get connection from connection pool and return connection to connection pool is blocking i.e. if one thread is obtaining the connection or return the connection then all other threads have to wait. This implenation slowdown the complete application if the establishing new connection is slow. Using JNDI, we can switch to datasource implementation with are non blocking.

Thanks,

 

Like
>
mohit1984
's comment
2019-11-21 18:47:41
2019-11-21 18:47:41
>
mohit1984
's comment

Thanks, and that’s very interesting to hear, Mohit. I think it deserves more discussion, for everyone’s sake.

First, are you saying that in cases where you saw that problem, it was not resolved by turning off the “maintain connections” option in the CF datasource configuration screen? (I do realize that it doesn’t always have the impact people expect. I’m simply asking if you had tried it, in trying to solve the problem you refer to.)

Second, had you explored any other possible solutions to that, with Adobe? I just find it odd that the ONLY way to solve that problem would be to switch to a JNDI datasource. (I’m not aware that this was marketed as a feature ever, by Adobe at least.)

Finally, do you have evidence of confirmation of that? Either from someone from Adobe confirming it, or from running something that did confirm it (such as viewing stack traces/profiles or requests running, showing they never used any connection pooling).

If you are from Adobe (and we just can’t tell from your handle here), do let us know. Again, I do find this VERY interesting, and yes I HAVE had situations where people wanted to consider removing the impact of CF’s use of db connection pooling.

Like
>
mohit1984
's comment
2019-11-21 18:54:22
2019-11-21 18:54:22
>
mohit1984
's comment

Also, Mohit, there are two comments here that are very close to each other (but different). I suppose the problem was that you didn’t realize it was being moderated (all comments are moderated), so you re-wrote it, though slightly differently.

Note that you could pick one and edit it as you please, and then you could edit the other to say “removed as a duplicate”. No worries if you don’t want to bother. Just trying to help.

Like
>
Charlie Arehart
's comment
2019-11-23 21:16:35
2019-11-23 21:16:35
>
Charlie Arehart
's comment

Hi Charlie,

Please find below my response to your queries

  1. If we turn off the maintain connection then the problem become worse if the database connection establishment is slow due to network issues.
  2. I haven’t connected adobe about this. I am not aware of the process.
  3. Yes, I do have thread dump which prove that JDBC pool checkin and checkout methods are blocking in nature.
    “ajp-nio-8016-exec-88” prio=5 tid=217 BLOCKED
    at coldfusion.server.j2ee.pool.ObjectPool.checkIn(ObjectPool.java:263)
    at coldfusion.server.j2ee.sql.pool.JDBCPool.returnConnection(JDBCPool.java:892)
    at coldfusion.server.j2ee.sql.pool.JDBCPool.connectionClosed(JDBCPool.java:333)

My finding is about the way CF database pool work. CF database pool is using synchronized block which will always degrade the performance of the application if multiple request comes to the server at same time and for any reason connection creation is slow then the problem become worse.

There are multiple instances in CF code which have been implemented code using blocking calls. Below are couple if references

  1.  https://mohitratra.blogspot.com/2017/11/coldfusion-use-cfthrow-with-percautions.html
  2. parseDateTime(date) has blocking nature with can impact system performance but on the other hand parseDateTime(date, format) is doesn’t have blocking nature.
Like
>
mohit1984
's comment
2019-11-24 13:45:14
2019-11-24 13:45:14
>
mohit1984
's comment

Mohit, you misread my reply, but I see now how you could. 🙂 I’ll re-frame my question, because I’m indeed intrigued about what you are sharing.

First, I wasn’t asking for evidence that normal cf dsn connection pooling could be blocked/is synchronized. (I am well aware of that. And FWIW, it’s usually that the checkin’s/checkout’s are waiting for one thread that is doing a fetchConnection operation on the pool.)

Instead, I was asking if you had evidence confirming that your use of a jndi dsn would NOT ever block that way. 🙂

But I see now said specifically, “Using JNDI we can switch to different datasource pool which have non blocking implementation. Ah, so can you clarify more on how you did that? That sounds very interesting.

And I wonder (Priyank or anyone else) if we might also be able to configure NON-jndi dsn’s to be non-blocking this way.

(That said, the hangup in pool processing is usually the RESULT of some other problem that LEADS to the blocking, like how you say “if for any reason connection creation is slow”. So this change to a non-blocking connection may merely MASK a bigger problem, in some situations. But I hear you, in saying it’s only helped in yours.)

If jndi dsn’s (alone, perhaps) can add this benefit of being able to be configured as non-blocking (without any known negative impact), it could well be an important new weapon in addressing problems of requests hanging in connection pool processing, and would indeed deserve better documenting of that fact. .

Like
>
Charlie Arehart
's comment
2019-12-06 02:49:00
2019-12-06 02:49:00
>
Charlie Arehart
's comment

Mohit?

Like
>
Charlie Arehart
's comment
2020-04-08 22:21:50
2020-04-08 22:21:50
>
Charlie Arehart
's comment

Charlie, I was not able to respond you early on this as I was not working on performance issue in my project and didn’t have any stats. Currently, I have started working on proof of concept to jndi datasource. And after implementing the solution in our dev with low system configration, we are observing the high throughput and less response time.

We are using  Hikari for the connection pool implementation.

 

 

 

Like
>
mohit1984
's comment
2020-04-10 03:04:38
2020-04-10 03:04:38
>
mohit1984
's comment

That’s great, Mohit. Thanks. Can you share more on how you configured things.. It could benefit many. You could even do a blog post, or I’d be happy to help you do one. This sound like a compelling possibility.

Like
Add Comment