Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-10356

Oracle error ORA-01795 on some web services involving more than 1'000 projects

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.7.1
    • Fix Version/s: 6.7.2, 7.1
    • Component/s: Database, Web API
    • Labels:
      None

      Description

      When attempting a Web API call to api/projects/bulk_delete for more than 1000 projects on a SonarQube 6.7.1 platform using an Oracle DB, you get and error back and in the SonarQube web logs:

      2018.01.26 10:52:29 ERROR web[AWEnyJi2K2n4UcLlBRqt][o.s.s.w.WebServiceEngine] Fail to process request http://<sq_root_url>/sonar/api/projects/bulk_delete
      org.apache.ibatis.exceptions.PersistenceException: 
      ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      
      ### The error may exist in org.sonar.db.component.ComponentMapper
      ### The error may involve defaultParameterMap
      ### The error occurred while setting parameters
      ### SQL: select             p.id,     p.organization_uuid as organizationUuid,     p.uuid as uuid,     p.uuid_path as uuidPath,     p.project_uuid as projectUuid,     p.module_uuid as moduleUuid,     p.module_uuid_path as moduleUuidPath,     p.main_branch_project_uuid as mainBranchProjectUuid,     p.kee as kee,     p.deprecated_kee as deprecatedKey,     p.name as name,     p.long_name as longName,     p.description as description,     p.tags as tagsString,     p.qualifier as qualifier,     p.scope as scope,     p.language as language,     p.root_uuid as rootUuid,     p.path as path,     p.enabled as enabled,     p.copy_component_uuid as copyComponentUuid,     p.developer_uuid as developerUuid,     p.private as isPrivate,     p.created_at as createdAt               from projects p           where       p.enabled=1       AND p.main_branch_project_uuid is null       AND p.copy_component_uuid is null                 and p.organization_uuid=?                         and p.qualifier in            (               ?            )                                          and p.kee in          (             ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            [snip]
      ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          ,            ?          )                                                  ORDER BY LOWER(p.name), p.name, p.id
      ### Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      
      	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
      	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
      	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:135)
      	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
      	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
      	at com.sun.proxy.$Proxy82.selectByQuery(Unknown Source)
      	at org.sonar.db.component.ComponentDao.selectByQueryImpl(ComponentDao.java:60)
      	at org.sonar.db.component.ComponentDao.selectByQuery(ComponentDao.java:113)
      	at org.sonar.server.project.ws.BulkDeleteAction.handle(BulkDeleteAction.java:137)
      	at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:103)
      	at org.sonar.server.ws.WebServiceFilter.doFilter(WebServiceFilter.java:86)
      	at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:126)
      	at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:95)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:87)
      	at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:71)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.sonar.server.platform.web.SecurityServletFilter.doHttpFilter(SecurityServletFilter.java:72)
      	at org.sonar.server.platform.web.SecurityServletFilter.doFilter(SecurityServletFilter.java:48)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.sonar.server.platform.web.RedirectFilter.doFilter(RedirectFilter.java:61)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.sonar.server.platform.web.requestid.RequestIdFilter.doFilter(RequestIdFilter.java:63)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.sonar.server.platform.web.RootFilter.doFilter(RootFilter.java:62)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
      	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
      	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
      	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
      	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
      	at ch.qos.logback.access.tomcat.LogbackValve.invoke(LogbackValve.java:256)
      	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
      	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
      	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
      	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
      	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
      	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
      	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      	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:447)
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
      	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
      	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
      	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
      	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
      	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
      	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
      	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
      	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
      	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
      	at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:60)
      	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
      	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
      	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
      	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
      	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
      	... 49 common frames omitted
      

      The solution is to delete only the 1'000 first projects. Other projects are ignored and must be processed by a new WS request.

      The same error occurs on these web services:

      • api/projects/search_my_projects when user has more than 1'000 projects. The search should be restricted to 1'000 projects, so that the WS does not crash if user has more than 1'000 projects
      • api/projects/search when parameters projects¬†or projectIds have more than 1'000 values. Instead of failing with a 500 internal error (SQL error), it should fail with 400 and a clean message
      • api/permissions/bulk_apply_template when parameter projects has more than 1'000 values. Instead of failing with a 500 internal error (SQL error), it should fail with 400 and a clean message

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                simon.brandhof Simon Brandhof
                Reporter:
                olivier.korach Olivier Korach
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: