Towards node-oracledb 3.0

[cjbj]

I've pushed the current set of node-oracledb fixes & enhancements to the master branch. You can see that the version number is 3.0.0-dev. From the CHANGELOG:

• Added support for specifying the number of iterations to executeMany() instead of always requiring an input binds array. This is useful when there are no binds, or only OUT binds.

• Eliminated a memory leak when fetching LOBs and more than one internal fetch occurs.

• Removed the queueRequests property and altered the internal pool connection acquisition logic to work better with Oracle 18c pool changes. Now pool.getConnection() calls are always queued if the pool is fully in use.

• Unused properties in objects such as the execute() result are no longer set. Previously some were set to undefined.

• Change the binary package filename format from '...-node-vXX...' to to '...-node-abiXX...' to reduce Node version and ABI confusion.

• Added binary installer proxy authentication support. Reuse npm config proxy. PR #919 (Cemre Mengu)

There's no ETA for release date set yet; but presumably sometime this year :) We are working on a big new feature (that I want to keep as a surprise). We'll see what else we can squeeze in.

I'll keep this issue updated.

[mrapczynski]

@cjbj Are finally going to get support for AQ? 🙏

I see cx-oracle has it 😒

[anthony-tuininga]

Yes, cx_Oracle does indeed have it and this is definitely on the list of things we wish to implement soon, but unfortunately I don't think it will be ready for the first release of 3. :-(

[cjbj]

@mrapczynski PRs are welcome! Is there anything else on your wishlist too?

[bbhad]

Hi cjbj, looking for some help ,

getting below error when transactions are running around 4-6 tps.

2018-06-27T22:20:35.144Z pid:9185 worker:0 ERROR supervisor worker id 3 (pid 18193) accidental exit with SIGSEGV

RHEL7 oracledb@1.13.1 Node v4.6.2 NPM v2.15.11 strongloop v6.0.3 ora-client-12.1.0.2.0 what would be the correct version of oracledb to use with NodeJs4,unfortunately we are not able to update to node8 now

any help much appreciated, thanks bbhad

[cjbj]

@bbhad please keep to the topic and avoid duplicate (or triplicate) posts. thanks.

[vlindhol]

Hi! Looking good!

Excuse me if this is stating the obvious, or if this is already supported, but perhaps 3.0 would be a good opportunity to swith over to the N-API?

[cjbj]

@vlindhol N-API is something that seems to suit the 4.0 timeframe. I'd prefer to put our current resources into supporting more DB features (e.g. object binding).

I'd be happy to consider a PR, though - will you submit one?

[cjbj]

This week's update: other work is delaying us, but there is slow progress.

[fusiondesign]

Can we please please please have the lob fix in the 2.x branch?

[cjbj]

@fusiondesign this is the fix: https://github.com/oracle/node-oracledb/commit/9b715b7f759cda24d224e19f8290fa5b2a329e52 I assume you think you have a problem? Let us know if it fixes it.

I'm still hoping to get 3.x out in the near future and not need another 2.x release, since that will delay things by yet another a week or so.

[fusiondesign]

@cjbj - Nope turned out to be 2 codepaths that I missed closing the connection on that was resulting in NJS-040 errors. It would be really handy if pool._logStats() included pool.connectionsOut - this was the missing piece that helped solve our issue.

We also took the code from pool._logStats() and made our own function that returned an object instead of logging to console; so that we could monitor our pool's stats from a socket connection.

-- Sorry about the noise. I had been debugging for days before I posted here.

[dmcghan]

@fusiondesign Good catch. We should add pool._connectionsOut.

[cjbj]

@fusiondesign success is great!

pool._connectionsOut is the JS counter that matches lower layer's pool.connectionsInUse, give or take a timing iota while the JS layer calls the session pool to get the real connection. Were you seeing something different?

[dmcghan]

Oh. I read pool.connectionsInUse as the number of connections actually doing work, like executing a statement.

[fusiondesign]

connectionsInUse was not matching connectionsOut - it was drifting over time because database connections were not closed. So having these two numbers match is crucial in debugging database connection leaks.

[fusiondesign]

I took the pool._logStats() function and modified it to show some more crucial pieces for debugging, as seen below:

connectionsOut was sitting at around 45 while connectionsInUse was 0-2. connectionsOut turns out to be a flag to tell you if database connections aren't properly closed after a query. Since we had so many Oracle store procedures, we had to check every code path.

Pool Statistics total up time (seconds): 202434.055 total connection requests: 64973 total requests enqueued: 269 total requests dequeued: 269 total requests failed: 0 total request timeouts: 0 max queue length: 186 sum of time in queue (milliseconds): 8787024 min time in queue (milliseconds): 0 max time in queue (milliseconds): 48131 avg time in queue (milliseconds): 32666 pool connections in use: 5 pool connections open: 13 poolAlias: default queueTimeout (milliseconds): 60000 poolMin: 10 poolMax: 100 poolIncrement: 2 poolTimeout (seconds): 3 poolPingInterval: 60 stmtCacheSize: 30 connectionsOut: 5 process.env.UV_THREADPOOL_SIZE: 140

[cjbj]

@dmcghan is documented as 'the number of connections currently “checked out” using getConnection()'. I will circle back with the OCI dev team to triple check if there are any subtleties or client version difference. (And I've already pinged the OCI doc writer about improving the OCI doc description of the underlying attribute.)

connectionsOut is incremented before the call to the C layer so under a connection request storm I might expect connectionsOut to surge until the C layer can catch up and actually dispense connections, at which time connectionsInUse should have the same value as connectionsOut.

It wouldn't hurt to add connectionsOut to _logStats() with a description like 'Pending and satisfied C layer connection requests', but would it be fair to say the pool queue time and maxlength stats would already show if there is a problem with the design and connection load?

Have you seen the pool size recommendations https://oracle.github.io/node-oracledb/doc/api.html#connpooling ?

Another note: UV_THREADPOOL_SIZE is/was limited in libuv to a max of 128.

[cjbj]

I've pushed some more updates for node-oracledb 3.0 to the master branch. The updated CHANGELOG is here.

[cjbj]

The next node-oracledb 3.0 updates were pushed to the master branch.

The main update was a new drainTime option when closing a pool. This was a submission from @danilohgds's PR #950 that landed in https://github.com/oracle/node-oracledb/commit/499998211dc7c1e235b3956b9fa45bd33d6473ea. The pool.close() doc explains the change. For anyone testing, note the discussion of DISABLE_OOB in the doc.

A small doc update from PR #951 by @cemremengu also landed. All contributions are useful!

One small fix worth noting is https://github.com/oracle/node-oracledb/commit/b41bb3af07102c37e0a27bbbd1ada0b07a512d52 which was a latent bug with the order of closing streamed LOBs. This only became apparent with Node 10.8 onwards.

[cjbj]

Node-oracledb 3 is getting closer. I just pushed the big new feature - SODA - to the master branch. After some final checks, I'll do the release and push out binaries. Yay!

[cjbj]

Node-oracledb 3.0 has been released. See https://blogs.oracle.com/opal/node-oracledb-30-introduces-soda-document-storage for what's new.