Too many connections error

[ghost]

Hi guys,

For this script I need to create and close a new connection all the time because I don't know when my file readers stop.

Have you any refactoring idea for using only one connection in this script ? The main problem is I don't know when I need to close the connection with connection.end().

fs = require('fs')
config = require('config')
mysql = require('mysql')
glob = require('glob')
lineReader = require('line-reader')
Promise = require('bluebird')

dbConfig = config.get('dbConfig');
eachLine = Promise.promisify lineReader.eachLine, context: lineReader

glob "data/blacklists/*.csv", {}, (err, files) ->
  throw err if err

  files.forEach (file) ->
    count = 0
    eachLine(file, (line) ->
      m = line.match /\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b/i
      if m && m.length > 0
        connection = mysql.createConnection dbConfig
        connection.query 'SELECT id, email FROM leads WHERE email = ?', [ m[0] ], (err, rows, fields) ->
          throw err if err
          connection.end()

          if rows && rows.length > 0
            count++
            connection = mysql.createConnection dbConfig
            connection.query 'DELETE FROM leads WHERE id = ?', [ rows[0].id ], (err) ->
              throw err if err
              connection.end()
              return
          return
        return
    ).then ->
      console.log "#{file} (#{count})"
      return
  return

[dougwilson]

You can probably just use pool, right? Change all the mysql.createConnection to pool.getConnection and connection.end with connection.release.

[ghost]

Do you mean mysql.createPool when you said pool.getConnection ?

[dougwilson]

No. You would createPool once at the very top of your file. Sorry I don't know CoffeeScript, otherwise I would edit your code there.

[ghost]

Ok, you can use this for a online conversion to js : http://js2.coffee/

[dougwilson]

var Promise, config, dbConfig, eachLine, fs, glob, lineReader, mysql;

fs = require('fs');

config = require('config');

mysql = require('mysql');

glob = require('glob');

lineReader = require('line-reader');

Promise = require('bluebird');

dbConfig = config.get('dbConfig');

eachLine = Promise.promisify(lineReader.eachLine, {
  context: lineReader
});

var pool = mysql.createPool(dbConfig);

glob("data/blacklists/*.csv", {}, function(err, files) {
  if (err) {
    throw err;
  }
  files.forEach(function(file) {
    var count;
    count = 0;
    return eachLine(file, function(line) {
      var m;
      m = line.match(/\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b/i);
      if (m && m.length > 0) {
        pool.query('SELECT id, email FROM leads WHERE email = ?', [m[0]], function(err, rows, fields) {
          if (err) {
            throw err;
          }
          if (rows && rows.length > 0) {
            count++;
            pool.query('DELETE FROM leads WHERE id = ?', [rows[0].id], function(err) {
              if (err) {
                throw err;
              }
            });
          }
        });
      }
    }).then(function() {
      console.log(file + " (" + count + ")");
    });
  });
});

// ---
// generated by coffee-script 1.9.2

[dougwilson]

I hoped that helped & thanks for that CoffeeScript link! Here is another example, that uses the async library to manage the operation in a way that is even better than the example above (note I have not actually tested it, but it should be pretty good):

var assert = require('assert');
var async = require('async');
var glob = require('glob');
var lineReader = require('line-reader');
var mysql = require('mysql');

var pool = mysql.createPool(/* your config */);

glob("data/blacklists/*.csv", {}, function(err, files) {
  assert.ifError(err);
  async.eachLimit(files, 10, function (fileName, done) {
    var count = 0;
    lineReader.eachLine(fileName, function (line, last, next) {
      var match = line.match(/\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b/i);
      if (match && match.length > 0) {
        var email = match[0];
        pool.query('SELECT id, email FROM leads WHERE email = ?', [email], function (err, rows, fields) {
          assert.ifError(err);
          if (rows && rows.length > 0) {
            count++;
            pool.query('DELETE FROM leads WHERE id = ?', [rows[0].id], function (err) {
              assert.ifError(err);
              next();
            });
          }
        });
      }
    }, function (err) {
      console.log('%s (%d)', fileName, count);
      done(err);
    });
  }, function (err) {
    assert.ifError(err);
    pool.end();
  });
});