Unique membership constraints ignored on MySQL

Under MySQL, users and groups can wind up with duplicate membership entries in the cwd_membership table. The existing constraint:

alter table cwd_membership add constraint cwd_unique_membership unique (parent_id, child_group_id, child_user_id)

has no effect thanks to child_group_id and child_user_id both allowing nulls. We need to apply a fix similar to the fix we did for the content permissions unique constraints fix, and apply different unique constraints in environments where nulls are ignored (mysql, postgres) vs nulls are distinct values too (oracle, sql server) vs illegal (db2).

Patch

Attached is CONF-22541-patch-v2.zip, which will repair any duplicate memberships in MySQL, and add corrected constraints to the cwd_membership table. The patch is compatible with Confluence 3.5.1 - 3.5.5. Confluence 3.5.6 contains this fix, so does not require the patch. Due to changes in upgrade infrastructure between Confluence 3.5 and 3.5.1, the patch is not backwards compatible with Confluence 3.5.

Contents

The patch contains 15 files in 3 directories:

• confluence/WEB-INF
  • com/atlassian/confluence/upgrade/upgradetask
    • AbstractConstraintCreationUpgradeTask.class
    • 7x ContentPermissionConstraintsUpgradeTask classes
    • 4x EmbeddedCrowdSchemaUpgradeTask classes
  • com/atlassian/crowd/embedded/hibernate2
    • 2x additional_constraints properties files
• confluence/admin
  • fixcwdmemberships.jsp

Installation

To install the patch:

1. stop Confluence
2. extract the zip to the top level of your <confluence install dir>. This will create the required directories under confluence/WEB-INF for the patched files.
3. start Confluence
4. Log in as a confluence administrator, and navigate to <confluence base url>/admin/fixcwdmemberships.jsp, and follow the prompts

When running the jsp on a confluence 3.5.x instance that has already run any 3.5.x upgrade tasks, this patch will cause log messages containing messages such as

Constraint cwd_group_name_dir_id already exists

or

Could not create unique constraint ... Duplicate key name 'cwd_group_name_dir_id'

to appear in the logs. These are harmless, and can be safely ignored.