Bamboo upgrade task 60803 fails with duplicate entry for key 'cde_entity_unq'
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Bamboo upgrade to version 6.8 or higher fails with a violation constraint during upgrade task 60803 and the following errors can be found inside the <Bamboo server home directory>/logs/atlassian-bamboo.log files:
2021-07-06 05:01:06,921 INFO [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [AbstractUpgradeManager] ----------------------------------------------------------------------------------------------------------
2021-07-06 05:01:06,937 INFO [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [AbstractUpgradeManager] 60803 : Scheduling an initial cleanup of obsolete data stored for deleted users and group (post-bootstrap)
2021-07-06 05:01:06,937 INFO [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [AbstractUpgradeManager] ----------------------------------------------------------------------------------------------------------
2021-07-06 05:01:06,937 INFO [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData] Copying all Sids from ACL table to the Crowd deleted entities table
2021-07-06 05:01:07,101 INFO [active-objects-init-compatibility-tenant-0] [ActiveObjectUpgradeManagerImpl] Starting upgrade of data model, current version is 1
2021-07-06 05:01:07,117 INFO [active-objects-init-compatibility-tenant-0] [ActiveObjectUpgradeManagerImpl] Finished upgrading, model is up to date at version 1
2021-07-06 05:01:07,148 WARN [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [SqlExceptionHelper] SQL Error: 1062, SQLState: 23000
2021-07-06 05:01:07,148 ERROR [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [SqlExceptionHelper] Duplicate entry 'GROUP_PRINCIPAL-bamboo-users' for key 'cde_entity_unq'
2021-07-06 05:01:07,179 ERROR [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [AbstractUpgradeManager] org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [cde_entity_unq]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [cde_entity_unq]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:247)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:387)
at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:350)
at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate$1.doInTransaction(BambooTransactionHibernateTemplate.java:36)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:28)
at com.atlassian.bamboo.persistence.BambooTransactionHibernateTemplate.execute(BambooTransactionHibernateTemplate.java:33)
at com.atlassian.bamboo.upgrade.tasks.v6_8.UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.runStatement(UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.java:79)
at com.atlassian.bamboo.upgrade.tasks.v6_8.UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.doUpgrade(UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.java:62)
at com.atlassian.bamboo.upgrade.AbstractUpgradeManager.runUpgradeTask(AbstractUpgradeManager.java:182)
at com.atlassian.bamboo.upgrade.UpgradeManagerImpl.doUpgrade(UpgradeManagerImpl.java:133)
at com.atlassian.bamboo.upgrade.UpgradeLauncher.lambda$upgradeAndStartBamboo$0(UpgradeLauncher.java:115)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57)
at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at com.atlassian.bamboo.utils.BambooRunnables$1.run(BambooRunnables.java:48)
at com.atlassian.bamboo.security.ImpersonationHelper.runWith(ImpersonationHelper.java:26)
at com.atlassian.bamboo.security.ImpersonationHelper.runWithSystemAuthority(ImpersonationHelper.java:17)
at com.atlassian.bamboo.security.ImpersonationHelper$1.run(ImpersonationHelper.java:41)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:107)
at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1593)
at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:292)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594)
at com.atlassian.bamboo.upgrade.tasks.v6_8.UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.lambda$runStatement$0(UpgradeTask60803ScheduleInitialCleanupOfObsoleteUserData.java:83)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:384)
... 22 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'GROUP_PRINCIPAL-bamboo-users' for key 'cde_entity_unq'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:462)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 28 more
2021-07-06 05:01:07,195 INFO [17-UpgradeTaskBackgroundThread:pool-38-thread-1] [AbstractUpgradeManager] Task 60803 completed successfully.
2021-07-06 05:01:07,195 FATAL [localhost-startStop-1] [UpgradeLauncher] Upgrade task error: Task for build 60803 failed with exception: could not execute statement; SQL [n/a]; constraint [cde_entity_unq]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statementEnvironment
Bamboo version < 6.8 attempting to upgrade to version 6.8 or higher.
Diagnosis
Use the following select query to get a list of users, groups and roles that were assigned permissions in Bamboo:
select min(id), type, sid from acl_entry group by type, sid;Look for the presence of duplicate entries when combining type and sid. These are the following types you'll find in the results:
- GRANTED_AUTHORITY means permissions were assigned to a role (e.g. logged in users).
- GROUP_PRINCIPAL means permissions were assigned to a specific group (e.g. bamboo-admin).
- PRINCIPAL means permissions were assigned to a specific user (e.g. admin).
The results must have a unique combination of type and sid otherwise there will be a constraint violation during upgrade task 60803. Here are a couple of examples:
Scenario 1 
select min(id), type, sid from acl_entry group by type, sid;| min | type | sid |
|---|---|---|
| 131078 | GROUP_PRINCIPAL | bamboo-users |
| 557065 | GROUP_PRINCIPAL | BAMBOO-USERS |
| 131073 | PRINCIPAL | admin |
This table is showing us that permissions were assigned to three entities inside Bamboo 1) a group named bamboo-users, 2) another group named BAMBOO-USERS and 3) a user named admin. This will end up in a violation constraint during upgrade task 60803 because you have two groups with the same name (only different casing). The combination of type and sid will be the same except for the case difference.
Scenario 2 
select min(id), type, sid from acl_entry group by type, sid;| min | type | sid |
|---|---|---|
| 131077 | GROUP_PRINCIPAL | testing |
| 557064 | PRINCIPAL | testing |
| 557057 | PRINCIPAL | admin |
This table on the other hand is showing us that permissions were assigned to 1) a group named testing, 2) a user named testing and 3) a user named admin. This will NOT create problems during the upgrade because we still have a unique combination of type and sid in the results even though there's a user and a group both named testing.
Cause
As part of the upgrade task 60803 Bamboo is copying data from the acl_entry table to the newly created crowd_deleted_entity table in order to perform some cleanup that became required after the introduction of Embedded Crowd in Bamboo 6.6. This table is created during the upgrade process and has a unique constraint (named cde_entity_unq) made of the columns entity_type and entity_name. So Bamboo attempts to perform the following during the upgrade process:
| ACL_ENTRY | COPY | CROWD_DELETED_ENTITY |
|---|---|---|
| type | → | entity_type |
| sid | → | entity_name |
If you happen to have synchronized groups with the same name but different casing to Bamboo this will almost certainly cause problems when Bamboo tries to copy the data from acl_entry into crowd_deleted_entity. In other words, you'll run into the situation described in Scenario 1.
In previous versions of Bamboo you couldn't create groups with uppercase letters when working with local users and groups – which would be enough to avoid this issue altogether – so if you get results similar to Scenario 1 it's likely that your Bamboo instance is/ was connected to Jira/ Crowd or LDAP for user management at some point in time. Bamboo wasn't checking case sensitivity when synchronizing users and groups from external user directories so this is the most reasonable explanation as to why you're facing this problem.
Solution
The solution in this case is to ensure that you don't have any duplicates inside the acl_entry table. We won't be doing that by removing data because that means you'll be removing/ revoking permissions from certain users and groups but updating the existing entries to get rid of the case difference (bamboo-users vs BAMBOO-USERS).
It is important to note that the acl_entry table is not where groups are stored. We are not changing the name of the user or group themselves but the name of the user or group that the permission was assigned to. For Bamboo versions < 6.6 local groups are stored inside the database in the groups table and external groups (e.g. coming from Jira/ Crowd or LDAP) were kept in cache (memory). For Bamboo versions > 6.6 groups both local and external groups are stored inside the cwd_group table. In any case, updating permissions inside the acl_entry table will not affect the synchronization to Jira/ Crowd or LDAP.
If you haven't done so already you must start by trying to identify the duplicate entries by running the same select query performed by upgrade task 60803:
select min(id), type, sid from acl_entry group by type, sid;Please take a full Bamboo backup (database + <Bamboo server home directory>) before making any changes to the database.
Once you have been able to identify the duplicate entries you can then update one of them to make sure there are no more users or groups with the same name and different casing. For instance, if you want to ensure that all of the entries inside the acl_entry table have sid bamboo-users rather than BAMBOO-USERS you can run the following update:
| min | type | sid |
|---|---|---|
| 131078 | GROUP_PRINCIPAL | bamboo-users |
| 557065 | GROUP_PRINCIPAL | BAMBOO-USERS |
| 131073 | PRINCIPAL | admin |
update acl_entry set sid = 'bamboo-users' where sid = 'BAMBOO-USERS';In practice this shouldn't really change permissions because Bamboo sees those groups as one – i.e. users should not lose access to anything – but it will be enough to allow the Bamboo upgrade process to continue.