Batched notifications are not working when using MS SQL database

Summary

Symptoms

After upgrading or installing Jira 8.+ and turning on Batched Notifications, notifications are never sent at all.

Diagnosis

• Notifications are sent when disabling Batched Notifications in ⚙ > System > Batching email notifications

• The following error should be found in atlassian-jira.log, indicating that the tables used by the batched notification functionality can't be found:

  1 2 3 4 5 6 7 8 9 10 2020-07-23 12:35:49,196+0000 Caesium-1-4 ERROR ServiceRunner [c.a.j.p.i.batching.cron.BatchNotificationJob] Exception during batch notification job com.querydsl.core.QueryException: Caught SQLServerException for select "AO_733371_EVENT"."ID", "AO_733371_EVENT"."EVENT_TYPE", "AO_733371_EVENT"."USER_KEY", "AO_733371_EVENT"."ACTION", "AO_733371_EVENT"."ACTION_ID", "AO_733371_EVENT"."EVENT_BUNDLE_ID", "AO_733371_EVENT"."CREATED" from "null"."AO_733371_EVENT" "AO_733371_EVENT" where exists (select "AO_733371_EVENT_RECIPIENT"."ID" from "null"."AO_733371_EVENT_RECIPIENT" "AO_733371_EVENT_RECIPIENT" where "AO_733371_EVENT_RECIPIENT"."EVENT_ID" = "AO_733371_EVENT"."ID" and "AO_733371_EVENT_RECIPIENT"."STATUS" = ? and "AO_733371_EVENT_RECIPIENT"."CONSUMER_NAME" = ? and "AO_733371_EVENT_RECIPIENT"."SEND_DATE" <= ?) and ("AO_733371_EVENT"."EVENT_TYPE" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) and "AO_733371_EVENT"."ID" >= ? order by "AO_733371_EVENT"."ID" asc offset ? rows fetch next ? rows only at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502) ... at java.lang.Thread.run(Thread.java:748) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'null.AO_733371_EVENT'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256) ...

• When checking the DB configuration file <JIRA_HOME>/dbconfig.xml, you should see that the schema-name tag is missing::

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 <jira-database-config> <name>defaultDS</name> <delegator-name>default</delegator-name> <database-type>mssql</database-type> <jdbc-datasource> <url>jdbc:sqlserver://;serverName=172.1.1.1;portNumber=1433;databaseName=Jira</url> <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class> <username>Sanitized by Support Utility</username> <password>Sanitized by Support Utility</password> <pool-min-size>20</pool-min-size> <pool-max-size>20</pool-max-size> <pool-max-wait>30000</pool-max-wait> <validation-query>select 1</validation-query> <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis> <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis> <pool-max-idle>20</pool-max-idle> <pool-remove-abandoned>true</pool-remove-abandoned> <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout> <pool-test-on-borrow>false</pool-test-on-borrow> <pool-test-while-idle>true</pool-test-while-idle> </jdbc-datasource> </jira-database-config>

Cause

The DB configuration file is missing the schema-name tag, which is required for the batched notifications database tables to be properly created.

Solution

Resolution

Follow the steps in Connecting Jira applications to SQL Server 2017 to ensure that the dbconfig.xml file is properly configured, and ensure that the schema-name tag is properly listed. Once that configuration file has been updated, it will be necessary to restart Jira for the changes to take effect.