[BAM-20735] Unique constraint violation while syncing an external user repository

Type: Bug
Resolution: Fixed
Priority: Low
Fix Version/s: 7.2.2
Affects Version/s: 6.7.2, 6.10.4, 7.0.2, 7.0.4
Component/s: User Management, Crowd & LDAP integration
Labels:
None

Support reference count:
25
Symptom Severity:
Severity 2 - Major
UIS:
9
Bug Fix Policy:
View Atlassian Server bug fix policy

Issue Summary

Unique constraint violation o table auth_attempt_info when synchronizing an external user repository if there are failed prior attempts of login

Steps to Reproduce

On Bamboo, try to login with the user "Foo" using a wrong password (the user doesn't necessarily need to exist).
Try to log in again with the user "foo" (respect the lower "F"), again, using a wrong password.
On crowd, create a user called "Foo".
On Bamboo, go to Cog>Overview>User Directories and force a synchronization.
Back on Crowd, Rename the user "Foo" to "foo".
Force a synchronization in Bamboo again.

Expected Results

The synchronization will be executed without any errors

Actual Results

When Bamboo updates the entries on the auth_attempt_info table, it will try to update both entries added on steps 1 and 2 to use the same name from step 5, violating the unique constraint.
The synchronization fails and the following is thrown in the logs:

2019-11-28 19:04:19,601 ERROR [atlassian-scheduler-quartz2.local_Worker-4] [DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 72056833 ].
org.hibernate.exception.GenericJDBCException: could not extract ResultSet
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
	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.extract(ResultSetReturnImpl.java:69)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
	at org.hibernate.loader.Loader.doQuery(Loader.java:937)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
	at org.hibernate.loader.Loader.doList(Loader.java:2689)
	at org.hibernate.loader.Loader.doList(Loader.java:2672)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
	at org.hibernate.loader.Loader.list(Loader.java:2501)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1959)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370)
	at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:392)
	at com.atlassian.crowd.dao.user.UserDAOHibernate.findByNameInternal(UserDAOHibernate.java:485)
	at com.atlassian.crowd.dao.user.UserDAOHibernate.findByName(UserDAOHibernate.java:174)
	at com.atlassian.crowd.dao.user.UserDAOHibernate.update(UserDAOHibernate.java:398)
	at com.atlassian.crowd.dao.user.UserDAOHibernate.update(UserDAOHibernate.java:59)
	at com.atlassian.crowd.directory.CachingDirectory.updateUser(CachingDirectory.java:144)
	at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.updateUsers(DbCachingRemoteChangeOperations.java:287)
	at sun.reflect.GeneratedMethodAccessor2360.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy1668.updateUsers(Unknown Source)
	at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.addOrUpdateCachedUsers(DirectoryCacheImplUsingChangeOperations.java:57)
	at com.atlassian.crowd.directory.ldap.cache.RemoteDirectoryCacheRefresher.synchroniseAllUsers(RemoteDirectoryCacheRefresher.java:95)
	at com.atlassian.crowd.directory.synchronisation.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:45)
	at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseAll(EventTokenChangedCacheRefresher.java:62)
	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1039)
	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.lambda$synchronise$0(DirectorySynchroniserImpl.java:80)
	at com.atlassian.crowd.audit.NoOpAuditLogContext.withAuditLogSource(NoOpAuditLogContext.java:17)
	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:78)
	at sun.reflect.GeneratedMethodAccessor1077.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy168.synchronise(Unknown Source)
	at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:45)
	at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:85)
	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
	at com.atlassian.scheduler.quartz2.Quartz2Job.execute(Quartz2Job.java:32)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
	... 57 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_bm0lb4ya9whq89box05jyqjo1"
  Detail: Key (user_name)=(gerhard) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:462)
	at com.atlassian.bamboo.user.rename.UserRenameHelper.updateUserInTable(UserRenameHelper.java:38)
	at com.atlassian.bamboo.user.rename.UserRenameHelper.updateUserInTable(UserRenameHelper.java:23)
	at com.atlassian.bamboo.user.rename.UserRenameHelper.renameUserInAuthAttemptInfo(UserRenameHelper.java:60)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl.doRenameUser(UserRenameServiceImpl.java:174)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl.access$200(UserRenameServiceImpl.java:37)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl$1.lambda$null$0(UserRenameServiceImpl.java:127)
	at org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:37)
	at org.hibernate.internal.SessionImpl$1.accept(SessionImpl.java:2389)
	at org.hibernate.internal.SessionImpl$1.accept(SessionImpl.java:2386)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:320)
	at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2408)
	at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2393)
	at sun.reflect.GeneratedMethodAccessor2361.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.orm.hibernate5.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1226)
	at com.sun.proxy.$Proxy319.doWork(Unknown Source)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl$1.lambda$doInTransactionWithoutResult$1(UserRenameServiceImpl.java:119)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:383)
	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:335)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl$1.doInTransactionWithoutResult(UserRenameServiceImpl.java:118)
	at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:36)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl.lambda$renameUser$0(UserRenameServiceImpl.java:115)
	at com.atlassian.bamboo.core.ScopedExclusionServiceImpl.withLock(ScopedExclusionServiceImpl.java:63)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl.renameUser(UserRenameServiceImpl.java:102)
	at com.atlassian.bamboo.user.rename.UserRenameServiceImpl.onUserRenamedEvent(UserRenameServiceImpl.java:162)
	at sun.reflect.GeneratedMethodAccessor2369.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:36)
	at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$1$1.run(AsynchronousAbleEventDispatcher.java:46)
	at com.google.common.util.concurrent.MoreExecutors$DirectExecutorService.execute(MoreExecutors.java:299)
	at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.dispatch(AsynchronousAbleEventDispatcher.java:105)
	at com.atlassian.bamboo.event.spi.BambooEventDispatcher.dispatch(BambooEventDispatcher.java:34)
	at com.atlassian.event.internal.LockFreeEventPublisher$Publisher.dispatch(LockFreeEventPublisher.java:260)
	at com.atlassian.event.internal.LockFreeEventPublisher.publish(LockFreeEventPublisher.java:102)
	at com.atlassian.bamboo.event.TxAwareEventPublisher.publish(TxAwareEventPublisher.java:20)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at com.atlassian.crowd.core.event.DelegatingMultiEventPublisher.publishAll(DelegatingMultiEventPublisher.java:19)
	at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.publishEvents(DbCachingRemoteChangeOperations.java:880)
	at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.publishEvent(DbCachingRemoteChangeOperations.java:874)
	at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.updateUsers(DbCachingRemoteChangeOperations.java:277)
	... 38 more

Or:

...
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "auth_attempt_info_user_name_key"
  Detail: Key (user_name)=(user) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
        at com.atlassian.bamboo.user.rename.UserRenameHelper.updateUserInTable(UserRenameHelper.java:26)
        at com.atlassian.bamboo.user.rename.UserRenameHelper.updateUserInTable(UserRenameHelper.java:18)
        at com.atlassian.bamboo.user.rename.UserRenameHelper.renameUserInAuthAttemptInfo(UserRenameHelper.java:48)
...

Workaround

As the auth_attempt_info only stores the failed login attempts, it's safe to delete its content directly from the database:

Stop Bamboo
Backup Bamboo database
Run the following
```
delete from auth_attempt_info;
```
Start Bamboo Back.
Try to run the synchronization again.

Mentioned in

Bamboo KTLO: BAM-20735: Unique constraint violation while syncing an external user repository

Alexey Chystoprudov made changes - 16/Oct/2024 8:19 AM

Fix Version/s

Original: 7.1.5 [ 93604 ]

Alexey Chystoprudov made changes - 16/Oct/2024 8:18 AM

Fix Version/s

Original: 7.0.7 [ 93605 ]

Marcin Walerianczyk made changes - 22/Jan/2021 3:41 PM

Resolution		New: Fixed [ 1 ]
Status	Original: Waiting for Release [ 12075 ]	New: Closed [ 6 ]

SET Analytics Bot made changes - 19/Jan/2021 12:36 AM

UIS

Original: 10

New: 9

Bugfix Automation Bot made changes - 12/Jan/2021 7:30 PM

Support reference count

Original: 24

New: 25

SET Analytics Bot made changes - 01/Jan/2021 12:32 AM

UIS

Original: 9

New: 10

SET Analytics Bot made changes - 19/Dec/2020 12:34 AM

UIS

Original: 7

New: 9

Bugfix Automation Bot made changes - 18/Dec/2020 1:30 AM

Support reference count

Original: 23

New: 24

SET Analytics Bot made changes - 17/Dec/2020 12:41 AM

UIS

Original: 8

New: 7

SET Analytics Bot made changes - 16/Dec/2020 12:40 AM

UIS

Original: 6

New: 8

Assignee:: Marcin Walerianczyk

Reporter:: Gabriel Ribeiro

Affected customers:: 6 This affects my team

Watchers:: 17 Start watching this issue

Created:: 28/Nov/2019 7:21 PM

Updated:: 16/Oct/2024 8:19 AM

Resolved:: 22/Jan/2021 3:41 PM

Details

Description

Issue Summary

Steps to Reproduce

Expected Results

Actual Results

Workaround

Attachments

Issue Links

Forms

Activity

People

Dates