Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

createPoolCluster does not maintain a connection to the same database throughout execution #2971

Open
DatTN95 opened this issue Aug 21, 2024 · 0 comments

Comments

@DatTN95
Copy link

DatTN95 commented Aug 21, 2024

when i call await execSQLFC(sql); then the problem occurs.
if i add await connection.query(USE ${process.env.SQL_FCDB}); it will working.
my configure (something wrong?):

****DBConfig.js

const mysql = require('mysql2/promise')

const poolCluster = mysql.createPoolCluster();

poolCluster.add('FloorcareDB', {
    host: process.env.SQL_HOST,
    user: process.env.SQL_USER,
    password: process.env.SQL_PWD,
    database: process.env.SQL_FCDB,
    connectionLimit: 50,
    waitForConnections: true,
    maxPreparedStatements: 500,
    queueLimit: 0,
    dateStrings: true,
    keepAliveInitialDelay: 10000,
    enableKeepAlive: true
});

poolCluster.add('TraceDB', {
    host: process.env.SQL_HOST,
    user: process.env.SQL_USER,
    password: process.env.SQL_PWD,
    database: process.env.SQL_TRACEDB,
    connectionLimit: 50,
    waitForConnections: true,
    maxPreparedStatements: 500,
    queueLimit: 0,
    dateStrings: true,
    keepAliveInitialDelay: 10000,
    enableKeepAlive: true
});

module.exports = poolCluster;

const poolCluster = require('../Config/DBConfig');

ExecuteSQL.js

const execSQLFC = async (sql, values) => {
    let connection;

    try {
        connection = await poolCluster.getConnection('FloorcareDB');
        await connection.query(`USE ${process.env.SQL_FCDB}`);

        console.log('Connected to FloorcareDB');
        const [dbCheck] = await connection.query('SELECT DATABASE() as currentDB');
        console.log('Current database:', dbCheck[0].currentDB);

        console.log('FloorcareDB');
        const [rows] = await connection.query(sql, values);
        connection.release();

        return rows;
    } catch (err) {
        if (connection) connection.release();
        console.error(err);
        return err;
    }
};

const execSQLTrace = async (sql, values) => {
    let connection;

    try {
        connection = await poolCluster.getConnection('TraceDB');
        await connection.query(`USE ${process.env.SQL_TRACEDB}`);

        console.log('Connected to TraceDB');
        const [dbCheck] = await connection.query('SELECT DATABASE() as currentDB');
        console.log('Current database:', dbCheck[0].currentDB);

        const [rows] = await connection.query(sql, values);
        connection.release();

        return rows;
    } catch (err) {
        if (connection) connection.release();
        console.error(err);
        return err;
    }
};

module.exports = { execSQLFC, execSQLTrace };
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants