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. Notification helper shows they will not get notifications, although the user is listed in the notification scheme.

Upon re-add the same user notifications work again, but the user shows up as a duplicate entry as in this screenshot:

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).

Resolution

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:

1. Create a database backup before running any query;

2. Shutdown JIRA;

3. 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); 

4. Remove the duplicated entries:

   delete from notification where id in (select id from notification_fix);
   

5. Remove the temporary table:

   DROP TABLE notification_fix;
   

6. 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);
   

7. Restart JIRA.