javascript - 对数据库的多次调用无法正常工作

Question

我的 mysql 有两个问题:

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

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

RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed RackChecker connected with database! Connection Closed

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

ma​​in.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();
        }
    })
}

server.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();
}

Answer 1

我找到了答案，我更改了用于连接数据库的方法我开始使用 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();
   });
   });