Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-71251

Slow database query with Anonymising User preventing user from being anonymised

      Issue Summary

      Jira 8.7 introduces Anonymizing users where admins can anonymize specific users.

      Steps to Reproduce

      Anonymize a user/s according to the steps in Anonymizing users - anonymizing a user.

      Expected Results

      The user/s are anonymized.

      Actual Results

      In some Jira instances where there are a large number of issues & customfields, the anonymizing user can be slow. This is due to the query executed by the anonymized user function is slow.

      In these cases, the slow performance of the query used on the database to run the anonymization causes the operation to time out. A sample entry such as below can be found in the application log file.

      <timestamp> <http-thread> ERROR <username> <session> <hostname> /rest/api/2/user/anonymization [c.a.j.user.anonymize.UserPropertyChangeManager] Exception during getting affected entities for handler: com.atlassian.jira.user.anonymize:mention-username-change-handler
      com.querydsl.core.QueryException: Caught PSQLException for select count(distinct ISSUE.id)
      from public.jiraissue ISSUE
      left join public.customfieldvalue CUSTOM_FIELD_VALUE
      on ISSUE.id = CUSTOM_FIELD_VALUE.issue
      left join public.customfield CUSTOM_FIELD
      on CUSTOM_FIELD.id = CUSTOM_FIELD_VALUE.customfield
      where ISSUE.environment like ? or ISSUE.description like ? or CUSTOM_FIELD.customfieldtypekey in (?, ?) and (CUSTOM_FIELD_VALUE.stringvalue like ? or CUSTOM_FIELD_VALUE.textvalue like ?)
              at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
              at com.querydsl.sql.Configuration.translate(Configuration.java:459)
              at com.querydsl.sql.AbstractSQLQuery.unsafeCount(AbstractSQLQuery.java:627)
              at com.querydsl.sql.AbstractSQLQuery.fetchCount(AbstractSQLQuery.java:132)
              at com.atlassian.jira.user.anonymize.handlers.username.mention.issue.GetIssueWithCustomfieldsAffectedEntities.lambda$getAffectedEntitiesCount$0(GetIssueWithCustomfieldsAffectedEntities.java:35)
              at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:68)
              at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:105)
              at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)
              at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:100)
              at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:67)
              at com.atlassian.jira.user.anonymize.handlers.username.mention.issue.GetIssueWithCustomfieldsAffectedEntities.getAffectedEntitiesCount(GetIssueWithCustomfieldsAffectedEntities.java:21)
              at com.atlassian.jira.user.anonymize.handlers.username.mention.MentionsGetAffectedEntitiesCommand.getAffectedEntities(MentionsGetAffectedEntitiesCommand.java:15)
              at com.atlassian.jira.user.anonymize.handlers.username.mention.MentionUsernameChangeHandler.lambda$getAffectedEntities$0(MentionUsernameChangeHandler.java:35)
      ...com.atlassian.jira.user.anonymize.handlers.username.mention.MentionUsernameChangeHandler.getAffectedEntities(MentionUsernameChangeHandler.java:36)
              at com.atlassian.jira.user.anonymize.handlers.username.mention.MentionUsernameChangeHandler.getAffectedEntities(MentionUsernameChangeHandler.java:17)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeManager.lambda$getAffectedEntitiesFromHandler$2(UserPropertyChangeManager.java:147)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeManager.retryOnException(UserPropertyChangeManager.java:211)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeManager.getAffectedEntitiesFromHandler(UserPropertyChangeManager.java:147)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeManager.getAffectedEntities(UserPropertyChangeManager.java:72)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeService.getAffectedEntitiesFromHandlers(UserPropertyChangeService.java:195)
              at com.atlassian.jira.user.anonymize.UserPropertyChangeService.getAffectedEntities(UserPropertyChangeService.java:184)
              at com.atlassian.jira.user.anonymize.operations.UserNameChangePluginPointsOperationStep.getAffectedEntities(UserNameChangePluginPointsOperationStep.java:96)
              at com.atlassian.jira.user.anonymize.DefaultAnonymizeUserService.getAffectedEntities(DefaultAnonymizeUserService.java:275)
              at com.atlassian.jira.user.anonymize.DefaultAnonymizeUserService.validate(DefaultAnonymizeUserService.java:207)
              at com.atlassian.jira.user.anonymize.DefaultAnonymizeUserService.preValidateAnonymize(DefaultAnonymizeUserService.java:72)
       ...
      Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:349)
              at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
              at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
              at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
              at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
              at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
              at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:42)
              at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59)
              at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:70)
              at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
              at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59)
              at com.querydsl.sql.AbstractSQLQuery.unsafeCount(AbstractSQLQuery.java:616)
              ... 350 more
      Caused by: java.net.SocketTimeoutException: Read timed out
              at java.net.SocketInputStream.socketRead0(Native Method)
              at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
              at java.net.SocketInputStream.read(SocketInputStream.java:171)
              at java.net.SocketInputStream.read(SocketInputStream.java:141)
              at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
              at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)
              at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
              at org.postgresql.core.PGStream.receiveChar(PGStream.java:443)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2056)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
              ... 362 more
      

      Workaround

      If there was a recent change or upgrade in the database, it would be worth running an update statistics, or a vacuum analyze if its a PSQL database to improve overall database performance.
      Otherwise, there are no known other workarounds yet. New workaround steps will be added as they are discovered.

            [JRASERVER-71251] Slow database query with Anonymising User preventing user from being anonymised

            George Varghese made changes -
            Labels Original: whl-fy24q3 whl-fy24q4 whl-fy25q1 whl-fy25q2 whl-fy25q3 New: whl-fy24q3 whl-fy24q4 whl-fy25q1 whl-fy25q2 whl-fy25q3 whl-fy25q4 whl-fy25q4-20
            SET Analytics Bot made changes -
            UIS Original: 9 New: 4
            SET Analytics Bot made changes -
            UIS Original: 5 New: 9
            SET Analytics Bot made changes -
            Support reference count Original: 22 New: 23
            SET Analytics Bot made changes -
            UIS Original: 10 New: 5
            SET Analytics Bot made changes -
            UIS Original: 9 New: 10
            SET Analytics Bot made changes -
            UIS Original: 10 New: 9
            Dominik Wojtasik made changes -
            Remote Link Original: This issue links to "WILDT-123 (Bulldog)" [ 985363 ] New: This issue links to "WILDT-123 (JIRA Server (Bulldog))" [ 985363 ]
            Antoni Kowalski made changes -
            Remote Link New: This issue links to "WILDT-123 (Bulldog)" [ 985363 ]
            SET Analytics Bot made changes -
            UIS Original: 14 New: 10

              Unassigned Unassigned
              vshanmugam Vicknesh Shanmugam (Inactive)
              Affected customers:
              11 This affects my team
              Watchers:
              10 Start watching this issue

                Created:
                Updated: