-
Bug
-
Resolution: Unresolved
-
Low (View bug fix roadmap)
-
None
-
8.20.8
-
8.2
-
23
-
Severity 3 - Minor
-
4
-
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.
This is an optimized statement (optimized from our DBA for Oracle), which reduces execution time form originally 150 seconds to 13 seconds:
SELECT ID FROM jiraissue issue WHERE ISSUE.environment LIKE '%kermit-the-frog%' UNION SELECT ID FROM jiraissue issue WHERE ISSUE.description LIKE '%kermit-the-frog%' UNION SELECT ID FROM jiraissue issue WHERE EXISTS (SELECT NULL FROM customfield CUSTOM_FIELD JOIN customfieldvalue CUSTOM_FIELD_VALUE ON CUSTOM_FIELD.id = CUSTOM_FIELD_VALUE.CUSTOMFIELD WHERE CUSTOM_FIELD_VALUE.ISSUE = ISSUE.ID AND CUSTOM_FIELD.customfieldtypekey IN ('com.atlassian.jira.plugin.system.customfieldtypes:textfield', 'com.atlassian.jira.plugin.system.customfieldtypes:textarea') AND ( CUSTOM_FIELD_VALUE.stringvalue LIKE '%kermit-the-frog%' OR CUSTOM_FIELD_VALUE.textvalue LIKE '%kermit-the-frog%') );(for my reference: GHS-191154)