LDAP group can't be added during login due to duplicate key row

Summary

During user login, user data is updated and membership is synchronised with remote LDAP (happens as part of updateGroupsMembershipOnLogin). If any new group is found it will be added to local Jira cache (DB). If new group name differs from existing by:

• a trailing space
• the use of lower and upper cases(e.g. "Group" & "GROUP")
• or have an accented character(e.g. ã, á, ü)
  JIRA tries to create it but fails later due to conflict in DB: Cannot insert duplicate key row.. (see below).

Since updateGroupsMembershipOnLogin failed Jira denies user login.

Environment

• JIRA
• Remote Ldap with 2 groups:
  • 'Test Group ' (with space)
  • 'Test Group'
• User1 belongs to only one of the groups.

Steps to Reproduce

1. Run directory sync and check that one of groups is present
2. Add user to another group (not the one present)
3. Login to JIRA

Expected Results

JIRA should either:

• Create another group (with or without space)
• (Not-advisable) Normalise the groups, detect that groups are the same and skip the adding the group

Actual Results

• User not able to login to JIRA
• Error in the log:

  2017-12-13 01:34:30,983 http-nio-8000-exec-13 ERROR      [o.a.c.c.C.[.[.[/].[jsp.login_jsp]] Servlet.service() for servlet [jsp.login_jsp] in context with path [] threw exception
  com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Group][groupName,... (SQL Exception while executing the following:INSERT INTO dbo.cwd_group (ID, group_name, lower_group_name, active, local, created_date, updated_date, description, lower_description, group_type, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot insert duplicate key row in object 'dbo.cwd_group' with unique index 'uk_group_name_dir_id'. The duplicate key value is (Test Group, 10100).))
  	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:299)
  	at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.createValue(WrappingOfBizDelegator.java:172)
  	at com.atlassian.jira.crowd.embedded.ofbiz.OfBizGroupDao.add(OfBizGroupDao.java:129)
  	at com.atlassian.jira.crowd.embedded.ofbiz.OfBizGroupDao.add(OfBizGroupDao.java:117)
  	at com.atlassian.crowd.directory.AbstractInternalDirectory.addGroup(AbstractInternalDirectory.java:812)
  	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.syncRemoteGroupToInternalDirectory(DbCachingRemoteDirectory.java:497)
  	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.updateGroupsMembershipOnLogin(DbCachingRemoteDirectory.java:449)
  	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.authenticateAndUpdateInternalUser(DbCachingRemoteDirectory.java:328)
  	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.performAuthenticationAndUpdateAttributes(DbCachingRemoteDirectory.java:232)
  	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.authenticate(DbCachingRemoteDirectory.java:204)
  	at com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.authenticateUser(DirectoryManagerGeneric.java:283)
  	at com.atlassian.crowd.manager.application.ApplicationServiceGeneric.authenticateUser(ApplicationServiceGeneric.java:194)
  	at com.atlassian.crowd.embedded.core.CrowdServiceImpl.authenticate(CrowdServiceImpl.java:69)
  	at com.atlassian.crowd.embedded.core.DelegatingCrowdService.authenticate(DelegatingCrowdService.java:37)
  ...
  Caused by: java.sql.SQLException: Cannot insert duplicate key row in object 'dbo.cwd_group' with unique index 'uk_group_name_dir_id'. The duplicate key value is (Test Group, 10100).
  	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
  	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
  	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
  	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
  	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
  	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727)
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
  	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:673)
  

• Always same group and Dir_ID: Test Group, 10100

Notes

• Enabling logging for com.atlassian.crowd.directory.DbCachingRemoteDirectory will show the following attempt to create new group:

  Group Test Group doesn't exist during authentication of user test@local.com, trying to create
  

• Looks like there is misalignment in JIRA code in different abstraction levels:
  • During login JIRA things groups are different
  • Down the stack JIRA treats both groups as the same and that leads to unique index violation.

Workaround

Rename other group to avoid name duplicate