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;

          Form Name

            [JRASERVER-19317] Ofbiz generates queries with large IN clauses that result in ORA-01795 errors

            Oswaldo Hernandez (Inactive) added a comment -

            Hi liyong.liu@easesolutions.com,

            There is currently an outstanding issue similar to this for SQL Server that we are tracking at JRA-38790.

            Regards,

            Oswaldo Hernández.
            JIRA Bugmaster.
            [Atlassian].

            Oswaldo Hernandez (Inactive) added a comment - Hi liyong.liu@easesolutions.com , There is currently an outstanding issue similar to this for SQL Server that we are tracking at JRA-38790 . Regards, Oswaldo Hernández. JIRA Bugmaster. [Atlassian] .

            Leon Liu (ease solutions) added a comment -

            So is this max project number limit removed for oracle database? how about sql server?
            Thanks
            Leon

            Leon Liu (ease solutions) added a comment - So is this max project number limit removed for oracle database? how about sql server? Thanks Leon

            Pawel Bugalski (Inactive) added a comment -

            jasbris we have resolved this issue by splitting IN clauses with lists of expressions longer than 1000 elements into multiple IN clauses of 1000 elements each combined into a single clause with an OR operator. This is done in OfBiz entity engine so no query issued by JIRA's core should generate ORA-01795 error.
            However, any code that does not use OfBiz entity engine (notably: plugins that use Active Objects) does not benefit from this fix and has to work around this error. JIRA 6.1.1 contains a new fixed version of one such plugin: DVCS connector.

            Pawel Bugalski (Inactive) added a comment - jasbris we have resolved this issue by splitting IN clauses with lists of expressions longer than 1000 elements into multiple IN clauses of 1000 elements each combined into a single clause with an OR operator. This is done in OfBiz entity engine so no query issued by JIRA's core should generate ORA-01795 error. However, any code that does not use OfBiz entity engine (notably: plugins that use Active Objects) does not benefit from this fix and has to work around this error. JIRA 6.1.1 contains a new fixed version of one such plugin: DVCS connector.

            Jason Brison added a comment -

            So in 6.1.1 is there no limit in Oracle now?

            Jason Brison added a comment - So in 6.1.1 is there no limit in Oracle now?

            MattS added a comment -

            The project role has two parts - users and groups. Are the users there as part of a group or did someone have add 1000 users individually to the role (blinks)

            MattS added a comment - The project role has two parts - users and groups. Are the users there as part of a group or did someone have add 1000 users individually to the role (blinks)

            Jeff Louwerse added a comment -

            yes exactly "1000+ users defined in a Project Role for a given project". Many of our projects include an "all employee's" group in the developer role which allows any designer the ability to edit, update, move any issue to any project. As a result if you go to the project page (non administrative) and select people it throws this error.

            I realize this could be managed differently to avoid the error however it still an error.

            Jeff Louwerse added a comment - yes exactly "1000+ users defined in a Project Role for a given project". Many of our projects include an "all employee's" group in the developer role which allows any designer the ability to edit, update, move any issue to any project. As a result if you go to the project page (non administrative) and select people it throws this error. I realize this could be managed differently to avoid the error however it still an error.

            Mark Lassau (Inactive) added a comment -

            It should be noted that this is not only a problem with 1000 projects but also if a project has 1000+ users (don't ask) and you want to view the PEOPLE in that project.

            deake
            Can you please clarify what you mean by "if a project has 1000+ users"?
            Do you mean that there are 1000+ users defined in a Project Role for a given project?

            Mark Lassau (Inactive) added a comment - It should be noted that this is not only a problem with 1000 projects but also if a project has 1000+ users (don't ask) and you want to view the PEOPLE in that project. deake Can you please clarify what you mean by "if a project has 1000+ users"? Do you mean that there are 1000+ users defined in a Project Role for a given project?

            Jeff Louwerse added a comment -

            It should be noted that this is not only a problem with 1000 projects but also if a project has 1000+ users (don't ask) and you want to view the PEOPLE in that project. This seems like it could be easily avoided with efficient queries.

            Jeff Louwerse added a comment - It should be noted that this is not only a problem with 1000 projects but also if a project has 1000+ users (don't ask) and you want to view the PEOPLE in that project. This seems like it could be easily avoided with efficient queries.

            Jamie Echlin added a comment - - edited

            Note that the oracle 1000 projects limitation comes in to play in two places, 1) the user activity stream, if the user has BROWSE permission in > 1000 projects, and 2) in the view project roles page.

            #2 Seems to cause jira to die through max active thread death. Given this, I think you should prevent creation of the 1001st project if the db is oracle, or at least put a warning on the admin pages.

            Documenting as in JRA-21972 is not nearly sufficient.

            IMHO oracle should be treated as a first-class database citizen as part of your "enterprise" program. This is only one of numerous oracle-specific issues.

            Anyway, I patched 5.2 and 4.4.3 locally to make it query only the first 1000 projects that it finds... far from ideal but should prevent crashing.
            cheers, jamie

            Jamie Echlin added a comment - - edited Note that the oracle 1000 projects limitation comes in to play in two places, 1) the user activity stream, if the user has BROWSE permission in > 1000 projects, and 2) in the view project roles page. #2 Seems to cause jira to die through max active thread death. Given this, I think you should prevent creation of the 1001st project if the db is oracle, or at least put a warning on the admin pages. Documenting as in JRA-21972 is not nearly sufficient. IMHO oracle should be treated as a first-class database citizen as part of your "enterprise" program. This is only one of numerous oracle-specific issues. Anyway, I patched 5.2 and 4.4.3 locally to make it query only the first 1000 projects that it finds... far from ideal but should prevent crashing. cheers, jamie

            CS DTACC Unity Support. added a comment -

            Do we have any update on this, please ?

            Frequently our Prod instance is destabilized & coming down during business hours, causing unexpected outage to thousands of our users globally.

            Appreciate if this can be looked into & let us know if any solution/workaround for this.

            Thanks.

            CS DTACC Unity Support. added a comment - Do we have any update on this, please ? Frequently our Prod instance is destabilized & coming down during business hours, causing unexpected outage to thousands of our users globally. Appreciate if this can be looked into & let us know if any solution/workaround for this. Thanks.

              jhazelwood James Hazelwood
              pwhite Peter White [Atlassian]
              Affected customers:
              9 This affects my team
              Watchers:
              24 Start watching this issue

                Created:
                Updated:
                Resolved: