read_receipts MongoError: Index build failed

[Sc0th]

Anyone come across this before/have a fix?

After upgrade (docker) from 4.7.0 -> 4.8.1

| Rocket.Chat Version: 4.8.1 | | NodeJS Version: 14.18.3 - x64 | | MongoDB Version: 5.0.9 |

Error creating indexes for read_receipts MongoError: Index build failed: 42a676ef-774f-4ed9-a97a-0791bf2b705a: Collection rocketchat.rocketchat_read_receipts ( f996721f-c895-4221-b308-a1ae10e5e4e0 ) :: caused by :: E11000 duplicate key error collection: rocketchat.rocketchat_read_receipts index: roomId_1_userId_1_messageId_1 dup key: { roomId: "8Msort4RJKCHhgun2M6RSWhdssRGstwMSp", userId: "8Msort4RJKCHhgun2", messageId: "vCvXNfzET7ZXYTv58" } at MessageStream.messageHandler (/app/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/connection.js:272:20) at MessageStream.emit (events.js:400:28) at MessageStream.emit (domain.js:475:12) at processIncomingData (/app/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/message_stream.js:144:12) at MessageStream.write (/app/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/message_stream.js:42:5) at writeOrBuffer (internal/streams/writable.js:358:12) at MessageStream.Writable.write (internal/streams/writable.js:303:10) at Socket.ondata (internal/streams/readable.js:731:22) at Socket.emit (events.js:400:28) at Socket.emit (domain.js:475:12) at addChunk (internal/streams/readable.js:293:12) at readableAddChunk (internal/streams/readable.js:267:9) at Socket.Readable.push (internal/streams/readable.js:206:10) at TCP.onStreamRead (internal/stream_base_commons.js:188:23) at TCP.callbackTrampoline (internal/async_hooks.js:130:17) { ok: 0, code: 11000, codeName: 'DuplicateKey', keyPattern: { roomId: 1, userId: 1, messageId: 1 }, keyValue: { roomId: '8Msort4RJKCHhgun2M6RSWhdssRGstwMSp', userId: '8Msort4RJKCHhgun2', messageId: 'vCvXNfzET7ZXYTv58' }, '$clusterTime': { clusterTime: Timestamp { bsontype: 'Timestamp', low: 3, high: 1657757757 }, signature: { hash: [Binary], keyId: 0 } }, operationTime: Timestamp { bsontype: 'Timestamp', low: 3, high_: 1657757757 } }

[degewo]

Same here with Rocket.Chat:

Version 5.0.0 Apps Engine Version 1.33.0 Node Version v14.19.3 Database Migration 279 (July 28, 2022 3:02 AM) MongoDB 5.0.9 / wiredTiger (oplog Enabled) Commit Details HEAD: (59cae1210) Branch: HEAD

Occours after upgrade from 4.8.2 -> 5.0.0.

Log-File shows:

Some indexes for collection 'rocketchat_read_receipts' could not be created:
Index build failed: cdc42b7d-3304-4897-acab-f79b71c600c4: Collection rocketchat.rocketchat_read_receipts ( 2cf60644-30f3-4e33-80b5-2bce4958f2d2 ) :: caused by :: E11000 duplicate key error collection: rocketchat.rocketchat_read_receipts index: roomId_1_userId_1_messageId_1 dup key: { roomId: "3GCpuapWtLE7rzmPz", userId: "KiS9WwYSr3sQZCNW5", messageId: "OO0JTxxG7uSAvBszy" }

Any help is greatly appreciated.

[Magelo]

Same duplicate key message error here:

Version 5.0.0 Apps Engine Version 1.33.0 Node Version v14.19.3 Database Migration 279 (July 29, 2022 2:57) MongoDB 5.0.10 / wiredTiger (oplog Enabled) Commit Details HEAD: (59cae1210) Branch: HEAD

And I did upgrade from 4.8.2, but had to do a mongodump, fresh install, mongorestore to be able to have Rocket.Chat running (I never saw the important note about adding ?replicaSet=rs0&directConnection=true for MONGO_URL and MONGO_OPLOG_URL).

[Jenso-DE]

Had the same problem / error messages but with different indexes, fixed it via mongosh:

db.users.dropIndexes() db.rocketchat_read_receipts.dropIndexes() db.rocketchat_room.dropIndexes() db.rocketchat_message.dropIndexes() db.rocketchat_credential_tokens.dropIndexes()

Found it here: https://github.com/RocketChat/Rocket.Chat/issues/24347#issuecomment-1032161111

[jadanah]

Here is my solution if it helps anyone.

1. Stop rocketchat
2. Run the folllowing mongodb command

var duplicates = [];

db.getCollection("rocketchat_read_receipts").aggregate([
    {
        "$group": {
            "_id": { "roomId": "$roomId", "userId": "$userId", "messageId": "$messageId" },
            "uniqueIds": { "$addToSet": "$_id" },
            "count": { "$sum": 1 }
        }
    },
    { "$match": { "count": { "$gt": 1 } } }
],
    { allowDiskUse: true }
)
    .forEach(function (doc) {
        // remove 1st element
        doc.uniqueIds.shift();
        doc.uniqueIds.forEach(function (dupId) {
            duplicates.push(dupId);
        }
        )
    })

printjson(duplicates);

db.getCollection("rocketchat_read_receipts").remove({ _id: { $in: duplicates } });

3. Restart rocketchat

[sandrotanner]

I have the same problem and the above script gets nothing ( [ ] )... even when I try and manually find the entry with the _id I get nothing. but whenever I restart the server the error comes again :thinking:

Edit: nevermind, I had the wrong DB in use. But damn it returns many duplicates :sweat_smile:. Will have to test this at night so I can restore if things go south

[Alfs29]

Jodanah, your idea is excellent but there is problem running script. Please help fixing it ;) Error before .forEach .....

....... rs0:PRIMARY> .forEach(function (doc) { ... // remove 1st element ... doc.uniqueIds.shift(); ... doc.uniqueIds.forEach(function (dupId) { ... duplicates.push(dupId); ... } ... ) ... }) SyntaxError: expected expression, got '.' :

[TomaszDom]

@Alfs29 there are no problems with the script (huge thanks @jadanah), you just can't run it like this from mongo shell, try it like this (4 separate commands):

var duplicates = [];
db.getCollection("rocketchat_read_receipts").aggregate([{"$group": {"_id": { "roomId": "$roomId", "userId": "$userId", "messageId": "$messageId" },"uniqueIds": { "$addToSet": "$_id" },"count": { "$sum": 1 }}},{ "$match": { "count": { "$gt": 1 } } }],{ allowDiskUse: true }) .forEach(function (doc) {doc.uniqueIds.shift();doc.uniqueIds.forEach(function (dupId) {duplicates.push(dupId);})})
printjson(duplicates);
db.getCollection("rocketchat_read_receipts").remove({ _id: { $in: duplicates } });