Agenda fails to trigger jobs with Cosmosdb/mongodb database

[Royce]

Cosmosdb's mongodb API implementation requires an index that agenda does not provide.

Problem

Debug logs:

  agenda:internal:processJobs [MyJobName] job lock failed while filling queue MongoServerError: Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: *; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: *; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: *; Reason: (Message: {"Errors":["The order by query does not have a corresponding composite index that it can be served from."]}
ActivityId: *, Request URI: /apps/****/services/****/partitions/*****/replicas/******/, RequestStats: Microsoft.Azure.Cosmos.Tracing.TraceData.ClientSideRequestStatisticsTraceDatum, SDK: Windows/10.0.19041 cosmos-netstandard-sdk/3.18.0);););
    at MessageStream.messageHandler (./node_modules/mongoose/node_modules/mongodb/src/cmap/connection.ts:740:20)
    at MessageStream.emit (node:events:390:28)
    at MessageStream.emit (node:domain:475:12)
    at processIncomingData (./node_modules/mongoose/node_modules/mongodb/src/cmap/message_stream.ts:167:12)
    at MessageStream._write (./node_modules/mongoose/node_modules/mongodb/src/cmap/message_stream.ts:64:5)
    at writeOrBuffer (node:internal/streams/writable:389:12)
    at _write (node:internal/streams/writable:330:10)
    at MessageStream.Writable.write (node:internal/streams/writable:334:10)
    at TLSSocket.ondata (node:internal/streams/readable:754:22)
    at TLSSocket.emit (node:events:390:28) {
  ok: 0,
  code: 2,
  codeName: 'BadValue'

The query that failed is approximately this:

const JOB_RETURN_QUERY = { returnDocument: "after", sort: { nextRunAt: 1, priority: -1 } };
const JOB_PROCESS_WHERE_QUERY = {
    $and: [
        {
            name: jobName,
            disabled: { $ne: true },
        },
        {
            $or: [
                {
                    lockedAt: { $eq: null },
                    nextRunAt: { $lte: this._nextScanAt },
                },
                {
                    lockedAt: { $lte: lockDeadline },
                },
            ],
        },
    ],
}

_coll.findOneAndUpdate(JOB_PROCESS_WHERE_QUERY, JOB_PROCESS_SET_QUERY, JOB_RETURN_QUERY)

Workaround

Assuming default config etc. which is: new Agenda({db: {collection: 'agendaJobs'}}) and .sort({ nextRunAt: 1, priority: -1 })

Create this index.

    await MongoConnector.Connection.db
      .collection('agendaJobs')
      .createIndex({ nextRunAt: 1, priority: -1 }, { name: 'findAndLockNextJobIndex_sort' })

[harisvsulaiman]

@Royce We don’t have plans to support cosmosdb anytime soon. I will be not be closing the issue, rather I will convert this into a discussion.