Unable to Manage Page Restrictions
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Symptoms
When using SQL Server or Oracle, user lookup hangs when attempting to restrict pages. The following lines appear in the atlassian-confluence.log:
1
2
ERROR [http-9090-4] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Violation of UNIQUE KEY constraint 'cp_unique_group'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'.
ERROR [http-9090-4] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
Diagnosis
Use your database administration tools and check for key contraints on the CONTENT_PERM table, if you have key contraints named 'cp_unique_user' or 'cp_unique_group' on these tables then you have this issue.
Cause
This issue is the result of some key contraints on the CONTENT_PERM table that were not removed during the upgrade process to Confluence 4.0 but should have been.
Solution
Workaround for Confluence 4.0.x and 4.1.x
For SQL Server and Oracle:
Use your database administration tools to remove the key constraints 'cp_unique_user' and 'cp_unique_group' on table CONTENT_PERM.
Execute the following to add the constraint 'cp_unique_user_groups' (if it doesn't already exist):
alter table CONTENT_PERM add constraint cp_unique_user_groups unique (CPS_ID, CP_TYPE, USERNAME, GROUPNAME);
⚠️ Do not do this if you are using PostgreSQL or MySQL. These databases require the constraints 'cp_unique_user' and 'cp_unique_group'. If you experience any similar issues with these databases, raise a support request instead.
Resolution
Upgrade to Confluence 4.2 or later. The upgrade will reapply the correct constraints to the CONTENT_PERM table in your database. Note that the correct constraints differ between different database servers.
⚠️ If you experience that after upgrading to 4.2.x you still unable to manage page restrictions and in your atlassian_confluence.log you find error messages while trying to do so like the following, please try to apply the steps below as a workaround.
1
2
3
javax.servlet.error.exception: org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.security.ContentPermission#11337731]; SQL [
]; Violation of UNIQUE KEY constraint 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'.; nested exception is java.sql.SQLException: Violation of UNIQUE KEY constra
int 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'.As a workaround, follow these steps:
Remove all restrictions from the problematic page/space.
Re-apply all permissions.
In some cases, there seems to be a problem while upgrading when you previously faced problems with managing page restrictions, however the steps above seem to fix the issue.
Was this helpful?