2.7.0.beta2 upgrade failed with ERROR: duplicate key

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 #<Process::Status: pid 4032 exit 1>

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

2 Likes

I think you can get the site back up with ./launcher start app

Then take a look at ways to delete the duplicate:

and

5 Likes

More information I should probably provide:

  • This is a single container install.
  • The first rebuild completed without any errors. This error occurred when I was running the second rebuild, after the first rebuild completed successfully.

Thanks,
Gunnar

1 Like

Do you have enough disk space? Error related to the required PostgreSQL update to version 13 afaik. Did you follow this upgrade method?

cd /var/discourse
git pull
 ./launcher rebuild app

If you search the forum for those errors you’ll see a lot of past information and possible fixes.

Try this.

Search the forum for your posted errors to see various fixes that worked for others.

1 Like

I got the site back up by renaming postgres_data_old back to postgres_data and then running ./launcher start app to start the old image.

So, I’m back to square 1, but at least the site is up.

I tried to find the duplicate key, by entering this:

select * from users WHERE username = 'DaveW';

(username = DaveW was the problem key as shown in the error message in my first post in this thread.)

The command returned just one row, one user. What am I missing?

Thanks,
Gunnar

1 Like

You do have two, possibly capitalized differently in the DB:
Screen Shot 2021-01-22 at 3.25.53 PM

1 Like

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

1 Like

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.

3 Likes

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

2 Likes

Right.

Once I do, should I be able to restart the upgrade from scratch? I.e. run rebuild twice?

Thanks!
Gunnar

4 Likes

That should work. Unless there are other duplicate users.

2 Likes

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?

2 Likes

A less invasive approach may be to reindex the database before upgrading and should tell you which ones need to be fixed.

2 Likes

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?

2 Likes

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?

1 Like

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?

1 Like

I think yes, and I don’t know if there is a better way to do it. On my test instance it just runs without any output. When I updated my production site, there was only one duplicate to contend with, so my experience may not be a good example for your situation. In any case backup before database operations—I usually create a snapshot at DigitalOcean so restoring in case of trouble would be quick and easy.

1 Like

I think that’s how I’ve resolved similar in the past. Rebuild the index, when it fails, it’ll give you the thing that’s a duplicate, then you fix it, then you try again. Repeat.

2 Likes

Thanks. When it fails, how do I find and get rid of the incomplete new index? Is it as simple as adding “_ccnew” to the name of the table that failed?

I.e., when it crashes on “users” because of a duplicate, do I first fix the duplicate, then:

DROP index ‘users_ccnew’;

Then rinse, repeat? Is it as simple as that?

Thanks,
Gunnar

1 Like

Ah, yes. I’m now in the situation that I’ve found two valid (and active) users with the same username and username_lower:

  id   | username | (redacted) | username_lower |
 42379 | DrC      | (redacted) | drc            |
 47695 | DRC      | (redacted) | drc            |

Looks like I need to change both the username and username_lower of the second user. How would I do that in psql?

Thanks!
Gunnar

1 Like