NJS-024: OracleDB - Nodejs中内存分配失败

Question

我试图使用OracleDB和Nodejs运行一个查询，以获得UI中填充的视图，但是我得到了一个NJS-024:内存分配失败的错误。有人能帮我吗？该视图总共包含120列，当我在中查询视图时，它工作得很好。

ConnectionPool.js：

var path = require('path');
var oracledb = require('oracledb');
var poolMap = {};

var logger = require(path.join(global.root + '/app/util/logger.js'))();

function createPool(poolName, config, callback) {
    oracledb.createPool(
        config,
        function(err, p) {
            if (err){
                logger.error(err);
                return;
            }

            poolMap[poolName] = p;

            callback(poolMap[poolName]);
        }
    );
}

function getPool(poolName) {
    return poolMap[poolName];
}

module.exports = {
    createPool: createPool,
    getPool: getPool
};

这是我的poolAttributes：

var pool;
oracledb.prefetchRows = 10000;
oracledb.maxRows = 400000;

var poolAttrs = {
    user: dbcfg.username,
    password: dbcfg.password,
    connectString: dbcfg.connectionString,
    connectionClass : 'Report API',
    poolMin : 3,
    poolMax : 10,
    poolIncrement: 2,
    poolTimeout : 600 //seconds
};

connectionPool.createPool("Reports", poolAttrs, function(connPool){
    pool = connPool;
    logger.info("Pool created by reports.");
});

这是我的密码：

router.post('/report/', jsonParser, function (req, res) {
    var data = req.body,
        startRow = data.startRow,
        numRows = data.numRows,
        sortCol = data.sortCol,
        sortDir = data.sortDir;

    var countQuery = 'SELECT COUNT(*) ' +
        'FROM this_view ' ;

    var query = 'SELECT * ' +
        'FROM this_view' ;

    var seg,
        orderBy,
        offset;

    orderBy = ' ORDER BY UPPER(' + sortCol + ') ' + sortDir;
    offset = ' OFFSET ' + startRow + ' ROWS FETCH NEXT ' + numRows + ' ROWS ONLY';

    query += orderBy;
    query += offset;

    logger.info("Begin: " + (new Date().toString()));

    async.parallel({
        rows: function (callback) {
        pool.getConnection(function (err, connection) {
            logger.info("Begin Connection: " + (new Date().toString()));
            if (err) {
                logger.error(err.message);
                return;
            }

            logger.info("Begin execute: " + (new Date().toString()));

            connection.execute(
                query,
                {},
                {
                    resultSet: true,
                    prefetchRows: 1000
                },
                function (err, results) {
                    logger.info("End execute: " + (new Date().toString()));
                    var rowsProcessed = 0;
                    var startTime;
                    if (err) {
                        logger.error(err.message);
                        callback("Something broke in the first thing");
                        doRelease(connection);
                        return;
                    }
                    var procJson = [];

                    function fetchRowsFromRS(connection, resultSet, numRows) {
                        resultSet.getRows(
                            numRows,  // get this many rows
                            function (err, rows) {
                                if (err) {
                                    console.error(err);
                                    doClose(connection, resultSet); // always close the result set
                                } else if (rows.length >= 0) {
                                    /**
                                     *  For each row in the result, pushes a new object to the rows array
                                     *  In each new object, the key is assigned and the result row value set
                                     */
                                    for (var i = 0; i < rows.length; i++) {
                                        procJson.push({});
                                        console.log(procJson);
                                        for (var j = 0; j < resultSet.metaData.length; j++) {
                                            procJson[i][resultSet.metaData[j].name.toLowerCase()] = rows[i][j];
                                        }
                                    }

                                    //TODO: Add null handling
                                    logger.info("Send JSON: " + (new Date().toString()));
                                    logger.info("JSON Sent: " + (new Date().toString()));
                                    if (rows.length === numRows) // might be more rows
                                        fetchRowsFromRS(connection, resultSet, numRows);
                                    else
                                        doClose(connection, resultSet); // always close the result set
                                } else { // no rows
                                    doClose(connection, resultSet); // always close the result set
                                }
                            });
                    }
                    fetchRowsFromRS(connection, result.resultSet, numRows);
                    callback(null, procJson);
                });
        });
    },
        totalRows: function (callback) {
            pool.getConnection(function (err, connection) {
                logger.info("Begin Connection: " + (new Date().toString()));
                if (err) {
                    logger.error(err.message);
                    return;
                }

                logger.info("Begin execute: " + (new Date().toString()));

                connection.execute(
                    countQuery,
                    function (err, result) {
                        logger.info("End execute: " + (new Date().toString()));
                        if (err) {
                            logger.error(err.message);
                            callback("Something broke");
                            doRelease(connection);
                            return;
                        }

                        logger.info("Send JSON: " + (new Date().toString()));
                        console.log(result.rows);
                        callback(null, result.rows[0][0]);
                        logger.info("JSON Sent: " + (new Date().toString()));

                        doRelease(connection);
                    });
            });
        }
    }, function(err, result){
        if(err){
            logger.error(err);
        }

        res.send(result);
    });
});

如果rows.length >=0和查询返回0的结果，我将得到以下结果。

​

​

Answer 1

您的Node.js服务器有多少内存？您正在将maxRows设置得非常高，并在一次拍摄中获取所有数据。这很可能导致你的内存耗尽。通常，关键是平衡往返行程(您想要减少)和内存使用(随着往返行程的减少而增加)。

您将希望利用ResultSet API，它允许您以较小的块来流读取一致的数据视图。看看这个创意：https://jsao.io/2015/07/an-overview-of-result-sets-in-the-nodejs-driver/

与其在Node.js服务器中缓冲数据(这会导致同样的问题)，不如将其流到http请求中。

最后，但也许最重要的是，请注意，您的代码目前对SQL注入是开放的。用户通过req.body输入的值不能被信任。必须在使用绑定变量时绑定它们，或者使用类似于dbms_assert的方法对它们进行净化。

只能绑定值(如numRows)。标识符(如sortCol)必须进行消毒。您可能希望在Node.js中进行消毒，所以下面是一个非常基本的检查，应该会有所帮助。

您可以创建一个“断言”模块：

function simpleSqlName(name) {
  if (name.length > 30) {
    throw new Error('Not simple SQL');
  }

  // Fairly generic, but effective. Would need to be adjusted to accommodate quoted identifiers,
  // schemas, etc.
  if (!/^[a-zA-Z0-9#_$]+$/.test(name)) {
    throw new Error('Not simple SQL');
  }

  return name;
}

module.exports.simpleSqlName = simpleSqlName;

function validSortOrder(order) {
  if (order !== 'desc' && order !== 'asc') {
    throw new Error('Not valid sort order');
  }

  return order;
}

module.exports.validSortOrder = validSortOrder;

然后您的代码看起来会更像这样(请注意，我同时使用了断言模块和绑定变量)：

let assert = require('assert.js');  

router.post('/report/', jsonParser, function (req, res) {
    var data = req.body,
        startRow = data.startRow,
        numRows = data.numRows,
        sortCol = assert.simpleSqlName(data.sortCol),
        sortDir = assert.validSortOrder(data.sortDir);

    var countQuery = 'SELECT COUNT(*) ' +
        'FROM this_view ' ;

    var query = 'SELECT * ' +
        'FROM this_view' ;

    var seg,
        orderBy,
        offset;

    orderBy = ' ORDER BY UPPER(' + sortCol + ') ' + sortDir;
    offset = ' OFFSET :start_row ROWS FETCH NEXT :num_rows ROWS ONLY';

    query += orderBy;
    query += offset;

    logger.info("Begin: " + (new Date().toString()));

    async.parallel({
        rows: function (callback) {
            pool.getConnection(function (err, connection) {
                logger.info("Begin Connection: " + (new Date().toString()));
                if (err) {
                    logger.error(err.message);
                    return;
                }

                logger.info("Begin execute: " + (new Date().toString()));

                connection.execute(
                    query,
                    {
                      start_row: startRow,
                      num_rows: numRows
                    },
                    function (err, result) {
                        logger.info("End execute: " + (new Date().toString()));
                        if (err) {
                            logger.error(err.message);
                            callback("Something broke in the first thing");
                            doRelease(connection);
                            return;
                        }
                        console.log(result.rows);

                        var procJson = [];

                        /**
                         *  For each row in the result, pushes a new object to the rows array
                         *  In each new object, the key is assigned and the result row value set
                         */
                        for (var i = 0; i < result.rows.length; i++) {
                            procJson.push({});
                            for (var j = 0; j < result.metaData.length; j++) {
                                procJson[i][result.metaData[j].name.toLowerCase()] = result.rows[i][j];
                            }
                        }

                        logger.info("Send JSON: " + (new Date().toString()));
                        callback(null, procJson);
                        logger.info("JSON Sent: " + (new Date().toString()));

                        doRelease(connection);
                    });
            });
        },
        totalRows: function (callback) {
            pool.getConnection(function (err, connection) {
                logger.info("Begin Connection: " + (new Date().toString()));
                if (err) {
                    logger.error(err.message);
                    return;
                }

                logger.info("Begin execute: " + (new Date().toString()));

                connection.execute(
                    countQuery,
                    function (err, result) {
                        logger.info("End execute: " + (new Date().toString()));
                        if (err) {
                            logger.error(err.message);
                            callback("Something broke");
                            doRelease(connection);
                            return;
                        }

                        logger.info("Send JSON: " + (new Date().toString()));
                        console.log(result.rows);
                        callback(null, result.rows[0][0]);
                        logger.info("JSON Sent: " + (new Date().toString()));

                        doRelease(connection);
                    });
            });
        }
    }, function(err, result){
        if(err){
            logger.error(err);
        }

        res.send(result);
    });
});

在这里了解有关绑定变量的更多信息：https://github.com/oracle/node-oracledb/blob/master/doc/api.md#bind

另外，看看我最近做的一次演讲的幻灯片。你可以从他们身上得到一些东西..。https://www.dropbox.com/s/2rhnu74z2y21gsy/Tips%20and%20Tricks%20for%20Getting%20Started%20with%20the%20Oracle%20Database%20Driver%20for%20Node.pdf?dl=0