Using MySQL, if the group name in LDAP differs from what's in Crowd by a trailing space, the next directory sync will delete the group in Crowd

Steps to Reproduce

1. In the LDAP server (I used Active Directory), create a test group
2. Install Crowd with MySQL database
3. Inside Crowd, configure a Connector type directory to the LDAP server and sync. Verify that the test group is correctly synced over
4. On the LDAP server, rename the group such that it contains a trailing space
5. Inside Crowd, sync the directory again

Expected behavior

The group name has technically changed. The original group should be removed from Crowd, and the new group with the trailing space should be added. (Whether this is the desired behavior is another conversation... this is simply what the expected behavior should be.)

Actual behavior

The original group is removed from Crowd, but the new group is not added.

Analysis

1. Crowd finds the "new" group (with the trailing space) and tries to add it to the DB cache

   2015-03-13 17:57:15,615 scheduler_Worker-9 INFO [directory.ldap.cache.RemoteDirectoryCacheRefresher] found [ 1 ] remote groups in [ 65 ms ]
   2015-03-13 17:57:15,615 scheduler_Worker-9 INFO [atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] scanning [ 1 ] groups to add or update
   2015-03-13 17:57:15,617 scheduler_Worker-9 INFO [atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanned and compared [ 1 ] groups for update in DB cache in [ 1ms ]
   

2. MySQL fails to insert this row because it sees it as a duplicate group, therefore violating unique constraint uk_group_name_dir_id.

   2015-03-13 17:57:15,623 scheduler_Worker-9 WARN [engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1062, SQLState: 23000
   2015-03-13 17:57:15,623 scheduler_Worker-9 ERROR [engine.jdbc.spi.SqlExceptionHelper] Duplicate entry 'examplegroup -32771' for key 'uk_group_name_dir_id'
   2015-03-13 17:57:15,623 scheduler_Worker-9 ERROR [jdbc.batch.internal.BatchingBatch] HHH000315: Exception executing batch [could not perform addBatch]
   2015-03-13 17:57:15,624 scheduler_Worker-9 WARN [persistence.hibernate.batch.AbstractBatchProcessor] batch failed falling back to individual processing
   org.hibernate.exception.ConstraintViolationException: could not perform addBatch
   ce.hibernate.batch.hibernate4.Hibernate4BatchProcessor.flushSession(Hibernate4BatchProcessor.java:144)
   ...
   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'examplegroup -32771' for key 'uk_group_name_dir_id'
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
   	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
   	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
   ...
   2015-03-13 17:57:15,632 scheduler_Worker-9 WARN [atlassian.crowd.directory.DbCachingRemoteChangeOperations] Could not add the following entity to the directory [ Crowd Internal Directory ]: examplegroup 
   

3. Crowd log this group-add operation as a success anyway

   2015-03-13 17:57:15,632 scheduler_Worker-9 INFO [atlassian.crowd.directory.DbCachingRemoteChangeOperations] added [ 1 ] groups in [ 13ms ]
   2015-03-13 17:57:15,633 scheduler_Worker-9 INFO [atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] synchronized [ 1 ] groups in [ 18ms ]
   

4. Crowd then removes the original group (without the trailing space)

   2015-03-13 17:57:15,636 scheduler_Worker-9 INFO [atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanned and compared [ 1 ] groups for delete in DB cache in [ 3ms ]
   2015-03-13 17:57:15,636 scheduler_Worker-9 INFO [atlassian.crowd.directory.DbCachingRemoteChangeOperations] removing [ 1 ] groups
   2015-03-13 17:57:15,644 scheduler_Worker-9 INFO [atlassian.crowd.directory.DbCachingRemoteChangeOperations] removed [ 1 ] groups in [ 8ms ]
   

5. The result: sync completes, no new group was added, and the original group was removed.

Other notes

• This appears to affect MySQL only and is likely specific to the way MySQL interprets trailing spaces on INSERT operations on VARCHAR column types.
• This bug is a bit of an edge case since it requires the group to change in LDAP (where the trailing space is added, likely by error), and the Crowd must be running on MySQL
• This bug is likely to affect JIRA, Confluence, Stash, and any other system using Crowd libraries to sync with LDAP.

Workaround

Sync the directory again (manually, or by waiting for the next automatic sync). The new group (with the trailing space) will then be added into Crowd.