ECONNRESET Connection reset by peer (104)

[komanton]

Thanks for your work. We are using the connection pool of mysql2 in production in CloudRun with "CPU is always allocated" (i.e. simply say, it is a regular nodejs app) with enabled TCP's keepAlive option.

But from time to time (~2-5 errors during a minute almost every week), during invocation of pool.query, we receive the ECONNRESET Connection reset by peer (104) error.

According to the documentation, this error maybe a result of 'infrastructure updates' .

My questions:

1. Does mysql2's connection pool follow the recommendation of Google Cloud from the link above? I.e. If your application reuses long-lived connections, then we recommend that you configure your application to re-establish connections to avoid the reuse of a dead connection.
2. What would you recommend according mysql2 in the situation when application received the error ECONNRESET(104)?
3. Why the connection pool doesn't remove connection automatically in case ECONNRESET(104) and this error raised during call pool.query()?
4. Is a connection removed from the pool after the first occurrence of ECONNRESET(104) in pool.query()? Will the next call of pool.query(), after the error ECONNRESET(104), use a new connection?

Connection pool configuration:

const pool = mysql.createPool({
        host: 'localhost',
        port: 3307,
        user: 'dev',
        password: 'dev',
        database: 'dev',
        multipleStatements: true,
        connectionLimit: 5,
        maxIdle: 0,
        idleTimeout: 60000,
        enableKeepAlive: true,
    });

Error stack:

at PromisePoolConnection.query (/app/node_modules/.pnpm/[email protected]/node_modules/mysql2/promise.js:93:22)
at /app/modules/server/external-apis/mysql-api/dist/ConnectionPool.js:79:49
at step (/app/modules/server/external-apis/mysql-api/dist/ConnectionPool.js:33:23)
at Object.next (/app/modules/server/external-apis/mysql-api/dist/ConnectionPool.js:14:53)
at fulfilled (/app/modules/server/external-apis/mysql-api/dist/ConnectionPool.js:5:58)
at runMicrotasks (<anonymous>)
at processTicksAndRejections (internal/process/task_queues.js:95:5) {
code: 'ECONNRESET',
errno: -104,
sql: undefined,
sqlState: undefined,
sqlMessage: undefined

[sidorares]

4 - yes, this is the behaviour I'd expect. The only explanation I have is that mysql closes one side of the connection due to timeout somewhere behind the proxy and this is not communicated back to the client ( for example proxy still responds to tcp keepalive heartbeat from mysql2 client )

PoolConnection is removed from pool on end / error events here: https://github.com/sidorares/node-mysql2/blob/991160ca30aa4969b834e605fa4aa75494aa1d7d/lib/pool_connection.js#L19

[sidorares]

Why the connection pool doesn't remove connection automatically in case ECONNRESET(104) and this error raised during call pool.query()?

Worth investigating the following scenario:

• connection acquired from the pool
• some delay due to another async action
• mysql server closes connection
• connection is removed from the pool and marked as closed
• you make a query and get back ECONNRESET(104) error

Can you try to attach some logging to a connection end / error events and see if above scenario happens? If thats the case maybe worth adding a retry in that case

[sidorares]

@komanton I wonder if google's "cloud sql connector" would make any difference

https://cloud.google.com/sql/docs/mysql/connect-connectors#node.js_1

Internally it creates it's own stream and manages it, and mysql connections live on top of that stream(s)

[benyaminl]

Why the connection pool doesn't remove connection automatically in case ECONNRESET(104) and this error raised during call pool.query()?

Worth investigating the following scenario:

• connection acquired from the pool
• some delay due to another async action
• mysql server closes connection
• connection is removed from the pool and marked as closed
• you make a query and get back ECONNRESET(104) error

Can you try to attach some logging to a connection end / error events and see if above scenario happens? If thats the case maybe worth adding a retry in that case

For the part of retry, is there anyway take example, we catch it the econnreset and retry out function using mysql2 or we need to do it ourself in our own code?

Thank you