How get notifications when a connection is closed/broken in version 4.0.0 ?

[wallride]

In previous versions the Connection interface exposed .on() method giving ability to handle 'error', 'close' and even '_after_close' events. In latest version and api docs I cannot find a proper way to handle these events. Though still .on() method and all the events work fine whereas they are not mentioned in typescript definition.

So how am I supposed to deal with it from now on?

[dmcghan]

I'm a little confused by your description. Please separate the code, the doc, and the TS definitions. Did something change in any or all of these? What was working but isn't working now? What version was it working in?

[wallride]

@dmcghan, well, the problem I have is a little confusing by itself :) I'll try to separate the main question and all other context.

The main question is: How can I poperly receive notifications when a connection is closed (network fault, server restart, etc)?

Context: I use typescript and definitions from definitelyTyped. They are based on node-oracledb API docs. In earlier versions (TS definitions @types/oracledb": "^1.11.34) the Connection interface exposed method .on(...) and it was doucmented in API docs.

The latest versions of API docs and TS definitions do not mention the .on(...) method at all and I cannot find a proper way to handle connection breaks. And this is the main problem.

However in JS code the Connection class does extend EventEmitter and does support events, particularly _after_close (never been documented, but I use it for my needs). I would be happy if this feature was documented and described in typescript definitions.

[cjbj]

@wallride Since the underlaying C library that handles the connection pool doesn't / can't expose events for normal or abnormal closes of connections to the DB, node-oracledb has never exposed events.

I don't know what you were looking at before. It possibly just showed when connections were taken or checked back into the pool at the JS level, which is a different kettle of fish.

How and why are you approaching your high availability solution? Code for the expected (successful) use of connections, use a connection pool, use appropriate Oracle DB features such as FAN, and Application Continuity, check failures after execute() etc. There are some notes at https://oracle.github.io/node-oracledb/doc/api.html#connectionha

[wallride]

I'm a bit confused. I've rechecked everything and it seems something has mislead me.

So, did I get it clear that it's impossible to get notifications about the fact that connection is closed?

[dmcghan]

@wallride Focusing on your main question:

How can I poperly receive notifications when a connection is closed (network fault, server restart, etc)?

There's no built-in support for this. You could poll dictionary views like v$process and v$session to detect it, but that sounds like a lot of effort.

Could you tell us why this is important to you? What would you do if you knew a connection was closed?

In addition to what @cjbj mentioned, another reason that events weren't implemented was due to their synchronous nature and thus limited use. For example, a getConnection event could have been added, but it couldn't have been passed the connection object in a way that would have allowed it to be used (say to execute PL/SQL to initialize session state) before allowing the propagation of the connection object to the caller of getConnection.

Someone wrote such an async event emitter, but it never landed in core: https://github.com/sindresorhus/emittery

[wallride]

@dmcghan,

Could you tell us why this is important to you? What would you do if you knew a connection was closed?

Sure. I work on internal frameworks handling resources (many kinds of adapters with similar facades) and distributed transactions. In our workflow it is important to have consistent state of transaction's environment (resource, e.g. connection) before the transaction is executed. Also it is important to minimize probability of ORA-03113: end-of-file on communication channel or ORA-03114: not connected to ORACLE errors to separate problems with transactions themselves (as a result of execution) and environmental problems.

So I was happy under a delusion that events are supported. :)

May I hopefully expect such feature (get the connection's state) to be available soon?

[dmcghan]

@wallride You still didn't say what you would do if you knew a connection was closed.

[cjbj]

@wallride

minimize probability of [errors]

This is the key statement. You can minimize the probability in various ways, with various costs. But you still can't get to zero. Network dropouts can occur at any time, so your execute() (and other calls to the DB) still need to have error handling and recovery.

Fundamentally a dead connection can't itself tell you it's dead - until you try to use it.

An argument is that although you can do things like an explicit connection.ping() before execution, that adds a guaranteed extra round-trip for the ping, which affects scalability. The alternative is to assume the connection is valid and simply do the execute. Then do error recovery, which shouldn't be needed in most cases (hopefully!).

Into the mix you can add things like pingInterval, tuning network timeouts and adding network level pings (in sqlnet.ora), and adding FAN which uses an external monitor (ONS) to check connection aliveness to get errors faster. Then you can add Application Continuity to silently reconnect and replay transactions if the underlaying connection is broken. It all depends on the how many point 9's of reliability you are willing to architect for, and the costs of doing so.

PS:

consistent state of transaction's environment

Don't forget to use a sessionCallback.