sidorares / sidorares/node-mysql2

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

Open
#2,971 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

needs investigation
Dominant language
TypeScript
Stars
4.4k
Forks
680
Avg merge
9h 7m
Merged PRs (30d)
59

Description

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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the createPoolCluster and getConnection usage shown in DBConfig.js, then trace execSQLFC and execSQLTrace in ExecuteSQL.js. Reproduce the behavior with the two configured database names and compare the selected database before and after connection reuse. Done should include a confirmed cause and a focused regression test or documented expected behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, mysql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.