118

when I use node mysql, an error is appear between 12:00 to 2:00 that the TCP connection is shutdown by the server. This is the full message:

Error: Connection lost: The server closed the connection.
at Protocol.end (/opt/node-v0.10.20-linux-x64/IM/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:920:16
at process._tickCallback (node.js:415:13)

There is the solution. However, after I try by this way, the problem also appear. now I do not know how to do. Does anyone meet this problem?

Here is the way I wrote follow the solution:

    var handleKFDisconnect = function() {
    kfdb.on('error', function(err) {
        if (!err.fatal) {
            return;
        }
        if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
            console.log("PROTOCOL_CONNECTION_LOST");
            throw err;
        }
        log.error("The database is error:" + err.stack);

        kfdb = mysql.createConnection(kf_config);

        console.log("kfid");

        console.log(kfdb);
        handleKFDisconnect();
    });
   };
   handleKFDisconnect();
Damodaran
  • 10,882
  • 10
  • 60
  • 81
jackieLin
  • 1,237
  • 2
  • 9
  • 9
  • Note that in your code you had an incorrect message: `if (err.code !== 'PROTOCOL_CONNECTION_LOST') { console.log("PROTOCOL_CONNECTION_LOST"); throw err; }`. You execute the `if()` block if it is **not** `PROTOCOL_CONNECTION_LOST` and yet the message says it is that error... probably very confusing. – Alexis Wilke Dec 15 '20 at 15:20

5 Answers5

198

Try to use this code to handle server disconnect:

var db_config = {
  host: 'localhost',
    user: 'root',
    password: '',
    database: 'example'
};

var connection;

function handleDisconnect() {
  connection = mysql.createConnection(db_config); // Recreate the connection, since
                                                  // the old one cannot be reused.

  connection.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
      throw err;                                  // server variable configures this)
    }
  });
}

handleDisconnect();

In your code i am missing the parts after connection = mysql.createConnection(db_config);

CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
  • ok, I will try this. but how could I simulation this siutation – jackieLin Nov 26 '13 at 08:23
  • Why do you need to simulate this? – CloudyMarble Nov 26 '13 at 08:44
  • Now the code is work. The database object is not null, howerer, when select the database, it can not execute and stop all the time. But the sever is not show any error! – jackieLin Nov 27 '13 at 01:26
  • Thank you very much!! Finally I use mysql pool connection.Although the error is still appearced, the application can connect to the server. – jackieLin Dec 01 '13 at 12:25
  • I tried it but it is not working I got the server disconnected again. – moderns Jul 01 '14 at 23:23
  • 2
    Just a tip: I'm testing reconnecting by restarting mysql service to ensure everything works well. – kriskodzi Sep 01 '14 at 19:30
  • Any idea how to re-recreate and cast the PROTOCOL_CONNECTION_LOST via console or mysql so I can test this code? I am randomly getting what the OP is getting like every other 2 days, very annoying as it shuts down my server. I'm properly closing and writing queries correctly as well. Hmm – NiCk Newman Mar 16 '15 at 17:48
  • I want to add mysql strict mode after connection opening. But it's giving error. `SET SESSION sql_mode='STRICT_ALL_TABLES` – user5858 Oct 24 '15 at 11:59
  • 2
    @jackieLin you can simulate the situation, restarting mysql service, on ubuntu sudo service mysql restart – igor Nov 06 '15 at 10:57
  • 1
    Thank you @user3073745, this problem is solved by restart – jackieLin Nov 10 '15 at 15:00
  • Is **the call of `handleDisconnect()` inside `setTimeout`** in the above script a recursive method? **If yes**, than the script will not run forever. Because, the javascript engine has limitation of recursion depth. – Oki Erie Rinaldi Dec 16 '15 at 03:08
  • 1
    @OkiErieRinaldi, the timers in JavaScript execute only from the main loop. So it's not recursive. – Alexis Wilke Mar 04 '19 at 01:18
  • @CloudyMarble, _Why do you need to simulate this?_ — really? You have no idea why a user would need to know whether your code works? – Alexis Wilke Mar 04 '19 at 01:19
  • 1
    Works perfectly for node 8.* , npm 5.6.0 and mysql:5.7... Thanks!! – JRichardsz Oct 12 '19 at 19:50
  • How to use it with connections pools? – Alexander Reshytko Oct 19 '20 at 19:41
  • err, where exactly is your query being executed? this seems to do nothing for large inserts failing with that error. – notAChance Aug 08 '23 at 15:42
49

I do not recall my original use case for this mechanism. Nowadays, I cannot think of any valid use case.

Your client should be able to detect when the connection is lost and allow you to re-create the connection. If it important that part of program logic is executed using the same connection, then use transactions.

tl;dr; Do not use this method.

A pragmatic solution is to force MySQL to keep the connection alive:

setInterval(function () {
    db.query('SELECT 1');
}, 5000);

I prefer this solution to connection pool and handling disconnect because it does not require to structure your code in a way thats aware of connection presence. Making a query every 5 seconds ensures that the connection will remain alive and PROTOCOL_CONNECTION_LOST does not occur.

Furthermore, this method ensures that you are keeping the same connection alive, as opposed to re-connecting. This is important. Consider what would happen if your script relied on LAST_INSERT_ID() and mysql connection have been reset without you being aware about it?

However, this only ensures that connection time out (wait_timeout and interactive_timeout) does not occur. It will fail, as expected, in all others scenarios. Therefore, make sure to handle other errors.

Gajus
  • 69,002
  • 70
  • 275
  • 438
  • 1
    Just curious, where would you put that db query at? At the bottom of the nodejs server right? The only issue is, I only use mysql to authenticate a user once, and then I store their data in a temporary users object for my rpg game. I don't know why I got this mysql closed error randomly today, hmm. I am closing the connection properly, etc as well too. – NiCk Newman Mar 14 '15 at 01:03
  • 2
    You should connect to the database and disconnect on demand basis. This solution is for services that run continuously and utilize database connection at all time. – Gajus Mar 15 '15 at 17:50
  • If that is the case, would it be possible that I have a possible memory leak or forgot to apply a db.end()? – NiCk Newman Mar 15 '15 at 18:16
  • 1
    Neither sounds probable given that your code follows the described pattern (something similar to https://gist.github.com/gajus/5bcd3c7ec5ddcaf53893). If this happened only once or twice out of thousands of queries, I would assume connectivity issues, server overload, or something along those lines. – Gajus Mar 15 '15 at 18:21
  • 5
    That was possibly the worst ever suggestion! A Person suggesting that you should hammer the database with queries just so that the connection won´t go dont? What happens if 100 people do this? or why not 10 000, If your app does noting, the thread should be returned to the MYSQL Thread Pool NOT hogging a thread so that your weak code don´t break!, in this case you implement functionality for reconnecting if such event has occured! – Patrik Forsberg Jan 21 '19 at 14:56
  • 1
    Patrik your comment is (accidentally) funny on many levels that you don't even realise. Brightened my day. Either way, Patrik is not wrong – I thoroughly do not recommend this solution for anything other than a dirty hack when running a single process on a local machine. Even then, this is fairly shortsighted suggestion and I cannot think of a valid use case for it. – Gajus Jan 21 '19 at 15:10
  • Connection socket management is expensive, therefore strive to limit how long you keep the connections open. In general (other than working in a sandbox), you should be using connection pooling. In Postgres, that would be pgpool (I don't know what is the MySQL equivalent). Keep transactions short. Keep queries short. Any program that relies on having the same connection open for extended duration has some fundamental design flaws and will not scale well. – Gajus Jan 21 '19 at 15:14
  • 4
    I have updated answer to reflect that I do not recommend this approach. Thanks for the heads up Patrik. – Gajus Jan 21 '19 at 15:18
  • Patrik, you won't be able to keep a job at FB if you code things that scale or can be easily be refactored by the new team of job skippers that come in every 18 months. I think this is bad advice. Sometimes the brute force arse-backwards way is best in a corporate setting. If you have a hobbyist or portfolio project, then I think this solution is not good. I concur with Gajus. – ThickMiddleManager Dec 31 '19 at 15:47
26

better solution is to use the pool - it will handle this for you.

const pool = mysql.createPool({
  host: 'localhost',
  user: '--',
  database: '---',
  password: '----'
});

// ... later
pool.query('select 1 + 1', (err, rows) => { /* */ });

https://github.com/sidorares/node-mysql2/issues/836

Karl
  • 5,613
  • 13
  • 73
  • 107
Lalgulab Khatkar
  • 261
  • 3
  • 3
  • 3
    This should be the accepted answer. Using the pool mechanism is going to hide all the complicated details on how to handle connection time outs. Also, there is documentation on how to use it in the `node_modules/mysql/Readme.md` file that comes with the MySQL node module. – Alexis Wilke Dec 15 '20 at 18:08
  • 2
    @AlexisWilke , awesome, do you happen to know - similar thinking applies to mysql2 ? – Fattie Aug 17 '22 at 14:47
12

To simulate a dropped connection try

connection.destroy();

More information here: https://github.com/felixge/node-mysql/blob/master/Readme.md#terminating-connections

Fabian Lauer
  • 8,891
  • 4
  • 26
  • 35
Simon Fearby
  • 177
  • 1
  • 6
2

Creating and destroying the connections in each query maybe complicated, i had some headaches with a server migration when i decided to install MariaDB instead MySQL. For some reason in the file etc/my.cnf the parameter wait_timeout had a default value of 10 sec (it causes that the persistence can't be implemented). Then, the solution was set it in 28800, that's 8 hours. Well, i hope help somebody with this "güevonada"... excuse me for my bad english.

Aleks Towers
  • 59
  • 1