Details
-
Bug
-
Resolution: Fixed
-
Low
-
6.7.2, 6.10.4, 7.0.2, 7.0.4
-
None
-
25
-
Severity 2 - Major
-
9
-
Description
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.