2.7.0.beta2 upgrade failed with ERROR: duplicate key

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

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.

1 Like

Plot twist: No matter which of the two users I click in the web GUI, I get the profile details of just one of them (the first one, the one with the lowest id#). Seems even the web GUI is getting confused by these duplicates.

I was going to rename the newest user, because I figure the first to register the username has dibs, but the GUI doesn’t give me that option.

This is all making me very nervous. I think yet another backup is in order.

1 Like

Right. The GUI will use the index, which will return the first one (because it’s the one in the index).

I would probably rename the new user in rails, I guess. Here ends my free support on this one. If you can’t find an example and no one else will help, you can email jay@literatecomputing.com if you’ve got a budget.

2 Likes

In my case, the problem was duplicate users. So, focusing on that, one way of finding them that worked well for me, was this:

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

This gave me a list of the duplicates, with the count for each.

 username_lower | count 
----------------+-------
 jagger         |     2
 richards       |     2
 patrick        |     2
 peter          |     2
 jagman         |     2
(5 rows)

After fixing those, I was able to run a successful reindex, and after that, a successful rebuild.

5 Likes

Good one! I couldn’t remember to do the count(*) offhand.

2 Likes

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