error ER_CLIENT_INTERACTION_TIMEOUT 4031 fires randomly

[gayratv]

Andrey wrote that using a pool automatically solves problems with loss of connection to the server and missing connection.

But simple code show that the error occurs randomly.

Error: The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. code: 'ER_CLIENT_INTERACTION_TIMEOUT', errno: 4031,

Code to reproduce:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  port: parseInt(process.env.MYSQL_PORT),
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DBNAME,
  host: process.env.MYSQL_HOST,
  namedPlaceholders: true,
});
let counterG = 0;

for (let i = 0; i < 20; i++) {
  console.log('>>>> ', i);

  try {
   // any valid sql
    const [data] = await pool.query(`select * from user_profiles limit 1`);
    // @ts-ignore
    console.log('idUser : ', data[0].idUser, ' counterG ', counterG++);
  } catch (err) {
    console.log('*************** ERROR ', err.errno, counterG);
  }

  await delay(30_000);
}
process.exit(0);

In my case output was:

>>>>  0
idUser :  0  counterG  0
>>>>  1
idUser :  0  counterG  1
>>>>  2
idUser :  0  counterG  2
>>>>  3
idUser :  0  counterG  3
>>>>  4
idUser :  0  counterG  4
>>>>  5
Warning: got packets out of order. Expected 1 but received 0
*************** ERROR  4031 

[gayratv]

I solve this problem with this wrap:

export async function retriableQuery(sql: string, values?: any | any[] | { [param: string]: any }) {
  let retry = false;
  let retryCount = 0;
  let data;
  let delayTime = 100;
  do {
    retry = false;

    try {
      const [dataS] = await pool.query(sql, values);
      data = dataS;
    } catch (err) {
      if ([4031, -4077].includes(err?.errno)) {
        // code: 'ECONNRESET', errno: -4077,
        retry = true;
        retryCount++;
        log.error(err.errno, err.code, ' retryCount ', retryCount);
        await delay(delayTime);
        delayTime = delayTime * 2;
      } else {
        log.error(err);
        throw err;
      }
    }
  } while (retry && retryCount < 5);
  if (retryCount >= 5) {
    throw new Error('MYSQL превышено максимальное количество retry :5');
  } else return data;
}

[komanton]

MySQL server uses TCP protocol to interact with its client. So, when client does not interact with the server long period of time(see WAIT_TIMEOUT config of MySQL, you can change it), the server can decide to disconnect their clients (see WAIT_TIMEOUT config of MySQL). But to be absolutely sure that MySQL does not disconnect it's client you need to enable keepAlive option on TCP connection from client side.

But I do not recommend having long running connection in production. It would be better to close them automatically from time to time if they are in idle.

So, this set of options for the mysql2 connection pool should help you to make 'balanced' communication in your specific case with your MySQL server:

maxIdle: 0,
idleTimeout: 60000,
enableKeepAlive: true,

The main idea behind this configuration is the idleTimeout should much-much less than MySQL server side option WAIT_TIMEOUT. So, with this idea, all idle connections in pool will be closed from client side automatically by connection pool before MySQL server will decide close them from server side.

And, keepAliveInitialDelay should have default value (i.e. much-much less than idleTimeout). It will allow client to send keep-alive packets during idleTimeout until connection closing from client side.