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

Database queries with more than 2000 parameters cause SQLExceptions

XMLWordPrintable

      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
        • Oracle

      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

              Unassigned Unassigned
              cchan Chung Park Chan
              Votes:
              16 Vote for this issue
              Watchers:
              34 Start watching this issue

                Created:
                Updated: