Warning: got packets out of order. Expected 1 but received 28

[gajus]

I am trying to create a MySQL server using node-mysql2, but I am getting the following errors:

Warning: got packets out of order. Expected 1 but received 28
Warning: got packets out of order. Expected 2 but received 29
Warning: got packets out of order. Expected 3 but received 30
Warning: got packets out of order. Expected 4 but received 31
Warning: got packets out of order. Expected 5 but received 32
[ [ TextRow { '1': 1 } ],
  [ { catalog: 'def',
      schema: '',
      name: '1',
      orgName: '',
      table: '',
      orgTable: '',
      characterSet: 63,
      columnLength: 1,
      columnType: 8,
      flags: 129,
      decimals: 0 } ] ]

This is the server initialization code, https://github.com/gajus/seeql/blob/fb615713a6fe3b856c543d2dd5e27286ba3e665a/src/index.js#L135-L142.

connection.serverHandshake({
  capabilityFlags: 0xffffff,
  characterSet: 8,
  connectionId: connectionId++,
  protocolVersion: 10,
  serverVersion: '5.6.10',
  statusFlags: 2
});

What am I missing?

Using Node.js v7.7.1.

[sidorares]

I think it's an issue with mysql2 server implementation. Need to add proper sequence id handling server side. It's a bit tricky, you increment it with each packet but reset at the start of command

[trevorr]

I'm having the same issue, with lots of these messages being displayed:

Warning: got packets out of order. Expected 6 but received 2
Warning: got packets out of order. Expected 7 but received 3
Warning: got packets out of order. Expected 8 but received 4
Warning: got packets out of order. Expected 9 but received 5
Warning: got packets out of order. Expected 10 but received 6
Warning: got packets out of order. Expected 11 but received 3
Warning: got packets out of order. Expected 12 but received 4
Warning: got packets out of order. Expected 13 but received 5
Warning: got packets out of order. Expected 14 but received 6
Warning: got packets out of order. Expected 15 but received 7
Warning: got packets out of order. Expected 16 but received 4
Warning: got packets out of order. Expected 17 but received 5
Warning: got packets out of order. Expected 18 but received 6
Warning: got packets out of order. Expected 19 but received 7
Warning: got packets out of order. Expected 20 but received 8
Warning: got packets out of order. Expected 21 but received 5
Warning: got packets out of order. Expected 22 but received 6
Warning: got packets out of order. Expected 23 but received 7
Warning: got packets out of order. Expected 24 but received 8
Warning: got packets out of order. Expected 25 but received 9
Warning: got packets out of order. Expected 26 but received 6
Warning: got packets out of order. Expected 27 but received 7
Warning: got packets out of order. Expected 28 but received 8
Warning: got packets out of order. Expected 29 but received 9
Warning: got packets out of order. Expected 30 but received 10
Warning: got packets out of order. Expected 31 but received 7
Warning: got packets out of order. Expected 32 but received 8
Warning: got packets out of order. Expected 33 but received 9
Warning: got packets out of order. Expected 34 but received 10

I got 591 of these from streaming back 601874 records.

[einsqing]

how to resolve it?

[sidorares]

@einsqing is there any way to make example I can reproduce? Is it coming from client or using mysql2.createServer() ?

[einsqing]

@sidorares
server.js

const mysql = require('mysql2');
const auth = require('mysql2/lib/auth_41.js');
function authenticate(params, cb) {
    console.log(params);
    const doubleSha = auth.doubleSha1('xxx');
    const isValid = auth.verifyToken(
        params.authPluginData1,
        params.authPluginData2,
        params.authToken,
        doubleSha
    );
    if (isValid) {
        cb(null);
    } else {
        // for list of codes lib/constants/errors.js
        cb(null, { message: 'wrong password dude', code: 1045 });
    }
}

const server = mysql.createServer();

server.on('connection', conn => {
    console.log(conn);

    conn.serverHandshake({
        protocolVersion: 10,
        serverVersion: 'node.js rocks',
        connectionId: 0,
        statusFlags: 2,
        characterSet: 0,
        capabilityFlags: 0xffffff,
        authCallback: authenticate
    });

    conn.on('field_list', (table, fields) => {
        console.log('field list:', table, fields);
        conn.writeEof();
    });

    const remote = mysql.createConnection({ user: 'xxx', database: 'xxx', host: 'xxx', password: 'xxx' });

    conn.on('query', sql => {
        console.log(`proxying query: ${sql}`);
        remote.query(sql, function (err) {
            // overloaded args, either (err, result :object)
            // or (err, rows :array, columns :array)
            if (Array.isArray(arguments[1])) {
                // response to a 'select', 'show' or similar
                const rows = arguments[1], columns = arguments[2];
                conn.writeTextResult(rows, columns);
            } else {
                // response to an 'insert', 'update' or 'delete'
                const result = arguments[1];
                conn.writeOk(result);
            }
        });
    });

    conn.on('end', remote.end.bind(remote));
});

// 捕获未知错误
process.on("uncaughtException", err => {
    console.log(err);
});

server.listen(3306);

client.js

const knex = require('knex')({
    client: 'mysql2',
    connection: {
        host: 'localhost',
        port: 3306,
        user: 'xxx',
        password: 'xxx',
        database: 'xxx'
    }
});
knex("ads").select().limit(2).then(console.log)

[haganbt]

I am seeing the same behavior using the documented example proxy and an example client request such as listed above by @einsqing.

Warning: got packets out of order. Expected 1 but received 4
Warning: got packets out of order. Expected 2 but received 5
...

Please let me know if there is further testing I can do or examples I can provide.

Mysql server v5.7.
Mysql2 v2.1.0

[fjeddy]

Experiencing the same issue.

[Zorono]

the same here but with different packets...
Warning: got packets out of order. Expected 9 but received 0

[karlosgaldino]

Passei a receber esse erro hoje. Um Ano se passou e ninguém soube como resolver?

[iradofurioso]

same...

Warning: got packets out of order. Expected 0 but received 1
Error trying to connect to Amazon RDS

[haganbt]

The packets need to be reset at various points (found by trial and error!). I'll try and post an example ASAP. FYI, my experience has only been with a proxy use case.

[iradofurioso]

Just update to latest NODE version and BAG!! 14.17.5

[nathan-mhk]

Just update to latest NODE version and BAG!! 14.17.5

Am using 14.17.6 and got this error. My script uploads a large amount of local data in batch to a local MySQL server that is running in a docker container.

Edit:
More info:
The data I put into the INSERT statement was huge. I occasionally get Got a packet bigger than 'max_allowed_packet' bytes. I was using a connection pool with connectionLimit set to 10.

const mysql = require('mysql2')

const pool = mysql.createPool({
    connectionLimit: 10,
    ...
})

pool.query(
    'INSERT INTO table_name (col1, col2, ...) VALUES ?',
    /* Data in a 3d array (very large). val1, val2, etc are either strings or null. */
    [[[val1, val2], [val1, val2], ...]],
    ...
)

In my case this issue rarely happens. Only 8 times out of the 130,000 pool.query() calls. In some cases, I got both got packets out of order and got a packet bigger than for the same pool.query() call.

[cctv1005s]

Is there anybody has resolved this problem?

[Isaac-jairi]

Same problem here

[JensVanhulst]

@gajus How did you fix it. I currently have the same issue.

[gajus]

Sorry, it was 3 years ago – I don't recall the outcome. I typically follow up if I do find a solution though. I am guessing I moved to other solving problems.

[JensVanhulst]

@gajus Understandable. This issue is driving me crazy. At this point i'm thinking to move to another package.

With me it is only occuring when I run it in a docker container. When I run barebone node everything works.

[bttger]

Has anyone yet noticed that it leads to unusual behavior or even errors? Or is it just a warning that you can ignore?

[JensVanhulst]

@bttger I use this package with a feathersJS backend and FeathersVuex frontend.

This error causes feathers to error in "cannot read property parse of undefined". And no realtime updates are issued. So in my case it broke my whole app.

I rolled back to v2.2.4 and it seems to be fixed.

[haganbt]

I had this issue many month ago when using mysql2 for a project. I ended up having to manually reset the sequencId's (through trial and error and packet analysis). Ive done my best to extract the important parts of the code where these need to be set. In my example is was using a proxy which requires a server, so this should be relevant for both.

The snippet below will not execute, its just for illustrative purposes. Summary of where to set sequenceId:

1. After successful auth handshake: conn.sequenceId = 0
2. On ping: conn.sequenceId = 0
3. Before query: conn.sequenceId = 1
4. After query: conn.sequenceId = 0

I am no expert but after seeing so may people still have this issue, hopefully it helps :)

  const server = mysql.createServer()

  server.listen(4000, '0.0.0.0', () => {
    logger.info('proxy accepts new connections on port ' + 4000)
  })

  server.on('connection', async conn => {
    

    conn.serverHandshake({
      protocolVersion: 10,
      serverVersion: '5.7',
      connectionId: connectionId++,
      statusFlags: 2,
      characterSet: 8,
      capabilityFlags: 0xffffff ^ ClientFlags.SSL,
      authCallback: async data => {
        
        try {
         
         ...

          remoteConn = await proxy.createConnection(cache)

          remoteConn.connect(err => {
            if (err) {
              if (err.message === TOO_MANY_USER_CONNECTIONS) {
                return conn.writeError({
                  message: TOO_MANY_USER_CONNECTIONS,
                  code: ER_TOO_MANY_USER_CONNECTIONS,
                })
              }

              conn.writeError(err)
              return
            }

            conn.writeOk()
               
            conn.sequenceId = 0
          })
        } catch (err) {
          logger.error(err)
          
          conn.writeError(err)
        }
      
    },
  })

  conn.on('ping', async () => {
    conn.writeOk()

    conn.sequenceId = 0
  })

  conn.on('query', async sql => {
    try {
      logger.debug(sql)

      conn.sequenceId = 1

      const {
        data: [results, fields],
        isDql,
      } = await proxy.onQuery(sql, remoteConn, cacheId)

      ...
      
    } catch (error) {
      logger.error(error)

      
    } finally {
      
      conn.sequenceId = 0
    }
  })

[minustime]

I see these errors when the connection thread Time shown via show processlist; expires.

I updated the wait_timeout and interactive_timeout values from the default 8 hours to 60 seconds in my db and I can replicate the issue consistently: My app makes a couple of queries to the db, the processes go to Sleep, the value in the Time column counts down to 1 and the thread disappears.

Warning: got packets out of order. Expected 15 but received 0
Warning: got packets out of order. Expected 12 but received 0
Warning: got packets out of order. Expected 15 but received 0

v16.1.0
MySQL v8.0.27
mysql2 v2.3.3

[Luxiorawa]

Still have exactly the same problem. My logs keep getting spammed of this at every request i make, even when i'm just starting the project. I use connection pool, and just execute queries on connection.

Node 14.15.5
MySQL v8.0.28
mysql2 v2.3.3

Tried downgrading / upgrading :

• Node version
• MySQL
• mysql2 package

But it didn't changed anything.

Note that my database is hosted on AWS RDS, maybe it matters.

Not sure what is the problem, but to be frank, i don't really want to manage this by myself, either by handling myself sequenceId, or anything else.

Is there a meaning to logging this without user knowing ? Since it's a warning, and i have the issue since a long time, it doesn't seems to be anything so serious. Maybe add an option to enable / disable logs like this on the package end ?

Any news on this @sidorares ?

[dietrichg]

Getting this problem in production on mysql 8, mysql2 package (was also getting on mysql package). Perhaps the problem is mysql8.

[pierogiDev]

I also got the same error. Is the cause of this error in node-mysql2 or in mysql itself? Why is this problem left unattended?

[jchapelle]

Can you fix this ?

[feightwywx]

Getting the same error. My environment:

• Node.js v16.17.0
• mysql2 v2.3.3
• MySQL v8.0.30

and the minimal reproduction code:

const conn = await createConnection({
    host: 'localhost',
    user: 'feightwywx',
    password: '',
    database: 'some_database'
});
const [rows, fields] = await conn.execute(myQueryString);
conn.destroy();

I've tried setting max_allowed_packet to 16M, but no effect.

However, since I started to use createPool() instead of createConnection(), and replaced conn.destroy() with conn.release(), it never happens.

Update: For createConnection(), conn.end() also seems ok. Confirmed that it's conn.destroy() that caused the problem in my condition.

[yogithesymbian]

i have same issue when checking a winlogger. but i only have 2 warning.

2022-10-13T19:04:29: Warning: got packets out of order. Expected 2 but received 1
2022-10-13T19:06:00: Warning: got packets out of order. Expected 2 but received 1