How to make a long connection to mysql?

Question

MySQL

In general, the essence: there is a script that connects to mysql. The socket server is also started there. The client connects and receives some information from the database throughout the session (i.e. the client needs information - a request is made to the database in the script). A session can last either 1 minute or a whole day.
Now implemented like this:

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

var connection  = mysql.createConnection({
  connectionLimit : 10,
  host            : 'localhost',
  user            : 'root',
  password        : '',
  database		: 'dbase'
});

connection.connect();

var app = http.createServer(function(req, res) { }).listen(5555);

net.createServer(function (socket) {
  socket.on('data', function (data) {
    var json = JSON.parse(data);
    
    connection.query('SELECT * FROM os_devices WHERE device_hash =  ?', [json.device], function(err, rows) {
      if(rows) {
        connection.query('UPDATE os_devices SET counter = counter + 1 WHERE device_hash =  ?', [json.device], function(err, rows) {
          socket.write("ok\r\n");
        });
      }
      else {
        socket.write("err\r\n");
      }
    });
      
  });
});

The whole problem is that sooner or later the script crashes with an error:

Error: Connection lost: The server closed the connection.
at Protocol.end (/var/www/server/node_modules/mysql/lib/protocol/Protocol.js:109:13)
at Socket. (/var/www/server/node_modules/mysql/lib/Connection.js:102:28)
at Socket.emit(events.js:117:20)
at _stream_readable.js:944:16
at process._tickCallback(node. js:448:13)

I tried via https://github.com/felixge/node-mysql#pooling-conn... , but for some reason, if I have multiple nested queries, then one of them may not work, so I left this way.
How to implement correctly?

Answer 1

Catch the try{ }cath(error){ } error and open another connection, no way?)

Answer 2

If you use a connection pool, try to raise the limit to 100. If it is limited to 10 as in the code, then a nested 11 connection will not be created, as far as I understand.