After upgrading JIRA some specific users don't receive mail notifications
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
Symptoms
JIRA was recently upgraded and some specific users that were included with Single-User notification to the notification scheme don't receive mail notifications. The Notification helper shows they will not get notifications, although the user is listed in the notification scheme.
Upon re-adding the individual user in the notification scheme, the notifications work again, but the user shows up as a duplicate entry in the scheme.
Diagnosis
Execute the following query and in case it returns any thing, follow the steps of the resolution section:
select * from notification N1, notification N2
where N1.id < N2.id
and N1.scheme = N2.scheme
and (N1.event = N2.event or (N1.event is null and N2.event is null))
and N1.event_type_id = N2.event_type_id
and (N1.template_id = N2.template_id or (N1.template_id is null and N2.template_id is null))
and N1.notif_type = N2.notif_type
and lower(N1.notif_parameter) = lower(N2.notif_parameter);
Cause
This issue is caused by database not configured with a supported collation. JIRA needs to use a case-sensitive collation (e.g. for MySQL the database needs to be created using utf8_bin).
Solution
Alter your database collation to case-sensitive and execute the following queries against your JIRA database to update the entries to the new casing format:
Create a database backup before running any query.
Shutdown JIRA.
Create a temporary table to store the IDs of the entries that we need to remove from the database:
-- For most databases use this query: CREATE TEMPORARY TABLE notification_fix AS select N1.id from notification N1, notification N2 where N1.id < N2.id and N1.scheme = N2.scheme and (N1.event = N2.event or (N1.event is null and N2.event is null)) and N1.event_type_id = N2.event_type_id and (N1.template_id = N2.template_id or (N1.template_id is null and N2.template_id is null)) and N1.notif_type = N2.notif_type and lower(N1.notif_parameter) = lower(N2.notif_parameter); -- For Oracle use the following query: CREATE GLOBAL TEMPORARY TABLE notification_fix AS select N1.id from notification N1, notification N2 where N1.id > N2.id and N1.scheme = N2.scheme and (N1.event = N2.event or (N1.event is null and N2.event is null)) and N1.event_type_id = N2.event_type_id and (N1.template_id = N2.template_id or (N1.template_id is null and N2.template_id is null)) and N1.notif_type = N2.notif_type and lower(N1.notif_parameter) = lower(N2.notif_parameter);Remove the duplicated entries:
delete from notification where id in (select id from notification_fix);Remove the temporary table:
DROP TABLE notification_fix;Set the entries into the notification table to lower case:
update notification set notif_parameter = lower(notif_parameter) where notif_parameter not in (select lower_user_name from cwd_user) and lower(notif_parameter) in (select lower_user_name from cwd_user);Restart JIRA.
Was this helpful?