Uploaded image for project: 'JIRA Server (including JIRA Core)'
  1. JIRA Server (including JIRA Core)
  2. JRASERVER-63290

Queries with large IN clauses that result in SQL Server errors

    Details

    • Support reference count:
      46
    • Regular Expression:
      Prepared or callable statement has more than 2000 parameter markers
    • Symptom Severity:
      Major
    • Occurrence Factor:
      10%
    • UIS:
      6

      Description

      Summary

      SQL Server does not allow more than 2000 parameters in a query.

      Environment

      • JIRA Cloud
      • JIRA Software 7.1.10
        • MS SQL Server 2014
        • PostgreSQL

      Steps to Reproduce

      1. Import a backup from JIRA Cloud to JIRA Server connected to SQL Server 2014 or PostgreSQL
      2. Monitor the log files

      Expected Results

      No exception in regards to exceeding 2000 parameters.

      Actual Results

      The below exception is thrown in the atlassian-jira.log file:
      MS SQL:

      2016-11-22 14:04:46,657 Caesium-1-1 ERROR      [c.a.s.caesium.impl.SchedulerQueueWorker] Unhandled exception thrown by job QueuedJob[jobId=com.atlassian.jira.internal.mail.services.MailPullerJobRunner,deadline=1479783868047]
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:DELETE FROM jiraschema.rundetails WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?)  (Prepared or callable statement has more than 2000 parameter markers.)
      	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.removeByCondition(DefaultOfBizDelegator.java:144)
      	at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.removeByCondition(WrappingOfBizDelegator.java:136)
      	at com.atlassian.jira.entity.Delete$DeleteWhereContext.execute(Delete.java:154)
      	at com.atlassian.jira.entity.EntityEngineImpl.delete(EntityEngineImpl.java:55)
      	at com.atlassian.jira.entity.Delete$DeleteWhereContext.execute(Delete.java:136)
      	at com.atlassian.jira.scheduler.OfBizRunDetailsDao.addRunDetails(OfBizRunDetailsDao.java:116)
      	at com.atlassian.scheduler.core.AbstractSchedulerService.addRunDetails(AbstractSchedulerService.java:151)
      	at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:98)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:401)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeLocalJob(CaesiumSchedulerService.java:369)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:347)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:255)
      	at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:252)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59)
      	at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34)
      	at java.lang.Thread.run(Unknown Source)
      Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:DELETE FROM jiraschema.rundetails WHERE ID IN (?, ?, ?, ??, ?, ?, ?, ?, ?)  (Prepared or callable statement has more than 2000 parameter markers.)
      	at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:571)
      	at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:521)
      	at org.ofbiz.core.entity.GenericDAO.deleteByCondition(GenericDAO.java:1370)
      	at org.ofbiz.core.entity.GenericHelperDAO.removeByCondition(GenericHelperDAO.java:221)
      	at org.ofbiz.core.entity.GenericDelegator.removeByCondition(GenericDelegator.java:1364)
      	... 16 more
      Caused by: java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.
      	at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1254)
      	at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:165)
      	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.<init>(JtdsPreparedStatement.java:111)
      	at net.sourceforge.jtds.jdbc.JtdsConnection.prepareStatement(JtdsConnection.java:2492)
      	at net.sourceforge.jtds.jdbc.JtdsConnection.prepareStatement(JtdsConnection.java:2450)
      	at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:288)
      	at org.ofbiz.core.entity.jdbc.interceptors.connection.DelegatingConnection.prepareStatement(DelegatingConnection.java:41)
      	at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:566)
      	... 21 more
      

      PostgreSQL:

      2016-12-22 12:07:19,608 Caesium-1-3 ERROR ServiceRunner     [c.a.s.caesium.impl.CaesiumSchedulerService] Unhandled exception during the attempt to execute job 'com.atlassian.jira.internal.mail.services.MailPullerJobRunner'; will attempt recovery in 60 seconds
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:DELETE FROM public.rundetails WHERE ID IN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
      ...
      An I/O error occured while sending to the backend.)
      	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:683)
      	at org.ofbiz.core.entity.GenericDAO.deleteByCondition(GenericDAO.java:1377)
      	at org.ofbiz.core.entity.GenericHelperDAO.removeByCondition(GenericHelperDAO.java:221)
      	at org.ofbiz.core.entity.GenericDelegator.removeByCondition(GenericDelegator.java:1364)
      	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.removeByCondition(DefaultOfBizDelegator.java:142)
      Caused by: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
      	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
      	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:673)
      	... 21 more
      Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 50815
      	at org.postgresql.core.PGStream.SendInteger2(PGStream.java:199)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1235)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1507)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1096)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:253)
      

      Workaround

      • Stop JIRA
      • Delete everything from rundetails table
        DELETE FROM rundetails;
        
      • Start JIRA

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                cchan Chung Park Chan
              • Votes:
                6 Vote for this issue
                Watchers:
                20 Start watching this issue

                Dates

                • Created:
                  Updated: