Potential bug when syncing deletes to multi-value key path

[dusty-phillips]

I don't have a full repro yet, but I have a db with a multi-value key path indexed like so:

      tagRelationships: '@id, bookid, *tags',

The typescript definition for the table is:

export type TagRelationship = {
  id: string
  name: string
  description: string
  tags: string[]

I have a complex mutation function that deletes the relationships in a transaction:

export async function deleteTag(tagid: string): Promise<void> {
  const database = db()
  await database
    .transaction(
      'rw',
      [database.tags, database.tagRelationships, database.outlineItems],
      async () => {
        await database.tags.delete(tagid)

        await database.tagRelationships.where({ tags: tagid }).delete()
      },
    )
}

If I add console.log statements to this, I find that inside this function, the tagRelationship is deleted, however after it completes, the tagRelationship magically comes back to the database.

I assumed this had something to do with transaction rollback, but after digging into the output of the sync messages I am seeing some funniness.

First, during the push phase, I see a Sync request with this object:

Collapsed for Brevity

{
    "v": 2,
    "dbID": "zc87hfdlq",
    "clientIdentity": "94VQ8qwOtCftzpme8btoCQ==",
    "schema": {
        "members": {
            "idPrefix": "mmb",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "roles": {
            "idPrefix": "rls",
            "primaryKey": "[realmId+name]",
            "generatedGlobalId": false,
            "markedForSync": true,
            "initiallySynced": true
        },
        "realms": {
            "idPrefix": "rlm",
            "primaryKey": "realmId",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "$jobs": {
            "idPrefix": "$jb",
            "initiallySynced": true
        },
        "$syncState": {
            "idPrefix": "$sy",
            "initiallySynced": true
        },
        "$baseRevs": {
            "idPrefix": "$bs",
            "primaryKey": "[tableName+clientRev]",
            "generatedGlobalId": false,
            "initiallySynced": true
        },
        "$logins": {
            "idPrefix": "$lg",
            "primaryKey": "claims.sub",
            "generatedGlobalId": false,
            "initiallySynced": true
        },
        "books": {
            "idPrefix": "bks",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "drafts": {
            "idPrefix": "drf",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "outlineItems": {
            "idPrefix": "otl",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "referenceImages": {
            "idPrefix": "rfr",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "sceneTexts": {
            "idPrefix": "scn",
            "primaryKey": "outlineitemid",
            "generatedGlobalId": false,
            "markedForSync": true,
            "initiallySynced": true
        },
        "settings": {
            "idPrefix": "stt",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "tagRelationships": {
            "idPrefix": "tgr",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "tags": {
            "idPrefix": "tgs",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "todos": {
            "idPrefix": "tds",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "wordCountByDate": {
            "idPrefix": "wrd",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        }
    },
    "lastPull": {
        "serverRevision": "1:15418",
        "realms": [
            "rlm-public",
            "dusty@phillips.codes"
        ],
        "inviteRealms": []
    },
    "baseRevs": [
        {
            "tableName": "books",
            "clientRev": 15,
            "serverRev": "1:15418"
        },
        {
            "tableName": "drafts",
            "clientRev": 903,
            "serverRev": "1:15418"
        },
        {
            "tableName": "members",
            "clientRev": 1,
            "serverRev": "1:15418"
        },
        {
            "tableName": "outlineItems",
            "clientRev": 504,
            "serverRev": "1:15418"
        },
        {
            "tableName": "realms",
            "clientRev": 1,
            "serverRev": "1:15418"
        },
        {
            "tableName": "referenceImages",
            "clientRev": 1,
            "serverRev": "1:15418"
        },
        {
            "tableName": "roles",
            "clientRev": 1,
            "serverRev": "1:15418"
        },
        {
            "tableName": "sceneTexts",
            "clientRev": 511,
            "serverRev": "1:15418"
        },
        {
            "tableName": "settings",
            "clientRev": 1,
            "serverRev": "1:15418"
        },
        {
            "tableName": "tagRelationships",
            "clientRev": 42,
            "serverRev": "1:15418"
        },
        {
            "tableName": "tags",
            "clientRev": 114,
            "serverRev": "1:15418"
        },
        {
            "tableName": "todos",
            "clientRev": 94,
            "serverRev": "1:15418"
        },
        {
            "tableName": "wordCountByDate",
            "clientRev": 446,
            "serverRev": "1:15418"
        }
    ],
    "changes": [
        {
            "table": "tags",
            "muts": [
                {
                    "type": "delete",
                    "ts": 1710502192342,
                    "opNo": 1,
                    "keys": [
                        "tgs0Ou1svifiVTDSD5|TilqGJ89des"
                    ],
                    "txid": "U8ht03cte8gTnekNjCx/XQ==",
                    "userId": "dusty@phillips.codes",
                    "rev": 114
                }
            ]
        },
        {
            "table": "tagRelationships",
            "muts": [
                {
                    "type": "delete",
                    "ts": 1710502192344,
                    "opNo": 2,
                    "keys": [
                        "tgr0Ou1s||_IPQ_rYWR_jk52fz5des"
                    ],
                    "criteria": {
                        "index": "tags",
                        "range": {
                            "type": 1,
                            "lower": "tgs0Ou1svifiVTDSD5|TilqGJ89des",
                            "upper": "tgs0Ou1svifiVTDSD5|TilqGJ89des"
                        }
                    },
                    "txid": "U8ht03cte8gTnekNjCx/XQ==",
                    "userId": "dusty@phillips.codes",
                    "rev": 42
                }
            ]
        },
        {
            "table": "outlineItems",
            "muts": [
                {
                    "type": "modify",
                    "ts": 1710502192346,
                    "opNo": 3,
                    "keys": [
                        "otl0Otpo_CNmFKetiWEpXgYcIWsdes"
                    ],
                    "criteria": {
                        "index": null,
                        "range": {
                            "type": 1,
                            "lower": "otl0Otpo_CNmFKetiWEpXgYcIWsdes",
                            "upper": "otl0Otpo_CNmFKetiWEpXgYcIWsdes"
                        }
                    },
                    "changeSpec": {
                        "tags": [
                            "tgs0Ot|OAj2H1aHiAVidjSpQRKxdes"
                        ]
                    },
                    "txid": "U8ht03cte8gTnekNjCx/XQ==",
                    "userId": "dusty@phillips.codes",
                    "rev": 504
                }
            ]
        }
    ]
}

I don't see anything suspicious in there, but included it because you might.

Later, during the pull event, I see this message:

Collapsed for Brevity

{
    "changes": [
        {
            "table": "outlineItems",
            "muts": [
                {
                    "type": "update",
                    "keys": [
                        "otl0Otpo_CNmFKetiWEpXgYcIWsdes"
                    ],
                    "changeSpecs": [
                        {
                            "tags": [
                                "tgs0Ot|OAj2H1aHiAVidjSpQRKxdes"
                            ]
                        }
                    ],
                    "txid": "4bkmP7Huih6Kutj2yvpe2A=="
                }
            ]
        },
        {
            "table": "tagRelationships",
            "muts": [
                {
                    "type": "upsert",
                    "keys": [
                        "tgr0Ou1s||_IPQ_rYWR_jk52fz5des"
                    ],
                    "values": [
                        {
                            "id": "tgr0Ou1s||_IPQ_rYWR_jk52fz5des",
                            "name": "o",
                            "tags": [
                                "tgs0Ot|OAj2H1aHiAVidjSpQRKxdes",
                                "tgs0Ou1svifiVTDSD5|TilqGJ89des"
                            ],
                            "owner": "dusty@phillips.codes",
                            "realmId": "dusty@phillips.codes",
                            "description": "e"
                        }
                    ],
                    "txid": "4bkmP7Huih6Kutj2yvpe2A=="
                }
            ]
        }
    ],
    "rejections": [],
    "dbId": "zc87hfdlq",
    "realms": [
        "rlm-public",
        "dusty@phillips.codes"
    ],
    "inviteRealms": [],
    "serverRevision": "1:15419",
    "schema": {
        "tags": {
            "idPrefix": "tgs",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "books": {
            "idPrefix": "bks",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "roles": {
            "idPrefix": "rls",
            "primaryKey": "[realmId+name]",
            "generatedGlobalId": false,
            "markedForSync": true,
            "initiallySynced": true
        },
        "todos": {
            "idPrefix": "tds",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "drafts": {
            "idPrefix": "drf",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "realms": {
            "idPrefix": "rlm",
            "primaryKey": "realmId",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "members": {
            "idPrefix": "mmb",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "tipSeen": {
            "idPrefix": "tps",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "initiallySynced": true
        },
        "features": {
            "idPrefix": "ftr",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "initiallySynced": true
        },
        "settings": {
            "idPrefix": "stt",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "sceneTexts": {
            "idPrefix": "scn",
            "primaryKey": "outlineitemid",
            "generatedGlobalId": false,
            "markedForSync": true,
            "initiallySynced": true
        },
        "outlineItems": {
            "idPrefix": "otl",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "referenceImages": {
            "idPrefix": "rfr",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "sceneWordCounts": {
            "deleted": true,
            "idPrefix": "Scn",
            "primaryKey": "outlineitemid",
            "initiallySynced": true
        },
        "wordCountByDate": {
            "idPrefix": "wrd",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "tagRelationships": {
            "idPrefix": "tgr",
            "primaryKey": "id",
            "generatedGlobalId": true,
            "markedForSync": true,
            "initiallySynced": true
        },
        "$jobs": {
            "idPrefix": "$jb",
            "initiallySynced": true
        },
        "$logins": {
            "idPrefix": "$lg",
            "primaryKey": "claims.sub",
            "generatedGlobalId": false,
            "initiallySynced": true
        },
        "$baseRevs": {
            "idPrefix": "$bs",
            "primaryKey": "[tableName+clientRev]",
            "generatedGlobalId": false,
            "initiallySynced": true
        },
        "$syncState": {
            "idPrefix": "$sy",
            "initiallySynced": true
        }
    }
}

Specifically, there is an upsert to the tag relationships table that I think is what is causing the relationship to be added back.

I know there have been issues with syncing multi-value key paths before, and I suspect this is a related issue. Hopefully it is an easy fix.

[dfahlander]

As always, you've nailed another multiEntry bug :) First time in liveQueries, then in the cache and now in dexie-cloud server. Every where...modify operation is re-executed on the server to assure sync consistency and the client get the server's picture of the reality after a sync. Now, the server is unaware of the tags being multiEntry so this need to be propagated from the client somehow.

[dfahlander]

A workaround until this is fixed would be to:

const idsToDelete = await database.tagRelationships.where({ tags: tagid }).primaryKeys();
await database.tagRelationships.bulkDelete(idsToDelete);

... just be aware that this would disable sync consistency though - if some other offline client had added new outlineItems with relationships to a tag being deleted, it could result in orphan relationships from the deleted tag afterwards.

Will prioritize to solve this soon after the public release on march 27.

[dusty-phillips]

Github should have a badge for that. 😂 Confirmed the workaround works for now. Good luck with the release.

[dfahlander]

If it happens a fourth time we must add the badge ;) Hopefully this won't repeat (fingers crossed)