Loopback oracle connector issues about loopback-connector-oracle HOT 16 CLOSED

Commonly firewalls and DB resource profiles are implicated in connections being closed prematurely.

General advice would be to close the connection when it is not being used, so that resources are freed up for other users. Specific advice depends on business and architectural practices, which only you know.

from loopback-connector-oracle.

@cjbj : Thanks. do you have any sample query written using loopback oracle connector which has connection open and close. We though connector is taking care of the connection. I am pasting my sample query which we are using in our application. we don't open and close connection before query call.

This query has been written in model Js file.
modelName.find(searchParams, function (err, result) {
});

from loopback-connector-oracle.

@jeyaraj87 my domain of expertise ends at the node-oracledb API.

from loopback-connector-oracle.

@cjbj Does the node-oracledb connection pool handle stale connections?

from loopback-connector-oracle.

@cjbj What about https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propdbpoolpinginterval?

from loopback-connector-oracle.

@jeyaraj87 Can you play with https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-3219-oracledbpooltimeout?

from loopback-connector-oracle.

@raymondfeng node-oracledb's connection pool does its best with stale connections. There are some tweaks coming soon in node-oracledb 3: oracle/node-oracledb#960 (comment)

Re-creating connections has a cost, so it would be better to fix the root cause of the network termination. Of course, connections can die at anytime so apps still need to have appropriate error handling if execute() fails due to a connection issue.

PS @raymondfeng production doc is at https://oracle.github.io/node-oracledb/doc/api.html

from loopback-connector-oracle.

@raymondfeng : poolPingInterval is not supported by loopback-connector-oracle": "4.1.0".

poolTimeout is not solving our case either. I have tried few combination of values such as 0, 60,180

from loopback-connector-oracle.

@raymondfeng I am facing the same issue. Here is my dataSources.json

  "oracle": {
    "host": "xx.xxx.xx.xxx",
    "port": 1521,
    "database": "xe",
    "password": "xxxxxxxxx",
    "name": "oracle",
    "tns": "",
    "user": "xxxxxxx",
    "connector": "oracle",
    "minConn":10,
    "maxConn":200,
    "incrConn":10,
    "timeout": 0
  }

@jeyaraj87 Were you able to fix this issue?

from loopback-connector-oracle.

@tinker20 what is the exact issue? Are you seeing an error? What is the underlying node-oracledb version are you using? What Oracle Client version is it linking with (run version.js)?

Also, you should review your maxConn size, since each connection in use will require a thread, and Node.js limits the number of threads to 128. See https://oracle.github.io/node-oracledb/doc/api.html#numberofthreads (Loopback's maxConn is node-oracledb's poolMax).

from loopback-connector-oracle.

@cjbj The underlying node-oracledb version that the connector is using is "oracledb": "2.2.0". What is the ideal datasource configuration that I need to use?

I am using this

  "oracle": {
    "host": "xx.xxx.xx.xxx",
    "port": 1521,
    "database": "xe",
    "password": "xxxxxxxxx",
    "name": "oracle",
    "tns": "",
    "user": "xxxxxxx",
    "connector": "oracle",
    "minConn":0,
    "maxConn":128,
    "incrConn":2,
    "timeout": 0
  }

from loopback-connector-oracle.

@tinker20 what's the Oracle client library version? Try logging oracledb.oracleClientVersion

from loopback-connector-oracle.

@cjbj The oracle client library version that I'm using is 12.2.0. I hope you are seeking for the Oracle instant client version, right?

Node.js version: v8.11.1 (linux x64)
Node-oracledb version: 3.0.1
Oracle Client library version: 1202000100
Oracle Client library version: 12.2.0.1.0

from loopback-connector-oracle.

@cjbj @raymondfeng Any update on the above issue?

from loopback-connector-oracle.

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

from loopback-connector-oracle.

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

from loopback-connector-oracle.