Paging over a large number of records returns repeat values.

[Vignesh Badrinath]

I noticed something strange while paging over a large number of records. I sometimes get duplicates or extra results inspite of using the same sort. Here is an example query

query: { $query: { campaign_job_id:
793 }, orderby: { destination_number: 1 } } planSummary: IXSCAN {
campaign_job_id: 1 }, IXSCAN { campaign_job_id: 1 } ntoreturn:1000
ntoskip:11000 nscanned:22894 nscannedObjects:22894 keyUpdates:0
numYields:42 locks(micros) r:358554 nreturned:448 reslen:144759 190ms

The query should return only 447 in the last page as the total number of records for this query is 11447. This happened a few times with varying number of extra records. Also sometimes I get 14447 records totally but the results set sometimes contains duplicate values. Anyone had this situation before ?

Mongo db version 2.6.9 hosted by compose.io.

[Tim Hawkins]

You should use the "$snapshot" query operator.

https://docs.mongodb.org/v3.0/reference/operator/meta/snapshot/

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/d9d14eee-32d2-403a-8933-bc5f68f03e64%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chris De Bruyne]

I'm not sure if $snapshot would help because the docs explicitly state :

"Even in snapshot mode, objects inserted or deleted during the lifetime of the cursor may or may not be returned."

@Vignesh can you give us the complete query ?

The output you provided does show skip and limit but not the query you are actually doing

Kind regards

Chris

[Tim Hawkins]

Yes but immeadiatly above that it says

"The $snapshot operator prevents the cursor from returning a document more than once because an intervening write operation results in a move of the document."

The original complaint was about duplicated objects appearing in the output.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/b045faa8-f7bf-4bd9-b577-899b511990a5%40googlegroups.com.

[Tim Hawkins]

What about updates that would have changed the sort key position?.

On Sat, Nov 14, 2015, 02:23 Vignesh Badrinath <vig...@sent.ly> wrote:

The duplicates don't happen in the same page. They happen across pages. Since I am ordering by destination number, there should be no duplicates across pages right ? 

P.S There are no inserts that would have added results to the query during its paging.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/1c049ef7-70a9-48ec-b2aa-dc290c3009e0%40googlegroups.com.

[Vignesh Badrinath]

There were no updates going on that would have affected the result set of the queries.

[Chris De Bruyne]

That is very strange indeed, and does not make sense at all,

can you provide sample data and the query you are doing to reproduce this?

[Vignesh Badrinath]

I will try to come up with sample data. Just to be clear about the
issue. This problem only came up when the collection size became very
large. Also when I say updates not modifying the query, I mean that in
the following query : { $query: { campaign_job_id:
793 }, orderby: { destination_number: 1 } } ;

The number of documents totally for that query is fixed throughout the
paging. Some of documents might get updated(not the destination
number) during the course of the paging. Hope that makes it clear,

On Tue, Nov 17, 2015 at 4:29 AM, Chris De Bruyne

> You received this message because you are subscribed to a topic in the
> Google Groups "mongodb-user" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/mongodb-user/mFJo6iHbyn8/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

> mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at http://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/mongodb-user/0d430db8-c521-4085-95a9-c9efbec65afb%40googlegroups.com.

[Chris De Bruyne]

Thanks for clearing this out.

That's what Tim was pointing at.

The only way to get this behaviour, is if there are updates done by another thread/program to the same set of documents you are quering on.

This would of course change the data and thus the result of the sorting.

Because you are doing a sort, you can't use $snapshot

So you need to change your code so you dont use skip but another query parameter that for every new page is updated to represent the new starting point.

Take a look at this post : http://blog.mongodirector.com/fast-paging-with-mongodb/

And adjust to your needs.

Kind regards

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CA%2B5Txvjgn975VeAqqP2Z_eBma6h6qPVtiUakbqA0Tt_ozhOEdw%40mail.gmail.com.

[Vignesh Badrinath]

I actually did move to that. Just was curious as to why this was
happened. I would assume if the updates weren't to the destination
number or campaign job Id there shouldn't be an issue.

On Tue, Nov 17, 2015 at 3:41 PM, Chris De Bruyne

> https://groups.google.com/d/msgid/mongodb-user/CAJt8-NA%3DA_mG56boA5PcKM3aCnDPSGASeGO74NdpXif6U_dVUw%40mail.gmail.com.

[Chris De Bruyne]

What I think happend is that the other inserts/updates changed the index your query is using.

As a result when you query with skip and limit, the order and data in the index would have changed and thus the result of your query.

This of course as far as I understand indexes ;-)

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CA%2B5TxvhtMJ1i4V8-M1n%2By58fYMaNJXTkYHAZVWNDT0HHc%2BV3Mg%40mail.gmail.com.

[Vignesh Badrinath]

Oh okay. Thank you so much for your help :).

On Tue, Nov 17, 2015 at 4:05 PM, Chris De Bruyne

> https://groups.google.com/d/msgid/mongodb-user/CAJt8-NBLO_fvgzj_URKDxLSf%3D-2Sv53yB_fW7sn2GsnnnTDWAQ%40mail.gmail.com.