多次调用数据库无法正常工作

debugcn 发表于 Dev

菲利普坦克

我的mysql有两个问题：

1 - 与 mysql 连接 2 小时后，它停止响应 2 - 多个呼叫无法正常工作

如果您查看 createandcall 函数，它将进入 for 循环以调用服务器。第一个调用正常工作，但所有其他调用都没有按预期工作，当查看日志时，我看到如下内容：

RackChecker 连接数据库！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭 RackChecker 与数据库连接！连接关闭

然后所有交互的查询结果，看起来像函数openconnection()，closeconnection()并且正在为所有交互执行，而第一个交互尚未完成处理。

主要.js：

function createandcall(rackname, racknameid, stb) {
    $('#maintable').append('<table class="table"><tbody><tr style="text-align:center"><td><h2>' + rackname + '</h2><table class="table"><tbody style="text-align:left"><tr id="STBL"></tr><tr id="STBL1"></tr><tr id="STBL2"></tr><tr id="STBL3"></tr></tbody></table></td></tr></tbody></table>');
    for (i = 1; i < stb + 1; i++) {
        createtable(i);
        callstb(rackname, racknameid, i);
    }
    return;
}

function callstb(rackname, racknameid, i) {

    $.ajax({
        type: "GET",
        dataType: 'text',
        url: "http://localhost:3000/index/" + rackname + ' ' + racknameid + ' ' + i,
        success: function (data) {
            response = '\#stb' + i;
            idtd = '\#tdstb' + i;
            $(response).html(data.replace(/\[32m/gi, '').replace(/\[0\;33m/gi, '').replace(/\[0m/gi, '').replace(/\[33m/gi, '').replace(/\[37m/gi, '').replace(/\[31m/gi, ''));
            pre = $(response).html().toString();

        },
        error: function (error) {
            $("#error").html('Error trying to get the STBs report');
            $("#error").show();
        }
    })
}

服务器.js：

app.get('/index/*', (req, res) => {
    parsedparam = req.params[0].split(" ")
    rackname = parsedparam[0]
    racknameid = parsedparam[1]
    stb = parseInt(parsedparam[2])
    verifystbs(rackname, racknameid, stb, res);
});

function openconnection() {
    con.connect(() => { console.log("RackChecker connected with database!") });
}

function closeconnection() {
    con.end(() => { console.log("Connection Closed") });
}

function verifystbs(rackname, racknameid, stb, res) {
    openconnection();
    con.query("SELECT (SELECT UCASE(name) FROM models WHERE s.model = id) as Model,\
        (SELECT UCASE(name) FROM manufacturers WHERE s.manufacturer = id) as Branch,\
        (SELECT UCASE(name) FROM racks WHERE s.rack = id) as Rack,\
        s.name as Stb,\
        x.pr as Jira, \
        x.reason as Reason,\
        x.requestor AS Stress_Request,\
        x.version as Version\
        FROM \
        stbs s \
        LEFT JOIN \
        stressrun x \
        ON (s.active = 1 && s.rack = (SELECT id FROM racks WHERE name = '"+ racknameid + "')) \
        WHERE x.id = (SELECT max(id) FROM stressrun y WHERE y.stb_id = s.id) and s.name like ('STB_%"+ stb + "')\
        and x.reason in ('failed','other','new build') ORDER BY s.name;", (err, result) => {
        console.log(result)
        if (!Array.isArray(result) || !result.length) {
            callnewstb = shell.exec('./shellscript/callnewstb.sh ' + rackname + ' ' + stb, { async: true });
            callnewstb.stdout.on('data', (data) => {
                res.send(data);
            });
        }
        else {
            for (i = 0; i < result.length; i++) {
                parsestbnumber = result[i].Stb.split("_");
                stbnumber = parseInt(parsestbnumber[1]);
                stbnumber = stbnumber * 1;
                if (stb == stbnumber) {
                    res.send("Stress Test is not running on <b>" + result[i].Stb + "</b><br>Reason: <b>" + result[i].Reason + "</b><br>Jira Ticket: <b><a href='https://link.jira.com/browse/" + result[i].Jira + "'>" + result[i].Jira + "</a></b><br>Build Version: <b>" + result[i].Version)
                    break
                }
                else {
                    callnewstb = shell.exec('./shellscript/callnewstb.sh ' + rackname + ' ' + stb, { async: true });
                    callnewstb.stdout.on('data', (data) => {
                        res.send(data);
                    })
                }
            }
        }
    });
    closeconnection();
}

菲利普坦克

我找到了一个答案，我改变了我用来连接到数据库的方法我开始使用 createPool() 而不是 createConnection()

const mysql = require('mysql');

var pool = mysql.createPool({
  connectionLimit: 16,
  host: "localhost",
  user: "user",
  password: "password",
  database: "test"
});


function verifystbs(rackname, racknameid, stb, res) {
  pool.getConnection((err, connection) => {
    if (err) { console.log(err) }
    connection.query("select * from table", result)
    connection.release();
   });
   });

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。