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: (59cae12)
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: (59cae12)
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: #24347 (comment)

[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 🤔

Edit: nevermind, I had the wrong DB in use. But damn it returns many duplicates 😅. 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 } });