MS SQL データベースの利用時にバッチ通知が動作しない
症状
After upgrading or installing Jira 8.+ and turning on Batched Notifications, notifications are never sent at all.
診断
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: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::<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>
原因
The DB configuration file is missing the schema-name tag, which is required for the batched notifications database tables to be properly created.
ソリューション
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.