Jira batched notifications are not sent after restoring Jira data to another Data Cent instance
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In this article we are going to describe a scenario where the Jira batched notifications are not working after restoring Jira data to another Data Center instance by copying the Jira Database data to another Jira Database.
Environment
Jira Data Center on any version from 9.0.0
The Jira application is connected to an Oracle Database
Diagnosis
Only the Batched Notifications are impacted: the Jira notifications are sent when Batched Notifications are disabled from the page ⚙ > System > Batching email notifications
The issue only occurs if Jira is connected to an Oracle Database
Look for the error mentioned below in the atlassian-jira.log files.
If you see the error ORA-00001: unique constraint being thrown while Jira is trying to insert data to the database table AO_733371_EVENT_PARAMETER, then this KB article is relevant:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21Caused by: Error : 1, Position : 0, Sql = insert into "AO_733371_EVENT_PARAMETER" ("NAME", "VALUE", "EVENT_ID") values (:1 , :2 , :3 ) RETURNING ID INTO :4 , OriginalSql = insert into "AO_733371_EVENT_PARAMETER" ("NAME", "VALUE", "EVENT_ID") values (?, ?, ?) RETURNING ID INTO ?, Error Msg = ORA-00001: unique constraint (SomeConstrains) violated at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) [ojdbc8.jar:19.3.0.0.0] ... 75 more Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SampleConstraint_Staging) violated at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711) [ojdbc8.jar:19.3.0.0.0] at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447) [ojdbc8.jar:19.3.0.0.0] at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487) [ojdbc8.jar:19.3.0.0.0] at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237) [ojdbc8.jar:19.3.0.0.0] at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) [commons-dbcp2-2.1.jar:2.1] at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) [commons-dbcp2-2.1.jar:2.1] at com.atlassian.jira.ofbiz.sql.StatementWrapper.executeBatch(StatementWrapper.java:154) [classes/:?] at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeBatch$17(DiagnosticPreparedStatement.java:146) [classes/:?] at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91) [atlassian-diagnostics-platform-2.0.5.jar:?] at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62) [classes/:?] at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeBatch(DiagnosticPreparedStatement.java:146) [classes/:?] at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:386) [querydsl-sql-4.1.4.jar:?] ... 54 more
Cause
Table AO_733371_EVENT_PARAMETER stores events that are used by the Jira Batched Notification feature (these events are periodically combined to generate the batched notifications)
If the Jira application is unable to add events to this database due to the "unique constraint" violation, it will not be able to send Batched Notifications
The violation is happening because the Sequence associated to the DB table AO_733371_EVENT_PARAMETER is lower than the MAX(ID) of this table
The exact root cause for this discrepancy is unclear, but it is known to occur in case of data migration from a Jira application to another, as mentioned in the KB article Getting ORA-00001: unique constraint error while performing some actions in JIRA Data Center
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The resolution steps for this issue are the same as the ones from the KB article Getting ORA-00001: unique constraint error while performing some actions in JIRA Data Center, so we recommend checking this other KB article for more details. The main steps consist in:
Checking the MAX(ID) of the DB table AO_733371_EVENT_PARAMETER
Checking the last_number value of the sequence used by this DB table
Increasing the last_number value to a value higher than the MAX(ID) of AO_733371_EVENT_PARAMETER
⚠️ Note that a re-start of the Jira application will be required after these steps are followed.
Was this helpful?