Issue Summary

During user directory sync between Crowd 3.7.0 and Jira, where both were running with MySQL database, queries triggered by the directory FULL Sync against table cwd_membership were doing full scan. This table has around 3.5 million rows for the affected customer.

Steps to Reproduce

1. Perform FULL synchronisation for directory:
   Caesium-2-1 INFO [atlassian.crowd.directory.DbCachingRemoteDirectory] FULL synchronisation for directory [ 66650113 ] starting
2. It fails to complete:
   Caesium-2-1 INFO [atlassian.crowd.directory.DbCachingRemoteDirectory] failed synchronisation complete for directory [ 66650113 ] in [ 9503296ms ]

Expected Results

The FULL sync completes successfully.

Actual Results

The below exception is thrown in the atlassian-crowd.log file:

2019-10-31 13:56:40,550 Caesium-2-4 INFO [atlassian.crowd.directory.DbCachingRemoteDirectory] FULL synchronisation for directory [ 66650113 ] starting
First time ERROR's seen
2019-10-31 15:25:25,457 Caesium-2-4 ERROR [hibernate.batch.hibernate5.Hibernate5BatchProcessor] Could not process class com.atlassian.crowd.util.persistence.hibernate.batch.TransactionGroup: com.a
tlassian.crowd.util.persistence.hibernate.batch.TransactionGroup@126b66d3[primaryObject=com.atlassian.crowd.model.group.InternalGroup@7ddc47a8[id=69033307,name=DL-EXAMPLE-1,ty
pe=GROUP,active=true,description=*** Deleted Mail DL 12/19/2018 per TASK1666016 by P2184675adm ***,lowerName=dl-example-1,createdDate=2019-10-04 23:11:05.0,updatedDate=2019-10
-04 23:11:05.0,directoryId=66650113,externalId=b147b4b93a0bde4289a6b4f951f3baf3]]
2019-10-31 15:25:39,290 Caesium-2-4 ERROR [hibernate.batch.hibernate5.Hibernate5BatchProcessor] Could not process class com.atlassian.crowd.util.persistence.hibernate.batch.TransactionGroup: com.a
tlassian.crowd.util.persistence.hibernate.batch.TransactionGroup@93b6be5[primaryObject=com.atlassian.crowd.model.group.InternalGroup@6864899a[id=69013590,name=DL-EXAMPLE-2
,type=GROUP,active=true,description=*** Deleted Mail DL 09/04/2018 per TASK1413175 by p2187871ADM ***,lowerName=dl-example-2,createdDate=2019-10-04 23:11:05.0,updatedDate=
2019-10-04 23:11:05.0,directoryId=66650113,externalId=a1eed53126adda4cbe63b61c0580411e]]
2019-10-31 16:39:14,030 Caesium-2-4 INFO [atlassian.crowd.directory.DbCachingRemoteDirectory] failed synchronisation complete for directory [ 66650113 ] in [ 9753486ms ]

Workaround

• Query 1 doing full table scan in Jira MySQL Database.

  explain select * FROM jiradb.cwd_membership WHERE membership_type='GROUP_USER' AND child_name='P2895647' AND directory_id=10600 ;
  --------------------------------------------------------------------------------+
  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
  --------------------------------------------------------------------------------+
  1	SIMPLE	cwd_membership	ALL	NULL	NULL	NULL	NULL	14310854	Using where
  --------------------------------------------------------------------------------+
  

• Adding the index below fix this.

  CREATE INDEX idx_upperchild_mem_dir USING BTREE ON jiradb.cwd_membership (child_name,membership_type,directory_id);
  

• Query 2 also doing full table scans.

  mysql> explain select * FROM jiradb.cwd_membership WHERE parent_name='dl-example' AND directory_id=10600;
  --------------------------------------------------------------------------------+
  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
  --------------------------------------------------------------------------------+
  1	SIMPLE	cwd_membership	ALL	NULL	NULL	NULL	NULL	14387270	Using where
  --------------------------------------------------------------------------------+
  

• Adding the second index below fix this.

  CREATE INDEX idx_upperparent_dir USING BTREE ON jiradb.cwd_membership (parent_name,directory_id);
  

Please note: This issue can also happen with the Embedded Crowd for Jira. The above workaround is also valid for this case.