2.7.0.beta2 upgrade failed with ERROR: duplicate key

Installation
1

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
2

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
4

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
5

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
8

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
9

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

1 Like
10

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
11

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
12

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

2 Likes
13

Right.

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

Thanks!
Gunnar

4 Likes
14

That should work. Unless there are other duplicate users.

2 Likes
15

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
16

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

2 Likes
17

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
18

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
19

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
20

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
21

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
22

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
23

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