[SERVER-14491] error message for upserts without shard key misleading Created: 07/Jul/14  Updated: 22/May/19  Resolved: 22/May/19

Status: Closed
Project: Core Server
Component/s: Sharding, Write Ops
Affects Version/s: 2.6.3
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Alexander Komyagin Assignee: Janna Golden
Resolution: Done Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Sprint: Sharding 2019-04-22, Sharding 2019-05-06, Sharding 2019-05-20, Sharding 2019-06-03
Participants:

 Description   

Upserts require enforcing a uniqueness constraint via a query - in a sharded cluster the only way to do this is if the upsert query is targeted by the shard key. The error message "does not contain shard key", however, is a bit vague in that the shard key may be included in the update expression of the update, but the query also requires it.

Original description:
2.6 behavior:

mongos> sh.shardCollection("test.f1",{a:1})
{ "collectionsharded" : "test.f1", "ok" : 1 }
mongos> db.f1.update({x:1},{$setOnInsert:{a:1}},{upsert:true})
WriteResult({
   "nMatched" : 0,
   "nUpserted" : 0,
   "nModified" : 0,
   "writeError" : {
       "code" : 61,
       "errmsg" : "upsert { q: { x: 1.0 }, u: { $setOnInsert: { a: 1.0 } }, multi: false, upsert: true } does not contain shard key for pattern { a: 1.0 }"
   }
})

2.4 behavior:

mongos> db.f1.update({x:1},{$setOnInsert:{a:1}},{upsert:true})
Cannot use commands write mode, degrading to compatibility mode
WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"writeError" : {
		"code" : 13123,
		"errmsg" : "Can't modify shard key's value. field: a: 1.0 collection: test.f1"
	}
})



 Comments   
Comment by Janna Golden [ 22/May/19 ]

This has been fixed as a part of SERVER-39158.

Comment by Akira Kurogane [ 01/Mar/17 ]

For ticket search reasons: please note that the error string "does not contain shard key for pattern" in 3.2 (or lower?) is replaced by "An upsert on a sharded collection must contain the shard key and have the simple collation" in 3.4, and the context in this ticket applies to it so long as it is just a shard key issue and collation is absent/irrelevant.

Comment by Alexandr Bolbat [ 29/Jun/16 ]

Hello

We have exactly the same issue.

On our mongo setup (geographically distributed sharded cluster with shard tags) with next configuration:

sh.addTagRange("some-db.some-collection", {tag:MinKey}, {tag:1}, "DEFAULT")
sh.addTagRange("some-db.some-collection", {tag:1}, {tag:1001}, "DC1")
sh.addTagRange("some-db.some-collection", {tag:1001}, {tag:2001}, "DC2")
sh.addTagRange("some-db.some-collection", {tag:2001}, {tag:MaxKey}, "DEFAULT")
 
use some-db
db.createCollection("some-collection")
db.capping.ensureIndex({"tag":1, "_id":1}, {background:true})
db.capping.ensureIndex({"sId":1, "sType":1, "appId":1}, {background:true})
 
sh.enableSharding("some-db")
sh.shardCollection(""some-db.some-collection", {"tag":1, "_id":1})

Can't execute the next "upsert query":

db.some-collection.update({"sId": "user-1", "sType": "1", "appId": "app-1"}, {$setOnInsert: {tag: 1, _id: 1, created: 1}, $set:{updated: 1}}, {upsert: true, multi: false})

Error message is:

WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"nModified" : 0,
	"writeError" : {
		"code" : 61,
		"errmsg" : "upsert { q: { sId: \"user-1\", sType: \"1\", appId: \"app-1\" }, u: { $setOnInsert: { tag: 1.0, _id: 1.0, created: 1.0 }, $set: { updated: 1.0 } }, multi: false, upsert: true } does not contain shard key for pattern { tag: 1.0, _id: 1.0 }"
	}
})

Current workaround is to execute find query with:

{"sId": "user-1", "sType": "1", "appId": "app-1"}

and:

  • if result exist then use '_id' and 'tag' from it
  • otherwise generate new '_id' and set 'local tag'
    then use this fields in 'upsert' query

But i think this work can be done by 'mongos' with '$setOnInsert' operator without our participation.

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