[JSDSERVER-5239] 500 error when performing search in automation rule audit log with MSSQL DB

Type: Bug
Resolution: Unresolved
Priority: Medium
Fix Version/s: None
Affects Version/s: 3.5.0
Component/s: Automation
Labels:
- cqt
- ril

Support reference count:
5
Symptom Severity:
Severity 3 - Minor
Bug Fix Policy:
View Atlassian Server bug fix policy

Summary

When user tries to perform a search in automation rule audit log a HTTP 500 error is thrown if JIRA is running on MS SQL DB.

Environment

MS SQL DB

Steps to Reproduce

Create a Service Desk project.
Navigate to Project settings > Automation.
Choose a automation rule and click on "View log".
Try to perform a search in the View log page.

Expected Results

No error is thrown

Actual Results

A HTTP 500 error is returned and the below exception is thrown in the JIRA log:

2017-07-07 11:32:12,151 http-nio-8180-exec-20 ERROR admin 692x1158012x1 10d8q56 /rest/servicedesk/automation/1/audit/ruleset/32/issue-keys [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLException for select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_EXEC_RULE_MSG_ITEM" "AO_9B2E3B_EXEC_RULE_MSG_ITEM" on "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_EXECUTION_ID" = "AO_9B2E3B_RULE_EXECUTION"."ID" where "AO_9B2E3B_RULESET"."ID" = ? and ("AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_KEY" = ? and ("AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" like ? escape '\')) group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc offset ? rows fetch next ? rows only
com.querydsl.core.QueryException: Caught SQLException for select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "MgtG_JNJIRA_AppProd"."AO_9B2E3B_EXEC_RULE_MSG_ITEM" "AO_9B2E3B_EXEC_RULE_MSG_ITEM" on "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_EXECUTION_ID" = "AO_9B2E3B_RULE_EXECUTION"."ID" where "AO_9B2E3B_RULESET"."ID" = ? and ("AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_KEY" = ? and ("AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" like ? escape '\')) group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc offset ? rows fetch next ? rows only
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:453)
	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
	at com.atlassian.servicedesk.plugins.automation.internal.execution.history.dao.querydsl.ExecutionHistoryDaoImpl.lambda$getIssueKeysByRuleSetId$4(ExecutionHistoryDaoImpl.java:272)
	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:68)
	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:91)
	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor$1.execute(DefaultTransactionalExecutor.java:45)
	at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.lambda$borrowConnectionAndExecute$0(JiraHostConnectionAccessor.java:62)
	at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67)
	... 2 filtered
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
	at com.sun.proxy.$Proxy401.executeQuery(Unknown Source)
	... 2 filtered
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136)
	at com.sun.proxy.$Proxy401.executeQuery(Unknown Source)
	at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.borrowConnectionAndExecute(JiraHostConnectionAccessor.java:50)
	at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:40)
	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:42)
	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:66)
	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:42)
	at com.atlassian.servicedesk.plugins.automation.internal.execution.history.dao.querydsl.ExecutionHistoryDaoImpl.getIssueKeysByRuleSetId(ExecutionHistoryDaoImpl.java:256)
	at com.atlassian.servicedesk.plugins.automation.internal.execution.history.ExecutionHistoryManagerImpl.getIssueKeysForRuleExecutions(ExecutionHistoryManagerImpl.java:51)
	at com.atlassian.servicedesk.plugins.automation.internal.rest.RuleSetAuditResource.lambda$getRuleSetIssueKeys$3(RuleSetAuditResource.java:140)
	at io.atlassian.fugue.Either$Right.fold(Either.java:606)
	at com.atlassian.servicedesk.plugins.automation.internal.rest.RuleSetAuditResource.getRuleSetIssueKeys(RuleSetAuditResource.java:138)
	... 2 filtered
	at java.lang.reflect.Method.invoke(Method.java:498)
	... 19 filtered
	at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
	... 1 filtered
	at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
	... 32 filtered
	at com.atlassian.servicedesk.internal.web.ExternalCustomerLockoutFilter.doFilter(ExternalCustomerLockoutFilter.java:56)
	... 4 filtered
	at com.atlassian.servicedesk.internal.web.UrlOperationalStatusCheckFilter.doFilterWhenLicensed(UrlOperationalStatusCheckFilter.java:38)
	at com.atlassian.servicedesk.internal.web.OperationalStatusAwareHttpFilter.doFilter(OperationalStatusAwareHttpFilter.java:27)
	... 4 filtered
	at com.atlassian.servicedesk.internal.web.PopulateEyeballForRestFilter.doFilterWhenLicensed(PopulateEyeballForRestFilter.java:36)
	at com.atlassian.servicedesk.internal.web.OperationalStatusAwareHttpFilter.doFilter(OperationalStatusAwareHttpFilter.java:27)
	... 8 filtered
	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
	... 53 filtered
	at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:80)
	... 1 filtered
	at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:78)
	... 16 filtered
	at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
	... 20 filtered
	at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
	... 5 filtered
	at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.lambda$invokeFilterChain$0(CustomerContextSettingFilter.java:181)
	at com.atlassian.servicedesk.internal.utils.context.ReentrantThreadLocalBasedCodeContext.rteInvoke(ReentrantThreadLocalBasedCodeContext.java:139)
	at com.atlassian.servicedesk.internal.utils.context.ReentrantThreadLocalBasedCodeContext.runOutOfContext(ReentrantThreadLocalBasedCodeContext.java:92)
	at com.atlassian.servicedesk.internal.utils.context.CustomerContextServiceImpl.runOutOfCustomerContext(CustomerContextServiceImpl.java:64)
	at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.outOfCustomerContext(CustomerContextSettingFilter.java:174)
	at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilterImpl(CustomerContextSettingFilter.java:130)
	at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilter(CustomerContextSettingFilter.java:121)
	... 4 filtered
	at com.atlassian.jwt.internal.servlet.JwtAuthFilter.doFilter(JwtAuthFilter.java:32)
	... 8 filtered
	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
	... 4 filtered
	at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
	... 7 filtered
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:220)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:197)
	at net.bull.javamelody.PluginMonitoringFilter.doFilter(PluginMonitoringFilter.java:85)
	at net.bull.javamelody.JiraMonitoringFilter.doFilter(JiraMonitoringFilter.java:118)
	... 25 filtered
	at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
	... 28 filtered
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:373)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
	... 299 more

Notes

As explained in this documentation :

Columns of type ntext, text, image, geography, geometry, and xml cannot be used in an ORDER BY clause.

And following method is called when searching in the audit log :

// com.atlassian.servicedesk.plugins.automation.internal.execution.history.dao.querydsl.ExecutionHistoryDaoImpl
    public List<String> getIssueKeysByRuleSetId(final long id, String issueKeySearchPattern) {
        return databaseAccessor.runInTransaction(connection ->
                connection
                        .select(EXECUTION_RULE_MESSAGE_ITEM.RULE_MESSAGE_VALUE)
                        .from(RULE_EXECUTION)
                        .leftJoin(RULE)
                        .on(RULE_EXECUTION.RULE_ID.eq(RULE.ID))
                        .leftJoin(RULE_SET_REVISION)
                        .on(RULE_SET_REVISION.ID.eq(RULE.RULESET_REVISION_ID))
                        .leftJoin(RULE_SET)
                        .on(RULE_SET_REVISION.RULE_SET_ID.eq(RULE_SET.ID))
                        .leftJoin(EXECUTION_RULE_MESSAGE_ITEM)
                        .on(EXECUTION_RULE_MESSAGE_ITEM.RULE_EXECUTION_ID.eq(RULE_EXECUTION.ID))
                        .where(RULE_SET.ID.eq(id).and(getIssueKeySearchPatternWhereClause(issueKeySearchPattern)))
                        .limit(ISSUE_KEY_QUERY_LIMIT)
                        .groupBy(EXECUTION_RULE_MESSAGE_ITEM.RULE_MESSAGE_VALUE)
                        .orderBy(EXECUTION_RULE_MESSAGE_ITEM.RULE_MESSAGE_VALUE.asc())
                        .fetch()
        );
    }

Workaround

No workaround at the moment (except from migrating to another DB).

is related to

JSDSERVER-5907 Database error : "ORA-00932: inconsistent datatypes: expected - got CLOB" when searching in automation log.

Gathering Impact

links to

Internal ticket

Marc Dacanay made changes - 14/Oct/2024 10:49 PM

Labels

Original: cqt

New: cqt ril

Marc Dacanay made changes - 14/Oct/2024 10:49 PM

Remote Link

New: This issue links to "Internal ticket (Web Link)" [ 955022 ]

SET Analytics Bot made changes - 18/May/2023 4:03 AM

Support reference count

Original: 4

New: 5

Gonchik Tsymzhitov added a comment - 03/Oct/2021 8:41 AM - edited

Met on postgresql 11 and jira 8.13.9 with the same stacktrace

Gonchik Tsymzhitov added a comment - 03/Oct/2021 8:41 AM - edited Met on postgresql 11 and jira 8.13.9 with the same stacktrace

Bugfix Automation Bot made changes - 14/Jul/2021 6:31 AM

Support reference count

Original: 3

New: 4

Owen made changes - 27/Mar/2019 5:47 AM

Workflow	Original: JSD Bug Workflow v5 - TEMP [ 2406252 ]	New: JAC Bug Workflow v3 [ 3125901 ]
Status	Original: To Do [ 10071 ]	New: Gathering Impact [ 12072 ]

Owen made changes - 24/Oct/2018 5:22 AM

Symptom Severity

Original: Minor [ 14432 ]

New: Severity 3 - Minor [ 15832 ]

Jeffrey Sjauw Mook (Inactive) made changes - 10/Jul/2018 1:11 PM

Link

New: This issue is related to JSDSERVER-5907 [ JSDSERVER-5907 ]

SET Analytics Bot made changes - 09/Jun/2018 1:25 AM

Support reference count

New: 3

Bartosz Ornatowski made changes - 18/Jan/2018 11:17 PM

Labels

New: cqt

Assignee:: Unassigned

Reporter:: Chen Mao (Inactive)

Affected customers:: 9 This affects my team

Watchers:: 7 Start watching this issue

Created:: 10/Jul/2017 11:43 AM

Updated:: 14/Oct/2024 10:49 PM

Details

Description

Summary

Environment

Steps to Reproduce

Expected Results

Actual Results

Notes

Workaround

Attachments

Issue Links

Forms

Activity

Collapse comment: Gonchik Tsymzhitov added a comment - 03/Oct/2021 8:41 AM, Edited by Gonchik Tsymzhitov - 03/Oct/2021 8:41 AM

Expand comment: Gonchik Tsymzhitov added a comment - 03/Oct/2021 8:41 AM, Edited by Gonchik Tsymzhitov - 03/Oct/2021 8:41 AM

People

Dates