PG::UniqueViolation Problem during 3.1.0.beta4 upgrade

Installation
1

while upgrading to 3.1.0.beta4 I got the following error

I, [2023-04-19T00:02:26.057232 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'LOAD_PLUGINS=0 bundle exec rake plugin:pull_compatible_all'
I, [2023-04-19T00:02:27.340128 #1]  INFO -- : discourse-adplugin is already at latest compatible version
discourse-cakeday is already at latest compatible version
discourse-formatting-toolbar is already at latest compatible version
discourse-whos-online is already at latest compatible version
docker_manager is already at latest compatible version
vbulletin-bbcode is already at latest compatible version

I, [2023-04-19T00:02:27.340368 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2023-04-19 00:02:30.080 UTC [634] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_users_on_username"
2023-04-19 00:02:30.080 UTC [634] discourse@discourse DETAIL:  Key (username)=(xxx) already exists.
2023-04-19 00:02:30.080 UTC [634] discourse@discourse STATEMENT:  UPDATE users SET password_algorithm = '$pbkdf2-sha256$i=64000,l=32$'
	WHERE id IN (
	  SELECT id FROM users
	  WHERE users.password_hash IS NOT NULL
	  AND users.password_algorithm IS NULL
	  LIMIT 5000
	)
	
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_users_on_username"
DETAIL:  Key (username)=(xxx) already exists.

After fixing the duplicate users, I get again new ones.

discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(mhm) is duplicated.
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(ahmedhafez) is duplicated.
CONTEXT:  parallel worker
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(hany) is duplicated.
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(eng_ali) is duplicated.
CONTEXT:  parallel worker
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(saad_saad) is duplicated.
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(eng_ali) is duplicated.
CONTEXT:  parallel worker
discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(mostafa11) is duplicated.
CONTEXT:  parallel worker
1 Like
2

That looks like either a corrupt index or that somehow users were able to create accounts with the same username with different capitalization.

You should be able to start the old container with

./launcher start app

You can then try to reindex the table, see which user is broken, fix it, and repeat until the index will rebuild.

1 Like
3

Thanks for your reply.

Yes, I am able to start / enter the app, I am also doing as you suggested.

discourse=# REINDEX TABLE users;
ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(mostafa11) is duplicated.

The problem is duplicates keep coming, I have a lot of user over 300K.
Is there any way to check how many duplicates or list all of them?

For fixing, I was updating the username_lower like this: I was appending some number at the end.

UPDATE users SET username_lower = 'xxx' WHERE id = xxx;

Do I need also to update the username column to match username_lower? Is there any faster way, like automatically fixing those duplicates?

My website is now offline, so any help is highly appreciated.

1 Like
4

I’m not aware of one. It shouldn’t happen, I think. What version are you upgrading from?

Maybe you can search for users where username! = username_lower?

It went down even though you started the app?

No. It’s ok for a username to have mixed case but you don’t want to have two usernames that have the same letters.

Is it the case that you have both a Joe and also a joe? If that’s the case then I think there should be a query that could find them, but I don’t know how to do it offhand.

2 Likes
5

Thanks Jay
I was able to rebuild and get my website online again.

First as you suggested the solution is to reindex, fix and repeat until done. I used the following command to see how many

SELECT username_lower, count(*) from users GROUP by username_lower HAVING count(*) > 1;

it was giving me zero.

Of course there are many duplicated and wanted to know how many. so I slightly adjusted the query:

SELECT username_lower, count(username) from users GROUP by username_lower HAVING count(username) > 1;

This worked and returned about 50 duplicates, so I fixed them, reindexed rebuilt successfully.

1 Like
6

Great! So glad you got it figured out. Your notes should be very helpful to others with the same issue.

I edited your post to make it easier for others to copy/paste your SQL.

1 Like
7

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