syncIndexes() should handle schema-level collation differences

[niftylettuce]

I ran into an issue today where sorting on an indexed field was hitting the 32MB MongoDB memory issue.

It turns out that defining new Schema({ ..., full_name: { index: true, type: String } }, { collation: { locale: 'en' } }); doesn't automatically set the collation option on the index for full_name.

I've fixed it manually by:

> db.users.dropIndex({ full_name: 1 });
{ "nIndexesWas" : 38, "ok" : 1 }
> db.users.ensureIndex({ full_name: 1 }, { collation: { locale: 'en' } });
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 37,
    "numIndexesAfter" : 38,
    "ok" : 1
}

However if I migrate this to another server, Mongoose doesn't set this by default, so I'd have to re-run the above script.

Is there a way to set this as a global default if collation: ... option is passed to the schema for indices?

[vkarpov15]

That's an interesting idea. Will consider for a future release

[niftylettuce]

Thanks

[niftylettuce]

For anyone else reading this, here is the workaround (remove index: true in your schema first):

// fix per <https://github.com/Automattic/mongoose/issues/7621>
User.index({ full_name: 1 }, { collation: { locale: 'en' } });

[Enado95]

can't you add strength for case insensitivity @niftylettuce ? I tried and it didn't really work. Only works if i create the collection manually first. trying to achieve this link behavior so i don't have to specify on each query

[vkarpov15]

I took a closer look and it looks like #9912 does fix this:

    const testSchema = new Schema(
      { name: { type: String, index: true } },
      { collation: { locale: 'en' } }
    );
    const Test = db.model('Test', testSchema);

    await Test.init();

    console.log(await Test.collection.listIndexes().toArray());

Prints:

[
  {
    v: 2,
    key: { _id: 1 },
    name: '_id_',
    collation: {
      locale: 'en',
      caseLevel: false,
      caseFirst: 'off',
      strength: 3,
      numericOrdering: false,
      alternate: 'non-ignorable',
      maxVariable: 'punct',
      normalization: false,
      backwards: false,
      version: '57.1'
    }
  },
  {
    v: 2,
    key: { name: 1 },
    name: 'name_1',
    background: true,
    collation: {
      locale: 'en',
      caseLevel: false,
      caseFirst: 'off',
      strength: 3,
      numericOrdering: false,
      alternate: 'non-ignorable',
      maxVariable: 'punct',
      normalization: false,
      backwards: false,
      version: '57.1'
    }
  }
]

However, keep in mind that adding a collation option to your schema will not affect existing indexes, unless you call syncIndexes(). Mongoose doesn't modify existing indexes by default. There is an issue with syncIndexes() with different collations that we will fix for 6.3.