Duplicate Users Appear in the 'external_entities' Table

Still need help?

The Atlassian Community is here for you.

Ask the community

Always backup your data before performing any modifications to the database.

Symptoms

Duplicate users appear in the external_entities table in the database.

Cause

The cause is currently undetermined. Please let us know if you encounter a cause by adding a comment to the page.

Resolution

This is a script to remove duplicates by first assigning all the relationships to the minimum id (suggesting original) user id, then deleting all the duplicate rows:

-- Goal: consolidate all duplicate users into 1 user by choosing the minima, assigning all other
-- copies' permissions to that minima, and then deleting the rest
-- Create a minima table ()
-- Tested on MySQL


DROP TABLE
    IF EXISTS external_entities_min;
CREATE
    TABLE external_entities_min AS
    (
        SELECT
            MIN(id)AS id,
            name,
            'EXT' AS type
        FROM
            external_entities ee
        GROUP BY
            name
    );
ALTER TABLE
    external_entities_min ADD PRIMARY KEY (id);
--Create a join set
DROP TABLE
    IF EXISTS compareset;
CREATE
    TABLE compareset AS
    (
        SELECT
            eem.id          AS min_id,
            emt.groupid     AS source_group_id ,
            emt.extentityid AS source_ent_id
        FROM
            external_members emt
        JOIN external_entities ee
        ON
            emt.extentityid = ee.id
        JOIN external_entities_min eem
        WHERE
            eem.name = ee.name
    );


--POINT OF NO RETURN.  DO NOT DO THIS IF YOU HAVE NO BACKUP

--Clear the existing table
TRUNCATE external_members;


--Repopulate the table with just the minimum values 
INSERT
INTO
    external_members
    (
        SELECT
            min_id,
            source_group_id
        FROM
            compareset c
        GROUP BY
            min_id,
            source_group_id
    );



-- Remove all duplicate users.
DELETE
FROM
    external_entities
WHERE
    id NOT IN
    (
        SELECT
            id
        FROM
            external_entities_min
    );
--Cleanup
DROP TABLE
    IF EXISTS compareset;
DROP TABLE
    IF EXISTS external_entities_min;

 

 

 

 

 

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

Last modified on Feb 23, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.