CF2023 java.sqI.SQLException: Timed out trying to establish connection

[SajanM]

We had a new linux vm built (RHEL 9) and installed CF2023. We migrated the settings (.car file) from the existing CF2021 and trying to get it up and running. So far we are good with admin console and app folder migrations. But we could get the datasource connection working.

The issue we are seeing is when trying to verify an oracle datasource. (using macromedia.jd bc.oracle.OracleDnver)

        Connection verification failed for datasource:ptcltst
        java.sqI.SQLException: Timed out trying to establish connection

We have checked network connectivity between the cf 2023 server and the orace database on port 1521 and it successfull but it fails in the admin console with above error. There is nothing being recd at the target oracle db.

When we try the same connection from CF2021 server admin console, we are able to connect and oracle is able to recieve the connection info
20-NOV-2024 15:00:22 * (CONNECT_DATA=(SERVICE_NAME=tstdb)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=coldfusion))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.xxx.xx)(PORT=49816)) * establish *

Any help is greatly appreciated.

Thanks

Sajan

[BKBK]

What happens when you use for CF 2023 the same user, service name, host IP (10.10.xxx.xx) and port (49816) that you used for CF 2021?

[SajanM]

I get the error:

 Connection verification failed for datasource:ptcltst
        java.sqI.SQLException: Timed out trying to establish connection

[BKBK]

What is the Oracle version? I ask because something might have changed in the implementation of Oracle between CF 2021 and CF 2023.

For example, what about doing the following test. Download the Oracle JDBC driver (that is, the Jar file) that corresponds to your Oracle version. Place the file in ColdFusion 2023's lib directory. Then restart ColdFusion 2023.

[BKBK]

An addition to my last post:

• Download location for the Oracle JDBC driver corresponding to your Oracle version;
• After you've implemented the Jar file and restarted ColdFusion, create the datasource using "Other" as driver type, not "Oracle". This means there will be no need to enter the Service Name.
• Driver class: oracle.jdbc.OracleDriver
• Driver name(optional): ojdbc.jar
• Use one of the formats for JDBC URL Test the following examples to see which one works:
  JDBC URL: jdbc:oracle:thin:@myOracleDBhost:1521/myOracleSID
  
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=__jdbc__)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=tstdb))
  
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=__jdbc__)(PORT=49816)) (CONNECT_DATA=(SERVICE_NAME=tstdb))
  
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=10.10.xxx.xx)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=tstdb))
  
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=10.10.xxx.xx)(PORT=49816)) (CONNECT_DATA=(SERVICE_NAME=tstdb))
  
  
  Note: I assume the host-name and service name of the database to be __jdbc__ and tstdb..
            You should test by interchanging between the default port 1521 and 49816.
            You should fill in the placeholders in the IP 10.10.xxx.xx

[Charlie Arehart]

Before doing all that, I hope you both (and all readers) will see the seemingly far simpler solution I offered as another reply yesterday. In this threaded interface, it's appearing (currently) at the bottom of this page, not within this thread of responses to bkbk's first reply. 

[SajanM]

Version details

    Oracle is ver 19

    Coldfusion 2023 Update 11

I will download the oracle jdbc driver and see how that works per you notes.

Thanks

[Charlie Arehart]

Sajan, you don't clarify if you'd applied any updates after installing cf2023, but I'll assume you did--and specifically the latest, update 11 (from last month).

And in the update technote for that is a know issues section (pointed out also at the top) warning of this problem and offering the solution (with more details in the first bullet above the one regarding oracle data sources).

Let us know how that goes for you. 

[BKBK]

@SajanM , I am assuming that you will, first and foremost, aim to use your usual datasource configuration method. So I take it for granted that you will first follow Charlie's suggestion.

Try the other suggestions only if it doesn't help.

[SajanM]

CF2023 is at version 11 (updated via Admin console)

Oracle version is 19

I will check the update technote.

Thanks

Sajan

[Charlie Arehart]

Yep, so you should find that this is resolved by simply a) stopping cf, b) deleting the cfusion/bin/felix-cache, then c) starting cf and testing of your datasource now works. We look forward to confirmation of success or failure.

And if it works, the big lesson (for all) to take away is the importance of reading the update technotes. Yes, it's like flossing. 🙂 I'll add that I also do a blog post on each update when they come out, where I highlight things that folks should pay attention to. More at carehart.org/blog (including a simple signup form to get notified of new posts). 

[reubenbrown13]

So, what do you recommend if running a fresh install of CF 2023 in commandbox and this error suddenly comes up when trying to connect to mysql or ms sql (only 2 I tried so far).   I followed your instructions to no avail.  I even removed the server folder completely to force a fresh install and am still getting the issue.  I have confirmed I can connect to the local database that is on the same system.  It was working earlier today even, it is very strange.

[Charlie Arehart]

Reuben, the "instructions" were related to a known issue with oracle, not mysql or ms sql. As for your getting "the error" with those, I'll assume you mean the title of this thread, "Timed out trying to establish connection".

Note that error can happen for so many reasons having nothing to do with CF. Normally it's simply a networking problem between your cf machine and where the dbs are. Can you clarify for us? I see that you say, "I have confirmed I can connect to the local database that is on the same system." It's not clear what you may mean. Can you elaborate? 

And it's interesting that you say it "was working" previously today, then stopped. Does it go on and off, since then as well? 

To prove whether this is about cf or not, please try connecting to the db server from something OTHER than cf--but running on the SAME machine AS CF. You can use db-specific tools like mysql workbench or sql server studio, or the cli tools available for those dbs. And you should use the exact server info and port defined in your cf admin.

Again, the key here is not to test if you can connect to the db using such tools on another machine, like your local laptop if cf and the db are on other machines. Test from the cf machine. 

You may find that when cf works, these tools do as well--and that when cf fails, these do as well. That will prove it's not about cf.

Let's hear what you find with this important diagnostic step before elaborating/guessing any further. 

[reubenbrown13]

sorry I didn't provide enough info.  Since this is a local dev env, CF and mysql are on the same server.  I can connect to mysql using the same credentials via workbench, cli, AND lucee 6.  I checked the CF logs, but there was not much there that was helpful for me to see where the timeout was coming from.  The issue is not intermittant for this commandbox instance of CF 2023.  I am going to try to test connections for other dev sites I have setup with other commandbox instances of 2023 to see if they are working still.  I will also try CF 2021 for this site to see if the connections work that way too.

[reubenbrown13]

installed cf 2021 in commandbox for the same site - the database connection works.  There must be another cache that cf2023 is using that I need to clear.

[Charlie Arehart]

I can't fathom one. Given your previous reply, and your seeming willingness to experiment, I would be very interested to hear if you were to install cf2023 (separate from Commandbox, I mean). You can do that free, as you may know. And there's either an installer or a zip. Both are covered in the cf docs if you're unfamiliar. It would take just minutes, I cover that in a talk whose recording and/or pdf are at carehart.org/presentations. 

This way you could see if somehow the problem is unique to the Commandbox variant. And this is not to fault it: there could simply be some configuration difference that you may not be readily seeing.

There's even a way to easily compare the cf admin settings between them, whether with the Commandbox cfconfig module's diff feature, or another approach I outline in another talk I did last year on migrating admin settings. 

Looking forward to whatever else you may learn and share. 

[mike_5992]

This is happening in my local instance for CF2023 hitting my Azure SQL instance. It WAS working now, without changing anything, it's not. I tried the stop, delete, start and no success.

[mike_5992]

Scratch that. I had to hit the 8501 instance which is under the main install. Seem to be working now.  Thanks Charlie.

[reubenbrown13]

So a "funny" thing happened trying to install CF from zip and installer... neither would run on my ubuntu 22.04 based distro.  The zip version would error out saying that it could not find ../../jre/bin/java (which did exist), and the installer version would not run and the only clue I could get was from coldfusion-error.log:
INFO: The Apache Tomcat Native library which allows using OpenSSL was not found on the java.library.path: [/media/data/opt/ColdFusion2023/cfusion/bin/../lib:/media/data/opt/ColdFusion2023/cfusion/bin/../lib/_linux64:/media/data/opt/ColdFusion2023/cfusion/bin/../lib::/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib]

So best guess is there is either something about my system that is causing me issues.  For the record "/media/data" is just a partition on my drive and I wanted the installer to work there since there is more space than on the root.

[reubenbrown13]

last update - so I cleared out EVERY felix-cache folder I could find on my system, whether it was lucee or acf.  I then removed the commandbox install for the specific adobe 2023 server instance that was setup for the site I was testing.  I triggered the commandbox start command for adobe@2023 as the engine, and the server started right up.  I went to the datasources to setup the DSN again, and it was already there and it did verify w/o error.  There is definately something shared somewhere, but I am not sure where it is at the moment.  Best guess is a neo-datasources.xml somewhere that got corrupted, but I have no proof of this.

[Charlie Arehart]

Glad you're sorted out.

As for the "Apache Tomcat Native library" message, note that it's a warning rather than an error. It happens on all cf deployments, whether the zip or install. And FWIW it's been there for several cf versions. You can just ignore it.

As for your larger issue, it seems it would be really hard to sort out what was amiss, given how you removed things.

But since you'd said the issue came and went previously, it may well return. If it does, again consider the db tool testing I'd proposed yesterday. 

[reubenbrown13]

I could access the DB using mysql workbench, so that was not the issue.

[Charlie Arehart]

The key question was whether you could do that at the same time cf could not--and presuming workbench was on the same machine as cf, and using the exact same connection info (server and port) as cf.

But sure, if yes to all those, then we can let it go until/if the problem recurs. 🙂 

[reubenbrown13]

sorry - yes, I was able to connect to the database using workbench while CF could not using the same credentials and connection values as CF, and having workbench on the same machine as CF, so I was able to rule out an issue w/ the database being inaccessible.

[BKBK]

Hi @SajanM ,

You asked the question originally. So has your problem been solved?