Uploaded image for project: 'Jira Service Management Data Center'
  1. Jira Service Management Data Center
  2. JSDSERVER-5907

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

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Medium Medium
    • None
    • 3.9.6, 3.11.1, 3.15.3, 3.16.1, 4.5.2, 4.20.8, 5.12.7, 5.17.1
    • Automation

      Summary

      When using an Oracle database it is not possible to filter the automation logs.

      Environment

      This issue is experienced when using Oracle as a Database.

      Steps to Reproduce

      1. Install JIRA with an Oracle Database.
      2. Trigger an automation rule in a Service Desk project.
      3. Go to project settings > automation > click on the automation you just triggered > Try to type an search in the filter.

      Expected Results

      The Automation logs should show the filtered results.

      Actual Results

      Nothing happens the wait icon remains.

      The following error appears in the logs :

      2018-07-10 12:53:14,977 http-nio-8080-exec-10 ERROR admin 773x858x1 12y3w7s 172.21.48.76 /rest/servicedesk/automation/1/audit/ruleset/14/issue-keys [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLSyntaxErrorException for select * from (   select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "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" = ? group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc ) where rownum <= ?
      com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select * from (   select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "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" = ? group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc ) where rownum <= ?
      	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
      	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
      	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:69)
      	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:86)
      	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:42)
      	at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:125)
      	... 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.$Proxy491.runInManagedTransaction(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.$Proxy491.runInManagedTransaction(Unknown Source)
      	at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.runInStartedOrExistingTransaction(JiraHostConnectionAccessor.java:130)
      	at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:60)
      	at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:39)
      	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:67)
      	at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:43)
      	at com.atlassian.pocketknife.api.querydsl.DatabaseAccessor.runInTransaction(DatabaseAccessor.java:134)
      	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:620)
      	at com.atlassian.servicedesk.plugins.automation.internal.rest.RuleSetAuditResource.getRuleSetIssueKeys(RuleSetAuditResource.java:138)
      	... 3 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)
      	... 13 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:66)
      	... 1 filtered
      	at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
      	... 16 filtered
      	at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
      	... 19 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.api.util.context.ReentrantThreadLocalBasedCodeContext.rteInvoke(ReentrantThreadLocalBasedCodeContext.java:137)
      	at com.atlassian.servicedesk.internal.api.util.context.ReentrantThreadLocalBasedCodeContext.runOutOfContext(ReentrantThreadLocalBasedCodeContext.java:90)
      	at com.atlassian.servicedesk.internal.utils.context.CustomerContextServiceImpl.runOutOfCustomerContext(CustomerContextServiceImpl.java:47)
      	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)
      	... 26 filtered
      	at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
      	... 23 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:745)
      Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
      
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
      	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
      	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
      	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
      	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
      	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
      	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
      	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
      	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
      	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)
      	... 289 more
      Caused by: Error : 932, Position : 779, Sql = select * from (   select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "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" = :1  and "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_KEY" = :2  group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc ) where rownum <= :3 , OriginalSql = select * from (   select "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" from "AO_9B2E3B_RULE_EXECUTION" "AO_9B2E3B_RULE_EXECUTION" left join "AO_9B2E3B_RULE" "AO_9B2E3B_RULE" on "AO_9B2E3B_RULE_EXECUTION"."RULE_ID" = "AO_9B2E3B_RULE"."ID" left join "AO_9B2E3B_RULESET_REVISION" "AO_9B2E3B_RULESET_REVISION" on "AO_9B2E3B_RULESET_REVISION"."ID" = "AO_9B2E3B_RULE"."RULESET_REVISION_ID" left join "AO_9B2E3B_RULESET" "AO_9B2E3B_RULESET" on "AO_9B2E3B_RULESET_REVISION"."RULE_SET_ID" = "AO_9B2E3B_RULESET"."ID" left join "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" = ? group by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" order by "AO_9B2E3B_EXEC_RULE_MSG_ITEM"."RULE_MESSAGE_VALUE" asc ) where rownum <= ?, Error Msg = ORA-00932: inconsistent datatypes: expected - got CLOB
      
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
      	... 306 more
      

      Workaround

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

          Form Name

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

            SET Analytics Bot made changes -
            Support reference count Original: 16 New: 17
            Marcos Vinicius made changes -
            Affects Version/s New: 5.12.7 [ 107622 ]
            SET Analytics Bot made changes -
            UIS Original: 3 New: 4
            SET Analytics Bot made changes -
            UIS Original: 4 New: 3
            SET Analytics Bot made changes -
            UIS Original: 3 New: 4
            SET Analytics Bot made changes -
            Support reference count Original: 15 New: 16
            Nicole Reichert (Inactive) made changes -
            Affects Version/s New: 5.17.1 [ 108612 ]
            SET Analytics Bot made changes -
            UIS Original: 4 New: 3
            SET Analytics Bot made changes -
            Support reference count Original: 14 New: 15
            Marc Dacanay made changes -
            Labels Original: cqt New: cqt ril

              Unassigned Unassigned
              jsjauwmook Jeffrey Sjauw Mook (Inactive)
              Affected customers:
              18 This affects my team
              Watchers:
              19 Start watching this issue

                Created:
                Updated: