Mongodb generating duplicate _id value!

[Karanveer Singh]

Hi,
This has been bothering me for weeks now.
I have a python script that periodically queries timeseries data from a crypto api, and inserts it into my mongodb collection.

Every now and then, seemingly at random, the collection.insert_many function will fail with the error message shown below. I formatted the json so it's easier to read.
The id field is not generated by me and is supposed to be created and managed by Mongo. So how can I prevent the collision on a field I don't provide?

pymongo.errors.BulkWriteError: batch op errors occurred, full error: {  "writeErrors":[    {      "index":0,      "code":11000,      "keyPattern":{        "_id":1      },      "keyValue":{        "_id":"ObjectId(""6255cce8bdfc280a8c8d6ee8"")"      },      "errmsg":"E11000 duplicate key error collection: crypto.btc_ohlc_minute index: _id_ dup key: { _id: ObjectId('6255cce8bdfc280a8c8d6ee8') }",      "op":{        "timestamp":1649786640,        "time":"Timestamp(""2022-04-12 18:04:00+0000",        "tz=""UTC"")",        "symbol":"BTCUSD",        "open":39993.64,        "high":40008.58,        "low":39979.97,        "close":40007.3,        "volume":0.0382197,        "_id":"ObjectId(""6255cce8bdfc280a8c8d6ee8"")"      }    }  ],  "writeConcernErrors":[],  "nInserted":0,  "nUpserted":0,  "nMatched":0,  "nModified":0,  "nRemoved":0,  "upserted":[] }

[Roland Mueller]

Just a guess: the timestamp of two entries 'op' that are in epoch (=seconds since 1.1.1970 00:00) is the same and causes the error since timestamp is defined "unique:true".

Please provide the schema of your op records! This would shed more light on the issue and allow to reduce the amount of guesswork.

[Karanveer Singh]

I do have a unique timestamp index, but before I insert, I have the following python logic to filter only those timestamps which are not present in the collection.

set_of_timestamps = {entry["timestamp"] for entry in collection.find()} # filter the records by timestamps not already present in db data_dict = [    rec    for rec in df.to_dict("records")    if rec["timestamp"] not in set_of_timestamps ] if data_dict:    logger.info(f"Inserting to db...")    collection.insert_many(data_dict)    logger.info(f"Completed inserting {len(data_dict)} record(s) to db.")

This is a sample document.

{     "_id": {    "$oid": "61f6ba8d23135306891e8240"  },     "timestamp": 1640908800,     "open": 47110.3,     "high": 47112.2,     "low": 47097.03,     "close": 47097.03,     "volume": 0.15180822,     "time": { "$date": "2021-12-31T00:00:00Z"  },     "symbol": "BTCUSD" }

Is there some other way for me to filter timestamps to those not present in the db?

[Roland Mueller]

... data_dict = [    rec    for rec in df.to_dict("records")    if rec["timestamp"] not in set_of_timestamps ] ...
Isn't it possible that the data that will be inserted (df.to_dict("records")) already contains duplicates in terms of "timestamp"?

[Karanveer Singh]

Oh. I hadn't considered that.

I've now updated to

records = {r["timestamp"]: r for r in df.to_dict("records")} # filter the records by timestamps not already present in db data_dict = [ rec for rec in records.values if rec["timestamp"] not in set_of_timestamps ]

[Prasad Saya]

Hello. Just some thoughts on the error and its handling.

"errmsg":"E11000 duplicate key error collection: crypto.btc_ohlc_minute index: _id_ dup key: { _id: ObjectId('6255cce8bdfc280a8c8d6ee8') }"...

This error says that the unique index constraint on the _id field is violated. Every MongoDB collection document has this mandatory field, which functions as a "primary key", is of type ObjectId and is created by default. This value can be user supplied, optionally.

When doing a bulk insert (as using the insertMany method), when any error occurs the operation is aborted - as in this case. But, there is an option to continue with the bulk insert even when there is an error. And you can try to use it. The insertMany has an optional parameter ordered, and is by default True - the process aborts when there is an error. When set to False, the insert operation continues even when there is an error. This is useful when inserting many documents and do not need to stop the process in case of an error, like an "E11000". This kind of situation is quite common in MongoDB bulk operations.