Favourite filters REST Resource is needlessly retrieving associated share users

Summary

This is an edge case of JRASERVER-38147 and JRASERVER-44236.
Thanks to the fix for JRASERVER-44236, the performance problem seemed to be solved in case that the filter permission was global.
However, this problem could still happen in case that the filter permission was not global (e.g. the filter was limited to a specific project) and tons of users were associated with the project.

Environment

• JIRA Server 7.4.3
• MySQL (any database is possible)
• OpenLDAP (any directory service is possible)

Steps to Reproduce

1. Prepare a fresh installed JIRA 7.4.3 + MySQL
2. Integrate JIRA with any external directory and synchronize users and groups
   • In the external directory,
     • there are some users like 'testuser1', 'testuser2'...
     • there is one group 'testgroup' and the above users belong to the group
   • Please make sure that JIRA internal directory is on top of the order
3. Make sure that the user who is reproducing this bug has Browse Users global permission
4. Create a project on JIRA
5. Go to Project management > Permission and give Browse project permission to 'testgroup'
   • In case Any logged in user has the permission, please revoke the permission and assigned it to the user who is reproducing this bug as well
6. Create some filters and share them with the project which you created in the step 4
7. Register the filters as your favorite ones
8. Open Issue Navigator

Expected Results

No performance problem happens.

Actual Results

The following SQL were executed once for each favorite filter (like /rest/api/2/filter/10001) and as many times as the number of the favorite filters for rest/api/2/filter/favorite and they could consume the DB resources and JVM heap memory (cause Full GC, see another ticket for details: JRASERVER-65930)

2017-09-04 07:52:35,407 http-nio-55049-exec-5 admin 472x898x1 1l1x7vx /rest/api/2/filter/10001 0ms "SELECT user_name FROM cwd_user WHERE (lower_user_name IN ('testuser1', 'testuser2') ) AND (directory_id =  '1' ) ORDER BY lower_user_name"

Moreover, all the users in the group appeared in the WHERE condition lower_user_name IN ('testuser1', 'testuser2'). The SQL seemed to check if the users who belong to the group are not in the higher ordered directory.

In conclusion, the more users in the external directory are associated with the project, the more DB resources are consumed. It doesn't seem to be scalable. Let's think about the following scenario.

1. There are many filters which are limited to specific projects
2. Tons of users are associated with the each project
3. Some users register many of such filters as their favorite ones
4. The users access Issue Navigator at the same time

Notes

• There seemed to be some difference in the SQL between JIRA 7.4.x and JIRA 7.3.x.
  • JIRA 7.4.x

    2017-09-04 07:52:35,407 http-nio-55049-exec-5 admin 472x898x1 1l1x7vx /rest/api/2/filter/10001 0ms "SELECT user_name FROM cwd_user WHERE (lower_user_name IN ('testuser1', 'testuser2') ) AND (directory_id =  '1' ) ORDER BY lower_user_name"
    

  • JIRA 7.3.x

    2017-09-04 07:24:08,162 http-nio-41186-exec-4 admin 444x1796x1 1luqcea /rest/api/2/filter/10003 0ms "SELECT user_name FROM cwd_user WHERE ((lower_user_name =  'testuser1' ) OR (lower_user_name =  'testuser2' )) AND (directory_id =  '1' ) ORDER BY lower_user_name"
    

• Please refer to my internal comment for the additional information

Workaround

• Reduce number of favourite filters
• Dark feature com.atlassian.jira.rest.v2.search.UserListResolver.getShareCount.disabled flag could solve the performance problem
  • Only available with Jira 7.0.0+
  • API break. There is a risk that /rest/api/2/filter/favourite API doesn't return a correct number of the share count
• Change the directory order
  • This problem could still happen in case that there are tons of users in JIRA internal directory who were associated with the project

Note on fix

• The issue was work-arounded in 7.2.12, 7.6.1 and 7.7 by introducing an extra query param enableSharedUsers to /rest/api/2/filter/favourite and /rest/api/2/filter/id to show that users shouldn't be calculated.
  • By default, this param is true, so endpoints are fully backwards compatible. So that still can cause the mentioned problem
  • We've started using this param in JIRA itself ( issue-nav uses enableSharedUsers=false), so the problem should be fixed for JIRA.