Pool cannot detect idle connection disconnected by MySQL and will trigger `read ECONNRESET` on newly acquired connection

[t83714]

MySQL will disconnect idle connections after certain time frame.

This time frame is determined by MySQL variable wait_timeout.

It seems mysql2's pool implementation cannot detect this type of disconnection (ping is not sufficient).

And when it happens, any queries sent through the acquired connection will trigger a read ECONNRESET error.

At this moment, I have to send a SELECT 1 query to test the acquired connection before use.

And previous code that calls pool.query directly will have to be re-written.

Can Mysql2 provides two extra pool creation options:

• verifyConnectionInterval: number of seconds before pool should send SELECT 1 query to test connections in the pool.
• verifyConnection: boolean. If set true, the pool will test a connection in the pool every verifyConnectionInterval seconds.

[sidorares]

I saw this problem as well, and I think there is no way to automatically detect "server closed connection" automatically other than doing some kind of heartbeat messages

Are you sure doing .ping(callback) is not enough? Probably not much difference compared to SELECT 1

[sidorares]

I think first step would be adding all events supported by mysqljs/mysql pool - https://github.com/mysqljs/mysql/#release

That way you can start/stop heartbeat in your code manually in reponse to release/acquire events

And later we could simplify that and make available as pool option

[t83714]

Thanks for your reply.
Will run more tests on using .ping(callback) for heartbeat test and get it back to you.

[t83714]

Hi @sidorares ,
Had a few more tests on this.

It seems I can't get .ping(callback) work for heartbeat test purpose but SELECT 1 works well.

Here are the code examples (I used generic-pool for pool management):

Working Example (use SELECT 1):

import mysql2 from "mysql2/promise";
import genericPool from "generic-pool";
import dbConfigInfo from "../config/db.local";

const opts = {
    host: dbConfigInfo.host,
    user: dbConfigInfo.username,
    password: dbConfigInfo.password,
    database: dbConfigInfo.database,
};

const PoolDB = genericPool.createPool({
    create : () => mysql2.createConnection(opts),
    destroy : (connection) => connection.end(),
    validate : (connection) => connection.query(`SELECT 1`).then(()=>true,()=>false),
}, {
    max : 5,
    min : 0,
    testOnBorrow : true
});
const db = {
    execute : async (sql, values, cb) => {
        let conn;
        try{
            conn = await PoolDB.acquire();
            const r = await conn.execute(sql, values, cb);
            await PoolDB.release(conn);
            return r;
        }catch(e){
            if(conn) await PoolDB.destroy(conn);
            throw e;
        }
    },
};

Non-working Example (use .ping(callback)):

import mysql2 from "mysql2/promise";
import genericPool from "generic-pool";
import dbConfigInfo from "../config/db.local";

const opts = {
    host: dbConfigInfo.host,
    user: dbConfigInfo.username,
    password: dbConfigInfo.password,
    database: dbConfigInfo.database,
};

const PoolDB = genericPool.createPool({
    create : () => mysql2.createConnection(opts),
    destroy : (connection) => connection.end(),
    validate : (connection) => connection.ping().then(()=>true,()=>false),
}, {
    max : 5,
    min : 0,
    testOnBorrow : true
});
const db = {
    execute : async (sql, values, cb) => {
        let conn;
        try{
            conn = await PoolDB.acquire();
            const r = await conn.execute(sql, values, cb);
            await PoolDB.release(conn);
            return r;
        }catch(e){
            if(conn) await PoolDB.destroy(conn);
            throw e;
        }
    },
};

Before the tests, I changed my local MySQL wait_timeout variable to only 10 seconds.
Then, I triggered queries every 15 seconds through my web app (call db.execute() defined above).
The first example works well and the second example will trigger Access Denied: Can't add new command when connection is in closed state error after 10 seconds.

[hallh]

I'm seeing this too, and not only due to wait_timeout.

About detecting closed connections, I've noticed that the number of free connections (during low-load) matches the number of actual open TCP connections. The pool._allConnections queue still remains the same. That is, ignorant of closed connections. But given that the TCP connections are being closed, it should be fairly straight forward to detect.

There are some notes in this gist.

As you can see from the notes, the amount of FREE connections slowly drops after the pool has reached its connectionLimit. I haven't looked into why the server is closing the connections, or whether it actually is the server closing them, but it's fairly common that MySQL does that, so I'd expect the library to just deal with it.

It seems a pretty serious issue, since after all the connections have been dropped - the pool will never regain any of them, practically stalling all subsequent DB queries until the process is restarted.

[midnightcodr]

I too was hit with the read ECONNRESET error recently and it's caused by default connection timeout with MySQL server. I have a long running program that uses mysql2 pool and make SQL updates whenever needed. During our UAT development, since there are no activities overnight, the queries run on the second day would simply fail with that error. Since node-mysql has better handling for the idled connections problem, I've switched to using node-mysql instead. I might give the validate option a try in the future.

[midnightcodr]

Did some tests today, I couldn't replicate the read ECONNRESET problem with a low wait_timeout setting. My setup: (doesn't seem setting of interactive_timeout is relavant either)
server: (mysql 5.6 from https://hub.docker.com/_/mysql/)

[mysqld]
wait_timeout=10
interactive_timeout=20

mysql2 version 1.5.3.

My testing code:

const mysql = require('mysql2/promise')
const settings = {
    host: 'localhost',
    user: 'dbuser',
    password: 'password',
    database: 'dbname',
    connectionLimit: 2
}
const sleep = ms => {
    return new Promise(resolve => {
        setTimeout(resolve, ms)
    })
}

const main = async () => {
    const pool = await mysql.createPool(settings)
    // console.log(pool)
    const r = await pool.query('show session variables like "%timeout"')
    console.log(r)
    const [rowsA, fieldsB] = await pool.query('select * from awesomeTbl')
    console.log('to query in 30 seconds')
    await sleep(30000)
    const [rows, fields] = await pool.query('select * from awesomeTbl')
    const [rows2, fields2] = await pool.query('select * from awesomeTbl')

    console.log('to query again in 30 seconds')
    await sleep(30000)
    const [rows1, fields1] = await pool.query('select * from awesomeTbl')

    await pool.end()
}

main()

None of the queries triggers an error.

[anthonywebb]

I am seeing this as well, it is a shame too, because mysql2 is so much faster with my DB, alas, I cant have the server throwing ECONNRESET without remedy.

[grega913]

I have problem with ECONNRESET (mysql, pool, knex, node) and I belive if you have the option to tweak this param on server, it should be ok . . .
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_connect_timeout

[midnightcodr]

@anthonywebb in one of my recent projects I am being hit with the same problem again and I implemented a quick and dirty solution by querying the mysql server with:

setInterval(() => {
  pool.query('select 1')
}, 120000)

It's ugly but at least it works.

[benbotto]

@midnightcodr How does that ensure that all of the connections in the pool are kept alive? That is, if you have 10 connections in the pool, is it possible that the keep-alive code will simply use the first connection over and over again and let the other 9 die off?

[midnightcodr]

@benbotto I doubt that's how pooling works. Let's say you specify a pool size of 10, mysql2 (or mysql) does not create or keep 10 connections alive all the time. It only create up to 10 connections when needed. Most of the time you should have fewer than 10 connections alive at the same time - unless your mysql server is mad busy.

[benbotto]

@midnightcodr Sure, that makes sense, but the question still stands. The 10 is arbitrary (and small fries!). Is it possible that your keep-alive code snippet will simply use the first connection over and over again and let the other n connections in the pool die off?

Let's say that 2 connections are created in the pool. After idling for a while, these connections are killed by MySQL, potentially due to the wait_timeout threshold. It appears to me that mysql2 does not detect the timeout/connection abortion, and subsequent queries against either connection will fail with a ECONNRESET error.

So with your keep-alive snippet, will it keep the both connections alive? Or is it possible that only one is kept alive, and the next time that 2 connections are needed the second connection fails with an ECONNRESET error?

And thank you for the response and help!

[sidorares]

Let's say that 2 connections are created in the pool. After idling for a while, these connections are killed by MySQL, potentially due to the wait_timeout threshold. It appears to me that mysql2 does not detect the timeout/connection abortion, and subsequent queries against either connection will fail with a ECONNRESET error.

So there are 2 different questions here: 1) the driver should (ideally) remove connection from the pool immediately after it's killed. 2) A way to prefer to have N hot connections in the pool all the time (and if it gets below that for some reason actively re open new one)

Regarding 1 - I previously had issues where network socket does not get any events at all when other end is closed. I need to test that again, if there is a way to see it's closed (ideally without heartbeats and just via os level notification) we should definitely make sure connection is removed from the pool

Open to more discussion re question 2

Also if anyone can debug what happens for example when you kill server or kill connection using server side sql that would be helpful ( do we get anything fired here?

node-mysql2/lib/connection.js

Line 81 in d8cac2a

if (this._closing) {

and here

node-mysql2/lib/connection.js

Line 67 in d8cac2a

this.stream.on('error', this._handleNetworkError.bind(this));

)