Node.JS and MySQL - queries lock up and execute extremely slowly

Question

I am getting strange behavior using Node.JS and MySQL with this driver - https://github.com/mysqljs/mysql

Essentially, I have a button on the frontend that triggers an app.get that makes a query in the database and I can happily use the results in my backend.

This works nicely, until I press the button 4-5 times in a second, where as the queries lock up and I have to wait for 2-3 minutes until they continue executing. I have a similar write function that behaves the same way.

Is it possible this is a problem, because I'm trying to execute the exact same query asynchronously? I.e. do I have to limit this from the front end or is it a backend problem?

Any ideas on how to debug what exactly is going on?

// database.js

var mysql = require('mysql');

var pool  = mysql.createPool({
    connectionLimit: 100,
    host     : 'localhost',
    user     : 'secret',
    password : 'secret',
    database : 'mydb'
});

exports.getConnection = function(callback) {
    pool.getConnection(function(err, connection) {
        callback(err, connection);
    });
};

---

// dbrw.js
var con = require('../config/database');

function read(id, done) {
    con.getConnection(function(err, connection){
        if(!err){
            connection.query("SELECT * FROM users WHERE id = ?",[id], function(err, rows) {
            connection.release();
                if (err)
                    done(err);
                if (rows.length) {
                    console.log("rows " + JSON.stringify(rows));
                    done(rows[0].progress);
                };
            });
        }
        else {
            console.log(err);
        }
    });
}

exports.read = read;

---

// routes.js
var dbrw = require('./dbrw.js');

app.get('/read', isLoggedIn, function(req, res) {
    dbrw.read(req.user.id, function(result) {
    console.log(result);
    });
});

---

// Frontend - angular app.js

$scope.tryread = function() {
    $http.get('/read');
}

Thanks in advance for any input.

Answer 1

I see a few issues:

function read(id, done) {
    con.getConnection(function(id, connection){...}
}

Notice how you overwrite the id passed to read by giving that same name to an argument of the callback to getConnection.

Also, your Express route doesn't actually end the request by sending back a response, which will make your browser time out the connection. At some point, it will even refuse to send more requests because too many are still pending.

So make sure to end the request:

app.get('/read', isLoggedIn, function(req, res) {
    dbrw.read(req.user.id, function(result) {
      console.log(result);
      res.end(); // or `res.send(result)`
    });
});

And a tip: you should use the callback calling convertion for Node, where the first argument represents an error (if there is any) and the second argument represents the return value.