[JRASERVER-63290] Database queries with more than 2000 parameters cause SQLExceptions

Type: Bug
Resolution: Unresolved
Priority: Medium (View bug fix roadmap)
Fix Version/s: None
Affects Version/s: 7.3.0, 7.1.10, 7.3.1, 7.3.3, 7.5.1, 8.20.11
Component/s: Migration - Cloud to Server
Labels:
- affects-server
- whl-fy24q3

Introduced in Version:
7.01
Support reference count:
77
Symptom Severity:
Severity 2 - Major
UIS:
2
Bug Fix Policy:
View Atlassian Server bug fix policy

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 You do not have permission to view this issue

mentioned in: Connecting Jira applications to SQL Server 2019; Connecting Jira applications to a database; 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...

(49 mentioned in)

Jamis A. Mack added a comment - 23/Sep/2024 6:07 PM

This is significantly affecting our DVCS sync.

Jamis A. Mack added a comment - 23/Sep/2024 6:07 PM This is significantly affecting our DVCS sync.

Boris T added a comment - 21/Nov/2023 10:16 PM - edited

We got following in both atlassian-jira.log and atlassian-servicedesk.log, so it could be JSW (9.4.11) or JSM(5.4.11) , if this issue should be reported as new issue please let me know.

com.querydsl.core.QueryException: Caught SQLServerException for delete from "dbo"."AO_0201F0_STATS_EVENT_PARAM" where "AO_0201F0_STATS_EVENT_PARAM"."STATS_EVENT_ID" in (?, ?, ?,

......

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Boris T added a comment - 21/Nov/2023 10:16 PM - edited We got following in both atlassian-jira.log and atlassian-servicedesk.log, so it could be JSW (9.4.11) or JSM(5.4.11) , if this issue should be reported as new issue please let me know. com.querydsl.core.QueryException: Caught SQLServerException for delete from "dbo" . "AO_0201F0_STATS_EVENT_PARAM" where "AO_0201F0_STATS_EVENT_PARAM" . "STATS_EVENT_ID" in (?, ?, ?, ...... Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Azfar Masut added a comment - 22/Mar/2017 9:23 AM

May I know what caused this? Was there any changes on the query ran by JIRA? (JSW 7.3.1)

Azfar Masut added a comment - 22/Mar/2017 9:23 AM May I know what caused this? Was there any changes on the query ran by JIRA? (JSW 7.3.1)

Chris Moreira added a comment - 04/Jan/2017 12:15 AM

Thanks, it worked for me

Chris Moreira added a comment - 04/Jan/2017 12:15 AM Thanks, it worked for me

Assignee:: Unassigned

Reporter:: Chung Park Chan

Affected customers:: 16 This affects my team

Watchers:: 34 Start watching this issue

Created:: 23/Nov/2016 1:09 AM

Updated:: 17/Jan/2025 4:05 AM

Jira Data Center

Details

Description

Summary

Environment

Steps to Reproduce

Expected Results

Actual Results

Workaround

Attachments

Issue Links

Forms

Activity

Collapse comment: Jamis A. Mack added a comment - 23/Sep/2024 6:07 PM

Expand comment: Jamis A. Mack added a comment - 23/Sep/2024 6:07 PM

Collapse comment: Boris T added a comment - 21/Nov/2023 10:16 PM, Edited by Boris T - 15/Jul/2024 7:45 PM

Expand comment: Boris T added a comment - 21/Nov/2023 10:16 PM, Edited by Boris T - 15/Jul/2024 7:45 PM

Collapse comment: Azfar Masut added a comment - 22/Mar/2017 9:23 AM

Expand comment: Azfar Masut added a comment - 22/Mar/2017 9:23 AM

Collapse comment: Chris Moreira added a comment - 04/Jan/2017 12:15 AM

Expand comment: Chris Moreira added a comment - 04/Jan/2017 12:15 AM

People

Dates