[SOLVED] MySQL Packets out of order. Got: 0 Expected: 2

MySQL Packets out of order. Got: 0 Expected: 2

I have 3 nodejs express applications running in pm2 using different databases each and two of them keep getting the error "Packets out of order. Got: 0 Expected: 2.". There are getting it both at the same time. I'm using pools with a connectionLimit of 50 in each of the application. This is how the configuration is made:

this.dbPool = mysql.createPool({ host: "localhost", user: "apiuser", password: "", database: "", debug: false, connectionLimit: 50, supportBigNumbers: true, bigNumberStrings: true, charset : 'utf8mb4' })

The issue is not coming after a specific event or query. After a long time inactivity (8-10 hours) the apps do crash with this error.

It was suggested to increase the max_connection and the max_allowed_packet values on mysql, which I did on the mysql.cnf file before restarting the mysql server. I already checked by querying SHOW VARIABLES LIKE ... and the values are shown as set in the mysql.cnf file:

max_connections=1024
max_allowed_packet=500M

I have also changed the group_concat_max_len group_concat_max_len=50000

However these changes haven't fixed the issue for now.

I am using the following mysql library version: "mysql": "^2.18.1" MySQL Version is: mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

What could be the issue here? Is it a query that I'm doing or any configuration issue? Or version mismatch between mysql and the library maybe? Unfortunately I'm not able to show you all the queries that I'm doing, since the API has around 80 endpoints and around 150 different queries.

Would love to hear any suggestion to solve this.

Stacktrace of the error:

Error: Packets out of order. Got: 0 Expected: 2
    at Parser._tryReadPacketHeader (/home/apis/node_modules/mysql/lib/protocol/Parser.js:470:15)
    at Parser.write (/home/apis/node_modules/mysql/lib/protocol/Parser.js:33:29)
    at Protocol.write (/home/apis/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/apis/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/apis/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:520:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'PROTOCOL_PACKETS_OUT_OF_ORDER',
  fatal: true
}

Solution

It seems like, that it was because of a not closed connection, like mentioned in my comment before. So if you have this error, just check for not closed connections retrieved for an transaction... I've just overseen it, since this was a startup script that made initial queries ensuring required data is present for the api.