Upgrading to current version of Discourse today, the rebuild is failing and I’m not exactly sure what’s causing it. The install was quite a few iterations behind (I think 2.8x). The docker image update went fine when I went to upgrade Discourse I was asked to do a manual pull & rebuild (Most of the time I get asked this) however the rebuild has been unsuccessful. Here’s the results of a search for “error” using discourse-doctor. Apologies for screenshots, the web based VNC client I have does not seem to support copy/paste [image] [image] [image] [image]

Expanding on what @Canapin has said, in case you haven’t already figured it out, it should be straightforward to get the usernames unique since you’ve already established that racerx is the only duplicated username. First, if you haven’t already, make sure racerx2 doesn’t already exist so you aren’t just moving the problem to a different username: (If you get an id, try a different username.) SELECT id FROM users WHERE username_lower = 'racerx2'; Once you have an unused username, get the IDs of the duplicate racerx accounts: SELECT id from users WHERE username_lower = 'racerx'; Pick one, doesn’t matter which though I would use the highest, then change its username: (Substituting racerx2 for a different username if needed and 12345 with the racerx ID you picked.) UPDATE users SET username_lower = 'racerx2' WHERE id = 12345;

It seems to think you have two users called racerx. I think there’s a topic about a similar issue here: [image]Import failing with `could not create unique index` We also had 8 users which had a duplicate username_lower (too many mike’s and marco’s). We renamed those by updating both username and username_lower. From the user data we noticed that the first corruption has happened in December 2019. Also a more recent one here too: [image] PG::UniqueViolation Problem during 3.1.0.beta4 upgrade Self-hosting 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. U…

Thanks I will start down that path… wish me luck..

I’m stuck on how to kickoff the re indexing. I’ve been able to start (Although it’s not web accessible) and enter the app but no luck moving forward from that. [image]

You need to run those commands inside the container. Something like ./launcher start app ./launcher enter app Before you sudo postgres Edit: oh, you got in the container. That postgres -c needs sudo before it.

I guess sudo wouldn’t work, but su did and now I’m poking around in postgres. So now I need to figure out how to actually fix the duplicates. The query appears to be.. UPDATE users SET username_lower = 'xxx' WHERE id = xxx; I tried a couple of variations to no avail. As you can tell I’m not 100% sure what the correct syntax here as the error is column related. [image] I’ll keep digging. I don’t know how many dups I have yet either… /edit… it’s just racerx. DAMMIT RACERX! [image]

Update (Was in drafts for a few days, sorry) Looks like I got it figured out. Not sure if it what I did was the right approach, but it rebuilt successfully. Found it hard to modify\append the racerx value in the username_lower column using the syntax so I went about it another way. by looking up the IDs for the rows and modifiying the username_lower value in a specific row. First I tried to get a report of any rows in the users table that had “racerx” in the username_lower column. select all from users where username_lower ='racerx'; From this I decided I’d modify racerx to racerx2, I can’t remember exactly what I typed to make that happen. I think it was UPDATE SET from users username_lower racerx to racerx2

[Site Down :(] Upgrade\Rebuild Failure - DB Migration Issue?

Product Support Self-hosting

Canapin (Coin-coin le Canapin) June 15, 2023, 9:22am 7

Did you figure out how to fix it?

I’d return the users’ ID (which is unique) as well to identify them properly and then rename one of these two users

Topic		Replies	Views
Upgrade issue with duplicate username in DB Self-hosting	2	576	January 29, 2021
PG::UniqueViolation Problem during 3.1.0.beta4 upgrade Self-hosting	5	709	April 19, 2023
2.7.0.beta2 upgrade failed with ERROR: duplicate key Self-hosting	24	2617	January 26, 2021
How does one run sql queries if the container doesn't start? (rebuild failed) Self-hosting	6	897	January 20, 2023
Error importing backup: "could not create unique index" Self-hosting	6	1211	November 1, 2021

[Site Down :(] Upgrade\Rebuild Failure - DB Migration Issue?

Related topics