Error "ORA-01795: maximum number of expressions in a list is 1000" in SLA gadgets

Issue #421 resolved
Damien Baboulaz [NeoXam] created an issue

SLA gadgets don't return any data and show instead the following message:

An internal server error occurred when requesting resource

The logs show the cause is that the number of elements is greater than 1,000:

2017-05-19 18:09:51,287 http-nio-8447-exec-1 ERROR Damien.Baboulaz 1089x34999x1 1c6uo3h 10.33.28.34,10.215.38.39 /rest/tts-api/1.0/piechart/generate [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
    Database:
        - name:Oracle
        - version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
        - minor version:1
        - major version:12
    Driver:
        - name:Oracle JDBC driver
        - version:12.1.0.1.0

    java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
    - name:Oracle
    - version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    - minor version:1
    - major version:12
Driver:
    - name:Oracle JDBC driver
    - version:12.1.0.1.0
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:136)
    at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:291)
    ... 2 filtered
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy3224.find(Unknown Source)
    at com.tuncaysenturk.jira.plugins.timetosla.service.v.a(TtsDatabaseServiceImpl.java:1131)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.issuesCountFromDatabase(PieChart.java:239)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.generateChartInternal(PieChart.java:102)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.generateInlineChart(PieChart.java:84)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartGeneratorImpl.generatePieChart(PieChartGeneratorImpl.java:34)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartResource.createChart(PieChartResource.java:146)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartResource.getPieChart(PieChartResource.java:113)
    ... 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)
    ... 86 filtered
    at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:70)
    ... 16 filtered
    at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
    ... 73 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-01795: maximum number of expressions in a list is 1000

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
    at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4999)
    at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:11314)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3818)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at net.java.ao.EntityManager.find(EntityManager.java:671)
    at net.java.ao.EntityManager.find(EntityManager.java:621)
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:134)
    ... 235 more

Workaround: modify the filter so that fewer than 1,000 tickets are returned.

Comments (12)

  1. Tuncay Senturk repo owner

    Hi Damien,

    Thanks for the feedback.

    We'll try to fix the problem.

    Thanks for your patience.

    Regards

  2. Damien Baboulaz [NeoXam] reporter

    Hello,

    The issue is resolved, my pie chart is displayed and the Oracle error does not occur anymore.
    I have a weird chart though, e.g. the 328 "breached" are shown as 1% of my 21,733 total tickets but the orange part of the chart looks like it's 90% or something.
    chart.png

    Anyway let's close this ticket for now. I'll look into this new issue and I'll open a new ticket if needed.

    Thanks.

  3. Tuncay Senturk repo owner

    You're right,

    %1 is according to total issues count which is 21733,
    Anyway, please raise another ticket for this problem and we will try to fix it ASAP

    Regards

  4. Log in to comment