Warning: got packets out of order

[BrandonNoad]

Hi,

I'm trying to combine the node-mysql2 and ssh2 modules so that I can connect to the database via ssh. Something similar to this:

https://stackoverflow.com/questions/30658906/trouble-connecting-to-mysql-via-ssh/30669454#30669454

Everything is working as expected. However, if I try to run several queries concurrently, then I get a got packets out of order warning and eventually an error.

Here is some code that will reproduce the issue:

db.js

'use strict';

const Bluebird = require('bluebird');
const Mysql = require('mysql2/promise');

module.exports = function (stream) {

    const connectionOptions = {
        host: 'localhost',
        user: 'root',
        password: 'YOUR_PASSWORD',
        database: 'YOUR_DB',
        connectionLimit: 10,
        dateStrings: true,
        decimalNumbers: true,
        timezone: 'Z',
        supportBigNumbers: true,
        stream: stream,
        Promise: Bluebird
    };

    const pool = Mysql.createPool(connectionOptions);

    function getSqlConnection() {

        return pool.getConnection().disposer(function (connection) {

            return connection.release();
        });
    }

    return {
        query(sqlString, values) {

            return Bluebird.using(getSqlConnection(), function (connection) {

                return connection.query(sqlString, values);
            });
        },
        closeAllConections() {

            return pool.end(function (err) {

                if (err) {
                    console.log(err);
                }
            });
        }
    };
};

index.js

'use strict';

const _ = require('lodash');
const Bluebird = require('bluebird');
const SshClient = require('ssh2').Client;

const ssh = new SshClient();

function runDbQueries() {

    return new Bluebird(function (resolve, reject) {

        ssh.on('ready', function () {

            ssh.forwardOut(
                '127.0.0.1',
                12345,
                '127.0.0.1',
                3306,
                function (err, stream) {

                    if (err) {
                        return reject(err);
                    }

                    const Db = require('./db.js')(stream);

                    const promises = _.map(_.range(5), function (n) {

                        return Db.query('Select * from `users`', []);
                    });

                    return resolve(Bluebird.all(promises));                            
                }
            );
        });

        ssh.connect({
            host: '127.0.0.1',
            username: 'vagrant',
            agent: process.env.SSH_AUTH_SOCK
        });
    });
}

runDbQueries()
    .then(function (results) {

        console.log(results);
    })
    .catch(function (error) {

        console.log(error);
    });

If I run the queries in series, then the code works just fine. Also, I have similar code running without the ssh2 requirement, and the concurrent queries run without problems there.

Any help would be appreciated. Thanks.

[sidorares]

hi @BrandonNoad

If you use pool you must pass a 'stream factory', not a stream so that each new mysql connection can get it's own stream. It's mentioned in the docs - https://github.com/sidorares/node-mysql2/blob/master/documentation/Extras.md#connecting-using-custom-stream

Signature is 'function that accepts (error, newStream) callback

'got packets out of order' happens during handhsake when driver sends hello packet to existing opened connection

[BrandonNoad]

Hi @sidorares

I tried using a function for the stream property, but it seems that the function's first parameter is an object (with a config property) and not a callback. I pulled this out of the source code:

// if stream is a function, treat it as "stream agent / factory"
if (typeof opts.config.stream == 'function') {
  this.stream = opts.config.stream(opts);
}

Also in this issue comment you mentioned that you need to "pass a function which returns stream".

If that's the case, I'm not sure how to use pool with the ssh2 module. Have you seen any examples of ssh2 and pool being used together?

Thanks!

[sidorares]

hm, looks like you are right. Need to either update documentation or code

At the moment this 'stream factory' function is synchronous. As a work around if you need to get your connection asynchronously you can return empty stream first and then later connect new ssh2 connection to it when you get it. Example code ( not tested, will try tomorrow: )

     const  = require('pump');
     function getStream() {
         const s = new Stream();
            ssh.forwardOut(
                '127.0.0.1',
                12345,
                '127.0.0.1',
                3306,
                function (err, stream) {
                    pump(s, stream);
                    pump(stream, s);             
                }
            );

       return s;
    }

    const connectionOptions = {
        host: 'localhost',
        user: 'root',
        password: 'YOUR_PASSWORD',
        database: 'YOUR_DB',
        connectionLimit: 10,
        dateStrings: true,
        decimalNumbers: true,
        timezone: 'Z',
        supportBigNumbers: true,
        stream: getStream,
        Promise: Bluebird
    };

[BrandonNoad]

Thanks. I will try your suggestion.

Ideally the stream factory would be async with a callback so we could just do the following:

function getStream(cb) {

    ssh.forwardOut(
        '127.0.0.1',
        12345,
        '127.0.0.1',
        3306,
        cb
    );
}

[BrandonNoad]

I tried this:

const connectionOptions = {
    host: config.db.host,
    user: config.db.user,
    password: config.db.password,
    database: config.db.database,
    connectionLimit: 10,
    dateStrings: true,
    decimalNumbers: true,
    timezone: 'Z',
    supportBigNumbers: true,
    stream: function (opts) {

        const newStream = require('duplex')();

        ssh.forwardOut(
            '127.0.0.1',
            12345,
            config.ssh.destinationIp,
            3306,
            function (err, stream) {

                if (err) {
                   throw err;
                }

                // pipe streams together
                Pump(newStream, stream);
                Pump(stream, newStream);
            }
        );

        return newStream;
    },
    Promise: Bluebird
};

But I'm getting an Error: connect ETIMEDOUT.

[sidorares]

looks like it's not connecting streams properly What we want is that when there is 'data' event on ssh stream same event is triggered on newStream and vice versa. I wander if two Pump() calls actually achieve that.

Can you try to connect manually?

// Pump(newStream, stream);
// Pump(stream, newStream);
stream.on('data', data => {
  console.log('got data from ssh side: ', data);
  newStream.write(data);
})

// not sure it's right thing to do for duplex stream, basically we want to intercept what is passed to newStream.write()
newStream.on('data', data => {
  console.log('got data from client side: ', data);
  stream.write(data);
})

[BrandonNoad]

When I try to connect them manually using the code you posted, I get the following output:

got data from ssh side: ...
Error: connect ETIMEDOUT

I don't know enough about streams to try and get it working that way.

I tried using the merge-stream package (https://www.npmjs.com/package/merge-stream):

const connectionOptions = {
    host: config.db.host,
    user: config.db.user,
    password: config.db.password,
    database: config.db.database,
    connectionLimit: 10,
    dateStrings: true,
    decimalNumbers: true,
    timezone: 'Z',
    supportBigNumbers: true,
    stream: function (opts) {

        const newStream = require('merge-stream')();

        ssh.forwardOut(
            '127.0.0.1',
            12345,
            config.ssh.destinationIp,
            3306,
            function (err, stream) {

                if (err) {
                   throw err;
                }

                newStream.add(stream);
            }
        );

        return newStream;
    },
    Promise: Bluebird
};

But I get the "got packets out of order" warning again:

Warning: got packets out of order. Expected 2 but received 1
Error: Unexpected packet during handshake phase

[gladchinda]

Any progress on this issue yet? I'm having a similar challenge in a project I'm working on recently. I am trying to use pool over an SSH connection to a remote server but I am getting ER_NET_PACKETS_OUT_OF_ORDER error. However, it works when I just use a single connection.

[gladchinda]

Hello @BrandonNoad and @sidorares I've got a workaround for this error that works for me. I'm not sure if it applies to your setup but you may as well try it out. So here is what I did:

• First, I created an SSH connection to the remote service.
• Then, I create the pool when the SSH connection is ready using the stream from the connection.

Here is what the implementation looks like:

const mysql = require('mysql2');
const Client = require('ssh2').Client;
const DB_OPTIONS = require('./db-config');
const SSH_OPTIONS = require('./ssh-config');

let pool = null;
let database_ssh = null;

const ssh = new Client();

const createPool = (stream) => {
  pool || (pool = mysql.createPool(Object.assign(DB_OPTIONS, { stream })))
  return pool;
}

const databaseSSH = () => new Promise((resolve, reject) => {
  ssh.on('ready', () => {
    ssh.forwardOut(
      // source address, this can usually be any valid address
      '127.0.0.1',
      // source port, this can be any valid port number
      12345,
      // destination address (localhost here refers to the SSH server)
      'remote.host.server.com',
      // destination port
      3306,
      (err, stream) => {
        if (err) return reject(err);

        try {
          const pool = createPool(stream);
          return resolve(pool);
        } catch (e) {
          return reject(e);
        }
      });
  }).connect(SSH_OPTIONS);
})

const getDatabaseSSH = () => {
  if (database_ssh) return Promise.resolve(database_ssh);

  return databaseSSH()
    .then(connection => {
      database_ssh = connection;
      return database_ssh;
    })
    .catch(err => {
      database_ssh = null;
      throw err;
    });
}

module.exports = {
  createPool,
  databaseSSH,
  getDatabaseSSH
};

[BrandonNoad]

I ended up using a single connection because my use case was fairly trivial. That said, my implementation is similar to yours.

db.js

'use strict';

const Mysql = require('mysql2/promise');

module.exports = (config, stream) => {

    // https://github.com/mysqljs/mysql#connection-options
    const connectionOptions = {
        host: config.host,
        user: config.user,
        password: config.password,
        database: config.database,
        stream: stream
    };

    const connectionPromise = Mysql.createConnection(connectionOptions);

    return {
        query(sqlString, values) {

            return connectionPromise
                .then((connection) => connection.query(sqlString, values));
        },
        end() {

            return connectionPromise
                .then((connection) => connection.end());
        }
    };
};

db-helper.js

'use strict';

const SshClient = require('ssh2').Client;

exports.runDbQueries = (config) => new Promise((resolve, reject) => {

    const ssh = new SshClient();

    ssh.on('ready', () => {

        // Open a connection b/w the host machine and the MySQL host.
        // The srcIP and srcPort can be any value since they aren't actually used for anything.
        ssh.forwardOut(
            '127.0.0.1',
            12345,
            config.ssh.destinationIp,
            3306,
            function (err, stream) {

                if (err) {
                    return reject(err);
                }

                const Db = require('./db.js')(config.db, stream);

                return resolve(
                    Db.query(SQL_STRING, PLACEHOLDER_VALUES)                    
                        .finally(() => {

                            // Close db and ssh connections regardless of success/fail.
                            Db.end();
                            ssh.end();
                    })
                );
            }
        );
    });

    // Connect to a server that can access the MySQL host.
    ssh.connect({
        host: config.ssh.host,
        port: config.ssh.port,
        username: config.ssh.username
    });    
});

[hjzheng]

I have the same problem, any updates?

[hjzheng]

maybe you can try this: https://www.npmjs.com/package/@jxz/ssh2mysql

[lxjwlt]

in my case, my local machine connect to remote datebase by ssh tunnel in development environment my solution is make a "ssh-stream-pool" to get through this issues:

class Sql {
  constructor(props) {
    this.initSSHPromise = this.initSSH(props);
    this.connectionInitPromise = this.setConnection(props.dbConfig);
  }
  async initSSH(props) {
    if (!props.ssh) {
      return;
    }
    this.sshConfig = props.ssh;
    this.ssh = new Client();
    this.ssh.connect({
      host: "xxx",
      port: "xxx",
      username: "xxx",
      password: "xxx",
    });

    // ssh pool
    this.SSHStreamList = await Promise.all(
      [...Array(20)].map(() => this.getSSHStream())
    );
  }
  getSSHStream() {
    return new Promise((resolve, reject) => {
      this.ssh.once('ready', () => {
        this.ssh.forwardOut(
          "xxx",
          "xxx",
          "xxx",
          "xxx",
          (err, stream) => {
            resolve(stream);
          });
      });
    });
  }
  async setConnection(dbConfig = {}) {
    await this.initSSHPromise;
    this.pool = poolMap[this.poolMd5] = mysql.createPool({
      ...dbConfig,
      connectionLimit: 10,
      stream: this.ssh ? () => {
        // make a new one ssh
        this.getSSHStream().then(stream => {
          this.SSHStreamList.push(stream);
        })

        // use ssh
        return this.SSHStreamList.shift();
      } : null
    });

     /**
       * updated 2020-12-17:  init connections immediately, otherwise connection created later would pedding forever.
       */
      if (this.ssh) {
        return new Promise<void>(resolve => {
          let count = 0
          for (let i = 0; i < connectionLimit; i++) {
            this.pool.getConnection((e: Error, connection: any) => {
              if (!e) {
                connection.release();
              }

              count += 1
              if (count >= connectionLimit) {
                resolve()
              }
            })
          }
        })
      }
  }
}

// use case 
new Sql({
  ssh: __DEV__ ? {
    // ...
  } : null
})

[psnfrench]

I am having this issue also. Would love to see a resolution.

[adminphdcommy]

Having the same issue too.

[TobiTenno]

connectionLimit

@lxjwlt did this work for you? i'm looking at implementing something to work for mysql2-ssh or just for myself, but not found anything that works quite right yet.

[dietrichg]

Getting this error still. I had my localhost sitting for about 12 hours and it randomly go this error.

[jchapelle]

I encounter the same error. I configured a connectionLimit of 40.

'got packets out of order' happens during handhsake when driver sends hello packet to existing opened connection

What does that means ? The driver sends hello packet to a connection which is used by somebody else ? How to avoid this ?

[misa198]

I also get this error, it happens randomly, maybe a few hours or a few days. I also tried mysqljs/mysql and the error still occurs.

[flowluap]

Up