Hi y’all. I’ve been running Akkoma for several months now and every now and then begins throwing 500 errors. Logs show that the Pleroma.User table has problems:
12:57:44.926 request_id=F26rfUqcd_URFPQAI6Ph [error] Internal server error: %Ecto.MultipleResultsError{message: "expected at most one result but got 2 in query:\n\nfrom u0 in Pleroma.User,\n where: u0.ap_id == ^"https://SOME.SERVER/USER\“\n”}
This will break my server until I go into PostgreSQL and delete the offending duplicate records.
I’m curious why I don’t see evidence that this is happening to other Akkoma admins. There are Pleroma issues filed from several years ago about this issue but the problem seems to have long been fixed in Pleroma.
Anyone know what is happening here? Manually fixing the db is getting old. 
Thanks for any pointers!
That’s weird, because there’s a constraint which shouldn’t allow this, even if Akkoma tried to add a duplicate record. Do you have users_ap_id_index?
This is the source of the index
CREATE UNIQUE INDEX users_ap_id_index ON public.users USING btree (ap_id)
And this is the error I have when I try to do an insert where I reuse an ap_id.
SQL Error [23505]: ERROR: duplicate key value violates unique constraint "users_ap_id_index"
Detail: Key (ap_id)=(http://localhost:4000/users/ilja) already exists.
Aha! The index is not there. I will add it. (well, after I remove the duplicate records that are already in there).
Thanks! Where can I find a list of the indices I need so that I can verify that I don’t have any others that might be missing?
Everything is done in migrations, so there’s no simple list. You can get it from a db dump probably, but then the big question is why you are missing it… Did you do a fresh install? Migrated from Pleroma (if so, from what version)? Did you restore the database at some point (backup restore, server migration…)? Something must’ve gone wrong somewhere and wasn’t caught.
Yep, I migrated servers and probably flubbed using pg_dump.