CraZySacX / node-jdbc

JDBC Wrapper for node.js
140 stars 107 forks source link

Detecting a lost connection. #184

Open bkkwok opened 6 years ago

bkkwok commented 6 years ago

Every other night, my company's JDE server restarts and the connection is lost. In order to re-establish the connection, I have to restart my node process when I wake up.

This is the error I receive:

{ Error: Error running instance method
java.sql.SQLException: Communication link failure. (Connection reset)
    at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:824)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:553)
    at com.ibm.as400.access.AS400JDBCConnection.sendAndReceive(AS400JDBCConnection.java:3036)
    at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1813)
    at com.ibm.as400.access.AS400JDBCStatement.executeQuery(AS400JDBCStatement.java:2446)
    at jdk.internal.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
 cause: nodeJava_java_sql_SQLException {} }

This question has been answered in #105.

However, how would my code be able to tell the difference between this specific error and a different type of sql exception.

I don't want to re-establish the connection unless it is a Communication link failure. One way I can think of is to catch the error, check if err.message.includes('Communication link failure'), and then reconnect.

I'm using expressJs

db.js

const JDBC = require('jdbc');    

const config = { ... };`

let pool = connect();

function connect() { 
    pool = new JDBC(config);
    pool.initialize()
}

function reconnect() {
    pool.purge(() => {
        connect();
   })
}

module.exports = {
    pool,
    connect,
    reconnect,
}

index.js

const { reconnect } = require('./db')

app.use((err, req, res, next) => {
    if(err.message.includes('Communication link failure') {
        reconnect() 
    } 
})

Would this be the recommended way?

fernandolobao commented 4 years ago

Did you ever get a response for this?

bkkwok commented 4 years ago

No, although I'd imagine the only better way is for a specific error code to be given for this exception. However this will suffice.

underoll commented 2 years ago

Well, i have the same problem, and my solution is to validate the reserved connection each time. I use bluebird, so my example is promisified.

const JDBC = require('jdbc');
const Promise = require('bluebird');
Promise.promisifyAll([
  require('jdbc/lib/pool'),
  require('jdbc/lib/connection'),
  require('jdbc/lib/statement'),
  require('jdbc/lib/callablestatement'),
  require('jdbc/lib/preparedstatement'),
  require('jdbc/lib/resultset')
]);

...

let connObj;
while (!connObj) {
  connObj = await db.reserveAsync();
  if (!(await connObj.conn.isValidAsync(1))) {
    await connObj.conn.closeAsync();
    // don't return the closed conn to the _pool and don't leave it in _reserved, just remove
    db._reserved = _.reject(db._reserved, ({uuid}) => uuid == connObj.uuid);
    connObj = undefined;
  }
}