3

MongoDB version 3.6

I got the duplicate key message when I executed this. What's the actual problem?

mongos> db.setup.findAndModify({
... query : {"_id" : ObjectId("5b3c7abbea95705803084cad")},
... update : {$set : { "test" : "xxxx" }},
... upsert : true
... })
2018-07-06T10:13:22.749+0000 E QUERY    [thread1] Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1530872002, 603),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    },
    "operationTime" : Timestamp(1530872002, 602)
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:724:1
@(shell):1:1
mongos>
4

This is the expected case if you have defined a unique index which is non-sparse: documents missing a value for the field with the unique index will have an indexed value of null. This means that only a single document in the collection can be missing the unique field.

The duplicate key message includes the index name and key violation:

"errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",

In your example, the collection setup in database testdb has a unique index on the name field. The attempted upsert failed because the name field was missing and there was already a document in this collection with a missing or null value for name.

If you want to use a unique index without requiring the field to be present you have a few options:

  • Drop and recreate the unique index with the sparse property:

    db.setup.createIndex(
        {name: 1},
        {unique:true, sparse:true}
    )
    
  • Drop and recreate the unique index using a partial filter expression (which allows further criteria if needed):

    db.setup.createIndex(
       { name: 1},
       { unique:true, partialFilterExpression: {name: {$exists:true }}}
    )
    
  • Thanks, @Stennie, it's my bad to not see "name" field in error message. – tao hiko Jul 10 '18 at 4:24
  • Hi. This is not working for me. It still complains about duplicates in the unique field. Can "duplicate documents" contains the field {...,"name":null,...} or they just should not contain that field? – Miguel Feb 25 '19 at 11:39
  • @Miguel I suggest posting a new question with relevant details for your environment (MongoDB server version, driver version, error message, ...). – Stennie Feb 25 '19 at 17:54
  • @Stennie, I just posted the question here. Thanks – Miguel Feb 26 '19 at 14:39
1

I found other indexes on that collection have been set "unique" to be "true" (exclude _id) then it shows error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.