Service Desk notifications send repeatedly when over 2000 notifications are batched

Summary

Starting in JIRA Service Desk 3.3, a notification batching functionality was introduced, with it's data kept in the AO_4E8AE6_NOTIF_BATCH_QUEUE database table.

When over 2000 notifications are generated in a short period of time, they will all be written to this table. When it's time for JIRA to send the notifications, they may encounter the following known issue depending on the database they are using: JRASERVER-63290 : Queries with large IN clauses that result in SQL Server errors.

The database will throw this error, and the scheduled job will fail, resulting in subsequent reattempts which will send the notifications over and over. This behavior results in the following stack trace in the application logs:

2017-05-03 08:49:22,951 Caesium-1-2 ERROR anonymous     [c.a.scheduler.core.JobLauncher] Scheduled job with ID 'sd.custom.notification.batch.send' failed
com.querydsl.core.QueryException: Caught SQLException for update "dbo"."AO_4E8AE6_NOTIF_BATCH_QUEUE"
set "SENT_TIME" = ? where "AO_4E8AE6_NOTIF_BATCH_QUEUE"."ID" in (?, ?, ?, ...?)
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:453)
	at com.querydsl.sql.dml.SQLUpdateClause.execute(SQLUpdateClause.java:205)
	at com.atlassian.servicedesk.plugins.notifications.internal.dao.NotificationBatchQueueDao.setSendTimeStamp(NotificationBatchQueueDao.java:68)
	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)

Environment

• JIRA Service Desk 3.3 and above
• Either of the databases affected by JRASERVER-63290 : Queries with large IN clauses that result in SQL Server errors.
  • Microsoft SQL Server
  • PostgreSQL

Expected Results

JIRA will only send the notifications once

Actual Results

JIRA will send the notifications repeatedly

Workaround

The logic utilized by the batching job checks the AO_4E8AE6_NOTIF_BATCH_QUEUE table for rows which have a NULL value in the SENT_TIME column. Thus, stopping the job will involve either artificially setting the value manually in the database, or removing the rows altogether.

Either of the following will stop the flooding:

Fixing SENT_TIME

1. Stop JIRA
2. Fill in the AO_4E8AE6_NOTIF_BATCH_QUEUE.SENT_TIME values with generic date timestamps
3. Start JIRA

Deleting the rows

1. Stop JIRA
2. Drop all of the rows out of the AO_4E8AE6_NOTIF_BATCH_QUEUE table where SENT_TIME is NULL
3. Start JIRA