Replies of deleted notes are no longer automatically deleted, causing timeline errors

[zotanmew]

💡 Summary

Replies of deleted notes are no longer automatically deleted, causing timeline errors

🥰 Expected Behavior

Either replyId of replies should be set to null, or replies should also be deleted (previous behavior)

🤬 Actual Behavior

When a note with replies is deleted, timeline shows an error occured, log shows

Jan 21 15:48:29 destiny.zotan.network misskey[890528]: EntityNotFoundError: Could not find any entity of type "Note" matching: {
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     "id": "9a9mqnudl5"
Jan 21 15:48:29 destiny.zotan.network misskey[890528]: }
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at /home/misskey/misskey/node_modules/.pnpm/typeorm@0.3.11_ioredis@4.28.5+pg@8.8.0/node_modules/typeorm/entity-manager/EntityManager.js:616:39
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async NoteEntityService.pack (file:///home/misskey/misskey/packages/backend/built/core/entities/NoteEntityService.js:217:54)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async Promise.all (index 22)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async awaitAll (file:///home/misskey/misskey/packages/backend/built/misc/prelude/await-all.js:5:28)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async NoteEntityService.pack (file:///home/misskey/misskey/packages/backend/built/core/entities/NoteEntityService.js:229:24)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async Promise.all (index 1)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async NoteEntityService.packMany (file:///home/misskey/misskey/packages/backend/built/core/entities/NoteEntityService.js:312:16)
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async file:///home/misskey/misskey/packages/backend/built/server/api/endpoints/notes/timeline.js:127:20
Jan 21 15:48:29 destiny.zotan.network misskey[890528]:     at async ApiCallService.call (file:///home/misskey/misskey/packages/backend/built/server/api/ApiCallService.js:273:16)

📝 Steps to Reproduce

1. post note
2. post reply
3. delete (or delete and edit) first note

📌 Environment

Misskey version: 13.1.0 Your OS: Arch linux Your browser: Chrome latest stable

[zotanmew]

This repeatedly occurred on my instance with under ten active users and is completely breaking the timeline :/

[zotanmew]

Manual fix is executing delete from note where "replyId" = '9a9mqnudl5'; (replacing the id with the affected missing note) on the postgres database

[zotanmew]

This query deletes all affected notes, restoring old behavior. However, this isn't exactly a fix, so I'll leave the issue open for now

DELETE
FROM   "note" l
WHERE
   "replyId" IS NOT NULL
   AND
   NOT EXISTS (
      SELECT "id"
      FROM   "note"
      WHERE  "id" = l."replyId"
   )
;

[zotanmew]

We basically need a migration like this: ALTER TABLE "note" ADD CONSTRAINT "FK_temp_reply_fix_zotan" FOREIGN KEY ("replyId") REFERENCES "note"("id") ON DELETE CASCADE ON UPDATE NO ACTION;, though that only works after spamming the query mentioned above til it responds DELETE 0 (on every pass it deletes one more note in a reply chain, the replies of which then become orphaned. I'm not good enough at (Postgre)SQL to write a query that will cascade that deletion process, but I'm sure it's possible.

[zotanmew]

I had to create two indices to get the server load down, though the second might be unrelated. CREATE INDEX "IDX_temp_reply_fix_zotan" ON "note" ("replyId"); CREATE INDEX "IDX_temp_reply_fix_zotan_perf_optimiz" ON "note" ("uri");

[outloudvi]

Another possible solution would be keeping those root-less notes, and only to display a warning to users viewing the note that the root note is deleted. Not sure whether way is the one that Mastodon uses.