Error: Packets out of order. Got: 0 Expected: 3

Question

I'm using the MySQL module for NodeJS, however I keep getting the following error after approximately 10 requests to the database. I'm using the following code to connect to the DB:

var con = mysql.createConnection({
    host: "10.37.100.15",
    user: process.env.MYSQLUSR,
    password: process.env.MYSQLPASSWD,
    database: process.env.MYSQLDB
});

Then I use con.query(sqlStatement, function (err, result, fields) {<function>}). I already tried changing the max_allowed_packet to 500M on the server but this didn't fix the issue. This is the full error:

events.js:377
      throw er; // Unhandled 'error' event
      ^

Error: Packets out of order. Got: 0 Expected: 3
    at Parser._tryReadPacketHeader (/app/node_modules/mysql/lib/protocol/Parser.js:470:15)
    at Parser.write (/app/node_modules/mysql/lib/protocol/Parser.js:33:29)
    at Protocol.write (/app/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
Emitted 'error' event on Connection instance at:
    at Connection._handleProtocolError (/app/node_modules/mysql/lib/Connection.js:423:8)
    at Protocol.emit (events.js:400:28)
    at Protocol._delegateError (/app/node_modules/mysql/lib/protocol/Protocol.js:398:10)
    at Protocol.handleParserError (/app/node_modules/mysql/lib/protocol/Protocol.js:380:10)
    at Parser._tryReadPacketHeader (/app/node_modules/mysql/lib/protocol/Parser.js:478:10)
    at Parser.write (/app/node_modules/mysql/lib/protocol/Parser.js:33:29)
    [... lines matching original stack trace ...]
    at Socket.emit (events.js:400:28) {
  code: 'PROTOCOL_PACKETS_OUT_OF_ORDER',
  fatal: true
}

Answer 1

default wait_timeout is 8 hrs.

I'd suspect the mysql-nodejs version isn't resetting the expected packet count on re-connection.

In a test environment you can reduce the wait_timeout.

It quite possibly is:

• https://github.com/mysqljs/mysql/issues/2534
• https://github.com/mysqljs/mysql/issues/2451

Last comment on #2534 is "I change my connection from mysql.createConnection to mysql.createPool, the error never happen again"

Answer 2

I had the same issue with my backend strapi & frontend MySQL server. What I did is put wait_timeout on the MySQL cnf file to 5 seconds.

I removed that wait_timeout and the issue was resolved.

Answer 3

I had similar error. It seems the SQL connection is dropped by SQL-server when idle for some (e.g. 8h) time.

I have solved it by adding 'pings' in express server:

const con = sql.createConnection(
    {...}

// 'ping' SQL server every hour to keep connection alive
setInterval(() => {
    con.query('SELECT 1');
}, 3600000);

...
app.use();