module.exports scope and node-mysql

Question

I have database.js file that connects to db and manage connections I export connection and reuse it in my app

    var mysql = require('mysql');

    pool = mysql.createPool({
        host: cfg.mysql.host,
        user: cfg.mysql.user,
        password: cfg.mysql.pass,
        database: cfg.mysql.db,
        port: cfg.mysql.port
    });


    function handleDisconnect() {
    pool.getConnection(function(err, cnt) {
    module.exports.connection = cnt;
    });

    pool.on('error', function (err) {
      console.log(err);
    });
    };

    handleDisconnect();
    process.on('uncaughtException', function (err) {
      console.error(err.code);
    if(err.code === "PROTOCOL_CONNECTION_LOST")
    handleDisconnect();
      console.log("Node NOT Exiting...");
    });

app.js

    var db = require('./database');
     app.get('/test', function(req, res) {
        db.connection.query('SELECT * from table', function(err, result) {
           console.log(result);
          });
        }

This works fine. My problem is when the mysql server disconnect. I handle this error by recalling the handleDisconnect() function to get a new connection. However, when that happens the connection is undefined in my app.js when I navigate to my browser /test

TypeError: Cannot call method 'query' of undefined

Thoughts?

AbdullahDiaa · Accepted Answer · 2013-10-27 07:53:16Z

You can simply put the connection back into pool following each query on your database server here's an example

db.js

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

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

app.js

var db = require('./db');
var http = require("http");
var server = http.createServer(function(request, response) {
    db.getConnection(function(err, connection){
        if(!err){
            connection.query('SELECT * from users' , function(err, rows) {
                var users = '';
                for (var i = 0; i < rows.length; i++) {
                    users += rows[i].name + "<br />";
                }
                connection.release(); // always put connection back in pool after last query
                response.writeHead(200, {"Content-Type": "text/html"});
                response.write("<!DOCTYPE html>");
                response.write("<html>");
                response.write("<head>");
                response.write("<title>Hello World Page</title>");
                response.write("</head>");
                response.write("<body>");
                response.write(users);
                response.write("</body>");
                response.write("</html>");
                response.end();
           });
        }else{
            response.writeHead(200, {"Content-Type": "text/html"});
            response.write("<!DOCTYPE html>");
            response.write("<html>");
            response.write("<head>");
            response.write("<title>Hello World Page</title>");
            response.write("</head>");
            response.write("<body>");
            response.write(err.toString());
            response.write("</body>");
            response.write("</html>");
            response.end();
        }
    });
});
server.listen(9999);
console.log("Server is listening localhost:9999");

ravi · Accepted Answer · 2013-10-27 16:10:43Z

1

UPDATE: See first comment below for a correction on my [mis]understanding of the problem. I am leaving this response around, rather than deleting it, since it touches on more than one issue.

You cannot keep reassigning to module.exports. It won't work because the export occurs only once at require() time (in fact, I suspect your code above works at all only because pool.getConnection executes synchronously i.e., assigning to module.exports in a callback is not typically safe). Here's what's going on:

In app.js you do: var db = require('./database');

This stores a reference not to module.exports (of database.js) but to the object that is assigned to module.exports (i.e., the connection object). When that object is destroyed because of a disconnect the variable "db" in app.js refers to nothing (note that node-mysql docs are clear that you cannot reuse the connection object). What you really want to do is to return a new connection object to app.js but that's not possible because app.js calls require only once.

I understand that you want to hide connection re-establishment from app.js. One way to do that is to wrap the node-mysql connection object in your own database.js object and return the latter to app.js. This wrapped object can obtain a connection from the pool when it's query() method is called and then call the query() method of the obtained connection. So on.

edited Oct 27, 2013 at 16:10

answered Oct 27, 2013 at 14:46

ravi

3,3894 gold badges21 silver badges11 bronze badges

2 Comments

robertklep Over a year ago

The connection isn't assigned to module.exports, it's assigned to module.exports.connection, so that should work just fine (although I agree the current setup is a bit dubious).

ravi Over a year ago

Good catch, I didn't notice that.

robertklep · Accepted Answer · 2013-10-26 12:55:14Z

Start by checking of pool.getConnection in handleDisconnect is throwing an error. Since you haven't built in a timeout before reconnecting in case a connection gets lost, the chances are that immediately calling pool.getConnection will fail because of the same problem.

Also, between the moment that the connection gets lost and handleDisconnect reconnecting, any incoming HTTP requests can't perform database queries so should check if db.connection is actually valid before trying to run a query.

That, or you need to handle errors in your Express routes too, for instance with an error handler route (which should be placed somewhere last in your route/middleware setup):

// middleware setup
...
// routes setup
...
// finally:
app.use(function(err, req, res, next) {
  res.send(500, err.message); // or whatever you want to send back in case of errors.
});

Collectives™ on Stack Overflow

module.exports scope and node-mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related