Node-oracledb: pool.close() promise is never settled

[janihur]

I'm trying to close a database connection when a Node.js (Express) server exits but in process.on('SIGINT, () => {}) dbpool.close() promise is never settled. The minimal but fully executable code that recreates the issue is available in gist: https://gist.github.com/janihur/f84295172c24f09d2830038722158f1c

An output of example run:

$ node oracle-1.js 
info: Starting the server.
info: (oracledb.poolPingInterval 60)(oracledb.poolTimeout 60)
info: Creating a connection pool
info: (pool.connectionsInUse 0)(pool.connectionsOpen 0)(pool.poolPingInterval 60)(pool.poolTimeout 60)
info: Executing a test query
info: Test query results: 1
info: (pool.connectionsInUse 1)(pool.connectionsOpen 1)(pool.poolPingInterval 60)(pool.poolTimeout 60)
info: HTTP server started
info: Database connection closed
info: (pool.connectionsInUse 0)(pool.connectionsOpen 1)(pool.poolPingInterval 60)(pool.poolTimeout 60)
^Cinfo: Received SIGINT (CTRL-C)
info: HTTP server closed
info: (pool.connectionsInUse 0)(pool.connectionsOpen 1)(pool.poolPingInterval 60)(pool.poolTimeout 60)
Terminated

The process "never" (I waited only 15 minutes though) exits but have to be killed with kill from another command line session.

AFAICS the example code correctly closes the connection it got from the pool so I'm expecting dbpool.close() to settle. This should be in line with the documentation:

A pool should be terminated with the pool.close() call, but only after all connections have been released.

If I don't use dbpool.getConnection() the dbpool.close() fulfills "immediatelly".

Answer the following questions:

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

$ node version.js 
Run at: Wed May 03 2017 21:28:10 GMT+0300 (EEST)
Node.js version: v6.10.0
Node-oracledb version: 11301
Node-oracledb text format: 1.13.1
Oracle Client library version: 1202000100
Oracle Client library text format: 12.2.0.1.0
Oracle Database version: 1201000200
Oracle Database text format: 12.1.0.2.0
$

1. What is your OS and version?

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.2 LTS
Release:    16.04
Codename:   xenial
$

1. What compiler version did you use? For example, with GCC, run gcc --version

$ gcc --version
gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
$

[JessieAMorris]

Yup, I can reproduce this issue as well. Really quite obnoxious.

[cjbj]

Possibly related to good-ol' signal handling 'things' like https://github.com/oracle/node-oracledb/issues/671 and https://github.com/oracle/node-oracledb/issues/128#issuecomment-187502867

[JessieAMorris]

It's possible, but I don't think it is. If you log the pool before calling pool.close() and after (using setTimeout or something similar) you can see that there's 1 connection in the pool that is "open" but 0 that are currently "in use".

[cjbj]

Any update on this?

[cjbj]

Closing - no update.

[JessieAMorris]

Why did you close this? Has the issue been fixed? If not, then we should keep the issue open so we can track it accurately. I'm still seeing this behavior with the latest package.

[VenPot]

Is there an updat

[jbhue]

@cjbj I just experienced the exact problem described in the original post. I then downloaded his gist and ran it on node 9.4.0 and oracle 2.2.3 which resulted in the exact issue my real application is having. You mentioned in your last post you needed an update I would be willing to answer any questions needed for this to be looked at again.

In my real application the pool has no issue closing out if I don't hit a route that queries oracledb via the connection pool. If I start my app hit an oracledb backed endpoint then try and stop the server the pool.close() promise never resolves or rejects.

[cjbj]

@jbhue details will help. Platforms & versions. A minimal reproducible test case. Note that tools like SQL*Plus can also hang on some environments when interrupting them (e.g. running sqlplus on macOS to a DB in a vbox VM).

[jbhue]

@cjbj okay no problem let me give some general details to start. In terms of a minimal reproducible test case please refer to the original Gist given by the original author of this issue. I personally ran that gist in the situation detailed below and was able to reproduce the issue.

Local machine is running the Gist on a macOS 10.13.3 and node 9.4.0 with the compiled version of oracledb version 2.3.0. The local server is communicating with a oracle database living on another host. The oracle version is "Oracle Database 12C Enterprise Edition Release 12.1.0.2.0 - 64bit production".

Just to reiterate, if no connections are used from the pool then the .close() method has no issues. The Gist does a simple query then tries to close the pool and you can see the promise never resolve or reject.

Finally, my application (not the Gist) also connects to MySQL living on another host and using their packages pooling features. This issues does not happen to the MySQL pool.

[jbhue]

@cjbj after writing my first comment 5min ago I decided to run the Gist and my application again and everything worked fine with no issues. I'll keep trying throughout the coming days and see if it starts happening again.

[cjbj]

@jbhue thank you.

Did you pull the latest Instant Client on macOS? It was recut at some stage. (Don't start me on the topic of the non-bumped version number)

[jbhue]

@cjbj (I won't even bring the version number up) No I didn't change anything about the instant client I have installed. I'll keep that factor in mind as I go through things.

[diogolmenezes]

I am with the same problem with the latest instant client version. @cjbj

Looking to oracledb source code, i realized that the problem is here:

When the code call self._close it never return if we called .getConnection once....

https://github.com/oracle/node-oracledb/blob/master/lib/pool.js#L280

self._closeCb = function () { self._close({ forceClose: forceClose }, function (err) { if (!err) { self.status = self._oracledb.POOL_STATUS_CLOSED; self.emit('_after_close', self); } self._closeCb = undefined; if (timeoutCb) clearTimeout(timeoutCb); closeCb(err); }); };

[cjbj]

@diogolmenezes since you have done all the research, you could really help by expanding on your statement. What do you mean by calling getConnection once? Maybe open a new issue?

Can you try with the dev-4.0 branch (although that particular segment is the same)? Also if you are force closing a pool with draining, then on macOS you'll almost certainly want to have a sqlnet.ora with DISABLE_OOB=ON set.

[diogolmenezes]

Hi ! You are the best.

I'm on macOs. I Created the sqlnet.ora file with DISABLE_OOB=ON set and it works.

For future information i created the file at ~lib/network/admin/sqlnet.ora

My instant client is on ~/lib folder.

https://www.oracle.com/technetwork/database/features/instant-client/ic-faq-094177.html#A5028

Thanks!!

[cjbj]

Oracle DB 19.3 will automatically enable/disable OOB as needed, so this sqlnet.ora parameter should become obsolete once everyone's up to 19c.