Problem with getConnection() if server doesn't respond to validationSQL

[Tommy-LSA]

Hi,

I try to use your package (I found the link on oracledb git) because it seems that I have an issue with idle connections to oracle. I use your modified pool.getConnection for this, but it doesn't fixes my problem. The Problem is, that the pool connections seems to look healthy but sending a SELECT 1 FROM DUAL returns simply silence. I don't know where the issue is (client or server). Releasing the connection and force the pool to rebuild the connection seems to fix that.
So my question is, is there somewhere in your library an option to define a timeout on outstanding server response? It seems that your getConnections wait endless for return of the SELECT 1 FROM DUAL and doesn't release this connection, after a while with no response, as dead and try a new one. So my code is waiting endless to get a connection. I hope I wrote understandable.

The code I use to create the pool with parameters, allmost as suggested in your readme

 initDatabase:function(dbconf){
        return new Promise(function (resolve, reject) {
            // console.log("Node-oracledb version:", oracledb.version); // numeric version format is useful for comparisons
            // console.log("Node-oracledb version suffix:", oracledb.versionSuffix); // e.g. "-beta.1", or empty for production releases
            utils.logger("Node-oracledb version: " + oracledb.versionString); // version (including the suffix)
            //console.log("Oracle Client library version:", oracledb.oracleClientVersion); // numeric version format
            utils.logger("Oracle Client library version: " + oracledb.oracleClientVersionString);
            utils.logger("Waiting for the pool to be created ...");

            oracledb.createPool({
                retryCount: 5, //The max amount of retries to get a connection from the pool in case of any error (default to 10 if not provided)
                retryInterval: 500, //The interval in millies between get connection retry attempts (defaults to 250 millies if not provided)
                runValidationSQL: true, //True to ensure the connection returned is valid by running a test validation SQL (defaults to true)
                usePingValidation: false, //If runValidationSQL, this flag will define if validation should first attempt to use connection.ping instead of running a SQL
                validationSQL: 'SELECT 1 FROM DUAL', //The test SQL to invoke before returning a connection to validate the connection is open (defaults to 'SELECT 1 FROM DUAL')
                user: dbconf.user,
                password: dbconf.password,
                connectString: dbconf.connectString,
                poolMax: 20,
                poolMin: 0,
                poolIncrement: 1,
                poolAlias: "mainpool",
                poolTimeout: 0
              }, function (error, pool) {
                  if (error) {
                    _oraclestate = error.message;
                    reject(error);
                  } else {
                    _pool = pool;
                    _pool.getConnection( function onConnection(err, connection) {
                      if (err) {
                        _orastate = "connection error";
                        _oraclestate = err.message;
                        utils.logger("Oracle Connection error")
                        releaseconnection(connection)
                        reject(err);
                      } else {
                        _orastate = "connected";
                        _oracleServerVersion = connection.oracleServerVersionString;
                        utils.logger("Oracle Database version: " + connection.oracleServerVersionString);
                        utils.logger("Oracle Database Pool created: " + pool.poolAlias);
                        releaseconnection(connection)
                        resolve(true);
                      }
                    });
                }
            });
        })
    }

[cjbj]

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.

[cjbj]

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

[sagiegurari]

@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.

[Tommy-LSA]

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.

[cjbj]

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?

[Tommy-LSA]

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.

[cjbj]

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?

[Tommy-LSA]

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.

[cjbj]

@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.