javascript - NJS-024 : memory allocation failed in OracleDB - Nodejs

Question

我尝试使用 OracleDB 和 Nodejs 运行查询，以获取 UI 中填充的 View ，但收到 NJS-024:内存分配失败 错误。有人可以帮我吗？该 View 总共包含 120 列，当我在 SQL Developer 中查询该 View 时，它工作得很好。

连接池.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
};

这是我的池属性:

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，它允许您以较小的 block 传输读取一致的数据 View 。看看这个的想法:https://jsao.io/2015/07/an-overview-of-result-sets-in-the-nodejs-driver/

您不想将数据缓冲在 Node.js 服务器中(这会导致同样的问题)，而是希望将其流式传输到 http 请求。

最后，但也许最重要的是，请注意您的代码当前对 SQL 注入(inject)开放。用户通过 req.body 传入的值不可信。它们必须使用绑定(bind)变量进行绑定(bind)，或者使用 dbms_assert 之类的东西进行清理。

只能绑定(bind)值(如 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;

那么你的代码看起来更像是这样的(注意我同时使用了断言模块和绑定(bind)变量):

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

在此处了解有关绑定(bind)变量的更多信息: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