Node-oracledb: Blocking on getConnection

[rafaelsavignonmarinho]

Hi folks,
i'm facing pool.getConnection blocking situation when oracle database is previously offline. Moreover, when server gets back online the pool.getConnection release and fullfilled handle called as expected. Why is it blocking while oracle is down? The same code running on a Linux CentOS machine behaves as expected, returning "ORA-12541: TNS:no listener" as soon as pool.getConnection reject handler get called.

const dbconfig= {
    user          : "system",
    password      : "oracle",
    connectString : "127.0.1.1:1521/orcl12c",
    poolMax       : 10,
    poolMin       : 0,
    poolIncrement : 1,
    poolPingInterval: 5,
    poolTimeout: 5,
    queueTimeout: 0
};

oracledb.getConnection(dbconfig).then((conn) => {
    console.log("Success!");
}).catch((e) => {
    console.log("Error: " +  e.message);
});

Thanks in advance.

Answer the following questions:

1. What is your version of Node.js? Run examples/version.js to find versions.
   node.js v6.9.1

2. What version of node-oracledb are you using?
   1.11.0

3. What is the version of your Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   12.1.0.2.
   Manually installed (instant client).

4. What is the version of Oracle Database?
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

5. What is your OS and version?
   Windows 7 64bit

6. What compiler version did you use? For example, with GCC, run gcc --version
   msvs_version=2015

7. What environment variables did you set? How exactly did you set them?

INSTANTCLIENT_PATH=C:\DEV\Oracle\instantclient 
OCI_INCLUDE_DIR=%%INSTANTCLIENT_PATH%%\sdk\include 
OCI_LIB_DIR=%%INSTANTCLIENT_PATH%%\sdk\lib\msvc\vc11 
OCI_VERSION=12' 
Path=%%INSTANTCLIENT_PATH%%;%%PATH%%;%%INSTANTCLIENT_PATH%%\vc11

1. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
   pool.getConnection()

2. What error(s) you are seeing?
   Expected ORA-12541: TNS:no listener, but nothing is returned.

[dmcghan]

@rafaelsavignonmarinho Do you have a test that demonstrates this is blocking and not just waiting?

[cjbj]

Update to node-oracledb 1.13.1 and/or use Oracle Instant Client 12.2 so you have connection pool pinging. Other than this, you may need to configure your network TCP and Oracle Net timeouts.

[dmcghan]

@rafaelsavignonmarinho Regarding the comment you made (and deleted?)...

Have you seen @cjbj's comments here?

[dmcghan]

@rafaelsavignonmarinho Here's a complete answer...

I created the following test:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function connectionTimeoutTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);
  } catch (err) {
    // Will reject the promise that processEmployees returns
    // but only after finally runs
    throw err; 
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.log('error closing conn', err);
      }
    }
  }
} 

connectionTimeoutTest()
  .then(() => {
    console.log('All good!');
  })
  .catch(err => {
    console.log('Something broke!', err);
  });

When I ran it with the DB up, it succeeded instantly.

Then I shutdown the database and reran it. It took 60 seconds to get:
Error: ORA-12170: TNS:Connect timeout occurred

Then I looked in the doc that @cjbj pointed to and found TCP.CONNECT_TIMEOUT.

I created a sqlnet.ora file with just one line:

TCP.CONNECT_TIMEOUT = 10

Then I set the TNS_ADMIN environment variable to point to the directory where the sqlnet.ora was located and reran the test. The timeout occurred in 10 seconds (as specified).

See slide 29 here for more info on creating the sqlnet.ora. You might find some other goodies in there too!

[ah584d]

Hi,
I worked for many months with node oracledb, and I met a lot of issues.
I’m now at a point that I need deeper understanding on how node-oracledb manages the pool connections.

This is my setup
Centos 7
Node 6.10.3
Driver: oracle-instantclient11.2-basic-12.2.0.1.0-1.x86_64.rpm
Oracledb:2.1.2

The bug observed is :
we do have an application which start to work with oracle-server, and after finishing a query we always do connection.close()

But after many hours, or many days of inactivity [the application is up but in idle, anyone use it], when the application starts again to execute a new query to the DB, in the code, we do pool.getConnection(), and here the application hangs, …..for .... hours!!! And never came back alive

No exception is thrown, and my assumption is that the opened connection has been ‘timeout/been invalid’, and the ping backup system does not know to provide me a ‘fresh’ connection , or the poolTimeout did not closed me the connection when I ask it to do it.

Pool parameters:

poolMax: 30,
poolMin 0,
poolIncrement: 1,
poolTimeout: 5,
queueTimeout: 50,
poolPingInterval : 5,
queueRequests: false,
_enableStats: true

Logs from _logStats() during regular work

• After: pool creation
  ...pool connections in use: 0
  ...pool connections open: 0

• After: connection = pool.getConnection()
  ...pool connections in use: 1
  ...pool connections open: 1

• After: connection.close()
  ...pool connections in use: 0
  ...pool connections open: 1

If I understand well:

/**

• The time (in seconds) after which the pool terminates idle connections (unused in the pool).
• The number of connections does not drop below poolMin.
  */
  poolTimeout = 5s ==> so it means that doing another _poolStats(), after 5 seconds (value defined in poolTimeout) should return
  ...pool connections in use: 0
  ...pool connections open: 0

But it’s not the case. We always have 1 connection opened.

So I thought maybe it was because I also defined

/**

• When a pool getConnection() is called and the connection has been idle in the pool for at least poolPingInterval seconds, an internal "ping" will be performed first to check
• the aliveness of the connection.
• Checks aliveness if the connection has been idle in the pool (not "checked out" to the application by getConnection()) for at least n seconds
  */
  poolPingInterval = 5s

Does it means that making a ping frequently will always save an opened connection in the pool?
If it true, then why when I define poolPingInterval=120s and poolTimeout =5s, so the last opened connection should be closed after 5 s before the ping interval starts to bring me a new connection.
But in reality what I observe is that I always have an opened connection no matter which parameters are defined.

Upgrading driver due to comment :
https://github.com/oracle/node-oracledb/issues/773#issuecomment-338042302

I recently upgraded to oracledb 3.0.1 and driver 12.1 hopping that the ping system will really work, according to what I found here
https://blogs.oracle.com/opal/node-oracledb-112:-working-with-lobs-as-string-and-buffer-connection-pinging

“_Unless node-oracledb is linked with Oracle 12.2 client libraries, any node-oracledb pooled getConnection() call could return one of the unusable connections. For users of Oracle 11.2 or 12.1 client libraries, a new poolPingIntervalsetting will do a 'ping' to the database to check the connection is OK before returning that connection to the application. If it isn't OK, then another connection is returned._”

“_The ping feature is a no-op when node-oracledb is linked with Oracle 12.2 client libraries (independent of the database version at the other end of the connection), since those libraries have an always-enabled, lightweight connection check that removes the need for node-oracledb to do its own ping. This adds even more certainty that a connection will be valid at time of first use after a getConnection() call._ ”

So to conclude:

• how can I be sure that the lightweight connection is the one that I see in the log here
  After: connection.close()
  ...pool connections in use: 0
  ...pool connections open: 1

• If i use driver 12.2 do i still need to use ping in the code?

• Is there a way to close everything in the pool without destroying the pool?

  • Is it logical that in version 11.2 a getConnection() can be stuck forever?

Thanks in advance

Avraham Hamu

[cjbj]

@ah584d this issue was closed last year. Please open a new issue. Also see https://github.com/oracle/node-oracledb/issues/1012