I was upgrading from 2.7.0.beta1 and got this error message: 2021-01-22 20:16:22.015 UTC [4055] discourse@discourse LOG: duration: 75335.241 ms statement: UPDATE notifications SET processed = true 2021-01-22 20:16:23.792 UTC [4055] discourse@discourse LOG: duration: 1776.591 ms statement: ALTER TABLE "notifications" ALTER COLUMN "processed" SET NOT NULL 2021-01-22 20:16:25.198 UTC [4055] discourse@discourse LOG: duration: 1323.298 ms statement: CREATE INDEX "index_notifications_on_processed" ON "notifications" ("processed") 2021-01-22 20:16:25.458 UTC [4055] discourse@discourse LOG: duration: 241.063 ms statement: CREATE TABLE "user_notification_schedules" ("id" bigserial primary key, "user_id" integer NOT NULL, "enabled" boolean DEFAULT FALSE NOT NULL, "day_0_start_time" integer NOT NULL, "day_0_end_time" integer NOT NULL, "day_1_start_time" integer NOT NULL, "day_1_end_time" integer NOT NULL, "day_2_start_time" integer NOT NULL, "day_2_end_time" integer NOT NULL, "day_3_start_time" integer NOT NULL, "day_3_end_time" integer NOT NULL, "day_4_start_time" integer NOT NULL, "day_4_end_time" integer NOT NULL, "day_5_start_time" integer NOT NULL, "day_5_end_time" integer NOT NULL, "day_6_start_time" integer NOT NULL, "day_6_end_time" integer NOT NULL) 2021-01-22 20:16:25.560 UTC [4055] discourse@discourse LOG: duration: 100.868 ms statement: CREATE INDEX "index_user_notification_schedules_on_user_id" ON "user_notification_schedules" ("user_id") 2021-01-22 20:16:25.782 UTC [4055] discourse@discourse LOG: duration: 142.180 ms statement: CREATE INDEX "index_do_not_disturb_timings_on_scheduled" ON "do_not_disturb_timings" ("scheduled") 2021-01-22 20:16:26.414 UTC [4055] discourse@discourse LOG: duration: 361.514 ms statement: UPDATE users SET locale = 'en_GB' WHERE locale = 'en' 2021-01-22 20:16:26.656 UTC [4055] discourse@discourse LOG: duration: 132.778 ms statement: UPDATE theme_translation_overrides SET locale = 'en_GB' WHERE locale = 'en' 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse ERROR: duplicate key value violates unique constraint "index_users_on_username" 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse DETAIL: Key (username)=(DaveW) already exists. 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse STATEMENT: UPDATE users SET locale = 'en' WHERE locale = 'en_US' rake aborted! StandardError: An error has occurred, this and all later migrations canceled: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_username" DETAIL: Key (username)=(DaveW) already exists. And then, at the end of the output: I, [2021-01-22T20:16:42.805286 #1] INFO -- : Terminating async processes I, [2021-01-22T20:16:42.805333 #1] INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/13/bin/postmaster -D /etc/postgresql/13/main pid: 49 I, [2021-01-22T20:16:42.805381 #1] INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 166 166:signal-handler (1611346602) Received SIGTERM scheduling shutdown... 2021-01-22 20:16:42.805 UTC [49] LOG: received fast shutdown request 2021-01-22 20:16:42.835 UTC [49] LOG: aborting any active transactions 2021-01-22 20:16:42.857 UTC [49] LOG: background worker "logical replication launcher" (PID 58) exited with exit code 1 166:M 22 Jan 2021 20:16:42.876 # User requested shutdown... 166:M 22 Jan 2021 20:16:42.876 * Saving the final RDB snapshot before exiting. 166:M 22 Jan 2021 20:16:44.758 * DB saved on disk 166:M 22 Jan 2021 20:16:44.758 # Redis is now ready to exit, bye bye... 2021-01-22 20:16:45.563 UTC [53] LOG: shutting down I, [2021-01-22T20:16:52.806177 #1] INFO -- : HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/13/bin/postmaster -D /etc/postgresql/13/main pid:49 did not terminate cleanly, forcing termination! FAILED -------------------- Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return # Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn' exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]} d627ad17d1f22d839a7dc8099878e6272eb3ea1772539f6628e2a23dd830aca2 ** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one. ./discourse-doctor may help diagnose the problem. I have no idea how there could be two accounts with the same username. Our site is hard down. What do I do now? Thanks, Gunnar

I think you can get the site back up with ./launcher start app Then take a look at ways to delete the duplicate: [image] Duplicate data explorer key blocking rebuild Support Hey all, Rare I ask for help, but I’m puzzled about how to resolve this. Just rebuilt my site and getting this: discourse-data-explorer is already at latest compatible version I, [2020-08-28T17:53:57.578920 #1] INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey" 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse DETAIL:… and [image] Failed upgrade: UniqueViolation: duplicate key value violates unique constraint "data_explorer_queries_pkey" Self-hosting Today’s upgrade to latest failed: FAILED -------------------- Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return # Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn' exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]} 590cf0611c566ea6df5f70ffdd2cec2359e84eaea29b7abcde77d56288a46370 ** FAILED TO BOOTSTRAP ** please scrol…

[image] omarfilip: You do have two Did you visit my site? :wink: I see what you mean, when I search the in the GUI, I do see two. However, when I click on both as an admin and bring up the details page, they appear to be the same user. I can’t find a single detail that’s different between the two. Basically, no matter which one of those I click, I get the data for the second one, the one with the full name entered. Since that user had never posted, and not visited in a while, I deleted the account from the GUI. Interestingly, I still have the other one, the first one on your list, without a full name. But now, if I click on that user, nothing happens. I can see a dialog trying to open, then immediately closing. Searching the database directly for username = DaveW now returns zero rows. However, if I search for select * from users WHERE name = 'DaveW'; (name, not username,) I get 1 row returned: 19732 | DaveW | 2016-11-15 12:43:02.708166 | 2016-11-15 12:43:02.708166 | DaveW | 0 | | | | t | davew | 2016-11-15 12:43:02.708166 | f | 2017-06-01 18:09:45.018058 | 1 | f | | | | | | | 0 | 0 | | f | | | | | | f | | | | | (1 row) Notice how it has DaveW (same spelling) in the username field! This account is also three years older than the other one. Can I get rid of it with: DELETE from users WHERE id = 19732; without any ill effects? Thanks for all your help! Gunnar

[image] Gunnar: Did you visit my site? :wink: Of course! :grinning: That should delete 19732 but I can’t guarantee the absence of any ill effects. In any case, a backup before proceeding further is advisable.

You might also change the username of one of the duplicates.

[image] pfaffman: change the username Right. Once I do, should I be able to restart the upgrade from scratch? I.e. run rebuild twice? Thanks! Gunnar

That should work. Unless there are other duplicate users.

[image] pfaffman: Unless there are other duplicate users. Turns out there are, at least one. PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_username_lower" DETAIL: Key (username_lower)=(robs) already exists. Is there any way I can scan the database for duplicates? Not just users, but any table that could cause this error during the upgrade? I’d like to find them and fix them all before I try again. Also, it would be good if this could be part of the upgrade process. Perhaps run a scan first, and stop the process before any changes have been made. Warn the user and provide a list of the duplicates, with a link to a page here on meta with instructions on how to fix?

[image] Gunnar: find them and fix them all before I try again A less invasive approach may be to reindex the database before upgrading and should tell you which ones need to be fixed. [image] Corrupt indexes in PG12, how do I fix? Self-hosting Sorry for bumping this once again, I got a very strange discourse=# REINDEX SCHEMA CONCURRENTLY public; ERROR: could not create unique index "index_tags_on_name_ccnew" DETAIL: Key (name)=(chronicillness) is duplicated. I’m assuming it can be fixed by the solution suggested above by @riking but I’m unable to figure out how to modify the syntax to suit my case. frowning

[image] omarfilip: reindex the database I’m not a database guy, so that looks scary. And if I read it correctly, you then end up with duplicate indices, and have to delete them manually? Besides, here’s the strange part. Like you found on my site with the previous duplicate, I found a duplicate user RobS in the GUI now. But, just like before, no matter which of them I clicked, I ended up on the profile page of just one of them. I.e.: List users named RobS in the GUI. Find two. See that both have different stats. Click user #1. See profile and stats belonging to user #2. Click user #2. See profile and stats belonging to user #2. select * from users WHERE username_lower = 'robs'; Returns 1 row: What looks like user #2 (the dates match). select * from users WHERE username = 'RobS'; Also returns only 1 row: What looks like user #1 (again, the dates match). This user has a different ID from the other one. Judging by the output, they both had the same username and username_lower, yet only 1 row was returned for each SELECT statement. Is my database in serious trouble? Would a reindexing fix this?

I tried rebuilding again, and found yet another duplicate with the username “drc”. In the GUI, I found these two: Deborah C David C Try clicking on either user to see their details on their profile “card”. You’ll see that no matter which of them you click, you’ll see the details for Deborah C. I have no idea how this happened, but what is my best course of action now? I’ve given up on the upgrade until I can get past this. How would I reindex? Like this? REINDEX SCHEMA CONCURRENTLY public; Would that tell me which keys are duplicate?

I wonder what the following would return: select id, username FROM users WHERE username_lower = 'robs' OR username = 'RobS': It’s not something silly like one of the username entries has whitespace, or a non-printing character / unicode somehow in it?

2.7.0.beta2 upgrade failed with ERROR: duplicate key

Product Support Self-hosting

pfaffman (Jay Pfaffman) January 25, 2021, 7:09pm 24

Easiest is probably to change DRC to DRC1 (or whatever) in the web interface (and I think it’ll also fix @ references in posts?). You don’t need to change username_lower, as that’s calculated.

Topic		Replies	Views
Upgrade failed with "duplicate key value violates unique constraint" error Self-hosting	17	2744	February 29, 2020
PG::UniqueViolation Problem during 3.1.0.beta4 upgrade Self-hosting	5	707	April 19, 2023
Upgrade Issues: Failed Upgrade due to Duplicate Key, Failed Snapshot Restore Self-hosting	7	1826	April 25, 2023
[Site Down :(] Upgrade\Rebuild Failure - DB Migration Issue? Self-hosting	8	945	June 19, 2023
Upgrade Failed from 2.7.0.beta1 to 2.7.0.beta3 Self-hosting	13	2385	February 15, 2021

2.7.0.beta2 upgrade failed with ERROR: duplicate key

Related topics