Incremental synchronization is failing when the Confluence is Connected to the Oracle 12c

Summary

Connected Confluence to Oracle DB and LDAP will never be able to run incremental synchronization. Incremental synchronization will fail and revert to full synchronization.

Environment

• Confluence 6.9.0
• Oracle 12c
• OpenLDAP

Steps to Reproduce

1. Install Confluence with connection to Oracle
2. Connect it to the LDAP server
3. Enable option for incremental synchronisation

Expected Results

1. Incremental synchronization will finish as expected.

Actual Results

1. Synchronization will fail and revert to full synchronization:
   The below exception is thrown in the atlassian-confluence.log file:

   2018-05-16 13:17:21,619 ERROR [Caesium-1-2] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache Incremental synchronisation for directory [ XXX ] was unexpectedly interrupted, falling back to a full synchronisation
   com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresherIncSyncException: Cache returned different number of guids and users (possible reason is overlapping guids in cache, most likely null/empty values).
   	at com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresher.validateAndReturnGuidsToAddAndDelete(UsnChangedCacheRefresher.java:283)
   

Notes

User count is same in the LDAP and CWD_USERS, and there are no NULL values in EXTERNAL_ID column.

Enabling the SQL logging will show that during the synchronization bottom query is run, and this query returns no results:

select this_.id as id1_30_0_, this_.user_name as user_name2_30_0_, this_.lower_user_name as lower_user_name3_30_0_, this_.active as active4_30_0_, this_.created_date as created_date5_30_0_, this_.updated_date as updated_date6_30_0_, this_.first_name as first_name7_30_0_, this_.lower_first_name as lower_first_name8_30_0_, this_.last_name as last_name9_30_0_, this_.lower_last_name as lower_last_name10_30_0_, this_.display_name as display_name11_30_0_, this_.lower_display_name as lower_display_nam12_30_0_, this_.email_address as email_address13_30_0_, this_.lower_email_address as lower_email_addre14_30_0_, this_.external_id as external_id15_30_0_, this_.directory_id as directory_id16_30_0_, this_.credential as credential17_30_0_
from cwd_user this_
where this_.directory_id=278855681 and this_.external_id is not null and not (this_.external_id='');

Oracle will change empty string to NULL in query (this_.external_id=NULL) and the statement will return no results.

Workaround

Use -Dcrowd.use.legacy.ad.incremental.sync=true in startup parameters until the bug is fixed.