Loopback oracle connector issues

[jeyaraj87]

any connection from our loopback application to oracle db becomes stale after 1 hour if the application is in idle state. I have to manually restart the application to get the connection back. Can you provide some insights on this issue.

[cjbj]

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.

[jeyaraj87]

@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) { });

[cjbj]

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

[raymondfeng]

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

[raymondfeng]

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

[raymondfeng]

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

[cjbj]

@raymondfeng node-oracledb's connection pool does its best with stale connections. There are some tweaks coming soon in node-oracledb 3: https://github.com/oracle/node-oracledb/issues/960#issuecomment-412793112

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

[jeyaraj87]

@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

[tinker20]

@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?

[cjbj]

@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).

[tinker20]

@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
  }

[cjbj]

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

[tinker20]

@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

[tinker20]

@cjbj @raymondfeng Any update on the above issue?

[stale[bot]]

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.

[stale[bot]]

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.