Payload V2 update and delete operations not working with Azure Cosmos DB for MongoDB

[jkauppinen]

Link to reproduction

No response

Describe the Bug

We have encountered that with latest version of Payload there seem to be problems with Azure Cosmos DB for MongoDB with at least delete and update operations on collections. In our local development environment, we have been able to run payload v2 with latest image of mongo, so it seems the problems are related to azure cosmos DB and the way payload operates the database. We had no issues with Payload v1.8.3, so there seem to be regressions and/or changes with v2+Cosmos DB.

What works

• Creating collections without relationships
• Adding items to collections without relationships
• Updating collections without relationships
• Querying collections without relationships

What doesn't work

• Deleting items in collections
• Updating collection with a relationship reference

What we have tried

• Starting with a clean database with Payload v2.1.1
• Verified that these problems do not reproduce with exact same code when using serverless mongodb atlas instance. We haven't changed mongo connection string since migrating from v1.8.3 to v2.1.1 when connecting to Cosmos DB
• Index true/false in relationship definition
• useFacet true & false in config
• disableIndexHints true & false in config
• indexSortableFields true & false in config

The same "Transaction is not active" error is thrown with delete and update operations.

Collection

import { CollectionConfig } from "payload/types";

export const Cars: CollectionConfig = {
  slug: "cars",
  fields: [
    {
      name: "Name",
      type: "text",
    },
  ],
};

Global referencing collection

import { GlobalConfig } from "payload/types";

const GlobalTest: GlobalConfig = {
  slug: "test",
  fields: [
    { name: "title", type: "text" },
    {
      name: "Cars",
      type: "relationship",
      relationTo: "cars",
      hasMany: true,
    },
  ],
};

export default GlobalTest;

Config

export default buildConfig({
  serverURL: process.env.PAYLOAD_PUBLIC_SERVER_URL,
  indexSortableFields: true,
  cors: "*",
  admin: {
    user: Users.slug,
    bundler: webpackBundler(),
  },
  collections: [
    Cars,
  ],
  globals: [
    GlobalTest,
  ],
  editor: slateEditor({}),
  db: mongooseAdapter({
    url: process.env.MONGODB_URI,
    disableIndexHints: true,
    connectOptions: {
      useFacet: false,
    },
  }),
});

Admin panel

Existing record in "Cars" collection

Adding reference to "Cars" from global collection

HTTP POST from Payload admin panel from "Save" button after adding reference to existing global collection:

-----------------------------
Content-Disposition: form-data; name="_payload"

{"Cars":["656465e724eceb71f72f18f6"],"title":"Test","updatedAt":"2023-11-27T09:49:41.889Z","createdAt":"2023-11-27T09:49:00.898Z"}
-----------------------------

HTTP response from payload API:

{"errors":[{"message":"Something went wrong."}]}

Error from Payload CMS logs:

2023-11-27T09:50:15.077216598Z [09:50:14] ERROR (payload): MongoServerError: Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 1101; ActivityId: XXXXXX; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 1101; ActivityId: XXXXX; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 1101; ActivityId: XXXXX; Reason: (Message: {"Errors":["Transaction is not active"]}
2023-11-27T09:50:15.077353351Z ActivityId: XXXXXX, Request URI: /apps/yyyyy/services/xxxx/partitions/zzzz/replicas/xxxxxxx/, RequestStats: Microsoft.Azure.Cosmos.Tracing.TraceData.ClientSideRequestStatisticsTraceDatum, SDK: Windows/10.0.17763 cosmos-netstandard-sdk/3.18.0);););
2023-11-27T09:50:15.077366295Z     at Connection.onMessage (/home/node/app/node_modules/mongodb/lib/cmap/connection.js:231:30)
2023-11-27T09:50:15.077384048Z     at MessageStream.<anonymous> (/home/node/app/node_modules/mongodb/lib/cmap/connection.js:61:60)
2023-11-27T09:50:15.077389127Z     at MessageStream.emit (node:events:513:28)
2023-11-27T09:50:15.077393796Z     at processIncomingData (/home/node/app/node_modules/mongodb/lib/cmap/message_stream.js:125:16)
2023-11-27T09:50:15.077397864Z     at MessageStream._write (/home/node/app/node_modules/mongodb/lib/cmap/message_stream.js:33:9)
2023-11-27T09:50:15.077402122Z     at writeOrBuffer (node:internal/streams/writable:392:12)
2023-11-27T09:50:15.077406300Z     at _write (node:internal/streams/writable:333:10)
2023-11-27T09:50:15.077410567Z     at Writable.write (node:internal/streams/writable:337:10)
2023-11-27T09:50:15.077414735Z     at TLSSocket.ondata (node:internal/streams/readable:766:22)
2023-11-27T09:50:15.077419284Z     at TLSSocket.emit (node:events:513:28)

Azure bicep

resource dbAccount 'Microsoft.DocumentDB/databaseAccounts@2023-04-15' = {
  name: 'test-db-account'
  location: 'westeurope'
  kind: 'MongoDB'
  properties: {
    databaseAccountOfferType: 'Standard'
    locations: [
      {
        locationName: 'westeurope'
        failoverPriority: 0
      }
    ]
    capabilities: [
      {
        name: 'EnableServerless'
      }
      {
        name: 'EnableMongo'
      }
    ]
    apiProperties: {
      serverVersion: '4.2'
    }
  }
}

resource mongoDb 'Microsoft.DocumentDB/databaseAccounts/mongodbDatabases@2023-04-15' = {
  parent: dbAccount
  name: 'test-db'
  properties: {
    resource: {
      id: 'test-db'
    }
    options: {}
  }
}

To Reproduce

Both scenarios can be reproduced with Azure Cosmos DB for MongoDB database.

Relationship update repro

1. Create two collections, A and B, where B is global
2. Add one value to A from admin panel
3. Reference the added value from global collection B from admin panel

Delete repro

1. Create collection
2. Add item in collection
3. Delete from Admin panel

Payload Version

2.1.1

Adapters and Plugins

mongooseAdapter

[tyteen4a03]

Can confirm we're hitting the same issue.

[DanRibbens]

Can anyone experiencing this confirm that it hasn't been fixed since v2.2.2? We had closed out some other transaction related issues specificaly in 2.2.0 and 2.2.2.

Please let me know before I start looking deeper into this.

[jkauppinen]

Can anyone experiencing this confirm that it hasn't been fixed since v2.2.2? We had closed out some other transaction related issues specificaly in 2.2.0 and 2.2.2.

Please let me know before I start looking deeper into this.

@DanRibbens in our environment with v2.3.1 and db-mongodb v1.1.0 this same issue persists.

[DanRibbens]

I spent some time here just trying to get a reproducable test environment. Ran into some problems using the azure cosmos db emulator. I'll revisit this when I can set up a hosted azure cosmos db to work against.

[craigomatic]

I'm blocked by this also - created a vanilla payload app via npx create-payload-app, point it to a vanilla Azure Cosmos MongoDB instance, get http 400 with "Transaction is not active" error when attempting to view admin panel and create first admin user.

I see the test database and collections have been written:

[DanRibbens]

Sorry for the delay. You might consider using the new db: mongooseAdapter( { transactionOptions: false } ) option which disables the use of transactions until this is sorted out.

[tenstan]

Sorry for the delay. You might consider using the new db: mongooseAdapter( { transactionOptions: false } ) option which disables the use of transactions until this is sorted out.

@DanRibbens From what I see, it looks like transactionOptions hasn't made it into @payloadcms/db-mongodb v1.1.1 (latest version). Unless you still mean to publish it.

[DanRibbens]

Ah you're right! That will be released tomorrow.

[thijssmudde]

Is there any update on this?

I added transactionOptions: false, but the issue "The index path corresponding to the specified order-by item is excluded." is still there with azure cosmos db.

[DanRibbens]

I added transactionOptions: false, but the issue "The index path corresponding to the specified order-by item is excluded." is still there with azure cosmos db.

That is unrelated to anything with transactions. Have you enabled indexSortableFields? https://payloadcms.com/docs/configuration/overview#options

[thijssmudde]

@DanRibbens Yes I've enabled it. The behaviour still seems flaky, it works sometimes and then it doesn't, while indexSortableFields has been enabled. I have a lot of collections with relationships. I'll report back if the issues come back again.

[BrianJM]

Sorry for the delay. You might consider using the new db: mongooseAdapter( { transactionOptions: false } ) option which disables the use of transactions until this is sorted out.

Possibly related to #4350.

[jkauppinen]

I've tried various configurations with Azure + Payload in version 2 but have faced quite many reliability challenges. I -really- want to use this product, pay+recommend for it in suitable projects, and dare I say even contribute to the codebase, however, the reduced Azure compatibility in the latest version(s) has been a setback, leading me to reconsider its use with Azure environments for the time being. I am, however, looking forward to testing the PostgreSQL package once its out of beta.

To provide some context, here are the issues I've encountered:

1. Update/delete operations with Payload + Azure Cosmos DB for MongoDB are not functioning (this issue)
2. Automatic index creation with Payload + Azure Cosmos DB for MongoDB is failing (Version fields are not indexed when indexSortableFields is true #4974).
3. The migration script from v1 to v2 with Payload + Azure Cosmos DB for MongoDB fails to create indexes (Payload migration script to v2 not working out of the box with Azure Cosmos DB #4161), possibly related to Version fields are not indexed when indexSortableFields is true #4974.
4. Connections between Payload + MongoDB Atlas serverless and Azure private endpoints consistently time out, despite the database being accessible. I haven't logged this as an issue yet, pending further investigation into whether Payload or the endpoint configuration is at fault.

At present, the only configuration I've found viable is using Payload + MongoDB Atlas serverless over the public internet, which is less than ideal for secure, hybrid environments.

My observation is that these issues with Payload + Azure were not present in v1.8.3, suggesting the problems have been introduced in newer versions. The same infrastructure and configuration worked perfectly with earlier versions.

If there is any extra information that could help you to investigate these issues with azure, I'm happy to help.

[DanRibbens]

Sorry for the delay @jkauppinen, last time I pulled up this issue I couldn't create an Azure cosmos DB because something was down in Azure. I have created a serverless instance to test against.

For a quick workaround, I recommend disabling transactions since it appears we haven't tested this feature enough to work on all mongo compliant databases. To do this set transactionOptions: false in your db adapter.

I opened a PR for the versions fields indexSortableFields bug:

Regarding item 4.

Connections between Payload + MongoDB Atlas serverless and Azure private endpoints consistently time out, despite the database being accessible. I haven't logged this as an issue yet, pending further investigation into whether Payload or the endpoint configuration is at fault.

Did you conclude anything on this?

[DanRibbens]

I am having trouble debugging on azure because I keep getting this:

Your account is currently configured with a total throughput limit of 1000 RU/s. This operation failed because it would have increased the total throughput to 1200 RU/s. See https://aka.ms/cosmos-tp-limit for more information.

How are RUs measured? This seems completely wild to me. I'm fetching a list view with ~5 seeded documets and getting this.

UPDATE:
Wow Cosmos defaults to 400 RUs per collection so my instance was starting up, only able to create 2 collections and then getting that error. I cannot adjust the RUs allowed on the free plan. I'm testing now with no limit.

It seems that I can make update and delete operations successfully. Including when I try it on collections with drafts.

[DanRibbens]

I ran into one issue in that if a colleciton is already made, you cannot add a unique constraint to it unless you drop the collection. I looked
I did find EnableUniqueIndexReIndex, though I didn't try it https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/how-to-configure-capabilities#available-capabilities

I honestly don't think there is anything else to fix. If there are transactions issues, we have some ongoing other issue threads related to those and I don't know that they're Azure DB specific.

I'm going to close this issue because I suspect you ran into similar issues with the RU limits and changing them after the fact can mean that your indexes aren't being made and it is wreaking havoc on your setup.

There are a number of issues with Cosmos that I fully agree are bad DX, but I don't know that there is anything Payload can do to make it better.