skip to Main Content

I have this query, but still get E11000 duplicate key error collection error

$collection->insertMany($allData,['ordered' => false]);

MongoDB: 4.4, php driver version: 1.12.0

—————————–Update——————————

According to dododo’s answer (and comments), all documents will be inserted successfully, but you will get the duplicate error at the end.

In other words, this code will insert all documents without killing the app:

try{
    $collection->insertMany($allData,['ordered' => false]);
}catch(Exception $e){
    echo "Some documents are duplilcate but everthing ok.."
}

2

Answers


  1. The error is quite clear.

    Between the array $allData and the table contents you have duplicate keys.

    CASUS: Empty Table

    So let’s say your database table is:

    _id | created_at | updated_at | value

    where _id is your primary key in the database.

    And your $allData is

    $allData = [
       ['_id' => 1, 'value' => 'foo']
       ['_id' => 2, 'value' => 'bar']
       ['_id' => 3, 'value' => 'baz']
       ['_id' => 1, 'value' => 'bal']
    ];
    

    As you can see, the primary key insert data has duplicate values, the value 1 appears twice.

    This gives then an error.

    The same can occur if a database column is marked with an UNIQUE INDEX forcing values to be unique.

    Dump the data that’s present in $allData, study the contents, and remove the duplicates.

    Casus: Filled table

    _id | created_at | updated_at | value
    1 | 2022-01-02 | 2022-01-02 | foo
    2 | 2022-01-02 | 2022-01-02 | bar
    3 | 2022-01-02 | 2022-01-02 | baz

    And your $allData is

    $allData = [
       ['_id' => 1, 'value' => 'foo']
       ['_id' => 2, 'value' => 'bar']
       ['_id' => 3, 'value' => 'baz']
    ];
    

    What happens is, it tries to insert a row into the table with primary key 1. The database engine checks for existing primary keys with that value. It finds one present, as there is already a row with primary key one. Then it throws an error, wich gets caught by php, which then throws it’s own exception which ends up with you. Duplicate key found.

    In that case use upsert your query with the setOnInsert modifier for upsert. Something like:

    $collection->insertMany($allData,[
           'ordered' => false, 
           'upsert' => true, 
           '$setOnInsert' => ['_id', 'value']
        ]);
    
    Login or Signup to reply.
  2. This is expected behavior that mentioned in the doc here. Also, it’s not php related, same you can see in the shell:

    db.coll.insertMany([{ _id: 1}, {_id : 1}, {_id: 2}], {ordered: false})
    

    which fails with:

    uncaught exception: BulkWriteError({
            "writeErrors" : [
                    {
                            "index" : 1,
                            "code" : 11000,
                            "errmsg" : "E11000 duplicate key error collection: test.coll2 index: _id_ dup key: { _id: 1.0 }",
                            "op" : {
                                    "_id" : 1
                            }
                    }
            ],
            "writeConcernErrors" : [ ],
            "nInserted" : 2,
            "nUpserted" : 0,
            "nMatched" : 0,
            "nModified" : 0,
            "nRemoved" : 0,
            "upserted" : [ ]
    })  
    

    result:

    MongoDB Enterprise mongos> db.coll.find()
    { "_id" : 1 }
    { "_id" : 2 }
    

    pay attention that there are 2 inserted documents, first attempt of { _id: 1 } and { _id : 2 }.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search