The backend server sends FIN packets from occasional time
A read ETIMEDOUT error might occur when a client calls the API and connects to Mysql on the backend server
The client cannot do anything because the hold continues
The API connects to the DB in the following ways:
import mysql from "mysql2/promise";
import config from "../config";
import { logger } from "src/config/logger";
const {
host,
slave_1_host,
slave_2_host,
port,
user,
password,
database,
connectionLimit,
replicaConnectionLimit,
} = config.mysql;
let pool: mysql.Pool;
let poolCluster: mysql.PoolCluster;
setInterval(async () => {
let pool = await poolCluster.of("SLAVE*", "RR");
let conn = await pool.getConnection();
try {
await conn.execute("SELECT 1");
} catch (err: any) {
logger.error("interval select error", err);
throw new Error(err);
} finally {
conn.release();
}
}, 30000);
setInterval(async () => {
let pool = await poolCluster.of("MASTER");
let conn = await pool.getConnection();
try {
await conn.execute("SELECT 1");
} catch (err: any) {
logger.error("interval select error", err);
throw new Error(err);
} finally {
conn.release();
}
}, 30000);
export const init = async () => {
try {
pool = await mysql.createPool({
connectionLimit,
host,
user,
password,
database,
port,
});
poolCluster = await mysql.createPoolCluster();
poolCluster.add("MASTER", {
host,
connectionLimit,
user,
password,
database,
port,
});
poolCluster.add("SLAVE1", {
host: slave_1_host,
user,
password,
database,
port,
connectionLimit: replicaConnectionLimit,
});
poolCluster.add("SLAVE2", {
host: slave_2_host,
user,
password,
database,
port,
connectionLimit: replicaConnectionLimit,
});
console.debug("MySql Adapter Pool generated successfully");
return { pool, poolCluster };
} catch (error: any) {
logger.error("[mysql.connector][init][Error]: ", error);
throw new Error(error);
}
};
export const getConnect = async () => {
return await pool.getConnection();
};
/**
* executes SQL queries in MySQL db
*
* @param {string} query - provide a valid SQL query
* @param {string[] | Object} params - provide the parameterized values used
* in the query
*/
export const execute = async (
query: string,
params?: string[] | Object
): Promise<any> => {
const pool = await poolCluster.of("MASTER");
const conn = await pool.getConnection();
try {
if (!poolCluster) {
throw new Error(
"Pool was not created. Ensure pool is created when running the app."
);
}
const [results] = await conn.execute(query, params);
return results;
} catch (error: any) {
logger.error("[mysql.connector][execute][Error]: ", error);
logger.error(`[query]: ${query}\n[params]: ${params}`);
if (error.message == "read ETIMEDOUT") {
return await execute(query, params);
} else {
throw new Error(error);
}
} finally {
conn.release();
}
};
export const select_execute = async (
query: string,
params?: string[] | Object
): Promise<any> => {
let pool = await poolCluster.of("SLAVE*", "RR");
let conn = await pool.getConnection();
try {
if (!poolCluster) {
throw new Error(
"Pool was not created. Ensure pool is created when running the app."
);
}
const [results] = await conn.execute(query, params);
return results;
} catch (error: any) {
logger.error("[mysql.connector][select_execute][Error]: ", error);
logger.error(`[query]: ${query}\n[params]: ${params}`);
if (error.message == "read ETIMEDOUT") {
return await select_execute(query, params);
} else {
throw new Error(error);
}
} finally {
conn.release();
}
};
export const after_transaction_execute = async (
query,
params
): Promise<any> => {
const conn = await pool.getConnection();
try {
const [results] = await conn.execute(query, params);
return results;
} catch (error: any) {
logger.error("[mysql.connector][execute][Error]: ", error);
logger.error(`[query]: ${query}\n[params]: ${params}`);
if (error.message == "read ETIMEDOUT") {
return await after_transaction_execute(query, params);
} else {
throw new Error(error);
}
} finally {
conn.release();
}
};
This is what logs are written to the server side:
error [request-id: ] : [mysql.connector][select_execute][Error]: read ETIMEDOUT
error [request-id: ] : [query]: real query
[params]: real params
error [request-id: ] : Error: Error: read ETIMEDOUT
at path/mysql.connector.ts:151:13
at Generator.throw (<anonymous>)
at rejected (path/mysql.connector.ts:6:65)
at runMicrotasks (<anonymous>)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
Connection Pool Info:
{
"_events": {},
"_eventsCount": 2,
"connection": {
"_events": {},
"_eventsCount": 1,
"config": {
"host": HOST,
"port": PORT,
"user": USER,
"password": PASSWORD,
"database": DB,
"connectTimeout": 10000,
"insecureAuth": false,
"supportBigNumbers": false,
"bigNumberStrings": false,
"decimalNumbers": false,
"dateStrings": false,
"trace": true,
"stringifyObjects": false,
"enableKeepAlive": true,
"keepAliveInitialDelay": 0,
"timezone": "local",
"pool": {
"_events": {},
"_eventsCount": 0,
"config": {
"waitForConnections": true,
"connectionLimit": 100,
"maxIdle": 100,
"idleTimeout": 60000,
"queueLimit": 0
},
"_allConnections": {
"_head": 3,
"_tail": 0,
"_capacityMask": 3,
"_list": [
null,
null,
null,
{
"_events": {
"error": [
null,
null
]
},
"_eventsCount": 2,
"stream": {
"connecting": false,
"_hadError": false,
"_parent": null,
"_host": HOST
"_closeAfterHandlingError": false,
"_readableState": {
"objectMode": false,
"highWaterMark": 16384,
"buffer": {
"head": null,
"tail": null,
"length": 0
},
"length": 0,
"pipes": [],
"flowing": true,
"ended": false,
"endEmitted": false,
"reading": true,
"constructed": true,
"sync": false,
"needReadable": true,
"emittedReadable": false,
"readableListening": false,
"resumeScheduled": false,
"errorEmitted": false,
"emitClose": false,
"autoDestroy": true,
"destroyed": false,
"errored": null,
"closed": false,
"closeEmitted": false,
"defaultEncoding": "utf8",
"awaitDrainWriters": null,
"multiAwaitDrain": false,
"readingMore": false,
"dataEmitted": true,
"decoder": null,
"encoding": null
},
"_events": {
"end": [
null,
null
]
},
"_eventsCount": 5,
"_writableState": {
"objectMode": false,
"highWaterMark": 16384,
"finalCalled": false,
"needDrain": false,
"ending": false,
"ended": false,
"finished": false,
"destroyed": false,
"decodeStrings": false,
"defaultEncoding": "utf8",
"length": 0,
"writing": false,
"corked": 0,
"sync": false,
"bufferProcessing": false,
"writecb": null,
"writelen": 0,
"afterWriteTickInfo": null,
"buffered": [],
"bufferedIndex": 0,
"allBuffers": true,
"allNoop": true,
"pendingcb": 0,
"constructed": true,
"prefinished": false,
"errorEmitted": false,
"emitClose": false,
"autoDestroy": true,
"errored": null,
"closed": false,
"closeEmitted": false
},
"allowHalfOpen": false,
"_sockname": null,
"_pendingData": null,
"_pendingEncoding": "",
"server": null,
"_server": null
},
"_internalId": 11,
"_commands": {
"_head": 1,
"_tail": 1,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_paused": false,
"_paused_packets": {
"_head": 0,
"_tail": 0,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_statements": {
"ttl": 0,
"ttlResolution": 1,
"ttlAutopurge": false,
"updateAgeOnGet": false,
"updateAgeOnHas": false,
"allowStale": false,
"noDisposeOnSet": false,
"noUpdateTTL": false,
"maxEntrySize": 0,
"noDeleteOnFetchRejection": false,
"noDeleteOnStaleGet": false,
"allowStaleOnFetchAbort": false,
"allowStaleOnFetchRejection": false,
"ignoreFetchAbort": false
},
"serverCapabilityFlags": 2415917055,
"authorized": true,
"sequenceId": 32,
"compressedSequenceId": 0,
"threadId": 39766042,
"_handshakePacket": {
"protocolVersion": 10,
"serverVersion": "8.0.25",
"capabilityFlags": 2415917055,
"connectionId": 39766042,
"authPluginData1": {
"type": "Buffer",
"data": [
85,
11,
72,
69,
100,
10,
70,
17
]
},
"authPluginData2": {
"type": "Buffer",
"data": [
82,
20,
68,
96,
104,
98,
79,
61,
58,
84,
50,
80,
0
]
},
"characterSet": 255,
"statusFlags": 2,
"autPluginName": "mysql_native_password"
},
"_fatalError": null,
"_protocolError": null,
"_outOfOrderPackets": [],
"clientEncoding": "utf8",
"packetParser": {
"buffer": [],
"bufferLength": 0,
"packetHeaderLength": 4,
"headerLen": 0,
"length": 5,
"largePacketParts": [],
"firstPacketSequenceId": 0
},
"serverEncoding": "utf8",
"connectTimeout": null,
"lastActiveTime": 1705619307906,
"connectionId": 39766042
}
]
},
"_freeConnections": {
"_head": 3,
"_tail": 0,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_connectionQueue": {
"_head": 0,
"_tail": 0,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_closed": false
},
"ssl": false,
"multipleStatements": false,
"rowsAsArray": false,
"namedPlaceholders": false,
"typeCast": true,
"maxPacketSize": 0,
"charsetNumber": 224,
"compress": false,
"clientFlags": 12252111,
"connectAttributes": {
"_client_name": "Node-MySQL-2",
"_client_version": "3.6.1"
},
"maxPreparedStatements": 16000
},
"stream": {
"connecting": false,
"_hadError": false,
"_parent": null,
"_host": HOST,
"_closeAfterHandlingError": false,
"_readableState": {
"objectMode": false,
"highWaterMark": 16384,
"buffer": {
"head": null,
"tail": null,
"length": 0
},
"length": 0,
"pipes": [],
"flowing": true,
"ended": false,
"endEmitted": false,
"reading": true,
"constructed": true,
"sync": false,
"needReadable": true,
"emittedReadable": false,
"readableListening": false,
"resumeScheduled": false,
"errorEmitted": true,
"emitClose": false,
"autoDestroy": true,
"destroyed": true,
"errored": {
"errno": -110,
"code": "ETIMEDOUT",
"syscall": "read",
"fatal": true
},
"closed": true,
"closeEmitted": true,
"defaultEncoding": "utf8",
"awaitDrainWriters": null,
"multiAwaitDrain": false,
"readingMore": false,
"dataEmitted": true,
"decoder": null,
"encoding": null
},
"_events": {
"end": [
null,
null
]
},
"_eventsCount": 4,
"_writableState": {
"objectMode": false,
"highWaterMark": 16384,
"finalCalled": false,
"needDrain": false,
"ending": false,
"ended": false,
"finished": false,
"destroyed": true,
"decodeStrings": false,
"defaultEncoding": "utf8",
"length": 0,
"writing": false,
"corked": 0,
"sync": false,
"bufferProcessing": false,
"writecb": null,
"writelen": 0,
"afterWriteTickInfo": null,
"buffered": [],
"bufferedIndex": 0,
"allBuffers": true,
"allNoop": true,
"pendingcb": 0,
"constructed": true,
"prefinished": false,
"errorEmitted": true,
"emitClose": false,
"autoDestroy": true,
"closed": true,
"closeEmitted": true
},
"allowHalfOpen": false,
"_sockname": null,
"_pendingData": null,
"_pendingEncoding": "",
"server": null,
"_server": null
},
"_internalId": 9,
"_commands": {
"_head": 2,
"_tail": 2,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_command": null,
"_paused": false,
"_paused_packets": {
"_head": 0,
"_tail": 0,
"_capacityMask": 3,
"_list": [
null,
null,
null,
null
]
},
"_statements": {
"ttl": 0,
"ttlResolution": 1,
"ttlAutopurge": false,
"updateAgeOnGet": false,
"updateAgeOnHas": false,
"allowStale": false,
"noDisposeOnSet": false,
"noUpdateTTL": false,
"maxEntrySize": 0,
"noDeleteOnFetchRejection": false,
"noDeleteOnStaleGet": false,
"allowStaleOnFetchAbort": false,
"allowStaleOnFetchRejection": false,
"ignoreFetchAbort": false
},
"serverCapabilityFlags": 2415917055,
"authorized": true,
"sequenceId": 1,
"compressedSequenceId": 0,
"threadId": 39757652,
"_handshakePacket": {
"protocolVersion": 10,
"serverVersion": "8.0.25",
"capabilityFlags": 2415917055,
"connectionId": 39757652,
"authPluginData1": {
"type": "Buffer",
"data": [
16,
11,
125,
55,
63,
9,
93,
45
]
},
"authPluginData2": {
"type": "Buffer",
"data": [
106,
117,
66,
81,
13,
45,
32,
118,
98,
48,
6,
10,
0
]
},
"characterSet": 255,
"statusFlags": 2,
"autPluginName": "mysql_native_password"
},
"_protocolError": null,
"_outOfOrderPackets": [],
"clientEncoding": "utf8",
"packetParser": {
"buffer": [],
"bufferLength": 0,
"packetHeaderLength": 4,
"headerLen": 0,
"length": 5,
"largePacketParts": [],
"firstPacketSequenceId": 0
},
"serverEncoding": "utf8",
"connectTimeout": null,
"_pool": null,
"lastActiveTime": 1705611494577,
"connectionId": 39757652,
"_closing": true
}
}
The last log recorded in the DB by the error process is as follows:
timestamp log_content
GENERAL_LOG
24-01-19 08:57:50 (UTC+09:00) 2024-01-19T08:57:50.233706+09:00 39766042 Execute [SELECT]
ERROR_LOG
2024-01-19 09:22:27 (UTC+09:00) 2024-01-19T09:22:20.890502+09:00 39766042 [Note] [MY-010914] [Server] Aborted connection 39766042 to db: DB user: USER host: HOST (Got an error reading communication packets).
DB settings are as follows:
connect_timeout - 10
max_connections - 3000
wait_timeout - 28800
Network Packets:
I don't know what the problem is.
It was set up by my predecessor, but I'm at a loss because my predecessor couldn't solve it and left the company.