How to fix duplicate entries in Bitbucket PostgreSQL database

Summary

Due to unique constraint violation in Postgres, duplicate records could exists in Bitbucket database tables such as sta_normal_user, project, & plugin_setting.

This will result in a user who no longer exists been assigned as a default reviewer when creating a pull-request.

Environment

Bitbucket 7.17.4

PostgreSQL

Diagnosis

1. The sta_normal_user table has duplicate entry of the user. The entry has different user_id & slug , but the same name.

2. Duplicates are found in other tables such as project and plugin_setting table.

Cause

This seems to have occurred due to the bug causing unique constraint violation in Postgres due to OS upgrade.

Solution

Step 1:

1. Set up a test instance.

2. Get all the users who are duplicated, in ascending order (older users first) of their user_id using the query below:

   SELECT * from sta_normal_user where name in (SELECT (sta_normal_user.name)::text from sta_normal_user GROUP BY sta_normal_user.name HAVING count(*) > 1) ORDER by user_id;

3. Locate the duplicated users which you no longer require. For example, if the first 17 users seem to be the old users, rename the username which are duplicate entries with the query below:

   UPDATE sta_normal_user SET         name = slug || '.dis',         slug = slug || '.dis' where user_id in  (SELECT user_id from sta_normal_user where name in (SELECT (sta_normal_user.name)::text from sta_normal_user GROUP BY sta_normal_user.name HAVING count(*) > 1) ORDER by user_id limit 17)

The above query would fix the duplicates for the sta_normal_user table .

Step 2:

We still need to fix the duplicate entries for the table project and plugin_setting tables.

1. To find the duplicates of project table run the below query:

   select * from project pr where (select count(*)        from project inr        where inr.name = pr.name) > 1 ORDER by name;

2. Select the id of the duplicate project and delete it using the query below:

   DELETE FROM public.project WHERE id = project_id

   The project_id is gotten from the query in step 1.

3. To find the duplicates on the plugin_setting table run the query below:

   select * from plugin_setting pr where (select count(*) from plugin_setting inr where inr.key_name = pr.key_name) > 1 ORDER by key_name;

4. To delete the duplicate entries execute the command below for all the entries:

   DELETE FROM plugin_setting WHERE id = plugin_setting_id

   The plugin_setting_id is gotten from the query in step 3.

Step 3:

1. Reindex all the tables using the command below:

   REINDEX TABLE sta_normal_user; REINDEX TABLE project; REINDEX TABLE plugin_setting;

2. Confirm if all duplicates are removed and your instance is fully functional.

3. Perform the same steps on your production instance.