UD Syncronization fails due to missing group on Confluence Database. Therefore, new Confluence users cannot login.

Steps to Reproduce:

1. User is unable to login on Confluence even though all permissions are properly set.
2. Try to run a User Directory Synchronization

Expected Results:

• The sync occurs properly.

Actual Results:

• The sync fails with missing group on Confluence database:

  2015-07-23 09:22:32.232006500 2015-07-23 09:22:32,227 INFO [TP-Processor17] [embedded.admin.list.DirectoriesController] sync User directory synchronisation requested: [ Crowd Repository ], type: [ CROWD ]
  2015-07-23 09:22:32.697594500 2015-07-23 09:22:32,696 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache INCREMENTAL synchronisation for directory [ 65537 ] starting
  2015-07-23 09:22:32.698531500 2015-07-23 09:22:32,698 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache Attempting INCREMENTAL synchronisation for directory [ 65537 ]
  2015-07-23 09:22:32.698690500 2015-07-23 09:22:32,698 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache Incremental synchronisation for directory [ 65537 ] was not completed, falling back to a full synchronisation
  2015-07-23 09:22:32.698904500 2015-07-23 09:22:32,698 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache INCREMENTAL synchronisation for directory [ 65537 ] was not successful, attempting FULL
  2015-07-23 09:22:32.777615500 2015-07-23 09:22:32,777 INFO [scheduler_Worker-7] [directory.ldap.cache.RemoteDirectoryCacheRefresher] findAllRemoteUsers found [ 19 ] remote users in [ 79 ms ]
  2015-07-23 09:22:32.780902500 2015-07-23 09:22:32,780 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleteCachedUsersNotIn scanned and compared [ 19 ] users for delete in DB cache in [ 3ms ]
  2015-07-23 09:22:32.781103500 2015-07-23 09:22:32,780 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleteCachedUsersNotIn scanned for deleted users in [ 3ms ]
  2015-07-23 09:22:32.782780500 2015-07-23 09:22:32,782 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] getUsersToAddAndUpdate scanning [ 19 ] users to add or update
  2015-07-23 09:22:32.786430500 2015-07-23 09:22:32,782 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] addOrUpdateCachedUsers scanned and compared [ 19 ] users for update in DB cache in [ 1ms ]
  2015-07-23 09:22:32.786432500 2015-07-23 09:22:32,783 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] addOrUpdateCachedUsers synchronised [ 19 ] users in [ 2ms ]
  2015-07-23 09:22:32.852883500 2015-07-23 09:22:32,851 INFO [scheduler_Worker-7] [directory.ldap.cache.RemoteDirectoryCacheRefresher] findAllRemoteGroups found [ 14 ] remote groups in [ 68 ms ]
  2015-07-23 09:22:32.855433500 2015-07-23 09:22:32,854 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleteCachedGroupsNotIn scanned and compared [ 14 ] groups for delete in DB cache in [ 3ms ]
  2015-07-23 09:22:32.855435500 2015-07-23 09:22:32,854 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleteCachedGroups removing [ 1 ] groups
  2015-07-23 09:22:32.860920500 2015-07-23 09:22:32,860 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleteCachedGroups removed [ 1 ] groups successfully in [ 5ms ]
  2015-07-23 09:22:32.865904500 2015-07-23 09:22:32,864 WARN [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions SQL Error: 0, SQLState: 23503
  2015-07-23 09:22:32.865905500 2015-07-23 09:22:32,865 ERROR [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Batch entry 0 delete from cwd_group where id='3702785' was aborted.  Call getNextException to see the cause.
  2015-07-23 09:22:32.866415500 2015-07-23 09:22:32,866 WARN [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions SQL Error: 0, SQLState: 23503
  2015-07-23 09:22:32.866672500 2015-07-23 09:22:32,866 ERROR [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ERROR: update or delete on table "cwd_group" violates foreign key constraint "fk_group_attr_id_group_id" on table "cwd_group_attribute"
  2015-07-23 09:22:32.866674500   Detail: Key (id)=(3702785) is still referenced from table "cwd_group_attribute".
  2015-07-23 09:22:32.867261500 2015-07-23 09:22:32,866 WARN [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions SQL Error: 0, SQLState: 23503
  2015-07-23 09:22:32.867517500 2015-07-23 09:22:32,867 ERROR [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Batch entry 0 delete from cwd_group where id='3702785' was aborted.  Call getNextException to see the cause.
  2015-07-23 09:22:32.868036500 2015-07-23 09:22:32,867 WARN [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions SQL Error: 0, SQLState: 23503
  2015-07-23 09:22:32.868295500 2015-07-23 09:22:32,868 ERROR [scheduler_Worker-7] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ERROR: update or delete on table "cwd_group" violates foreign key constraint "fk_group_attr_id_group_id" on table "cwd_group_attribute"
  2015-07-23 09:22:32.868297500   Detail: Key (id)=(3702785) is still referenced from table "cwd_group_attribute".
  2015-07-23 09:22:32.868865500 2015-07-23 09:22:32,868 ERROR [scheduler_Worker-7] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
  2015-07-23 09:22:32.869470500 2015-07-23 09:22:32,869 INFO [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache failed synchronisation complete for directory [ 65537 ] in [ 173ms ]
  2015-07-23 09:22:32.873728500 2015-07-23 09:22:32,872 ERROR [scheduler_Worker-7] [atlassian.crowd.directory.DbCachingDirectoryPoller] pollChanges Error occurred while refreshing the cache for directory [ 65537 ].
  2015-07-23 09:22:32.873730500 org.springframework.dao.DataIntegrityViolationException: Hibernate operation: Could not execute JDBC batch update; SQL []; Batch entry 0 delete from cwd_group where id='3702785' was aborted.  Call getNextException to see the cause.; nested exception is java.sql.BatchUpdateException: Batch entry 0 delete from cwd_group where id='3702785' was aborted.  Call getNextException to see the cause.
  2015-07-23 09:22:32.873750500 	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
  2015-07-23 09:22:32.873756500 	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  2015-07-23 09:22:32.873757500 	at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
  2015-07-23 09:22:32.873758500 	at org.springframework.orm.hibernate.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:605)
  2015-07-23 09:22:32.873763500 	at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:518)
  2015-07-23 09:22:32.873764500 	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
  2015-07-23 09:22:32.873765500 	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
  2015-07-23 09:22:32.873867500 	at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)
  2015-07-23 09:22:32.873867500 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  2015-07-23 09:22:32.873868500 	at java.lang.reflect.Method.invoke(Method.java:483)
  2015-07-23 09:22:32.873869500 	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
  2015-07-23 09:22:32.873892500 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
  2015-07-23 09:22:32.873893500 	at com.sun.proxy.$Proxy14.commit(Unknown Source)
  2015-07-23 09:22:32.873893500 	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:321)
  2015-07-23 09:22:32.873898500 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
  2015-07-23 09:22:32.873899500 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  2015-07-23 09:22:32.873900500 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  2015-07-23 09:22:32.873903500 	at com.atlassian.crowd.directory.$Proxy3471.deleteCachedGroupsNotIn(Unknown Source)
  2015-07-23 09:22:32.873904500 	at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.deleteCachedGroupsNotIn(DirectoryCacheImplUsingChangeOperations.java:102)
  2015-07-23 09:22:32.873905500 	at com.atlassian.crowd.directory.ldap.cache.RemoteDirectoryCacheRefresher.synchroniseAllGroups(RemoteDirectoryCacheRefresher.java:113)
  2015-07-23 09:22:32.873911500 	at com.atlassian.crowd.directory.ldap.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:91)
  2015-07-23 09:22:32.873911500 	at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseAll(EventTokenChangedCacheRefresher.java:67)
  2015-07-23 09:22:32.873912500 	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1122)
  2015-07-23 09:22:32.873917500 	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:76)
  2015-07-23 09:22:32.873918500 	at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:50)
  2015-07-23 09:22:32.873919500 	at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:93)
  2015-07-23 09:22:32.873925500 	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
  2015-07-23 09:22:32.873925500 	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
  2015-07-23 09:22:32.873926500 	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
  2015-07-23 09:22:32.873927500 	at com.atlassian.scheduler.quartz1.Quartz1Job.execute(Quartz1Job.java:32)
  2015-07-23 09:22:32.873932500 	at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
  2015-07-23 09:22:32.873932500 	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$467(ConfluenceQuartzThreadPool.java:19)
  2015-07-23 09:22:32.873933500 	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool$$Lambda$91/13922641.run(Unknown Source)
  2015-07-23 09:22:32.873934500 	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
  2015-07-23 09:22:32.873973500 Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from cwd_group where id='3702785' was aborted.  Call getNextException to see the cause.
  2015-07-23 09:22:32.873974500 	at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2586)
  2015-07-23 09:22:32.873975500 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1811)
  2015-07-23 09:22:32.873994500 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  2015-07-23 09:22:32.873995500 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
  2015-07-23 09:22:32.873996500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.873997500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.874003500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.874004500 	at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
  2015-07-23 09:22:32.874004500 	at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
  2015-07-23 09:22:32.874005500 	at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2465)
  2015-07-23 09:22:32.874009500 	at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2424)
  2015-07-23 09:22:32.874009500 	at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
  2015-07-23 09:22:32.874010500 	at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
  2015-07-23 09:22:32.874011500 	at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510)
  2015-07-23 09:22:32.874013500 	... 29 more
  2015-07-23 09:22:32.874013500 caused by:
  2015-07-23 09:22:32.874013500 org.postgresql.util.PSQLException: ERROR: update or delete on table "cwd_group" violates foreign key constraint "fk_group_attr_id_group_id" on table "cwd_group_attribute"
  2015-07-23 09:22:32.874015500   Detail: Key (id)=(3702785) is still referenced from table "cwd_group_attribute".
  2015-07-23 09:22:32.874027500 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
  2015-07-23 09:22:32.874028500 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
  2015-07-23 09:22:32.874028500 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  2015-07-23 09:22:32.874029500 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
  2015-07-23 09:22:32.874033500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.874034500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.874035500 	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
  2015-07-23 09:22:32.874035500 	at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
  2015-07-23 09:22:32.874042500 	at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
  2015-07-23 09:22:32.874043500 	at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2465)
  2015-07-23 09:22:32.874043500 	at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2424)
  2015-07-23 09:22:32.874044500 	at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
  2015-07-23 09:22:32.874044500 	at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
  2015-07-23 09:22:32.874051500 	at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510)
  2015-07-23 09:22:32.874052500 	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
  2015-07-23 09:22:32.874053500 	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
  2015-07-23 09:22:32.874080500 	at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)
  2015-07-23 09:22:32.874081500 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  2015-07-23 09:22:32.874081500 	at java.lang.reflect.Method.invoke(Method.java:483)
  2015-07-23 09:22:32.874082500 	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
  2015-07-23 09:22:32.874099500 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
  2015-07-23 09:22:32.874100500 	at com.sun.proxy.$Proxy14.commit(Unknown Source)
  2015-07-23 09:22:32.874100500 	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:321)
  2015-07-23 09:22:32.874101500 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
  2015-07-23 09:22:32.874113500 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  2015-07-23 09:22:32.874114500 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  2015-07-23 09:22:32.874115500 	at com.atlassian.crowd.directory.$Proxy3471.deleteCachedGroupsNotIn(Unknown Source)
  2015-07-23 09:22:32.874125500 	at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.deleteCachedGroupsNotIn(DirectoryCacheImplUsingChangeOperations.java:102)
  2015-07-23 09:22:32.874126500 	at com.atlassian.crowd.directory.ldap.cache.RemoteDirectoryCacheRefresher.synchroniseAllGroups(RemoteDirectoryCacheRefresher.java:113)
  2015-07-23 09:22:32.874127500 	at com.atlassian.crowd.directory.ldap.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:91)
  2015-07-23 09:22:32.874144500 	at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseAll(EventTokenChangedCacheRefresher.java:67)
  2015-07-23 09:22:32.874145500 	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1122)
  2015-07-23 09:22:32.874146500 	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:76)
  2015-07-23 09:22:32.874168500 	at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:50)
  2015-07-23 09:22:32.874169500 	at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:93)
  2015-07-23 09:22:32.874176500 	at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
  2015-07-23 09:22:32.874177500 	at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
  2015-07-23 09:22:32.874177500 	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
  2015-07-23 09:22:32.874178500 	at com.atlassian.scheduler.quartz1.Quartz1Job.execute(Quartz1Job.java:32)
  2015-07-23 09:22:32.874196500 	at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
  2015-07-23 09:22:32.874197500 	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$467(ConfluenceQuartzThreadPool.java:19)
  2015-07-23 09:22:32.874198500 	at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool$$Lambda$91/13922641.run(Unknown Source)
  2015-07-23 09:22:32.874198500 	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)

Workaround:

1. Check on Confluence database if this group exists in there. E.g.:

   select * from cwd_group where id = 3702785;

   This will display the group name.

2. Now, check if the same is on crowd database (same SQL above).
   1. If it does not exist on crowd, just visit the instance > go to User Management > Groups > Add and then remove the same group.
   2. Just ran a UD Sync and the problem will be fixed.

For Cloud users, please raise a ticket on Atlassian Support so we can check all that.

Notes:
This seems that for some reason, Confluence database is not updated with crowd. Usually this missing groups are related to JSD (even when customer does not have it enabled).