Node JS Express - Oracle Connection Pooling (ORA-24418: Cannot open further sessions)

Question

I'm having issues with a the Oracle DB module: https://github.com/oracle/node-oracledb/blob/master/doc/api.md

I have an application which has between 300 and 900 hits per hour (normally from around 100 users). The application has many $.post requests in the background to retrieve information from a database and display to the user.

I've recently switched to this module as it is Oracle's own (I was using https://github.com/joeferner/node-oracle previously).

Here's how I have it set out:

/bin/www

oracledb.createPool(
  {
    user            : 'USER'
    password        : 'PASS',
    connectString   : 'DB:1521/SID:POOLED',
    connectionClass : 'ARBITRARY_NAME',
    poolMin         : 1,
    poolMax         : 50,
    poolTimeout     : 300
  },
  function(err, pool)
  {

  if (err) {
      console.log(err);
  }

  require('../libs/db')(pool);    // Export pool to separate file

 }
)

/libs/db.js

module.exports = function(pool) {

// Require oracle module for formatting
var oracledb = require('oracledb');

// Export acquire and query function
module.exports.acquire_and_query = function(sql, params, callback){

  // ACQUIRE connection from pool
  pool.getConnection(function(err, connection){

    // NUMBER OF CONNCETIONS OPEN
    console.log("ORACLE: CONNX OPEN: " + pool.connectionsOpen);

    // NUMBER OF CONNEXTIONS IN USE
    console.log("ORACLE: CONNX IN USE: " + pool.connectionsInUse);
    if (err) {
      console.log(err.message);
      return;
    }

    // Use connection to QUERY db and return JSON object
    connection.execute(sql, params, {maxRows: 1000, isAutoCommit: true, outFormat: oracledb.OBJECT}, function(err, result){

      // Error Handling
      if (err) {
        console.log(err.message);   // Log the error
        return false;               // Return false for our error handling
      }

      // Release the connection back to the pool
      connection.release(function(err) {
        if (err) {
          console.log(err.message);
          return;
        }
      })

      // Return callback with rowset first, out bind paramaters second
      return callback(result.rows, result.outBinds, result.rowsAffected);
    })

  })

}

}

This module "acquire_and_query" is called from with in our application, and has SQL and it's params passed in to be executed.

The Oracle DB has a maximum allowed of Pooled connections set to 80 (and we are not exceeding them) - and generally looks pretty happy.

The node application however is constantly throwing out an ORA-24418: Cannot open further sessions, and I am unsure how to resolve this.

Thanks.

Answer 1

Resolved - Issue: My poor coding!

I had unwittingly set a Socket.IO event to update the view for EVERYONE connected multiple times (rather than querying the database once, then sending the view over the socket...) sigh

I was also even more stupidly using a for loop in a transaction based query (which was INSERTing multiple data on each run)... Once I had changed this to a recursive pattern - it went ran swimmingly!

Good article here about for loops and recursive patterns: http://www.richardrodger.com/2011/04/21/node-js-how-to-write-a-for-loop-with-callbacks/#.VaQjJJNViko

Anyway - here's what I use now (and it works rather well) Using node-oracledb v0.6 (https://github.com/oracle/node-oracledb) and Express 4 (http://expressjs.com/)

bin/www

 /**
 * Database
 */

// AS PER DOCUMENTATION: https://github.com/oracle/node-oracledb/blob/master/examples/dbconfig.js
var dbconfig = require("../libs/dbconfig.js");

oracledb.connectionClass = dbconfig.connectionClass,

oracledb.createPool({
    user:             dbconfig.user,
    password:         dbconfig.password,
    connectString:    dbconfig.connectString,
    poolMax:          44,
    poolMin:          2,
    poolIncrement:    5,
    poolTimeout:      4
}, function(err, pool) {

    if (err) {
      console.log("ERROR: ", new Date(), ": createPool() callback: " + err.message);
      return;
    }

    require('../libs/oracledb.js')(pool);

});

libs/oracledb.js

module.exports = function(pool) {

  ////////////////////////////
  // INSTANTIATE THE DRIVER //
  ////////////////////////////
  var oracledb = require("oracledb");



  //////////////////////
  // GET A CONNECTION //
  //////////////////////
  var doConnect = function(callback) {

    console.log("INFO: Module getConnection() called - attempting to retrieve a connection using the node-oracledb driver");

    pool.getConnection(function(err, connection) {

      // UNABLE TO GET CONNECTION - CALLBACK WITH ERROR
      if (err) { 
        console.log("ERROR: Cannot get a connection: ", err);
        return callback(err);
      }

      // If pool is defined - show connectionsOpen and connectionsInUse
      if (typeof pool !== "undefined") {
        console.log("INFO: Connections open: " + pool.connectionsOpen);
        console.log("INFO: Connections in use: " + pool.connectionsInUse);
      }

      // Else everything looks good
      // Obtain the Oracle Session ID, then return the connection
      doExecute(connection, "SELECT SYS_CONTEXT('userenv', 'sid') AS session_id FROM DUAL", {}, function(err, result) {

        // Something went wrong, releae the connection and return the error
        if (err) {
          console.log("ERROR: Unable to determine Oracle SESSION ID for this transaction: ", err);
          releaseConnection(connection);
          return callback(err);
        }

        // Log the connection ID (we do this to ensure the conncetions are being pooled correctly)
        console.log("INFO: Connection retrieved from the database, SESSION ID: ", result.rows[0]['SESSION_ID']);

        // Return the connection for use in model
        return callback(err, connection);

      });

    });

  }



  /////////////
  // EXECUTE //
  /////////////
  var doExecute = function(connection, sql, params, callback) {

    connection.execute(sql, params, { autoCommit: false, outFormat: oracledb.OBJECT, maxRows:1000 }, function(err, result) {

      // Something went wrong - handle the data and release the connection
      if (err) {
        console.log("ERROR: Unable to execute the SQL: ", err);
        //releaseConnection(connection);
        return callback(err);
      }

      // Return the result to the request initiator
      // console.log("INFO: Result from Database: ", result)
      return callback(err, result);

    });

  }  



  ////////////
  // COMMIT //
  ////////////
  var doCommit = function(connection, callback) {
    connection.commit(function(err) {
      if (err) {
        console.log("ERROR: Unable to COMMIT transaction: ", err);
      }
      return callback(err, connection);
    });
  }



  //////////////
  // ROLLBACK //
  //////////////
  var doRollback = function(connection, callback) {
    connection.rollback(function(err) {
      if (err) {
        console.log("ERROR: Unable to ROLLBACK transaction: ", err);
      }
      return callback(err, connection);
    });
  }



  //////////////////////////
  // RELEASE A CONNECTION //
  //////////////////////////
  var doRelease = function(connection) {

    connection.release(function(err) {
      if (err) {
        console.log("ERROR: Unable to RELEASE the connection: ", err);
      }
      return;
    });

  }



  //////////////////////////////
  // EXPORT THE FUNCTIONALITY //
  //////////////////////////////
  module.exports.doConnect  = doConnect;
  module.exports.doExecute  = doExecute;
  module.exports.doCommit   = doCommit;
  module.exports.doRollback = doRollback;
  module.exports.doRelease  = doRelease;

}

Example Usage

//////////////////////////////
// REQUIRE RELEVANT MODULES //
//////////////////////////////
var db          = require("../libs/oracledb.js");
var oracledb    = require('oracledb');
var sql         = "";

///////////////////////////
// RETRIEVE CURRENT DATE //
///////////////////////////
module.exports.getCurDate = function(callback) {

  sql = "SELECT CURRENT_DATE FROM DUAL";
  db.doConnect(function(err, connection){
    console.log("INFO: Database - Retrieving CURRENT_DATE FROM DUAL");
    if (err) {
      console.log("ERROR: Unable to get a connection ");
      return callback(err);
    } else {
      db.doExecute(
        connection, sql
        , {} // PASS BIND PARAMS IN HERE - SEE ORACLEDB DOCS
        , function(err, result) {
            if (err) {
              db.doRelease(connection);     // RELEASE CONNECTION
              return callback(err);                 // ERROR
            } else {
               db.doRelease(connection);     // RELEASE CONNECTION
               return callback(err, result.rows);    // ALL IS GOOD
            }
          }
      );
    }
  });

}

Answer 2

This error message is raised when the sessMax parameter supplied in OCISessionPoolCreate has been reached.

So, my first move would be verify if database sessions are being closed correctly.

When this error message arises execute the following three actions:

1.- (using sqlplus) show parameter sess

2.- (using sqlplus) select username,machine,program,count(*) from v$session group by username,machine ,program order by 4 ;

3.- verify in alert.log if there are any other ORA- messages during this event.

Did you perform this steps ? (share your results)