MySQL REST API Error

Question

MySQL REST API Error

Navigation

#1 by (1 votes)

2

Good morning,

I have designed an API Rest in NodeJS.

It works correctly, but when I left it up for more than 24 hours it gives me a connection error in MySQL DB.

events.js:160
      throw er; // Unhandled 'error' event
      ^

Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/ARM/Documentos/api-rest-iof/node_modules/mysql/lib/protocol/Protocol.js:113:13)
    at Socket.<anonymous> (/home/ARM/Documentos/api-rest-iof/node_modules/mysql/lib/Connection.js:109:28)
    at emitNone (events.js:91:20)
    at Socket.emit (events.js:185:7)
    at endReadableNT (_stream_readable.js:974:12)
    at _combinedTickCallback (internal/process/next_tick.js:80:11)
    at process._tickCallback (internal/process/next_tick.js:104:9)
[nodemon] app crashed - waiting for file changes before starting...

I have created a module with the information of the connection and I call it from the controller in such a way:

//Importamos los datos de la conexión
var conn=require('../models/connection');

//Importamos el paquete mysql
var mysql = require('mysql'),
//Creamos la conexión a nuestra base de datos con los datos almacenados en conn
connection = mysql.createConnection(
    conn
);

//funcion para comprobar si esta repetido el usuario
function getUserDB(email, done) {
  connection.query('SELECT * FROM usuarios WHERE email = ? LIMIT 1', [email], function(err, rows, fields) {
    if (err) throw err;
    //console.log(rows[0]);

    console.log('usuario ya registrado' + rows[0]);
    done(rows[0]);
  });
}

What could be the problem? A MySQL session close for time?

Thanks and best regards.

EDIT1:

I have changed the connection to the database in such a way:

I have created a module with the data and I import that module into the controller

var connection=require('../models/connection');
//Coger todos los usuarios
function usuariosfull(req, res) {
        if (connection)
        {
            connection.query('SELECT * FROM usuarios', function(err, rows) {
                if(err)
                {
                    throw err;
                    console.log('Error al Conectar' + error);
                }
                else
                {
            res.status(200).json(rows);
                }
            });
        }
            connection.end();

}

When logging the connection logically closes and when I try to call another API method it tells me:

OPTIONS http://xxx.xxx.xxx.xxx:3790/api/login net::ERR_CONNECTION_REFUSED

I understand that it is logical since you have closed and the connection is started when you start the API,

Where do I have to put the connection closure?

At the moment I'm launching my API with npm start . Is it possible that this is the problem and not having a service deployed?

Thanks again.

Edit2:

Good news again,

I have changed by what you mentioned, with a pool creating the connection when calling the method and closing it with the release () of connections.

But I do not know if it is the correct way to do it, looking for API Rest They only make a connection, my doubt is that I have 20 methods for the api, I have to put in all the call pool.getConnection?

Example:

Connect module:

var mysql = require('mysql');
var pool = mysql.createPool({
   host: 'localhost',
   user: 'root',
   password: 'xxxxxxxx',
   database: 'xxxxxxxx',
   port: 3306
});

module.exports = pool;

Controller:

var pool=require('../models/connection');


//Listar todos los datos del invernadero
function getInvfull(req, res) {

  pool.getConnection(function(err, connection){
  if (err) {
    console.log('Error al pedir una conexión: '+err);
    return;
  }
  console.log("pruebas de conexion");
  connection.query('SELECT * FROM usuarios', function(err, rows) {
    if(err) {
      throw err;
      console.log('Error al ejecutar la query ' + error);
    } else {
      res.status(200).json(rows);
    }
  });
  //para que pueda ser reutilizada por el pool, es importante "liberarla"
  connection.release();
});

}

mysql javascript nodejs

asked by Manolait 07.11.2017 в 09:44

source

1 answer

function document.getElementsByTagName does not work Play sound when changing content in a table

score 1 · Accepted Answer

You could use a pool of connections to automatically manage to always have an active connection:

var mysql = require('mysql');

var pool  = mysql.createPool({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'guess'
});

And then use it to get the connections:

pool.getConnection(function(err, connection){
  if (err) {
    console.log('Error al pedir una conexión: '+err);
    return;
  }
  connection.query('SELECT * FROM usuarios', function(err, rows) {
    if(err) {
      throw err;
      console.log('Error al ejecutar la query ' + error);
    } else {
      res.status(200).json(rows);
    }
  });
  //para que pueda ser reutilizada por el pool, es importante "liberarla"
  connection.release(); 
});

Little explanation: When creating a pool you use the same options as when creating a single connection, but you can also define others, such as a pretty explanatory one that I put below:

connectionLimit : The default value is 10.

How does the pool work? Each time you require a connection, you ask the pool using getConnection() and this:

If none exists, you will create it at the moment.
If it exists, but it is being used, it will create a new one, as long as the limit set with the option connectionLimit is not reached.
If it exists and it is not being used, then it is available and it will be given to you.

When your method no longer needs the connection, you return it with connection.release() . This does not close the connection, it simply returns it to the pool for reuse. If the connection "dies" because it is not used for a long time, then the pool will know it and will create another one when necessary.

If it seems too heavy and repetitive to request the connection and release it each time, you can create a wrapper like this response from StackOverflow in English