0

I've got a website I've written that gets data from a Restful web service. That Restful web service is run off node.js and MySQL using node-mysql. The problem I am running into is with connections running out. For example, my default page does a bunch of lookups to get data. On the 9th refresh of that page, one of my lookups to the Restful API throws this error "No connections available."

Here is kind of how things are architected on the Restful API side:

In server.js:

var db = require('mysql');
var db_pool = db.createPool(
{
    host : 'localhost',
    connectionLimit: 100,
    supportBigNumbers: true,
    waitForConnections: false
});

setRoutes(server, db_pool, passport, LocalStrategy, jwt, tokenSecret, bcrypt);

In router.js:

setRoutes = function(server, connectionPool, passport, LocalStrategy, jwt, tokenSecret, bcrypt)
{
    require('./lib/places.js');
    place.connectionPool = connectionPool;
}

server.get('api/place/:id', function(request, response)
{
    response.header("Access-Control-Allow-Origin", "*");
    response.header("Access-Control-Allow-Headers", "X-Requested-With");

    place.get(request.params.id, function(err, data)
    {
        response.send(data);
        next();
    });
});

Finally, in places.js:

place.connectionPool = null;

place.get = function(id, callback)
{
    place.connectionPool.getConnection(function(error, connection) 
    {
        var query = "select myColumn from myTable";

        connection.query
        (
            query,
            function (queryError, rows, fields)
            {
                try {connection.release();} catch(e){};

                if (queryError)
                {
                    console.log(JSON.stringify(queryError));
                    return (callback(null, queryError));
                }

                return (callback(null, rows));
            }
        );
    });
}

What is the best practice for implementing node-mysql and connection pooling for websites?

Thanks!

Adam Baruh
  • 11
  • 3
  • 1
    How does `place.connectionPool = null;place.connectionPool.getCo...` make any sense? – Kevin B Oct 01 '14 at 21:55
  • Good question - in the header/global properties of places.js is where I instantiate place.connectionPool = null. My assumption is that when setRoutes is called from server.js that the connectionPool is then populated with the pool created in server.js. The place.connectionPool.getConnection is actually within a function that is called from a route in router.js. Does that make sense? – Adam Baruh Oct 01 '14 at 22:18

1 Answers1

0

Solved the connection issue - I was not releasing the connection in all places where I was doing a callback.

Adam Baruh
  • 11
  • 3