So I tried to upgrade my board this morning and got an error:
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint “index_users_on_username”
It names a user “MikeC”. So I did some digging and it advised that I should restore postgres_data and start my board again, then look at the DB for duplicates. So I ran a quick query for username_lower = ‘mikec’ and found 1 row. Then I found this fancy query:
SELECT username_lower, count(*) from users GROUP by username_lower HAVING count(*) > 1;
Which returned no rows. So where is the duplicate? Any help appreciated. I am running on the old data but I would like to be able to rebuild the app properly.
Well here’s how I solved it but I’m sure there is a better way:
I went to my admin section and searched for ‘MikeC’ which returned 2 users. So I renamed the oldest one and instead of running rebuild again I simply logged into the DB and reindexed:
cd /var/discourse
./launcher enter app
sudo -i postgres psql discourse
discourse# REINDEX SCHEMA CONCURRENTLY public;
That would indicate another dupe and I would return to the WebUI and search for that user and rename the older user. Wash, rinse, repeat until no dupes found. Then rebuild and success!
I would however like to know:
Why does discourse allow dupes? Username can be case insensitive but username_lower normalizes to what should be a unique value. This seems like a pretty big bug.
I don’t think there is a better way. Duplicate user names are not allowed, but it seems some previous bug didn’t enforce the downcase check. There was another topic about this recently.