-
Bug
-
Resolution: Unresolved
-
Medium
-
None
-
7.3.0, 7.1.10, 7.3.1, 7.3.3, 7.5.1, 8.20.11
-
7.01
-
79
-
Severity 2 - Major
-
8
-
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
- Import a backup from JIRA Cloud to JIRA Server connected to SQL Server 2014 or PostgreSQL
- 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
- causes
-
JRASERVER-65961 Cannot delete a project if tied with an issue with more than 2000 comments on MSSQL
- Closed
- is related to
-
JSDSERVER-5083 Service Desk notifications send repeatedly when over 2000 notifications are batched
- Closed
-
JSMDC-668 Loading...
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...