122

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();
Improve this question
1
  • 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. Commented Dec 15, 2020 at 15:20

6 Answers 6

Reset to default
200

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);

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Just a tip: I'm testing reconnecting by restarting mysql service to ensure everything works well.
@jackieLin you can simulate the situation, restarting mysql service, on ubuntu sudo service mysql restart
Thank you @user3073745, this problem is solved by restart
@OkiErieRinaldi, the timers in JavaScript execute only from the main loop. So it's not recursive.
Works perfectly for node 8.* , npm 5.6.0 and mysql:5.7... Thanks!!
|
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.

Improve this answer

9 Comments

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.
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.
Neither sounds probable given that your code follows the described pattern (something similar to 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.
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!
I have updated answer to reflect that I do not recommend this approach. Thanks for the heads up Patrik.
|
28

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

Improve this answer

2 Comments

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.
@AlexisWilke , awesome, do you happen to know - similar thinking applies to mysql2 ?
13

To simulate a dropped connection try

connection.destroy();

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

Improve this answer

Comments

3

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.

Improve this answer

Comments

0

you can also use mysql:8.0 if you are using docker.

image: mysql:8.0

if you are using docker-compose for example.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.