external ID already in use

[Sehen]

Hello, 

I have some issue about Gerrt 2.16.6.

My Gerrit site's authentication type is LDAP, 

How can I solve it? How can I remove external ID from Gerrit DB?

Issue summary is,

1. one member cannot login to webUI.

  error_log:

     Email xxx@yyy is already assigned to account 3753; cannot create external ID gerrit:xxx with the same email for account 3850.

     'xxx' failed to sign in com.google.gerrit.server.account.AccountException: Email 'xxx@yyy' in use by another account

2. On Gerrit DB, his external_id(gerrit:xxx)'s some text was changed to uppercase.

3. I change the word to lowercase, it is correct LDAP ID. 

4. Still cannot login to Gerrt.

5. I did reindex accounts and changes.

6. Still cannot login and cannot be found username on gerrit(when set-account,,)

   error_log:

      Cannot assign external ID "username:xxxx" to account 3911; external ID already in use.

7. But his information is still exist in DB.

[RJ]

Hello Sehen,

    To resolve try below steps which worked from me before

1. Deactivate 3850 account

curl -X "DELETE" --user admin:<http token>  http://gerrit.com/a/accounts/3850/active

2. Remove email ID from 3850

curl -X "DELETE" --user admin:<http token>  http://gerrit.com/a/accounts/3850/emails/us...@domain.com

3. Request user to login and should be able to auto-register as new ID {could be 3911}

[Sehen]

Thanks for your reply

3850 is not exist, 3753 is his original account id.

After account reindex, 3753 cannot be found by gerrit command (set-account,,).

But 3753 account id is exist in DB, and cannot login with error messages. 

   error_log:

      Cannot assign external ID "username:xxxx" to account 3911; external ID already in use.

2019년 7월 25일 목요일 오전 9시 33분 58초 UTC+9, RJ 님의 말:

[RJ]

Hello Sehen,

    You can try to find out details of 3753 and verify the information with Ldap.

curl --user admin:<http token>  http://gerrit.com/accounts/3753

[Sehen]

Thanks RJ.

His LDAP information is right, and show details was failed :

  -- Account '3753' is not found or ambiguous

i'm sorry for complexd  history like below. 

He did first login to gerrit with uppercase id, but from second, he tried and failed with lowercase id.

after that, I updated his uppercase external id on gerrit DB(gerrit:xxxx) to lowercase, and do gerrit reindex. 

Now he cannot log in with lowercase or uppercase, and cannot found account id on gerrit.

How can I fix it? I should remove his information from DB manually? 

2019년 7월 26일 금요일 오전 2시 7분 30초 UTC+9, RJ 님의 말:

[Andrew Grimberg]

You state the information is still in the DB. Were all of your changes
against the external-ids table in your database? Since you mention the
DB I can only assume that you upgraded to 2.16. If this is true, then
the upgrade to 2.16 did a forced migration of all of the external-ids
into NoteDB and no longer references the DB for account checks.

See my recent email [0] about this exact problem.

Here's the relevant extract from the write-up I had to give to my team
on how to fix these sort of problems:

--[cut]--
Let's say
the following is true:

Account having issues: FooPerson
Original email: foop...@example.org
New LFID email: pers...@example.org

[code]
export GERRIT_USER=FooPerson
export GERRIT_BASE=gerrit.example.org/r

# GERRIT_BASE should be your canonicalWebUrl

curl "https://${GERRIT_BASE}/accounts/?suggest&q=FooPerson"
)]}'
[
{
"_account_id": 10000,
"name": "Foo Bear",
"email": "foop...@example.org",
"username": "FooPerson"
}
]

grep -Rm1 'accountId = 10000$'
1d/061ba9b156d95bd612fbf82cd4f7b28b514320: accountId = 10000
2d/e013766e621ed981b18d9927370ede7323023d: accountId = 10000
2d/064c9bc0860cfee5ab09aad770e999411a57b0: accountId = 10000

cat 1d/061ba9b156d95bd612fbf82cd4f7b28b514320
[externalId "gerrit:FooPerson"]
accountId = 10000
email = foop...@example.org

cat 2d/e013766e621ed981b18d9927370ede7323023d
[externalId "username:FooPerson"]
accountId = 10000

cat 2d/064c9bc0860cfee5ab09aad770e999411a57b0
[externalId "mailto:pers...@example.org"]
accountId = 10000
email = pers...@example.org
[/code]

NOTE: the username record, may or may not have a password field in it,
that field is the https password that they would be using encrypted
using a salted bcrypt.

NOTE: These are ini style files and they require a hard tab, not expand
tab as displayed here

In this example the account has the new address already attached... we
need to remove that record, move the old address into a new record and
replace the old address with a new one, all in a single commit.

[code]
git rm 2d/064c9bc0860cfee5ab09aad770e999411a57b0
echo -n 'mailto:foop...@example.org$' | sha1sum
fe6e22cf0f4618ac92c1c69292ff649c0349a322

vim fe/6e22cf0f4618ac92c1c69292ff649c0349a322
[externalId "mailto:foop...@example.org$"]
accountId = 10000
email = foop...@example.org$

git add fe/6e22cf0f4618ac92c1c69292ff649c0349a322

# change the email in 1d/061ba9b156d95bd612fbf82cd4f7b28b514320
vim 1d/061ba9b156d95bd612fbf82cd4f7b28b514320
[externalId "gerrit:FooPerson"]
accountId = 10000
email = pers...@example.org

git add 1d/061ba9b156d95bd612fbf82cd4f7b28b514320
[/code]

At this point, we should be good to commit and push this up

[code]
git commit -as
# commit message similar to:

Fixing accountId 10000 (FooPerson)

Issue: LINK_TO_ISSUE_TRACKER
Signed-off-by: Andrew Grimberg <agri...@linuxfoundation.org>
[/code]

Note: we want the issue linked to our jira since you're doing this to
another Gerrit

Now push it up

[code]
git push origin HEAD:refs/meta/external-ids
[/code]

As long as we did everything correctly, then Gerrit will accept the
change and the customer should now be able to login. If we did something
badly, then Gerrit will reject the change and give you a reason why
--[/cut]--

Please note, this operation will only succeed if you can successfully
push back to refs/meta/external-ids. It will fail if there are any other
consistency issues.

Since you did an upgrade there's a high probability of consistency
issues with your accounts. You're going to need to clean all of those up.

You can check for problems by doing something like this:

--[cut]--

echo '{
"check_accounts": {},
"check_account_external_ids": {}
}' > consistency.json

export GERRIT_BASE=gerrit.example.org/r

# GERRIT_BASE should be your canonicalWebUrl

curl -n -s -X POST -H "Content-Type: application/json" \
-d @consistency.json \
"https://${GERRIT_BASE}/a/config/server/check.consistency" | \
sed "s/\\\\u0027/'/g"
--[/cut]--

The -n in the curl tells it to use a .netrc file to lookup your user
credentials for the operations since the /a/ URI fragment is an
authenticated fragment.

If you get back any problems in the check_accounts_result you're going
to potentially have to take corrective action on their user account
object as well, but I would clean up all issues out of the external_ids
check first.

Account objects are sharded as

refs/users/NN/YYNN

Where YYNN == the user accountId and the NN from the end of the
accountId is the sharding index. If the accountId is less than 10 then
NN == 0Y

So for instance account 1 is at:

refs/users/01/1

account 10 is at:

refs/users/10/10

and account 456323456 is at:

refs/users/56/456323456

Well... at least from extrapolation, I bet if you had accountIds that
big there would be another level (or more) of sharding injected but I
don't know when the sharding happens ;)

-Andy-

[0]
https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!topic/repo-discuss/uNFC6MXucD8

[Doug Robinson]

Andrew:

Great information - thank you!

One quick sanity check: is there anything that would prevent changing the:

    externalId "gerrit:FooPerson"

into:

    externalId "gerrit:BarPerson"

(say their account name changed)?  From the series of operations it appears to be ok - but not knowing the internals makes me wonder?!

Cheers.

Doug

[Andrew Grimberg]

That totally works.

It gets more weird if you're trying merge accounts. I did all account
merges before a full migration to NoteDB (that is, the migration of
ReviewDB to NoteDB) as it was a little easier. I haven't looked into
what it would take to do it in a fully NoteDB world.

You have to make sure to update any "username:XXXX" objects as well as
they need to match up properly.

-Andy-

[Collins ILo]