61 database runtime_error ai_generated true

MongoServerError: Query targeting more than one shard but the query does not contain the shard key

ID: database/mongodb-shard-key-missing-in-query

Also available as: JSON · Markdown · 中文
80%Fix Rate
87%Confidence
1Evidence
2024-09-10First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MongoDB 5.0 active — — —
MongoDB 6.0 active — — —
MongoDB 7.0 active — — —

Root Cause

A query issued against a sharded MongoDB collection does not include the shard key in the filter, forcing the query to be broadcast to all shards, which is inefficient and may hit a configurable limit or be disallowed by recent MongoDB versions.

generic

中文

针对分片 MongoDB 集合的查询在过滤条件中不包含分片键，导致查询被广播到所有分片，效率低下，可能触及可配置限制或被较新的 MongoDB 版本禁止。

Official Documentation

https://www.mongodb.com/docs/manual/core/sharding-query-isolation/

Workarounds

  1. 95% success Include the shard key field in the query filter. For example, if shard key is { userId: 1 }, change db.collection.find({ name: "Alice" }) to db.collection.find({ userId: 123, name: "Alice" }). 
    Include the shard key field in the query filter. For example, if shard key is { userId: 1 }, change db.collection.find({ name: "Alice" }) to db.collection.find({ userId: 123, name: "Alice" }).
  2. 85% success If the shard key is unknown, retrieve it: sh.status() or use db.collection.getShardDistribution(); then modify the query accordingly. 
    If the shard key is unknown, retrieve it: sh.status() or use db.collection.getShardDistribution(); then modify the query accordingly.

中文步骤

  1. Include the shard key field in the query filter. For example, if shard key is { userId: 1 }, change db.collection.find({ name: "Alice" }) to db.collection.find({ userId: 123, name: "Alice" }).
  2. If the shard key is unknown, retrieve it: sh.status() or use db.collection.getShardDistribution(); then modify the query accordingly.

Dead Ends

Common approaches that don't work:

  1. Add a hint to force the query to a specific shard without including the shard key 90% fail

    Hints cannot bypass the shard key requirement; the query still targets all shards and fails.

  2. Create an index on the queried field(s) to improve performance 85% fail

    Indexes do not change query routing; the query still lacks the shard key for targeted routing.