Unable to Manage Page Restrictions
When using SQL Server or Oracle, user lookup hangs when attempting to restrict pages. The following lines appear in the
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
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.
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.
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.
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.
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:
- Rebuild your indices from scratch.
- 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.