mongodb nodejs multiple insert issue

Question

I'm trying to import a table from mysql to mongodb straight without any schema changes.

I wrote a small node script for that and my issue is with the way i implemented it.

Maybe I hit some limit of using mongo db insert limit while using it inside a loop. I think this problem would not have had come if it was in reverse (maybe not! )

So here's the thing. The row in the mysql table is more than 100,000 but when the loop hit's more than around 30000 the number of inserted items just get reduced.

so let's say if there was 100,000 items in the mysql table after complete import using the below mentioned script, i get only a maximum of 37000 or so.

My strong suscpicion is either in the node script/node mongodb connector, or some bug in the script or lastly a limit in mongodb concurrent db inserts.

I'm pasting the script below. Hoping i get a way around it.

Thanks,

var http = require('http'),
  mysql = require('mysql'),
  mongo = require('mongodb').MongoClient,
  format = require('util').format;
var connection = mysql.createConnection({
  user: "xxx",
  password: "xxx",
  database: "mydb" 
});

connection.connect();
var query = "select * from mytable";
var mysqlrows = '';
connection.query(query, function(err,rows,fields){
  if(err) throw err;

  console.log(rows.length+'rows found.');

  mongo.connect('mongodb://root:[email protected]:27017/mydb', function(err, db){ 
    if (err)
      throw err;

    var collection = db.collection('mytable');
    for(var i=0; i<rows.length;i++)
    {
       //console.log(JSON.stringify(rows[i]));

      (function(i){
        collection.insert(rows[i],function(err,docs){});
        console.log(i);
      })(i);
    }

    db.close();        
  });    
});
connection.end();

insert can take an array of docs; have you tried just passing rows into insert directly instead of one by one? — JohnnyHK
– JohnnyHK, Commented Jul 2, 2013 at 16:29
Thanks for the tip, i tried removing the loop and then tried inserting the whole "rows" array itself. It does work as you say, but here again, there is a problem. If the "rows" array has a count of <1150 it works flawlessly, but if the rows count goes >1200 or so according to my testing, it fails again. Something is terribly wrong in the method to insert large number of rows into mongdb concurrently. Here again I'm suspicious about some kind of mongodb multiple insert limit. But this is not mentioned in the mongo db limits document. [docs.mongodb.org/manual/reference/limits/] — Thomas George
– Thomas George, Commented Jul 2, 2013 at 17:42

JohnnyHK · Accepted Answer · 2013-07-02 20:48:38Z

2

The problem is that you're not waiting for the insert operations to complete before closing your connection to MongoDb via the db.close(); call. You need to keep track of your outstanding asynchronous requests and then only call db.close(); when they've all completed.

answered Jul 2, 2013 at 20:48

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas George Over a year ago

Thanks mate, Looks like it is, just removed db.close() & connection.end() from the code & it works fine in a round about way. But I'll update this thread with the final code which will use this concept, but still closing all the connections so that the solution becomes final.

mjmoody383 Over a year ago

I found using async.series quite helpful in managing database update operations, especially after reviewing this answer from freakish

innoSPG · Accepted Answer · 2013-07-02 16:24:24Z

0

To make sure that you are getting all the data from mySQL, try to access the last row. If you can get it, use the flag w and j of mongodb to make sure that each call inserts the data before moving to the next. with the w and j flag, you should consider multiple inserts by inserting multiple rows at each call using and array.

answered Jul 2, 2013 at 16:24

innoSPG

4,6561 gold badge31 silver badges42 bronze badges

3 Comments

Thomas George Over a year ago

Thanks @innospg, the mysql does return all the 100,000 rows as it's confirmed by this line console.log(rows.length+'rows found.'); in the script. What is the w & j flag you are talking about? This specific about the nodejs driver i'm using?

innoSPG Over a year ago

w and j are the write concern parameters in the last release of mongodb. To debug your code, set the option safe to true and check the parameters err of the callback function.

Thomas George Over a year ago

Thanks I guess the mongodb node drive insert option safe:true is same as w:1. Also setting the option j:1 ie journaling enabled, which makes write operation safe even if the mongod process fails automatically sets the w:1 flag. For my case j:1 flag was not of an issue which was a small import script with all counts of the items logged in the script itself. I think j:1 option should be always a go for production insert usage.

Collectives™ on Stack Overflow

mongodb nodejs multiple insert issue

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related