Connection closes after ~10 seconds

[matteodelabre]

I'm trying to connect to a database - now using new passwords - the connection is etablished normally, but after approximately 10 seconds, an error shows :

events.js:48
        throw arguments[1]; // Unhandled 'error' event
                       ^
Error: Connection lost: The server closed the connection.
    at Protocol.end (C:\Users\Administrateur\Desktop\server\node_modules\mysql\l
ib\protocol\Protocol.js:63:13)
    at Socket.onend (stream.js:80:10)
    at Socket.emit (events.js:88:20)
    at TCP.onread (net.js:388:51)

Just using this code :

var sql = require('mysql');

var connection = sql.createConnection({
    database: 'db',
    user: 'user',
    password: 'pwd',
    host: 'host',
    debug: true
});

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

With my own identifiers.

I've tried to connect with another node module, and it seems to works correctly.

[felixge]

Can you please provide the full output (the stuff generated by debug: true)?

[matteodelabre]

There's all the output :

<-- HandshakeInitializationPacket
{ protocolVersion: 10,
  serverVersion: '5.2.10-MariaDB-mariadb107-log',
  threadId: 56393914,
  scrambleBuff1: <Buffer 27 4d 30 46 51 45 45 69>,
  filler1: <Buffer 00>,
  serverCapabilities1: 63487,
  serverLanguage: 8,
  serverStatus: 2,
  serverCapabilities2: 32779,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 61 2b 4f 30 41 34 7e 43 70 2f 34 55>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password' }

--> ClientAuthenticationPacket
{ clientFlags: 194511,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'user',
  scrambleBuff: <Buffer ac bb 22 26 38 d9 bb 24 72 4c 5d 8b 98 f3 85 57 1e 7e 54 2c>,
  database: 'db' }

<-- OkPacket
{ fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '' }


events.js:48
        throw arguments[1]; // Unhandled 'error' event
                       ^
Error: Connection lost: The server closed the connection.
    at Protocol.end (C:\Users\Administrateur\Desktop\server\node_modules\mysql\lib\protocol\Protocol.js:63:13)
    at Socket.onend (stream.js:80:10)
    at Socket.emit (events.js:88:20)
    at TCP.onread (net.js:388:51)

[ScopeXL]

I too am experiencing this issue, using latest commit (360748d2a5)

When binding connection to 'close' and 'error' it seems to unbind when the connection is re-created using the example from the readme Server disconnects. The only way I'm able to keep a steady connection is before each query sending a newConn() function which re-creates the client and re-binds the 'error' and 'close' events so once produced it will not cause a fatal_error. I know this solution is not ideal and there is probably a better way of going about it. Here is my newConn() function for those who need a quick fix.

function newConn()
{
    connection = mysql.createConnection({
      host: 'host',
      user: 'user',
      password: 'password',
      database: 'db',
      debug: true
    });

    connection.on('error', function(err) {
      console.log(err.code); // 'ER_BAD_DB_ERROR'
    });

    connection.on('close', function(err) {
      if (err) {
        console.log("SQL Connection Closed");

        // We did not expect this connection to terminate
        // connection = mysql.createConnection(connection.config);
      } else {
        // We expected this to happen, end() was called.
        console.log('Manually called .end()');
      }
    });
}

[felixge]

I am not able to reproduce this so far.

Can you guys check the timeout settings on your database? Also @ScopeXL are you also using MariaDB?

@matteo78:

(I have put spaces on < Buffer> to display them properly)

Check out this page: http://github.github.com/github-flavored-markdown/ (Fenced code blocks)

@ScopeXL:

When binding connection to 'close' and 'error' it seems to unbind when the connection is re-created using the example from the readme Server disconnects.

Of course, but that's the way JavaScript works. If you point variable to a new instance of an object, any previous state (such as event listeners), will not be carried over. Once I have implemented a connection pool, I'll re-work this section, but this is really expected behavior.

Also: Your workaround is really bad, nobody should use that - let's get this fixed quickly instead : ).

[ScopeXL]

@felixge

I am using MySQL version 5.5.23-55. Here are my timeout variables grabbed from phpMyAdmin. Also my database is MyISAM so the innodb variables probably aren't the issue, better to be thorough however.

connect timeout                 20
delayed insert timeout          40
innodb lock wait timeout        50
innodb rollback on timeout      OFF
interactive timeout             10
lock wait timeout               31,536,000
net read timeout                30
net write timeout               60
slave net timeout               3,600
wait timeout                    3,600

[felixge]

@ScopeXL @matteo78 can you try 605f87b and let me know if this fixes the issue? It seems like I was not able to reproduce it because my local interactive timeout was set much higher.

--fg

[ScopeXL]

@felixge this definitely helped. After testing I am getting intermittent results. The "PROTOCOL_CONNECTION_LOST" still occurs, though at different time intervals. First time after 2:00 minutes, second time after 2:30 minutes, third time after 1:30 minutes The .on('error') and .on('close') help prevent the fatal_error, but only after 1 disconnect, then like you said the event listeners unbind and cause the fatal_error.

[felixge]

@ScopeXL that's super strange. @matteo78 are you seeing the same thing?

[matteodelabre]

It doesn't change anything for me, the server still closes the connection after 20 secs.

EDIT: Just tried with .on('error') and .on('close') event handlers, and the connection closes again (with PROTOCOL_CONNECTION_LOST exception). I can't see the thing ScopeXL mentions.

[ScopeXL]

@felixge @matteo78 I have implemented a setInterval that fires a null query in an effort to keep the connection open. It's been running all night without a problem. So the issue lies somewhere in the connection being idle for too long I believe.

@matteo78 try this and see if your connection still closes. If your connection closes after 20 seconds though I would set your interval to 15000 or lower.

setInterval(keepAlive, 60000);
function keepAlive() {
    connection.query('SELECT 1');
    console.log("Fired Keep-Alive");
    return;
}

[matteodelabre]

@felixge @ScopeXL That fix works for me, when I set the interval to 20 seconds. I didn't tried on a long period, but it works for more than 3 minutes.

[konstanta]

Any update?
I am experiencing this issue, using latest commit.

[felixge]

I'm honestly not sure what is causing this issue. Could you guys all try node-mysql v1 (npm install mysql) and see if that has the same issue?

[davidmurdoch]

@felixge, looks like I'm experiencing the same issue running node v0.6.19, mysql 5.5.24, and node-mysql 2.0.0-alpha2 on Windows 7 x64. The connection will remain open and idle without any issues for about 8 hours before failing; so testing and debugging is a bit difficult.

Let me know what I can do to help debug this.

[felixge]

@davidmurdoch that's expected on an inactive connection given a wait_timeout setting of 28800 (default). I'd advise you to not keep idle connections open that long, or implement re-connection yourself for now.

[andremoniz]

also experiencing the same issue

[felixge]

@andremoniz please clarify, people have been reporting different observations here. What exactly is happening in your case. What timings are involved? What do your timing related mysql settings look like?

[Windslash]

In my case, this happened exactly after the wait_timeout value of mysql server.

[felixge]

Can all of you try my new example for re-connecting lost connections: https://github.com/felixge/node-mysql#server-disconnects ? The old version was broken, sorry about that : /.

[goldfire]

I am still having this issue. I continually get the following, about every 5 seconds starting from the time my node app starts up. I never had any issues with 0.9.6.

Re-connecting lost connection: Error: Connection lost: The server closed the connection.
    at Protocol.end (/Users/James/Desktop/node/tables6/node_modules/mysql/lib/protocol/Protocol.js:63:13)
    at Socket.onend (stream.js:66:10)
    at Socket.EventEmitter.emit (events.js:115:20)
    at TCP.onread (net.js:418:51)

Maybe I'm not understanding the new API in v2 though? I've got it setup basically how I did before, is doing it like this (obviously very simplified) not the ideal method anymore?

var mysql = require('mysql');
var connection = mysql.createConnection({
    user: '***',
    password: '***',
    host: 'localhost',
    port: 1234,
    database: '***'
});
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);

app.get('/', function(req, res){
    var user = 'test';
    connection.query("SELECT id FROM users WHERE username = ?", [user], function(err, info) {
        res.end(info[0].id);
    });
});

app.get('/test', function(req, res){
    var user = 'test2';
    connection.query("SELECT id FROM users WHERE username = ?", [user], function(err, info) {
        res.end(info[0].id);
    });
});

[adamramadhan]

wow i just got the same error Connection lost: The server closed the connection after a few times of settimeouts

[meerface]

I was having the same issue for a while where the connection closes after about 10-20 seconds. Locally everything functioned fine as I was running the latest node v0.8.15 and [email protected] but on my production environment the node version is set to v0.6.17. I resolved the issue by downgrading to [email protected] and then all worked fine.

[dresende]

Confirm my conclusion. You're saying this 2 environments work fine:

• node v0.8.15 + [email protected]
• node v0.6.17 + [email protected]

[meerface]

Yes much more concise.

[jpmonette]

I'm still having this issue on AppFog, with Node.js 0.8.14. I tried 2.0.0-alpha7, 2.0.0-alpha2 and 2.0.0-alpha5.

Re-connecting lost connection: Error: Connection lost: The server closed the connection.
    at Protocol.end (/mnt/var/vcap.local/dea/apps/jpmonette-0-9f4975db371b8da3b83ba50a0cc79194/app/n
ode_modules/mysql/lib/protocol/Protocol.js:68:13)
    at Socket.onend (stream.js:66:10)
    at Socket.EventEmitter.emit (events.js:126:20)
    at TCP.onread (net.js:417:51)
Fired Keep-Alive
Re-connecting lost connection: Error: Connection lost: The server closed the connection.
    at Protocol.end (/mnt/var/vcap.local/dea/apps/jpmonette-0-9f4975db371b8da3b83ba50a0cc79194/app/n
ode_modules/mysql/lib/protocol/Protocol.js:68:13)
    at Socket.onend (stream.js:66:10)
    at Socket.EventEmitter.emit (events.js:126:20)
    at TCP.onread (net.js:417:51)
Fired Keep-Alive

[dresende]

Try using the new Pool.

[jpmonette]

Since I couldn't get it to work, I decide to move to any-db instead. I think I found the reason why. When we use connection.query(), we need to manually close the connection after the request. I thought it was done automatically. It was working locally, but not on AppFog.

Here's an example using any-db with Express.js, it should be similar with node-mysql:

exports.findByURI = function(uri, callback) {
    var connection = anyDB.createConnection(dbURL);
    connection.query('my request', function(err, rows) {
        callback(rows, err);
        connection.end();
    });
};

It could be a good thing to clarify in the documentation, it wasn't that clear (from my point of view).

Thanks!

[dresende]

That's already in the Pool documentation. You don't have to do that on a normal connection, that's probably because AppFog has a very low connection timeout.

[nachoweb]

Hello all, I was having the same issues when connecting to mysql.
The handleDisconnect function worked for me just once, and only for PROTOCOL_CONNECTION_LOST. So If I just restart mysql this generated a CONNECTiON_LOST and then handleDisconnect tried to connect right away and it fails with another error (something like ECONNREFUSED or similar).

My solution is simple, when an error happen I wait 5 sec and try to reconnect like this:

var express=require("express"),
    _=require("underscore"),
    mysql=require("mysql"),
    conn=mysql.createConnection({
        host:'localhost',
        database:'tpv',
        user:'removed',
        password:'removed'
    })
    , app=express();
//Para reconectar
var reconnect= function(err) {
//    console.log(err);
//    if (err.fatal && !err.fatal) {
//        return;
//    }
    console.log(err.code,'Trying to connect in 5 secs'+new Date());
    setTimeout(function (){
        conn = mysql.createConnection(conn.config);
        conn.on('error',reconnect);
        conn.connect();
    },5000);
};
conn.on('error',reconnect);
....
and then queries and so on 

I am sure this solution could be improved and since i am no expert problably i am not taking some things into accout but i works for me.
I'd be very happy to hear improvements
Cheers

[agalazis]

the above code would always stop executing at err.fatal && !err.fatal (just noticed that you commented it out sorry )

[agalazis]

https://github.com/felixge/node-mysql/search?q=Error%3A+Connection+lost%3A+The+server+closed+the+connection&type=Code
As I can see here fatal errors can be distinguished by checking err.fatal am I right?

[dougwilson]

This thread is from 2012; the code does not even apply to the current version. Use the pool is you want reconnections.