Duplicate Users Appear in the 'external_entities' Table

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

データベースを変更する前には必ずデータをバックアップするようにします。

症状

Duplicate users appear in the external_entities table in the database.

原因

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

ソリューション

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;

 

 

 

 

 

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

最終更新日: 2016 年 2 月 23 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.