merging-gerrit-user-accounts

[Chris Harris]

Can some point me to the location of the script that can be used to
merge duplicate OpenID accounts? I have seen references to be it being
on the wiki but can't locate it.

Thanks,

Chris

[Shawn Pearce]

http://code.google.com/p/gerrit/wiki/SqlMergeUserAccounts

[Chris Harris]

Many Thanks 

[Chris Harris]

Shawn,

I have modified your scripts to work with MySQL ( see below ), I am
happy to put them on the wiki if they would be helpful to others.

Chris

load_merge.sql:

CREATE TEMPORARY TABLE links
(from_id INT NOT NULL
,to_id INT NOT NULL,
UNIQUE(from_id, to_id));

DELETE FROM links;

INSERT IGNORE INTO links (from_id, to_id)
SELECT
f.account_id
,t.account_id
FROM
accounts f
,accounts t
WHERE
f.preferred_email is not null
AND t.preferred_email is not null
AND f.account_id <> t.account_id
AND f.preferred_email = t.preferred_email
AND f.registered_on > t.registered_on;

INSERT IGNORE INTO links (from_id, to_id)
SELECT DISTINCT
f.account_id
,t.account_id
FROM
account_external_ids e_t
,account_external_ids e_f
,accounts f
,accounts t
WHERE
e_t.external_id = CONCAT('Google Account ', e_f.email_address)
AND e_f.account_id <> e_t.account_id
AND e_f.account_id = f.account_id
AND e_t.account_id = t.account_id
AND f.registered_on > t.registered_on;

SELECT
l.from_id
,l.to_id
,f.registered_on
,t.registered_on
,t.preferred_email
FROM
links l
,accounts f
,accounts t
WHERE
f.account_id = l.from_id
AND t.account_id = l.to_id
ORDER BY t.preferred_email;

merge_accounts.sql:

DROP TABLE to_del;
CREATE TEMPORARY TABLE to_del (old_id INT);

CREATE TEMPORARY TABLE tmp_ids
(email_address VARCHAR(255)
,account_id INT NOT NULL
,from_account_id INT NOT NULL
,external_id VARCHAR(255) NOT NULL
);

START TRANSACTION;

DELETE FROM tmp_ids;
INSERT INTO tmp_ids
(account_id
,from_account_id
,email_address
,external_id)
SELECT
l.to_id
,l.from_id
,e.email_address
,e.external_id
FROM links l, account_external_ids e
WHERE e.account_id = l.from_id
AND NOT EXISTS (SELECT 1 FROM account_external_ids q
WHERE q.account_id = l.to_id
AND q.external_id = e.external_id);

DELETE FROM account_external_ids
WHERE EXISTS (SELECT 1 FROM tmp_ids t
WHERE account_external_ids.external_id = t.external_id
AND account_external_ids.account_id = t.from_account_id);

INSERT INTO account_external_ids
(account_id
,email_address
,external_id)
SELECT
account_id
,email_address
,external_id
FROM tmp_ids;

INSERT INTO account_ssh_keys
(ssh_public_key
,valid
,account_id
,seq)
SELECT
k.ssh_public_key
,k.valid
,l.to_id
,100 + k.seq
FROM links l, account_ssh_keys k
WHERE k.account_id = l.from_id
AND NOT EXISTS (SELECT 1 FROM account_ssh_keys p
WHERE p.account_id = l.to_id
AND p.ssh_public_key = k.ssh_public_key);

INSERT INTO starred_changes
(account_id, change_id)
SELECT l.to_id, s.change_id
FROM links l, starred_changes s
WHERE l.from_id IS NOT NULL
AND l.to_id IS NOT NULL
AND s.account_id = l.from_id
AND NOT EXISTS (SELECT 1 FROM starred_changes e
WHERE e.account_id = l.to_id
AND e.change_id = s.change_id);

INSERT INTO account_project_watches
(account_id, project_name)
SELECT l.to_id, s.project_name
FROM links l, account_project_watches s
WHERE l.from_id IS NOT NULL
AND l.to_id IS NOT NULL
AND s.account_id = l.from_id
AND NOT EXISTS (SELECT 1 FROM account_project_watches e
WHERE e.account_id = l.to_id
AND e.project_name = s.project_name);

INSERT INTO account_group_members
(account_id, group_id)
SELECT l.to_id, s.group_id
FROM links l, account_group_members s
WHERE l.from_id IS NOT NULL
AND l.to_id IS NOT NULL
AND s.account_id = l.from_id
AND NOT EXISTS (SELECT 1 FROM account_group_members e
WHERE e.account_id = l.to_id
AND e.group_id = s.group_id);

UPDATE changes c
INNER JOIN links l
ON c.owner_account_id = l.from_id
SET c.owner_account_id = l.to_id
WHERE l.to_id IS NOT NULL;

UPDATE patch_sets p
INNER JOIN links l
ON p.uploader_account_id = l.from_id
SET p.uploader_account_id = l.to_id
WHERE l.to_id IS NOT NULL;

UPDATE patch_set_approvals p
INNER JOIN links l
ON p.account_id = l.from_id
LEFT OUTER JOIN patch_set_approvals existing_approval
ON existing_approval.change_id = p.change_id
AND existing_approval.patch_set_id = p.patch_set_id
AND existing_approval.account_id = l.to_id
AND existing_approval.category_id = p.category_id
SET p.account_id = l.to_id
WHERE l.to_id IS NOT NULL
AND existing_approval.account_id IS NULL;

UPDATE change_messages c
INNER JOIN links l
ON c.author_id = l.from_id
SET c.author_id = l.to_id
WHERE l.to_id IS NOT NULL;

UPDATE patch_comments p
INNER JOIN links l
ON p.author_id = l.from_id
SET p.author_id = l.to_id
WHERE l.to_id IS NOT NULL;

-- Destroy the from account
--
INSERT INTO to_del
SELECT from_id FROM links
WHERE to_id IS NOT NULL
AND from_id IS NOT NULL;

DELETE FROM account_agreements WHERE account_id IN (SELECT old_id FROM
to_del);
DELETE FROM account_external_ids WHERE account_id IN (SELECT old_id
FROM to_del);
DELETE FROM account_group_members WHERE account_id IN (SELECT old_id
FROM to_del);
DELETE FROM account_project_watches WHERE account_id IN (SELECT old_id
FROM to_del);
DELETE FROM account_ssh_keys WHERE account_id IN (SELECT old_id FROM
to_del);
DELETE FROM accounts WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM starred_changes WHERE account_id IN (SELECT old_id FROM
to_del);
DELETE FROM patch_set_approvals WHERE account_id IN (SELECT old_id
FROM to_del);

On Nov 30, 10:11 am, Shawn Pearce <s...@google.com> wrote:

[Daniel Rahamim]

Is this still relevant and working for 2.10.2?

I got an error with Merge_accounts.sql 

ERROR 1051 (42S02) at line 1: Unknown table 'to_del'

Any help would be appreciated. 

[Daniel Rahamim]

Sorry if this is a double post but if it is I cannot see my original.

I am having trouble with the migrate_accounts script on 2.10.2 

It initially had an error on line one 

ERROR 1051 (42S02) at line 1: Unknown table 'to_del'

I fixed that by removing the first line but now I get 

ERROR 1146 (42S02) at line 13: Table 'gerrit.links' doesn't exist

I am wondering if this is due to the script not being updated to the new schema of 2.10,2.

Any help would be much appreciated. 

-Daniel 

 

On Thursday, December 1, 2011 at 1:52:43 PM UTC-8, Chris Harris wrote:

[Justin Clift]

On 21 Apr 2015, at 22:40, Daniel Rahamim <drah...@academixdirect.com> wrote:
> Sorry if this is a double post but if it is I cannot see my original.
> I am having trouble with the migrate_accounts script on 2.10.2
>
> It initially had an error on line one
> ERROR 1051 (42S02) at line 1: Unknown table 'to_del'
> I fixed that by removing the first line but now I get
> ERROR 1146 (42S02) at line 13: Table 'gerrit.links' doesn't exist
> I am wondering if this is due to the script not being updated to the new schema of 2.10,2.
>
> Any help would be much appreciated.

Looking at the code you have below, these are temporary tables
created during the process.

If they're not existing for you any more, it probably means the tables
themselves have disappeared since the last time you ran the code.

eg you may have restarted the database or something

If that's the case, just re-run the bits of code that create those
tables. :)

Regards and best wishes,

Justin Clift

> --
> --
> To unsubscribe, email repo-discuss...@googlegroups.com
> More info at http://groups.google.com/group/repo-discuss?hl=en
>
> ---
> You received this message because you are subscribed to the Google Groups "Repo and Gerrit Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to repo-discuss...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
GlusterFS - http://www.gluster.org

An open source, distributed file system scaling to several
petabytes, and handling thousands of clients.

My personal twitter: twitter.com/realjustinclift

[Daniel Rahamim]

Justin,

I think this is due to not a reset but that I had done the upgrade to gerrit 2.10.2 before attempting to merge users.

I am trying to get our gerrit install to work with OAuth as Google has deprecated their OpenID authentication. 

-Daniel

[Justin Clift]

On 22 Apr 2015, at 16:50, Daniel Rahamim <drah...@academixdirect.com> wrote:
> Justin,
> I think this is due to not a reset but that I had done the upgrade to gerrit 2.10.2 before attempting to merge users.
> I am trying to get our gerrit install to work with OAuth as Google has deprecated their OpenID authentication.

Yeah. We switch to using GitHub for authentication for the same reason.

It's been pretty painful. :(

+ Justin

[Daniel Rahamim]

Unfortunately I have to do the manual method due to our current Gerrit using an IP rather then a dns entry as its server ID for authentication making the OpenID to OAuth linking impossible as OAuth doesn't accept IP address.

I have now enlisted the help of one of the company devs to look at the DB and see how to modify the scripts to get this to work (the other fun part is that there are multiple ID entries for each user causing duplicates issues. ) 

Note: I inherited this cluster --------.

[Daniel Rahamim]

After some tinkering and head bashing we got it to work.

Below are the SQL scripts that worked for a 2.8.4 to 2.10.2 Gerrit upgrade with a migration to Google OAuth from Google OpenID.

As a note we also had a weird installation. 

Our Gerrit was originally configured to use an IP instead of a DNS URI which we changed prior to the 2.10.2 upgrade. 

Our users had multiple user accounts due to some weird issues that I am not aware of how they happened so the scripts below handle that situation. 

load_merge.sql

DROP TABLE links;

CREATE TABLE links

(from_id INT NOT NULL

,to_id INT NOT NULL,

UNIQUE(from_id ));

DELETE FROM links;

INSERT INTO links (from_id, to_id)

SELECT

 f.account_id

,t.account_id

FROM

 accounts f

,accounts t

WHERE

     f.preferred_email is not null

 AND t.preferred_email is not null

 AND f.account_id <> t.account_id

 AND f.preferred_email = t.preferred_email

 AND f.account_id < t.account_id

ON DUPLICATE KEY UPDATE from_id=from_id;

INSERT INTO links (from_id, to_id)

SELECT DISTINCT

 f.account_id

,t.account_id

FROM

 account_external_ids e_t

,account_external_ids e_f

,accounts f

,accounts t

WHERE

     e_t.external_id = CONCAT('Google Account ', e_f.email_address)

 AND e_f.account_id <> e_t.account_id

 AND e_f.account_id = f.account_id

 AND e_t.account_id = t.account_id

 AND f.account_id < t.account_id

ON DUPLICATE KEY UPDATE from_id=from_id;

SELECT

 l.from_id

,l.to_id

,f.registered_on

,t.registered_on

,t.preferred_email

FROM

 links l

,accounts f

,accounts t

WHERE

    f.account_id = l.from_id

AND t.account_id = l.to_id

ORDER BY t.preferred_email;

merge_accounts.sql

DELETE FROM account_external_ids WHERE account_id IN (SELECT old_id

FROM to_del);

DELETE FROM account_group_members WHERE account_id IN (SELECT old_id

FROM to_del);

DELETE FROM account_project_watches WHERE account_id IN (SELECT old_id

FROM to_del);

DELETE FROM account_ssh_keys WHERE account_id IN (SELECT old_id FROM

to_del);

DELETE FROM accounts WHERE account_id IN (SELECT old_id FROM to_del);

DELETE FROM starred_changes WHERE account_id IN (SELECT old_id FROM

to_del);

DELETE FROM patch_set_approvals WHERE account_id IN (SELECT old_id

FROM to_del);