Mysql: Error: This socket has been ended by the other party.

[JTallis]

What exactly is the error in question?

Error: This socket has been ended by the other party

This happens at random times, not quite sure what's triggering it. I received it twice, one after a long period of inactivity and one after a rather short period but doesn't always happen. I now have debug on and waiting for it to happen again but this could take all day, oh the joy.

I've set the timeout on the server to 60 seconds though, just to speed up debugging and well I must say the connection doesn't timeout after 60 seconds so I'm not sure what that is all about, at least with node as it would spit out an error when it is timeouted and then reconnect which doesn't happen. Hopefully I can find more information with debugging on, but until then what exactly is that error and how can I prevent it?

[dougwilson]

Hi! Can you at least post the stack trace that goes with the error? That will allow us to help you much better.

[JTallis]

Well I've put on debugging, and I'm waiting for it to happen again. How would I enable a stack trace?

[dougwilson]

When you get the error object, the stack trace is located in the stack property: error.stack. I looked into this error a little bit, and the error itself is generated somewhere in node.js core network stack, so the message by itself unfortunately doesn't help us if there is something wrong in this library. If you have some code we could run to reproduce the error, that would help as well.

[JTallis]

Okay finally managed to get a stack. Even with debug on, no query or anything is displayed so all I have to show for is this:

Query error: Error: This socket has been ended by the other party
Error: This socket has been ended by the other party
    at Socket.writeAfterFIN [as write] (net.js:276:12)
    at Protocol.<anonymous> (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/Connection.js:69:27)
    at Protocol.EventEmitter.emit (events.js:95:17)
    at Protocol._emitPacket (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/Protocol.js:208:8)
    at Query.<anonymous> (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/Protocol.js:121:12)
    at Query.EventEmitter.emit (events.js:95:17)
    at Query.start (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/sequences/Query.js:29:8)
    at Protocol._startSequence (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/Protocol.js:262:14)
    at Protocol._enqueue (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/Protocol.js:134:10)
    at Connection.query (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/Connection.js:165:25)
    --------------------
    at Protocol._enqueue (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/protocol/Protocol.js:110:48)
    at Connection.query (/home/jlt/jean-luctallis.com/ChatBot/node_modules/mysql/lib/Connection.js:165:25)
    at null.<anonymous> (/home/jlt/jean-luctallis.com/ChatBot/games/lottery.js:90:27)
    at wrapper [as _onTimeout] (timers.js:252:14)
    at Timer.listOnTimeout [as ontimeout] (timers.js:110:15)

[dougwilson]

Great, thanks! So here is what is happening: the library is accidentally trying to write over the TCP socket after a FIN packet has been received, which means the TCP connection is half-closed by the other side. I'll fix this to give you a better error message, but it's still an error; either your network is killing your MySQL connections or something.

Are you using the connection pool? It looks like you are just making a query on a connection after some timeout according to the stack trace. It's possible you are holding onto an inactive connection too long and something on the network or MySQL server is ending the connection.

[JTallis]

This seems strange though. I use this code which should reconnect automatically:

    // Generated by LiveScript 1.2.0
    (function(){
      var mysql, config, db, handleDisconnect, this$ = this;
      mysql = require('mysql');
      config = require('./config');
      db = null;
      handleDisconnect = function(){
        db = mysql.createConnection(config.db);
        db.connect(function(err){
          if (err != null) {
            console.log('Error connecting to mysql:', err);
            db = null;
            return setTimeout(handleDisconnect, 2000);
          }
        });
        return db.on('error', function(err){
          console.log('Database error:', err);
          if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            db = null;
            return handleDisconnect();
          } else {
            return process.exit(1);
          }
        });
      };
      handleDisconnect();
      module.exports = db;
    }).call(this);

Then later on I proceed with something like this:

    Connection = require('../connection');

  if (Connection == null) {
    this.send('We cannot reach the database right now, please try again later.');
    return;
  }

 // Variables are set else where but used below.
 Connection.query("UPDATE users SET experience = experience + ? WHERE username = ?", [experience, username], function(error){
      if (error != null) {
        console.log('Query error: ' + error);
        console.log(error.stack);
        process.exit(1);
      }
      this$.send(username + " earned " + experience + "XP");
      pointer++;
    });

At times the connection is timed out, it will reconnect, or at least I believe so. Could it be closing the connection and not throwing an error?

[dougwilson]

Oh, OK. It is because it's a simple JavaScript mistake. You are reconnecting, but you cannot actually change the Connection reference in the other file. You need to use this:

    // Generated by LiveScript 1.2.0
    // Edited: replaced all "db" with "exports.db"
    (function(){
      var mysql, config, handleDisconnect, this$ = this;
      mysql = require('mysql');
      config = require('./config');
      exports.db = null;
      handleDisconnect = function(){
        exports.db = mysql.createConnection(config.db);
        exports.db.connect(function(err){
          if (err != null) {
            console.log('Error connecting to mysql:', err);
            exports.db = null;
            return setTimeout(handleDisconnect, 2000);
          }
        });
        return exports.db.on('error', function(err){
          console.log('Database error:', err);
          if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            exports.db = null;
            return handleDisconnect();
          } else {
            return process.exit(1);
          }
        });
      };
      handleDisconnect();
    }).call(this);

then in your other place:

    Connection = require('../connection');

  // Edited: replaced all "Connection" with "Connection.db"
  if (Connection.db == null) {
    this.send('We cannot reach the database right now, please try again later.');
    return;
  }

 // Variables are set else where but used below.
 Connection.db.query("UPDATE users SET experience = experience + ? WHERE username = ?", [experience, username], function(error){
      if (error != null) {
        console.log('Query error: ' + error);
        console.log(error.stack);
        process.exit(1);
      }
      this$.send(username + " earned " + experience + "XP");
      pointer++;
    });

i.e. you need to reference an object lookup, not the variable directly. This is a common JavaScript mistake because people confuse global variables that are an implicit object lookup vs. lexical variables that are a direct reference. When you reconnect in your reconnect code, you are not actually changing what Connection points to in your other file, which is why you start making queries on disconnected connections.