Node JS Express - Oracle Pooling (ORA-24418: cannot open additional sessions)

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

I have an application that has 300 to 900 views per hour (usually around 100 users). The application has many requests for $ .post in the background to retrieve information from the database and display it to the user.

I recently switched to this module as it is proprietary to Oracle (I previously used https://github.com/joeferner/node-oracle ).

This is how I phrased it:

/ 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 "acquire_and_query" module is called from c in our application and has SQL and its parameters that must be executed.

The Oracle database has a maximum allowed number of pooled connections of 80 (and we don't exceed them) - and usually looks pretty happy.

However, the node app keeps throwing ORA-24418: Can't open additional sessions and I'm not sure how to resolve this.

Thank.

+3


source to share


2 answers


Solved - Problem: my bad coding!

I unwittingly set the Socket.IO event to update the view for EVERYONE connected multiple times (instead of querying the database once and then sending the view over the socket ...) sigh

I was even more stupid using a for loop in a transactional query (which was INSERTing multiple data in each run) ... Once I changed this to a recursive pattern - it went through without issue!

Good article here 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'm using now (and it works pretty well) Using node-oracledb v0.6 ( https://github.com/oracle/node-oracledb ) and Express 4 ( <a2> )

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;

}

      

Usage example

//////////////////////////////
// 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
            }
          }
      );
    }
  });

}

      

+3


source


This error message occurs when the sessMax parameter supplied in OCISessionPoolCreate was reached.

So my first step will be to check if the database sessions are closed correctly.

When you receive this error message, follow these three steps:

1.- (using sqlplus) show sess parameter



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

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

Have you followed the steps below? (share your results)

+1


source







All Articles