Missing unique constraints and index in certain fresh Confluence installation

Summary

When installing Confluence version from scratch, some constraints and indices are not being added.

This affects the following Confluence versions:

• 5.6.x
• 5.7.x
• 5.8.x

Steps to Reproduce

1. Configure your DB Server as per the following documentation (depending on your DB Server):
   • Database Setup for PostgreSQL
   • Database Setup for Oracle
   • Database Setup For MySQL
   • Database Setup for SQL Server
2. Install a fresh Confluence instance as per Confluence Installation Guide

Expected Results

The following constraints/indexes should exist on the tables:

The unique constraints specifically for Oracle/SQL Server is a bit different with the other Databases as the CONTENT_PERM only has CP_UNIQUE_USER_GROUPS unique constraint and the CWD_MEMBERSHIP only has CWD_UNIQUE_MEMBERSHIP unique constraint.

Actual Results

The above mentioned unique constraints and index are not present in the database.

Workaround

Make sure to back up the database before applying any SQL commands that modify the database, and ensure Confluence is not running while applying the change

1. First, let's check if there are any duplicate records in these Confluence tables:
   Query to find duplicate records in the USER_MAPPING table

   select lower_username, count(*) from user_mapping group by lower_username having count(*) > 1;
   

   Query to find duplicate records in the BANDANA table

   select bandanacontext, bandanakey, count(*) from bandana group by bandanacontext, bandanakey having count(*) > 1;
   

   Query to find duplicate records in the CONTENT_PERM_SET tables

   select content_id, cont_perm_type,count(*) from content_perm_set group by content_id, cont_perm_type having count(*) > 1;
   

   Query to find duplicate username records in the CONTENT_PERM table

   select cps_id, cp_type, username,count(*) from content_perm group by cps_id, cp_type, username having count(*) > 1 AND username is not null;
   

   Query to find duplicate groupname records in the CONTENT_PERM table

   select cps_id, cp_type, groupname, count(*) from content_perm group by cps_id, cp_type, groupname having count(*) > 1 AND groupname is not null;
   

   Query to find duplicate records in the CWD_GROUP table

   select lower_group_name, directory_id, count(*) from cwd_group group by lower_group_name, directory_id having count(*) > 1;
   

   Query to find duplicate records in the CWD_USER_ATTRIBUTE table

   select directory_id, user_id, attribute_name, attribute_lower_value, count(*) from cwd_user_attribute group by directory_id, user_id, attribute_name, attribute_lower_value having count(*) > 1;
   

   Query to find duplicate records in the CWD_GROUP_ATTRIBUTE table

   select directory_id, group_id, attribute_name, attribute_lower_value, count(*) from cwd_group_attribute group by directory_id, group_id, attribute_name, attribute_lower_value having count(*) > 1;
   

   Query to find duplicate child user records in the CWD_MEMBERSHIP table

   select parent_id, child_user_id, count(*) from cwd_membership group by parent_id, child_user_id having count(*) > 1 AND child_user_id is not null;
   

   Query to find duplicate child group records in the CWD_MEMBERSHIP table

   select parent_id, child_group_id, count(*) from cwd_membership group by parent_id, child_group_id having count(*) > 1 AND child_group_id is not null;
   

   Query to find duplicate records in the CWD_USER table

   select lower_user_name, directory_id, count(*) from cwd_user group by lower_user_name, directory_id having count(*) > 1;
   

1. Should there are no duplicate values being detected by the above queries, you may then run the below SQL statements to add the missing constraints and index.

   -- user_mapping constraint
   -- For vendors other than SQL Server (Oracle, MySQL and PostgreSQL)
   alter table user_mapping add constraint unq_lwr_username unique (lower_username);
   -- For SQL Server
   create unique index user_mapping_unq_lwr_username on user_mapping(lower_username) where lower_username is not null;
   
   -- Embedded Crowd constraints
   alter table cwd_group add constraint cwd_group_name_dir_id unique (lower_group_name, directory_id);
   alter table cwd_user add constraint cwd_user_name_dir_id unique (lower_user_name, directory_id);
   alter table cwd_group_attribute add constraint cwd_unique_grp_attr unique (directory_id, group_id, attribute_name, attribute_lower_value);
   alter table cwd_user_attribute add constraint cwd_unique_usr_attr unique (directory_id, user_id, attribute_name, attribute_lower_value);
   alter table cwd_membership add constraint cwd_unique_user_membership unique (parent_id, child_user_id);
   alter table cwd_membership add constraint cwd_unique_group_membership unique (parent_id, child_group_id);
   -- For Oracle/SQL Server - ALTER TABLE cwd_membership ADD CONSTRAINT cwd_unique_membership UNIQUE (parent_id, child_group_id, child_user_id);
   
   -- Content Permission constraints
   alter table CONTENT_PERM_SET add constraint cps_unique_type unique (CONTENT_ID, CONT_PERM_TYPE);
   
   alter table CONTENT_PERM add constraint cp_unique_user unique (CPS_ID, CP_TYPE, USERNAME);
   alter table CONTENT_PERM add constraint cp_unique_group unique (CPS_ID, CP_TYPE, GROUPNAME);
   -- For Oracle/SQL Server - ALTER TABLE CONTENT_PERM ADD CONSTRAINT CP_UNIQUE_USER_GROUPS UNIQUE ("CPS_ID", "CP_TYPE", "USERNAME", "GROUPNAME");
   
   -- Bandana constraints
   alter table BANDANA add constraint bandana_unique_key unique (BANDANACONTEXT, BANDANAKEY);
   
   -- Property Entry index
   create index ospe_entityid_idx on OS_PROPERTYENTRY (entity_id);
   

   However, if there are any duplicate records in your Confluence DB, please then raise a new ticket in our Support Portal http://getsupport.atlassian.com for further assistance.

Notes:

There's a KB about this issue that addresses missing constraints and how to delete the duplicated records in the database, mostly around user management related tables:

• User related issues in Confluence with the error message 'query did not return a unique result'