timeout in node mysql

Question

as we knows node has some mysql modules, some are pure js implemented(like node-mysql), some are based on c libmysql.

i quite prefer node-mysql because it doesn't need extra mysql library which seems more 'clean'. But i also notice that it does not support timeout feature in connect & query which may cause problem at some envrioment.

so my question is : does anyone have solve this timeout problem cleanly?

possible duplicate of [nodejs mysql Error: Connection lost The server closed the connection](http://stackoverflow.com/questions/20210522/nodejs-mysql-error-connection-lost-the-server-closed-the-connection) — Gajus, Jan 29 '15 at 13:11

score 1 · Answer 1 · answered Apr 18 '13 at 21:23

1

What we did to fix this was to check the error message, and re-connect if necessary

This is how they suggest it at https://github.com/felixge/node-mysql#server-disconnects

Here is the sample code in case the documentation ever changes

function handleDisconnect(connection) {
  connection.on('error', function(err) {
    if (!err.fatal) {
      return;
    }

    if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
      throw err;
    }

    console.log('Re-connecting lost connection: ' + err.stack);

    connection = mysql.createConnection(connection.config);
    handleDisconnect(connection);
    connection.connect();
  });
}

handleDisconnect(connection);

answered Apr 18 '13 at 21:23

Ryan Gibbons

3,511
31
32

what i mean is something like 'timeout' event on connections, just like connection.on('timeout', xxxx); i notice at the end of https://github.com/felixge/node-mysql, the to-do list contains "setTimeout() for Connection / Query" – tztxf Apr 20 '13 at 03:48
I agree, the solution above does not solve timeouts. I am using the solution above in production and it is failing on timeouts :-( – Alastair Brunton May 17 '13 at 09:59
This won't prevent the timeout from happening, but will re-create connection. I took a look at our code where we are using this, we are actually firing the reconnection on the 'close' event. – Ryan Gibbons May 17 '13 at 16:21

Tim · Answer 2 · 2016-01-26T14:12:33.397

I have had the same issue, using the method mysql.createPool instead of the method createConnection have work for me.

This is my code;

/*jslint node: true */
'use strict';
var Q = require('q');

var mysql = require('mysql');
var _config;
var env = process.env.NODE_ENV || 'development';
if (env === 'development') {
  _config = {
    host     : 'localhost',
    user     : 'root',
    charset : 'latin1',
    password : '123456',
    database : 'DEVDB',
    connectionLimit: 10
  };
}else {
  _config = {
    host : 'PRODUCTIONHOST',
    user     : 'root',
    charset : 'latin1',
        password : 'PRODUCTIONPASS',
    database: 'PRODUCTIONDB',
    connectionLimit: 10
  };
}

var _pool = mysql.createPool(_config);

var runquery = function() {
  var deferred = Q.defer();
  var args = Array.prototype.slice.call(arguments);

  //console.log(args);
  args.push(function(err, results){
    if (err) {
      deferred.reject(new Error(err));
    } else {
      deferred.resolve(results);
    }
  });

  _pool.query.apply(_pool, args);

  return deferred.promise;
};


module.exports = runquery;

Usage example;

runquery('select id from users where mail = ?', 'eugenioclrc')
.then(function(rows){
  console.log(rows);
});

Could you elaborate this answer? I'm using mysql.createPool too but it's not working for me. — jagc, Dec 15 '15 at 08:14

timeout in node mysql

2 Answers2