Uploaded image for project: 'Confluence Server and Data Center'
  1. Confluence Server and Data Center
  2. CONFSERVER-58261

Missing unique constraints and index in certain fresh Confluence installation

    XMLWordPrintable

Details

    Description

      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):
      2. Install a fresh Confluence instance as per Confluence Installation Guide

      Expected Results

      The following constraints/indexes should exist on the tables:

      CONSTRAINT/INDEX NAME TYPE TABLE NAME
      unq_lwr_username (or generated name on certain SQL vendors) Unique Constraint USER_MAPPING
      bandana_unique_key Unique Constraint BANDANA
      cp_unique_user Unique Constraint CONTENT_PERM
      cp_unique_group Unique Constraint CONTENT_PERM
      cps_unique_type Unique Constraint CONTENT_PERM_SET
      cwd_group_name_dir_id Unique Constraint CWD_GROUP
      cwd_unique_grp_attr Unique Constraint CWD_GROUP_ATTRIBUTE
      cwd_user_name_dir_id Unique Constraint CWD_USER
      cwd_unique_usr_attr Unique Constraint CWD_USER_ATTRIBUTE
      cwd_unique_user_membership Unique Constraint CWD_MEMBERSHIP
      cwd_unique_group_membership Unique Constraint CWD_MEMBERSHIP
      ospe_entityid_idx Index OS_PROPERTYENTRY

      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:

      Attachments

        Issue Links

          Activity

            People

              nhdang Nhan Dang
              lrura Lauretha Rura
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: