Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-19317

Ofbiz generates queries with large IN clauses that result in ORA-01795 errors

XMLWordPrintable

      Summary

      Oracle will not permit more than 1000 literals in an IN clause. It does, however, permit SELECT statements in the IN clause which can return an unlimited number of elements. This causes problem as OfBiz will attempt to query an Oracle DB with IN clauses that have >1k literals and will throw exceptions.

      Steps to Reproduce

      1. Go to a JIRA instance with more than 1000 projects
      2. Go into the "User Browser"
      3. For any user, click on the "Project Roles" link

      This can also be reproduced anywhere within JIRA where OfBiz queries the database in this manner, for example:

      • Accessing the Activity Stream.
      • Viewing a large change history on an issue.

      Expected Results

      JIRA functions without error.

      Actual Results

      This will generate the following SQL (Oracle) error:

      Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT DISTINCT PID FROM projectroleactor WHERE (PROJECTROLEID=? AND ROLETYPE=? AND ROLETYPEPARAMETER=?) AND (PID IN (?, ?, ?, ?,... ) ) (ORA-01795: maximum number of expressions in a list is 1000

      In SQL Server it will display a Prepared or callable statement has more than 2000 parameter markers error.

      Notes

      It seems that this can also use up connections in the DBCP and not release them back into the pool, subsequently multiple interactions with the database that experience this error can cause the DBCP to time out and JIRA will cease to function properly (as it cannot obtain connections from the DB). This is not currently verified, however has been anecdotally viewed on customer instances and was corrected by working around the SQL that was causing these errors. This was caused by issues with large history records (over 1k) and deleting some of the history records or restricting the access of the issue corrected the problem. This presents with the following exception:

      2013-04-10 08:25:22,407 StreamsCompletionService::thread-19910 ERROR cemerine 500x1935299x29 i2att9 3.185.141.134,3.239.244.206 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Error building feed
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000
)
	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:489)
	at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIdsByUsers(DefaultChangeHistoryManager.java:390)
...
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) ) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000
)
	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605)
	at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:1059)

      And can be verified with the below SQL:

      SELECT count(*) AS ttl_records, 
       p.pkey,
       ji.issuenum
FROM   changeitem ci 
       JOIN changegroup cg 
         ON cg.id = ci.groupid 
       JOIN jiraissue ji 
         ON ji.id = cg.issueid 
       JOIN project p
         ON ji.project = p.id
GROUP  BY p.pkey, ji.issuenum
HAVING count(*) >= 1000
ORDER  BY ttl_records DESC;

              jhazelwood James Hazelwood
              pwhite Peter White [Atlassian]
              Votes:
              9 Vote for this issue
              Watchers:
              24 Start watching this issue

                Created:
                Updated:
                Resolved: