A user encountered a database error during an upgrade due to a duplicate username, ‘MikeC’, despite no duplicates found in a query checking username_lower. The issue was resolved by identifying two users with the same username in the admin panel, renaming the older one, and reindexing the database. The user questioned why Discourse allows duplicate usernames despite username_lower being unique and asked for a better method to detect and fix such duplicates. Another user confirmed this is a known issue where previous bugs may have bypassed the case-insensitive uniqueness check, and there is no better solution currently available. volksmanvolksmanpfaffman
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.