No reconnection after connection lost

[vodolaz095]

I use nodeJS 0.10.1 on heroku hosting with mysql 2.0.0 alfa7 and i work with ClearDB mysql database.

I connect to database like this

var MYSQLConnection = require('mysql').createConnection('mysql://b453496a28495a:SECRET@us-cdbr-east-03.cleardb.com/heroku_1a4a27e1f6bed85?reconnect=true');

I try to reconnect after connection lost like there and it doen's works
https://github.com/felixge/node-mysql#server-disconnects

what am i doing wrong?

Affter losing connection i can't reconnect with code you have provided

Regards, Anatoly

[dougwilson]

The variable names in the example make it do nothing useful. Try this instead and see if it works:

function handleDisconnect(conn) {
  conn.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(conn.config);
    handleDisconnect(connection);
    connection.connect();
  });
}

handleDisconnect(connection);

[vodolaz095]

this is example of what i run

var config={
  "CLEARDB_DATABASE_URL":"mysql://b453496a28495a:SECRETSECRET@us-cdbr-east-03.cleardb.com/heroku_1a4a27e1f6bed85?reconnect=true"
}

var mysqlDriver = require('mysql');
var MYSQL = mysqlDriver.createConnection(config.CLEARDB_DATABASE_URL);

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

        if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
            throw err;
        }
        console.log('Re-connecting lost connection: ' + err.stack);
        MYSQL = mysqlDriver.createConnection(config.CLEARDB_DATABASE_URL);
        handleDisconnect(MYSQL);
        MYSQL.connect();
    });
}
handleDisconnect(MYSQL);

and it do not works(

i mean, it can't reconnect after connection lost, but it connects, and allows us to run some queries, but in about 2-3 minutes the mysql server closes the connection and i can't recconect

[dresende]

If using node 0.10.x, try the git version instead of the npm one.

[vodolaz095]

no, it doesn't works

Re-connecting lost connection: Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/nap/project/node_modules/mysql/lib/protocol/Protocol.js:73:13)
    at Socket.onend (stream.js:79:10)
    at Socket.EventEmitter.emit (events.js:117:20)
    at _stream_readable.js:883:14
    at process._tickCallback (node.js:415:13)

[yjwei]

I faces the same problem “No reconnection after connection lost”, does it solved? I use nodeJS 0.10.0 with mysql 2.0.0 alfa7

[jbrooksuk]

Same problem here. It prevents the rest of my Express 3.0 application from continuing to run.

function handleDisconnect(cnx) {
    cnx.on('error', function(err) {
        if (!err.fatal) return;
        if (err.code !== 'PROTOCOL_CONNECTION_LOST') throw err;

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

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

I'm using a slightly minified version of the code in the README. Using Node.js 0.10.3

For more information, when the re-connection is attempted this is the output I get:

> Re-connecting lost dbUser MySQL connection: Error: Connection lost: The server
 closed the connection.
    at Protocol.end (/var/www/admin/node_modules/mysql/lib/protocol/Protocol.js:
72:13)
    at Socket.onend (_stream_readable.js:457:10)
    at Socket.g (events.js:175:14)
    at Socket.EventEmitter.emit (events.js:117:20)
    at _stream_readable.js:883:14
    at process._tickCallback (node.js:415:13)

[vodolaz095]

i want to say, that i use the code provided in my previuos comment with
nodeJS of version 0.10.5 and it still DO not works

[jbrooksuk]

I've just ran npm install felixge/node-mysql and will give it ago. Sadly, it takes a bit of time to test this.

[dougwilson]

@jbrooksuk your code does not work because you are assigning the new connection to a variable that is only within the function (cnx). You need to reassign some global variable instead.

[jbrooksuk]

@dougwilson is it though? I'm passing through the variable containing my connection, so I assumed it'd be calling the createConnection on that?

[dougwilson]

So the wording in the README is a little misleading, as the code shown there (and that you are using above) doesn't actually reconnect your client to the server, rather when your client disconnects, it creates a new, completely different client object that connects to the server.

This line in your code above:

cnx = mysql.createConnection(cnx.config);

is what is doing the "reconnection" (creating a brand new client object using the configuration from the disconnected one). Since you are assigning the new object to the variable cnx, it ends up doing nothing, because function handleDisconnect(cnx) { is declaring the variable cnx to be only within that function's scope.

The example in the README assumes that you have a very simple node script that makes a single global MySQL client object, then handleDisconnect is called on it and will watch for a connection error and replace that global client object with a brand new one that is connected again.

In other words, it assumes this complete example:

var http = require('http');
var mysql = require('mysql');

var mysqlConfig = {'host': 'localhost', 'user': 'test', 'database': 'test'};
var mysqlClient = mysql.createConnection(mysqlConfig); // This is the global MySQL client
handleDisconnect(mysqlClient);

var server = http.createServer(function (req, res) {
  req.resume(); // Discard any request body
  // Each request simply uses the global client variable; when the client disconnects
  // This variable gets replaced with a connected client in `handleDisconnect`; this works
  // for globals as they are implicitly looked up on the `global` object (`global.mysqlClient`)
  // unlike normal variables, so they can be swapped.
  mysqlClient.query('SELECT * FROM `test`;', function (error, rows) {
    if (error) {
      console.error(error.stack);
      res.statusCode = 500;
      res.end();
      return;
    }
    res.statusCode = 200;
    res.setHeader('Content-Type', 'text/plain');
    res.end(JSON.stringify(rows, null, 2));
  });
});
server.listen(3000, function () {
  console.log('HTTP server listening on port 3000');
});

function handleDisconnect(client) {
  client.on('error', function (error) {
    if (!error.fatal) return;
    if (error.code !== 'PROTOCOL_CONNECTION_LOST') throw err;

    console.error('> Re-connecting lost MySQL connection: ' + error.stack);

    // NOTE: This assignment is to a variable from an outer scope; this is extremely important
    // If this said `client =` it wouldn't do what you want. The assignment here is implicitly changed
    // to `global.mysqlClient =` in node.
    mysqlClient = mysql.createConnection(client.config);
    handleDisconnect(mysqlClient);
    mysqlClient.connect();
  });
};

I hope this helps.

[jbrooksuk]

Cheers @dougwilson 👍

[alexcroox]

I've followed the above and I still can't get mine to reconnect automatically. It still fails to connect if I leave it idle for a couple of minutes and attempt a query after. I don't see any console logs to indicate it's trying to re-connect either.

Should I be opening and closing the connection on every query to get around this?

[vodolaz095]

Try to use with https://github.com/felixge/node-mysql#pooling-connections
it worked for me

But the reconnection in the way described above DO NOT work for me(((