mySQL-node closing/opening connections

[EchoFool]

Hey

 

I need help with understanding mySQL-node connection handling.

 

I currently have a script which does some queries but i don't know where to put:
 

connection.end();

 

 

I keep getting an error where ever i put it so I am really confused. The error is Error: Cannot enqueue Quit after invoking quit.

 

I cannot work out where I am suppose to end the connection.

 

My code is currently like this:

 

var http 	 = require('http'),
    fs           = require('fs'),
    sanitize 	 = require('validator').sanitize,
    mysql        = require('mysql');
	
var db_config = {
		host: '127.0.0.1',
		user: 'testDB',
		password: '[hidden]',
		database: '[hidden]'
};	

var clients = {},	
    connection;

connection = mysql.createConnection(db_config);
connection.connect(function(err) {              
   if(err) {   
     console.log('error when connecting to db:', err); 
   }                                     
});                                     

var app = http.createServer(function (request, response) {

fs.readFile(__dirname + 'client.html', 'utf-8', function (error, data) {
      if (error) {
	response.writeHead(500, {'Content-Type': 'text/html'});
	response.write(error.toString());
      } else {
	response.writeHead(200, {'Content-Type': 'text/html'});
	response.write(data);
      }
	response.end();
});


var io = require('socket.io').listen(app);

    io.sockets.on('connection', function(socket) {
       
       socket.on('connect', function(data,callBack){
	   var session = sanitize(data['session']).escape();					
	   var query = connection.query('SELECT uid FROM sessions WHERE id = ?', [session],
	               function(err,results){
			  if(err){ 
			        console.log('Oh No! '+err);
			  } else if(results.length == 1){
				if(!clients[socket.id]){clients[socket.id] = {};}
				clients[socket.id].uid = results[0].uid;
				io.sockets.socket(socket.id).emit('connectConfirm',{data : true});
			  }
			connection.end();
		       });
		});		
	socket.on('start', function(data,callBack) {			
	   var userId = sanitize(data["userID"]).escape();
	   var query = connection.query('SELECT name FROM users U WHERE U.uid = ?', [userId],
			 function(err,results){
				 if(err){ 
					 console.log('Oh No! '+err);
				 } else if(results.length == 1){
					 io.sockets.emit("name",{ name: results[0].name});
				 }
			connection.end();
			});
                 });
	});
}).listen(1339);

So where does connection.end(); go? I'm really confused how i end the connection gracefully so i don't leak connections.

Edited December 19, 2013 by EchoFool

[kicken]

At the end of the script. Either that or move your connection opening so that it is inside the processing of each request.

 

Right now you are opening a connection to your DB server once before starting up your HTTP server, but you're trying to close the connection to the DB server after each request. 1 open + many closes doesn't match up.

 

I've not used node.JS so I can't really tell you which method of fixing the problem is better, connecting per request or not disconnecting til the end. I'd suspect not disconnecting would work ok, however you will need to make sure you account for the fact that the connection to your server may die between requests and handle that gracefully (eg by attempting a re-connect or spitting out an error page).