SailsJS-Mongo force sort with sailsjs 1.0

[DarkIsDude]

Sails version:v1.0.0-34 Node version:v6.10.0 NPM version:4.6.1 Operating system:Mac OS 10.12.4

---

Hi all, I migrate from sails 0.12 to sails 1.0. Thanks for amazing work for this release.

I'm using MongoDB version v3.2.10. My model here:

     attributes: {

        name: { type: 'string' },
        user: { model: 'user', required: true },
        analysed: { type: 'boolean', defaultsTo: false },
        m19rows: { collection: 'M19Row', via: 'rhs' },
        m1Arows: { collection: 'M1ARow', via: 'rhs' },
        types: { type: 'json', columnType: 'array' },
        errors: { type: 'json', columnType: 'array', defaultsTo: [] },

    },

M19Row and M1ARow are multiple line (you can have 10000 references between RHS and one type of row). With SailsJS 0.12, I can fetch all lines without error. But with Sails 1.0, I have the following issue:

MongoError: Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.

RHS.findOne({ id: id }).populate('m1Arows').populate('m19rows')

It's seem the new adapter sails-mongo (^1.0.0-7) force sort by id... How I can disable it? Maybe we can add it in doc too?

Thanks for your help.

[sailsbot]

@DarkIsDude Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[sgress454]

@DarkIsDude Thanks for the report. Sails defaults to ordering by ID so that result sets are consistent, but the fact that this can cause a memory overflow definitely calls the practice into question. We'll look into workarounds; in the meantime, does adding an index as suggested by the error message help?

[luislobo]

@sgress454 in my opinion, it should not sort on anything. It should return in whatever order the db is returning the data. If one wants it to be sorted, adding the proper index (and depending on the database type) will put it in the order needed.

[mikermcneil]

@luislobo ideally I think you're right. And the same thing could be said for limit and select/omit. I'm open to it, but it's worth noting that it does make the spec more complicated. Also, it'd make sense to do this at the same time as limit and select/omit. Making that change would involve multiple PRs across core adapters and waterline core, and undergo some careful review by more than one pair of eyes before making its way to master(s).

There are also implications to base values in the normalized/expanded stage 3 query (emphasis on "3") that is received by the adapter, and some decisions to be made there. Perhaps:

Criteria clause	Proposed changes to s3q base value (i.e. value indicating nothing was explicitly passed in)
select	undefined _(or more specifically: excluded altogether. This is instead of auto-expansion to column names, which is what happens now when schema: true. This change would actually make this match the exact same behavior as what currently happens when schema: false. Note that this can't be [], because that is used for expressing that you want the minimal possible set of fields on each record in the result set. Also note that '*' is reserved and may not be used: neither as an attr name or column name)_
omit	[] (instead of just excluding the omit clause from the s3q altogether, which is what happens currently)
where	{} (no change for this one)
skip	0 (no change for this one)
limit	-1 _(instead of Number.MAX_SAFEINTEGER||9007199254740991 which is what it is now)
sort	[] (instead of [{pkColumnName: 'ASC'}], which is what it is now)

[mikermcneil]

@DarkIsDude and thanks for the heads up on this! In the mean time, would you try out @sgress454's solution and let us know?

Related links: • https://stackoverflow.com/questions/27023622/overflow-sort-stage-buffered-data-usage-exceeds-internal-limit • https://docs.mongodb.com/manual/reference/limits/#Sort-Operations

[mikermcneil]

(Tracked here: https://trello.com/c/b57sDgVr/124-adapter-spec-change-to-allow-for-more-flexible-base-values)

[luislobo]

Sidenote: strange thing is, the ID (_id) is inherently indexed in all mongodb collections...

[luislobo]

@mikermcneil well, I think the only "worrying" issue, at least for me, is sorting the result. I could definitely live with auto setting attribute names on select, and excluding omit. I think having a huge top limit, in case someone needs something higher, can just set it with an explicit limit. Waterline should allow a higher limit or not compare against that max limit if it is doing it right now. Will removing the sorting and leaving all the rest conventions affect much the current code?

[DarkIsDude]

Hi all, thanks for your time and your answer.

@mikermcneil @sgress454 I hava already try to add an index on my collection but still same error with this big collection. I will try other thing during this week, if I find a workaround, I will let a comment.

[DarkIsDude]

I have tried to add an index but no progress. Here the error in mongo console: 2017-06-03T14:52:13.977+0200 E QUERY [conn6] Plan executor error during find command: FAILURE, stats: { stage: "PROJECTION", nReturned: 0, executionTimeMillisEstimate: 20, works: 262, advanced: 0, needTime: 261, needYield: 0, saveState: 2, restoreState: 2, isEOF: 0, invalidates: 0, transformBy: { _id: 1, raw: 1, analysed: 1, type: 1, error: 1, error_detail: 1, warnings: 1, seen: 1, filler: 1, numero_de_version_du_format_du_RHS_groupe: 1, numero_FINESS_d_inscription_ePMSI: 1, numero_de_version_du_format_du_RHS: 1, numero_de_sejour_SSR: 1, numero_administratif_de_sejour: 1, groupage_version_classification: 1, groupage_GME_RGME_GN_CM: 1, groupage_GME_RGME_GN_code: 1, groupage_GME_RGME_subdivision: 1, groupage_GME_severite: 1, groupage_code_retour: 1, groupage_indicateur_d_erreur: 1, date_de_debut_de_sejour: 1, date_de_fin_de_sejour: 1, date_de_naissance: 1, sexe: 1, code_postal_du_lieu_de_residence: 1, type_d_hospitalisation: 1, date_d_entree_dans_l_unite_medicale: 1, mode_d_entree_dans_l_unite_medicale: 1, provenance: 1, date_de_sortie_de_l_unite_medicale: 1, mode_de_sortie: 1, destination: 1, numero_de_la_semaine: 1, journees_hors_weekend: 1, journees_weekend: 1, numero_unite_medicale: 1, type_d_autorisation_de_l_unite_medicale: 1, date_de_l_intervention_chirurgicale: 1, finalite_principale_de_prise_en_charge: 1, manifestation_morbide_principale: 1, affection_etiologique: 1, dependance_a_l_habillage: 1, dependance_au_deplacement: 1, dependance_a_l_alimentation: 1, dependance_a_la_continence: 1, dependance_au_comportement: 1, dependance_a_la_relation: 1, nombre_de_diagnostics_associes_dans_ce_RHS_n1: 1, nombre_d_actes_CSARR_dans_ce_RHS_n2: 1, nombre_d_actes_CCAM_dans_ce_RHS_n3: 1, poursuite_du_meme_projet_therapeutique: 1, filler_2: 1, lit_identifie_soins_palliatifs: 1, das: 1, acte_CSARR: 1, acte_CCAM: 1, rhs: 1, id: 1 }, inputStage: { stage: "SORT", nReturned: 0, executionTimeMillisEstimate: 20, works: 262, advanced: 0, needTime: 261, needYield: 0, saveState: 2, restoreState: 2, isEOF: 0, invalidates: 0, sortPattern: { id: 1 }, memUsage: 33622930, memLimit: 33554432, limitAmount: 9007199254740991, inputStage: { stage: "SORT_KEY_GENERATOR", nReturned: 0, executionTimeMillisEstimate: 0, works: 261, advanced: 0, needTime: 1, needYield: 0, saveState: 2, restoreState: 2, isEOF: 0, invalidates: 0, inputStage: { stage: "FETCH", nReturned: 260, executionTimeMillisEstimate: 0, works: 260, advanced: 260, needTime: 0, needYield: 0, saveState: 2, restoreState: 2, isEOF: 0, invalidates: 0, docsExamined: 260, alreadyHasObj: 0, inputStage: { stage: "IXSCAN", nReturned: 260, executionTimeMillisEstimate: 0, works: 260, advanced: 260, needTime: 0, needYield: 0, saveState: 2, restoreState: 2, isEOF: 0, invalidates: 0, keyPattern: { rhs: 1 }, indexName: "_rhs_", isMultiKey: false, isUnique: false, isSparse: false, isPartial: false, indexVersion: 1, direction: "forward", indexBounds: { rhs: [ "[ObjectId('5929a2d2cc1a4e3008623fb3'), ObjectId('5929a2d2cc1a4e3008623fb3')]" ] }, keysExamined: 260, dupsTested: 0, dupsDropped: 0, seenInvalidated: 0 } } } } }

I have two index:

[sailsbot]

@DarkIsDude,@sailsbot,@sgress454,@luislobo,@mikermcneil: Hello, I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message and simply close the issue if possible. On the other hand, if you are still waiting on a patch, please post a comment to keep the thread alive (with any new information you can provide).

If no further activity occurs on this thread within the next 3 days, the issue will automatically be closed.

Thanks so much for your help!

[DarkIsDude]

Hi @sailsbot , this issue is still open and I still need help to configure my mongo. Thanks

[sgress454]

Still tracking this -- trying to knock out some other small-fry issues before I tackle this beastie...

[sgress454]

This should be updated in Waterline v0.13.1!