I am developing software that runs 24/7 on PM2, performing long-polling by querying the database with SELECT statements every second (I also tried querying every 20 seconds, but that didn’t solve the issue). It frequently performs batch INSERT operations, but I am unable to resolve connection timeouts (ETIMEDOUT) and connection loss (PROTOCOL_CONNECTION_LOST).
import mysql from 'mysql2/promise';
import { config } from 'dotenv';
import { images } from './images.js';
import { labels } from './labels.js';
import { nodes } from './nodes.js';
import { runs } from './runs.js';
import { products } from './products.js';
import { utils } from '../utils/_controller.js';
import { queue } from './queue.js';
config();
let pool;
const logger = utils.createDebugLogger('MySQL');
/**
* Executes a query on the database
* @param {string} query - The query to execute
* @param {Array} params - The parameters to pass to the query
* @returns {Promise<[mysql.QueryResult, mysql.FieldPacket[]]>} - The result of the query
*/
const _execute = async (query, params, errorDelay, attempts) => {
if (attempts >= 5) {
logger.error(`Error while performing query: ${e.message}`, null, e);
throw new Error('Failed to execute query after 5 attempts');
}
let connection;
const pool = await getInstance();
try {
connection = await pool.getConnection();
return await connection.query(query, params);
} catch (e) {
console.error(e);
const timedOut = e.code == "ETIMEDOUT" || e.code == "PROTOCOL_CONNECTION_LOST";
logger.error(`Error while performing query: ${e.message}${timedOut ? ", retrying..." : ''}`, null, e);
if (timedOut) {
await new Promise(resolve => setTimeout(resolve, errorDelay)); // Wait 30 seconds to restore the connection
return _execute(query, params, attempts + 1);
}
} finally {
if (connection) {
try {
connection.release();
} catch (releaseError) {
logger.error(`Error while releasing connection: ${releaseError.message}`, null, releaseError);
}
}
}
}
const execute = async (query, params) => {
return await _execute(query, params, 30000, 0);
};
/**
* Gets the database connection instance
* @returns {Promise<mysql.Pool>}
*/
const getInstance = async () => {
if (pool) return pool;
try {
pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASS || "",
database: process.env.DB_NAME,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
idleTimeout: 10000,
});
logger.success('Created MySQL pool');
} catch (e) {
logger.error(`Error while creating MySQL pool: ${e.message}`, null, e);
}
return pool;
}
export const db = {
execute,
getInstance,
logger,
nodes,
images,
labels,
runs,
queue,
products
};
I tried to add delays between queries and made it capable of attempting multiple times to prevent the connection to timeout, but I still achieved nothing.