I develop in nodejs (expressjs) with mysql2. When I close or restart mysql server my application thorws error and nodemon crashes and don’t reconnect to mysql server. I want to reconnect every 5 minute.
This is my code:
const mysql = require('mysql2');
let db;
const dbConfig = {
host: HOST_DB,
user: USER_DB,
password: PASSWORD_DB,
database: DATABASE_NAME,
port: PORT_DB,
};
function handleDisconnect() {
db = mysql.createConnection(dbConfig);
db.connect(error => {
if (error) {
console.error('Error connecting to the database:', error);
setTimeout(handleDisconnect, 300000);
}
});
db.on('error', error => {
console.error('Database error:', error);
if (error.code === 'PROTOCOL_CONNECTION_LOST') {
console.log('Attempting to reconnect to the database...');
handleDisconnect();
} else {
throw error;
}
});
}
handleDisconnect()
db = mysql.createConnection(dbConfig);
Error:
node:events:497
throw er; // Unhandled 'error' event
^
Error: Connection lost: The server closed the connection.
at Socket.<anonymous> (C:UsersadminDesktopotpservernode_modulesmysql2libconnection.js:117:31)
at Socket.emit (node:events:519:28)
at TCP.<anonymous> (node:net:338:12)
Emitted 'error' event on Connection instance at:
at Connection._notifyError (C:UsersadminDesktopotpservernode_modulesmysql2libconnection.js:252:12)
at Socket.<anonymous> (C:UsersadminDesktopotpservernode_modulesmysql2libconnection.js:123:12)
at Socket.emit (node:events:519:28)
at TCP.<anonymous> (node:net:338:12) {
fatal: true,
code: 'PROTOCOL_CONNECTION_LOST'
}
Node.js v20.15.0
[nodemon] app crashed - waiting for file changes before starting...
I want to reconnect to mysql server every 5 minute. Then my backend server can work without having me to shut it down and restart it.
1
You can use a combination of connection pooling and a reconnect mechanism
const mysql = require('mysql2');
const dbConfig = {
host: HOST_DB,
user: USER_DB,
password: PASSWORD_DB,
database: DATABASE_NAME,
port: PORT_DB,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
};
let pool;
function createPool() {
pool = mysql.createPool(dbConfig);
pool.on('connection', function (connection) {
console.log('Database connection established');
});
pool.on('error', function (err) {
console.error('Database error:', err);
if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'ECONNREFUSED' || err.code === 'ER_ACCESS_DENIED_ERROR') {
console.log('Attempting to reconnect to the database in 5 minutes...');
setTimeout(createPool, 300000); // 5 minutes
} else {
throw err;
}
});
// This is an example query to test the connection
pool.getConnection((err, connection) => {
if (err) {
console.error('Error connecting to the database:', err);
setTimeout(createPool, 300000); // 5 minutes
} else {
console.log('Connected to the database');
if (connection) connection.release();
}
});
}
createPool();
module.exports = pool;
New contributor
Temidayo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.