Cannot enqueue Handshake after invoking quit

Question

I have implemented the following code:

module.exports = {
    getDataFromUserGps: function(callback)
    {
        connection.connect();
        connection.query("SELECT * FROM usergps", 
            function(err, results, fields) {
                if (err) return callback(err, null);
                return callback(null, results);
            }
        ); 
        connection.end();
    },
    loginUser: function(login, pass, callback)
    {
        connection.connect();
        connection.query(
            "SELECT id FROM users WHERE login = ? AND pass = ?",
            [login, pass],
            function(err, results, fields) 
            {
                if (err) return callback(err, null);
                return callback(null, results);
            }
        ); 
        connection.end();
    },
    getUserDetails: function(userid, callback)
    {
        connection.connect();
        connection.query(
            "SELECT * FROM userProfilDetails LEFT JOIN tags ON userProfilDetails.userId = tags.userId WHERE userProfilDetails.userid = ?",
            [userid],
            function(err, results, fields)
            {
                if (err) return callback(err, null);
                return callback(null, results);
            }
        );
        connection.end();
    },
    addTags: function(userId, tags)
    {
        connection.connect();
        connection.query(
            "INSERT INTO tag (userId, tag) VALUES (?, ?)",
            [userId, tags],
            function(err, results, fields)
            {
                if (err) throw err;
            }
        )
        connection.end();
    }
}

Everything works great only for the first time. If I want to "use" the query for the second time I get the following error:

Cannot enqueue Handshake after invoking quit

I have tried not to .end() connections but it didn't help.

How can I fix this issue?

Answer 1

If you using the node-mysql module, just remove the .connect and .end. Just solved the problem myself. Apparently they pushed in unnecessary code in their last iteration that is also bugged. You don't need to connect if you have already ran the createConnection call

Answer 2

According to:

• Fixing Node Mysql "Error: Cannot enqueue Handshake after invoking quit.":

  http://codetheory.in/fixing-node-mysql-error-cannot-enqueue-handshake-after-invoking-quit/

TL;DR You need to establish a new connection by calling the createConnection method after every disconnection.

and

Note: If you're serving web requests, then you shouldn't be ending connections on every request. Just create a connection on server startup and use the connection/client object to query all the time. You can listen on the error event to handle server disconnection and for reconnecting purposes. Full code here.

---

From:

• Readme.md - Server disconnects:

  https://github.com/felixge/node-mysql#server-disconnects

It says:

Server disconnects

You may lose the connection to a MySQL server due to network problems, the server timing you out, or the server crashing. All of these events are considered fatal errors, and will have the err.code = 'PROTOCOL_CONNECTION_LOST'. See the Error Handling section for more information.

The best way to handle such unexpected disconnects is shown below:

function handleDisconnect(connection) {
  connection.on('error', function(err) {
    if (!err.fatal) {
      return;
    }

    if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
      throw err;
    }

    console.log('Re-connecting lost connection: ' + err.stack);

    connection = mysql.createConnection(connection.config);
    handleDisconnect(connection);
    connection.connect();
  });
}

handleDisconnect(connection);

As you can see in the example above, re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

With Pool, disconnected connections will be removed from the pool freeing up space for a new connection to be created on the next getConnection call.

---

I have tweaked the function such that every time a connection is needed, an initializer function adds the handlers automatically:

function initializeConnection(config) {
    function addDisconnectHandler(connection) {
        connection.on("error", function (error) {
            if (error instanceof Error) {
                if (error.code === "PROTOCOL_CONNECTION_LOST") {
                    console.error(error.stack);
                    console.log("Lost connection. Reconnecting...");

                    initializeConnection(connection.config);
                } else if (error.fatal) {
                    throw error;
                }
            }
        });
    }

    var connection = mysql.createConnection(config);

    // Add handlers.
    addDisconnectHandler(connection);

    connection.connect();
    return connection;
}

Initializing a connection:

var connection = initializeConnection({
    host: "localhost",
    user: "user",
    password: "password"
});

Minor suggestion: This may not apply to everyone but I did run into a minor issue relating to scope. If the OP feels this edit was unnecessary then he/she can choose to remove it. For me, I had to change a line in initializeConnection, which was var connection = mysql.createConnection(config); to simply just

connection = mysql.createConnection(config);

The reason being that if connection is a global variable in your program, then the issue before was that you were making a new connection variable when handling an error signal. But in my nodejs code, I kept using the same global connection variable to run queries on, so the new connection would be lost in the local scope of the initalizeConnection method. But in the modification, it ensures that the global connection variable is reset This may be relevant if you're experiencing an issue known as

Cannot enqueue Query after fatal error

after trying to perform a query after losing connection and then successfully reconnecting. This may have been a typo by the OP, but I just wanted to clarify.

Answer 3

I had the same problem and Google led me here. I agree with @Ata that it's not right to just remove end(). After further Googling, I think using pooling is a better way.

node-mysql doc about pooling

It's like this:

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
    connection.query( 'bla bla', function(err, rows) {
        connection.release();
    });
});

Answer 4

Do not connect() and end() inside the function. This will cause problems on repeated calls to the function. Make the connection only

var connection = mysql.createConnection({
      host: 'localhost',
      user: 'node',
      password: 'node',
      database: 'node_project'
    })

connection.connect(function(err) {
    if (err) throw err

});

once and reuse that connection.

Inside the function

function insertData(name,id) {

  connection.query('INSERT INTO members (name, id) VALUES (?, ?)', [name,id], function(err,result) {
      if(err) throw err
  });


}

Answer 5

AWS Lambda functions

Use mysql.createPool() with connection.destroy()

This way, new invocations use the established pool, but don't keep the function running. Even though you don't get the full benefit of pooling (each new connection uses a new connection instead of an existing one), it makes it so that a second invocation can establish a new connection without the previous one having to be closed first.

Regarding connection.end()

This can cause a subsequent invocation to throw an error. The invocation will still retry later and work, but with a delay.

Regarding mysql.createPool() with connection.release()

The Lambda function will keep running until the scheduled timeout, as there is still an open connection.

Code example

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 100,
  host:     process.env.DATABASE_HOST,
  user:     process.env.DATABASE_USER,
  password: process.env.DATABASE_PASSWORD,
});

exports.handler = (event) => {
  pool.getConnection((error, connection) => {
    if (error) throw error;
    connection.query(`
      INSERT INTO table_name (event) VALUES ('${event}')
    `, function(error, results, fields) {
      if (error) throw error;
      connection.destroy();
    });
  });
};

Answer 6

I think this issue is similar to mine:

1. Connect to MySQL
2. End MySQL service (should not quit node script)
3. Start MySQL service, Node reconnects to MySQL
4. Query the DB -> FAIL (Cannot enqueue Query after fatal error.)

I solved this issue by recreating a new connection with the use of promises (q).

mysql-con.js

'use strict';
var config          = require('./../config.js');
var colors          = require('colors');
var mysql           = require('mysql');
var q               = require('q');
var MySQLConnection = {};

MySQLConnection.connect = function(){
    var d = q.defer();
    MySQLConnection.connection = mysql.createConnection({
        host                : 'localhost',
        user                : 'root',
        password            : 'password',
        database            : 'database'
    });

    MySQLConnection.connection.connect(function (err) {
        if(err) {
            console.log('Not connected '.red, err.toString().red, ' RETRYING...'.blue);
            d.reject();
        } else {
            console.log('Connected to Mysql. Exporting..'.blue);
            d.resolve(MySQLConnection.connection);
        }
    });
    return d.promise;
};

module.exports = MySQLConnection;

mysqlAPI.js

var colors          = require('colors');
var mysqlCon        = require('./mysql-con.js');
mysqlCon.connect().then(function(con){
   console.log('connected!');
    mysql = con;
    mysql.on('error', function (err, result) {
        console.log('error occurred. Reconneting...'.purple);
        mysqlAPI.reconnect();
    });
    mysql.query('SELECT 1 + 1 AS solution', function (err, results) {
            if(err) console.log('err',err);
            console.log('Works bro ',results);
    });
});

mysqlAPI.reconnect = function(){
    mysqlCon.connect().then(function(con){
      console.log("connected. getting new reference");
        mysql = con;
        mysql.on('error', function (err, result) {
            mysqlAPI.reconnect();
        });
    }, function (error) {
      console.log("try again");
        setTimeout(mysqlAPI.reconnect, 2000);
    });
};

I hope this helps.