[SERVER-424] mongoimport mangles large numbers (BIGINT) on import via CSV. Created: 13/Nov/09  Updated: 12/Jul/16  Resolved: 13/Nov/09

Status: Closed
Project: Core Server
Component/s: Usability
Affects Version/s: 1.1.3
Fix Version/s: 1.1.4

Type: Bug Priority: Minor - P4
Reporter: Alexey Zilber Assignee: Eliot Horowitz (Inactive)
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Linux, AWS m1.large instance, CentOS 5.2 x86_64
Linux ip-10-244-51-31 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:34:28 EST 2008 x86_64 x86_64 x86_64 GNU/Linux


Participants:

 Description   

This is a show stopper folks. Exported data from mysql in csv format. Imported into MongoDB. Some of the mysql BIGINT fields got mangled and created dupes, this relates to the dupe seen in (SERVER-423).
Using this import cmd:
mongoimport -d myfarm -c Farm --fields uid,name,last_change,width,height,plots,state,decorations,activity --type csv --file /mnt/Farm.csv --dbpath /mnt/mdb/ --drop

From the CSV:

[root@ip-10-244-51-31 mnt]# grep -i '501272713' Farm.csv
501272713,"","2009-11-10 08:08:52",3,2,6,"a:3:{i:0;a:2:{i:0;a:12:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"6\";s:5:\"stage\";i:5;s:10:\"stage_next\";i:6;s:11:\"stage_start\";i:1257862130;s:6:\"stolen\";i:2;s:9:\"stolen_by\";a:1:

{i:1542862066;i:2;}

s:9:\"harvested\";i:0;s:5:\"units\";i:31;}i:1;a:11:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"1\";s:5:\"stage\";i:2;s:10:\"stage_next\";i:3;s:11:\"stage_start\";i:1257862130;s:6:\"stolen\";i:0;s:9:\"stolen_by\";a:0:{}s:9:\"harvested\";i:0;}}i:1;a:2:{i:0;a:12:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"5\";s:5:\"stage\";i:2;s:10:\"stage_next\";i:3;s:11:\"stage_start\";i:1257862130;s:6:\"stolen\";i:0;s:9:\"stolen_by\";a:0:{}s:9:\"harvested\";i:0;s:8:\"weeds_by\";b:0;}i:1;a:4:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;}}i:2;a:2:{i:0;a:12:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"4\";s:5:\"stage\";i:2;s:10:\"stage_next\";i:3;s:11:\"stage_start\";i:1257862130;s:6:\"stolen\";i:0;s:9:\"stolen_by\";a:0:{}s:9:\"harvested\";i:0;s:7:\"bugs_by\";b:0;}i:1;a:4:

{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;}

}}","","a:4:{i:0;a:3:

{s:3:\"uid\";s:10:\"1542862066\";s:1:\"t\";i:1257862132;s:6:\"action\";s:38:\"volé 2 Aubergines de Ferme de Claire!\";}

i:1;a:3:

{s:3:\"uid\";s:10:\"1542862066\";s:1:\"t\";i:1257862132;s:6:\"action\";s:44:\"a éliminé vos insectes le Ferme de Claire!\";}

i:2;a:3:

{s:3:\"uid\";s:10:\"1542862066\";s:1:\"t\";i:1257862132;s:6:\"action\";s:49:\"a éliminé vos mauves herbes le Ferme de Claire!\";}

i:3;a:3:{s:3:\"uid\";s:10:\"1542862066\";s:1:\"t\";i:1257862131;s:6:\"action\";s:41:\"a arrosé vos plantes le Ferme de Claire!\";}}"
[root@ip-10-244-51-31 mnt]# grep -i '2009-10-10 19:29:11' Farm.csv
100000224825481,"army ferme","2009-10-10 19:29:11",3,2,6,"a:3:{i:0;a:2:{i:0;a:12:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"6\";s:5:\"stage\";i:5;s:10:\"stage_next\";i:6;s:11:\"stage_start\";i:1255169952;s:6:\"stolen\";i:4;s:9:\"stolen_by\";a:1:

{i:1494751562;i:4;}

s:9:\"harvested\";i:0;s:5:\"units\";i:31;}i:1;a:12:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"1\";s:5:\"stage\";i:5;s:10:\"stage_next\";i:6;s:11:\"stage_start\";i:1255195152;s:6:\"stolen\";i:4;s:9:\"stolen_by\";a:1:

{i:1494751562;i:4;}

s:9:\"harvested\";i:0;s:5:\"units\";i:22;}}i:1;a:2:{i:0;a:13:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"5\";s:5:\"stage\";i:5;s:10:\"stage_next\";i:6;s:11:\"stage_start\";i:1255216752;s:6:\"stolen\";i:1;s:9:\"stolen_by\";a:1:

{i:1494751562;i:1;}

s:9:\"harvested\";i:0;s:8:\"weeds_by\";b:0;s:5:\"units\";i:24;}i:1;a:4:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;}}i:2;a:2:{i:0;a:13:{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;s:5:\"plant\";s:1:\"4\";s:5:\"stage\";i:5;s:10:\"stage_next\";i:6;s:11:\"stage_start\";i:1255205952;s:6:\"stolen\";i:2;s:9:\"stolen_by\";a:1:

{i:1494751562;i:2;}

s:9:\"harvested\";i:0;s:7:\"bugs_by\";b:0;s:5:\"units\";i:19;}i:1;a:4:

{s:3:\"dry\";i:0;s:5:\"weeds\";i:0;s:4:\"bugs\";i:0;s:10:\"fertilized\";i:0;}

}}","","a:4:{i:0;a:3:

{s:3:\"uid\";s:10:\"1494751562\";s:1:\"t\";i:1255220951;s:6:\"action\";s:31:\"volé 2 Carottes de army ferme!\";}

i:1;a:3:

{s:3:\"uid\";s:10:\"1494751562\";s:1:\"t\";i:1255220950;s:6:\"action\";s:30:\"volé 1 Patates de army ferme!\";}

i:2;a:3:

{s:3:\"uid\";s:10:\"1494751562\";s:1:\"t\";i:1255220949;s:6:\"action\";s:33:\"volé 4 Aubergines de army ferme!\";}

i:3;a:3:{s:3:\"uid\";s:10:\"1494751562\";s:1:\"t\";i:1255220947;s:6:\"action\";s:28:\"volé 4 Radis de army ferme!\";}}"

From MongoDB:

> var cursor = db.Farm.find(

{ uid:501272713 }

)
> while (cursor.hasNext())

{ print(tojson(cursor.next())); }

{
"_id" : ObjectId("4afddb3f67b84c86a693ce82"),
"uid" : 501272713,
"name" : "\"\"",
"last_change" : "\"2009-11-10 08:08:52\"",
"width" : 3,
"height" : 2,
"plots" : 6,
"state" : "\"a:3:{i:0;a:2:{i:0;a:12:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"6\\";s:5:\\"stage\\";i:5;s:10:\\"stage_next\\";i:6;s:11:\\"stage_start\\";i:1257862130;s:6:\\"stolen\\";i:2;s:9:\\"stolen_by
";a:1:

{i:1542862066;i:2;}

s:9:\\"harvested\\";i:0;s:5:\\"units\\";i:31;}i:1;a:11:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"1\\";s:5:\\"stage\\";i:2;s:10:\\"stage_next\\";i:3;s:11:\\"stage_start\\";i:1257862130;s:6:\\"stolen\\";i:0;s:9:\\"stolen_by\\";a:0:{}s:9:\\"harvested\\";i:0;}}i:1;a:2:{i:0;a:12:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"5\\";s:5:\\"stage\\";i:2;s:10:\\"stage_next\\";i:3;s:11:\\"stage_start\\";i:1257862130;s:6:\\"stolen\\";i:0;s:9:\\"stolen_by\\";a:0:{}s:9:\\"harvested\\";i:0;s:8:\\"weeds_by\\";b:0;}i:1;a:4:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;}}i:2;a:2:{i:0;a:12:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"4\\";s:5:\\"stage\\";i:2;s:10:\\"stage_next\\";i:3;s:11:\\"stage_start\\";i:1257862130;s:6:\\"stolen\\";i:0;s:9:\\"stolen_by\\";a:0:{}s:9:\\"harvested\\";i:0;s:7:\\"bugs_by
";b:0;}i:1;a:4:

{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;}

}}\"",
"decorations" : "\"\"",
"activity" : "\"a:4:{i:0;a:3:

{s:3:\\"uid\\";s:10:\\"1542862066\\";s:1:\\"t\\";i:1257862132;s:6:\\"action\\";s:38:\\"volé 2 Aubergines de Ferme de Claire!\\";}

i:1;a:3:

{s:3:\\"uid\\";s:10:\\"1542862066\\";s:1:\\"t\\";i:1257862132;s:6:\\"action\\";s:44:\\"a éliminé vos insectes le Ferme de Claire!\\";}

i:2;a:3:

{s:3:\\"uid\\";s:10:\\"1542862066\\";s:1:\\"t\\";i:1257862132;s:6:\\"action\\";s:49:\\"a éliminé vos mauves herbes le Ferme de Claire!\\";}

i:3;a:3:{s:3:\\"uid\\";s:10:\\"1542862066\\";s:1:\\"t\\";i:1257862131;s:6:\\"action\\";s:41:
"a arrosé vos plantes le Ferme de Claire!\\";}}\""
}
{
"_id" : ObjectId("4afde0da67b84c86a6c65371"),
"uid" : 501272713,
"name" : "\"army ferme\"",
"last_change" : "\"2009-10-10 19:29:11\"",
"width" : 3,
"height" : 2,
"plots" : 6,
"state" : "\"a:3:{i:0;a:2:{i:0;a:12:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"6\\";s:5:\\"stage\\";i:5;s:10:\\"stage_next\\";i:6;s:11:\\"stage_start\\";i:1255169952;s:6:\\"stolen\\";i:4;s:9:\\"stolen_by
";a:1:

{i:1494751562;i:4;}

s:9:\\"harvested\\";i:0;s:5:\\"units\\";i:31;}i:1;a:12:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"1\\";s:5:\\"stage\\";i:5;s:10:\\"stage_next\\";i:6;s:11:\\"stage_start\\";i:1255195152;s:6:\\"stolen\\";i:4;s:9:\\"stolen_by
";a:1:

{i:1494751562;i:4;}

s:9:\\"harvested\\";i:0;s:5:\\"units\\";i:22;}}i:1;a:2:{i:0;a:13:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"5\\";s:5:\\"stage\\";i:5;s:10:\\"stage_next\\";i:6;s:11:\\"stage_start\\";i:1255216752;s:6:\\"stolen\\";i:1;s:9:\\"stolen_by
";a:1:

{i:1494751562;i:1;}

s:9:\\"harvested\\";i:0;s:8:\\"weeds_by\\";b:0;s:5:\\"units\\";i:24;}i:1;a:4:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;}}i:2;a:2:{i:0;a:13:{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;s:5:\\"plant\\";s:1:\\"4\\";s:5:\\"stage\\";i:5;s:10:\\"stage_next\\";i:6;s:11:\\"stage_start\\";i:1255205952;s:6:\\"stolen\\";i:2;s:9:\\"stolen_by
";a:1:

{i:1494751562;i:2;}

s:9:\\"harvested\\";i:0;s:7:\\"bugs_by\\";b:0;s:5:\\"units
";i:19;}i:1;a:4:

{s:3:\\"dry\\";i:0;s:5:\\"weeds\\";i:0;s:4:\\"bugs\\";i:0;s:10:\\"fertilized\\";i:0;}

}}\"",
"decorations" : "\"\"",
"activity" : "\"a:4:{i:0;a:3:

{s:3:\\"uid\\";s:10:\\"1494751562\\";s:1:\\"t\\";i:1255220951;s:6:\\"action\\";s:31:\\"volé 2 Carottes de army ferme!\\";}

i:1;a:3:

{s:3:\\"uid\\";s:10:\\"1494751562\\";s:1:\\"t\\";i:1255220950;s:6:\\"action\\";s:30:\\"volé 1 Patates de army ferme!\\";}

i:2;a:3:

{s:3:\\"uid\\";s:10:\\"1494751562\\";s:1:\\"t\\";i:1255220949;s:6:\\"action\\";s:33:\\"volé 4 Aubergines de army ferme!\\";}

i:3;a:3:{s:3:\\"uid\\";s:10:\\"1494751562\\";s:1:\\"t\\";i:1255220947;s:6:\\"action\\";s:28:
"volé 4 Radis de army ferme!\\";}}\""
}


O M G!!!!



 Comments   
Comment by Eliot Horowitz (Inactive) [ 13/Nov/09 ]

Please put the version using in the environment.

Comment by auto [ 13/Nov/09 ]

Author:

{'name': 'Eliot Horowitz', 'email': 'eliot@10gen.com'}

Message: fix import with long long SERVER-424
http://github.com/mongodb/mongo/commit/02dfcf62e7136b0b1dc74d9c9fe8e37a020d7b8f

Comment by Alexey Zilber [ 13/Nov/09 ]

I've done a followup test using quoted and unquoted csv data. I can replicate this without a problem. FARMUID db has everything encapsulated in quotes, and FARMUID2 is set to optional, so it doesn't enclose int. Here are the results:

> use FARMUID
switched to db FARMUID

> var cursor = db.Farm.find(

{ uid:"\"501272713\"" }

)
> while (cursor.hasNext())

{ print(tojson(cursor.next())); }
{
"_id" : ObjectId("4afdf5dc133aac262779d052"),
"uid" : "\"501272713\"",
"name" : "\"\"",
"last_change" : "\"2009-11-10 08:08:52\""
}
> var cursor = db.Farm.find({ last_change:"\"2009-10-10 19:29:11\"" })
> while (cursor.hasNext()) { print(tojson(cursor.next())); }

{
"_id" : ObjectId("4afdf654133aac2627ae6c95"),
"uid" : "\"100000224825481\"",
"name" : "\"army ferme\"",
"last_change" : "\"2009-10-10 19:29:11\""
}

> use FARMUID2
switched to db FARMUID2

> var cursor = db.Farm.find(

{ uid:501272713 }

)
> while (cursor.hasNext())

{ print(tojson(cursor.next())); }

{
"_id" : ObjectId("4afdf6fd8f0f033962fa4852"),
"uid" : 501272713,
"name" : "\"\"",
"last_change" : "\"2009-11-10 08:08:52\""
}
{
"_id" : ObjectId("4afdf7828f0f0339632ee8f2"),
"uid" : 501272713,
"name" : "\"army ferme\"",
"last_change" : "\"2009-10-10 19:29:11\""
}

Please fix!

Comment by Alexey Zilber [ 13/Nov/09 ]

Fyi, this is the mysql schema:

mysql> describe Farm;
------------------------------------------------------+

Field Type Null Key Default Extra

------------------------------------------------------+

uid bigint(20) NO PRI NULL  
name varchar(255) NO   NULL  
last_change timestamp NO   CURRENT_TIMESTAMP  
width tinyint(4) NO   NULL  
height tinyint(4) NO   NULL  
plots int(11) NO   6  
state longtext NO   NULL  
decorations longtext NO   NULL  
activity longtext NO   NULL  

------------------------------------------------------+
9 rows in set (0.00 sec)

Generated at Thu Feb 08 02:54:03 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.