Upgrade issue with duplicate username in DB

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.

1 Like

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?

2 Likes

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.

2 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.