Problem with getConnection() if server doesn't respond to validationSQL about simple-oracledb HOT 9 CLOSED

For Oracle applications, you can set timeouts on connection establishment and message timeouts in the Oracle Net layer, see https://oracle.github.io/node-oracledb/doc/api.html#connectionha

node-oracledb itself has a call timeout but you need to be using 18c Oracle client libraries.

From the sound of it, you have some deeper issue that you need to resolve.

from simple-oracledb.

PS Since you have poolMax set to 20, did you set UV_THREADPOOL_SIZE to >= 20? See https://oracle.github.io/node-oracledb/doc/api.html#numberofthreads

from simple-oracledb.

@Tommy-LSA I agree with @cjbj
I could add a timeout to the get connection and break the invalid connections but that might mask some serious network issue you are experiencing and the outcome would be a performance issue.
if you really wish, i could add it, but i think you must check what is going on.

from simple-oracledb.

I actually use following setup

"oracledbVersion": "3.1.1",
      "oracleClientVersion": "12.2.0.1.0",
      "oracleServerVersion": "12.2.0.1.0",
      "connectionsInUse": 0,
      "connectionsOpen": 20,
      "poolstate": "POOL_STATUS_OPEN",
      "poolIncrement": 1,
      "poolMax": 20,
      "poolMin": 0,
      "poolPingInterval": 60,
      "poolTimeout": 0,
      "queueTimeout": 60000,
      "stmtCacheSize": 30

I allready hat a talk with DBA who said that on serverside no limitatiton or timeout is configured. Firewall timeout are also not set, confirmed by network team. The whole application works like a charm as long enough queries go thru. If there is a longer time with no queries (3hrs or so) the connections are in this strange state where queries are not responded.

An actual workaround for now is to set the poolTimeout to 60 and run an intervalled keepalive query every 5 mins. This pool access let the pool close all open connections which have reached the pool timeout. Our goal is to leave the connections open as long as possible to avoid the overhead while opening connections. That's why I used poolTimeout = 0 which shouldn't be a problem normally with your library. I have no clue why the connections seem to be open but no respond on a query. A timeout for getConnection() on my end won't work because the connection instance, tried by your code, is unknown to me in this moment. So I cannot release it to repair it. I only could react on a timeout by recreating the complete pool.
The only chance to do it is on your end. You could use the responsetimeout optional (if set to 0 it is disabled - default). I would use milliseconds here because normally a SELECT 1 FROM DUAL returns within 50ms normally.

Edit: UV_THREADPOOL_SIZE is set to 25. Query cache works fine, I tested with a few bruteforce attac on the code.

from simple-oracledb.

I can't comment on simple-oracledb, which I don't use. @sagiegurari will have to chime in on its specifics. I will use pure node-oracledb terminology and options.

Are you releasing connections to the pool and then waiting 3 hours? Or have you done a getConnection() and kept the connection 'checked out' from the pool for the 3 hours? We'd recommend releasing the connection to the pool when you are not using the connection.

It still sounds like the network on the machine(s) running node-oracledb needs configuring. What is the operating system?

To give some brute-force suggestions, you could try (ENABLE=broken) in a connect string. And maybe DISABLE_OOB=ON in a sqlnet.ora file.

Can you explain more about what you meant by:

The only chance to do it is on your end. You could use the responsetimeout optional (if set to 0 it is disabled - default).

I don't understand. Or is this intended for @sagiegurari to add to simple-oracledb?

from simple-oracledb.

Yes, all connections are released directly after response is returned. Also if an error occur the a release is executed. I have observed that over a while and see always (with poolTimeout = 60)

"connectionsInUse": 0,
      "connectionsOpen": 1,

and with poolTimeout=0

"connectionsInUse": 0,
      "connectionsOpen": 20,

where the single open connection in first situation is resulting from my actual keepalive intervall query. The code is used while developing under windows and for test and production in a docker swarm under linux. The behavior is the same.

The mentioned sentence was indeed for @sagiegurari . As long getConnection() doesn't return I have no connection which I could release. So, building a timeout around getConnection() makes no sense from my code. I only have access to the pool instance and could rebuilt the pool itselve which is very timeconsuming on min=max=20 connections.

from simple-oracledb.

In the initial post, you said that SELECT 1 FROM DUAL hung. Now you say getConnection() doesn't return.

• Can you confirm what the problem is?
• Can you confirm what of the suggested network configurations you've tried?
• Can you give us (email is fine) a pstack / gstack of the process when there is a hang?

from simple-oracledb.

The getConnection() in simple-oracledb executes a SELECT 1 FROM DUAL as test. That's why getConnection() from simple-oracledb doesn't return.

Anyway, because the poolTimeout set to 30 minutes combined with a "keepalive" query every 5 minutes runs stable since a few days. 30 minutes open time for a connection is enough to reduce reconnects to a minimum and doesn't loose the connection thru to long open time.

So I have removed the whole simple-oracledb from my solution as it doesn't fix anything for me in actual release.

from simple-oracledb.

@Tommy-LSA Let us know if you want to continue with this; it would be good to identify the underlying cause so we can see where improvements can be made to node-oracledb.

from simple-oracledb.