Thread disappearing from category view?

[poVoq]

Issue Summary

https://lemmy.ml/post/43181 has been posted to !opensourcegames, but when viewing the listing it disappeared after a few hours: https://lemmy.ml/c/opensourcegames

As the thread creator and moderator of that community, I can confirm that I didn't do anything unusual with it.

No idea how to reproduce :-/

[Nutomic]

Now that is weird, the post looks completely in the database, and it also gets federated. I suspect its a bug in the database views.

[dessalines]

Yeah this one is pretty weird, I'll have to check the DB to see what's going on.

[dessalines]

Very strange, but somehow the local column got set to false:

  id   |                        name                        | community_id | community_name  | local
-------+----------------------------------------------------+--------------+-----------------+-------
 43181 | LibreJam, a monthly game jam for open source games |        16854 | opensourcegames | f

I've updated it to true in post_aggregates_fast and post manually, but considering that column is always true by default, I'm not sure how it got set. I'm gonna trace down that column a bit.

[Nutomic]

Oh I see, this must be a bug in the activitypub code (which makes more sense because the database is already well tested). Somehow lemmy.ml fetched that post from itself, or received it directly in an activity. Then it was parsed and upserted in the db, in which case local = false is hardcoded. I will try to debug it based on the activity log.

[Nutomic]

Unfortunately I cant get the Announce/Create/Page activity for this post from the database. Likely because no other instance was following !opensourcegames when the post was created. The first activity I can see is the comment by @DrivingForce.

Luckily (or not), there are a couple more posts affected by this bug:

sudo docker-compose exec postgres psql -U lemmy -d lemmy -P pager=off -c "select ap_id from post where ap_id like 'https://lemmy.ml%' and local=false"
ap_id
-----------------------------
https://lemmy.ml/post/42833
https://lemmy.ml/post/43040
https://lemmy.ml/post/43048
https://lemmy.ml/post/43393
https://lemmy.ml/post/43555
https://lemmy.ml/post/43557
(6 rows)

In the case of posts made to communities that is expected, as they are sent back to us by the remote community, and local is overriden. Still it is something we might want to change, but it wont cause any problems.

Unfortunately those dont help either, because post 43048 also wasnt federated, and post 42833 has way too many comments (and thus activities), so I cant check all of them in detail.

Note that the SQL query above returns nothing on the comment, user_ or community tables, so this bug only seems to affect posts.

What I'm gonna do is add some logging, so that we can get more information when this happens again.

Edit: Ran update post set local=true where ap_id like 'https://lemmy.ml%' and local=false; to fix the posts above. We will have to do that again once the bug is fixed (and other instances as well).

[dessalines]

Somehow lemmy.ml fetched that post from itself, or received it directly in an activity.

The only culprit I can think of here, would be in the post fetcher code (or the comment code which could fetch the post parent), which I'm pretty sure it already does a check.... Maybe the problem is the on_conflict_update? It shouldn't do that for local posts.