Loading...

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

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

Details

Description

Summary

Environment

Steps to Reproduce

Expected Results

Actual Results

Notes

Workaround

Attachments

Issue Links

Activity

People

Dates