Upgrade issue with duplicate username in DB

[volksman]

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.

[volksman]

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:

1. 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.

2. What is a better way to find and fix these dupes?

[pfaffman (Jay Pfaffman)]

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.