Same notification received several time

[franck-boullier]

The test:

In the prod environment, invite a user to a case

Expected result:

An email invitation is sent to the invited user

Actual result:

The same invitation is received multiple times

[kaihendry]

According to the logs, I am receiving the same event several times. I don't know why. Can you track the AuroraDB triggers?

As a workaround, I could perhaps test if I have seen the notification_id before https://github.com/unee-t/frontend/blob/c8d304366d5f08af0872194e5f899642bccf7d16/imports/api/rest/post-process-db-change-message.js#L17

[franck-boullier]

Trigger work fine: only one line is inserted in the SQL logs

[franck-boullier]

@nbiton random idea: the message was received 3 times, we use a 3 node cluster in Mongo, could this be related?

[kaihendry]

How does the trigger work, can you link to the relevant lines please?

[franck-boullier]

relevant lines

??? which lines This is a native SQL function. The script to create the trigger was

DELIMITER $$
CREATE
    TRIGGER `ut_prepare_message_case_invited` AFTER INSERT ON `cc` 
    FOR EACH ROW 
BEGIN
    # Clean Slate: make sure all the variables we use are properly flushed first
        SET @notification_type = NULL;
        SET @bz_source_table = NULL;
        SET @notification_id = NULL;
        SET @unique_notification_id = NULL;
        SET @created_datetime = NULL;
        SET @unit_id = NULL;
        SET @case_id = NULL;
        SET @case_title = NULL;
        SET @invitee_user_id = NULL;
        SET @case_reporter_user_id = NULL;
        SET @old_case_assignee_user_id = NULL;
        SET @new_case_assignee_user_id = NULL;
        SET @current_list_of_invitees_1 = NULL;
        SET @current_list_of_invitees = NULL;

    # We have a clean slate, define the variables now
        SET @notification_type = 'case_user_invited';
        SET @bz_source_table = 'ut_notification_case_invited';
        SET @notification_id = ((SELECT MAX(`notification_id`) FROM `ut_notification_case_invited`) + 1);
        SET @unique_notification_id = (CONCAT(@bz_source_table, '-', @notification_id));
        SET @created_datetime = NOW();
        SET @case_id = NEW.`bug_id`;
        SET @case_title = (SELECT `short_desc` FROM `bugs` WHERE `bug_id` = @case_id);
        SET @unit_id = (SELECT `product_id` FROM `bugs` WHERE `bug_id` = @case_id);
        SET @invitee_user_id = NEW.`who`;
        SET @case_reporter_user_id = (SELECT `reporter` FROM `bugs` WHERE `bug_id` = @case_id);
        SET @old_case_assignee_user_id = (SELECT `assigned_to` FROM `bugs` WHERE `bug_id` = @case_id);
        SET @new_case_assignee_user_id = (SELECT `assigned_to` FROM `bugs` WHERE `bug_id` = @case_id);
        SET @current_list_of_invitees_1 = (SELECT GROUP_CONCAT(DISTINCT `who` ORDER BY `who` SEPARATOR ', ')
            FROM `cc`
            WHERE `bug_id` = @case_id
            GROUP BY `bug_id`)
            ;
        SET @current_list_of_invitees = IFNULL(@current_list_of_invitees_1, 0);

    # We insert the event in the relevant notification table        
        INSERT INTO `ut_notification_case_invited`
            (`notification_id`
            , `created_datetime`
            , `unit_id`
            , `case_id`
            , `case_title`
            , `invitee_user_id`
            , `case_reporter_user_id`
            , `old_case_assignee_user_id`
            , `new_case_assignee_user_id`
            , `current_list_of_invitees`
            )
            VALUES
            (@notification_id
            , @created_datetime
            , @unit_id
            , @case_id
            , @case_title
            , @invitee_user_id
            , @case_reporter_user_id
            , @old_case_assignee_user_id
            , @new_case_assignee_user_id
            , @current_list_of_invitees
            )
            ;

    # We call the Lambda procedure to notify of the change
        CALL `lambda_notification_case_invited`(@notification_type
            , @bz_source_table
            , @unique_notification_id
            , @created_datetime
            , @unit_id
            , @case_id
            , @case_title
            , @invitee_user_id
            , @case_reporter_user_id
            , @old_case_assignee_user_id
            , @new_case_assignee_user_id
            , @current_list_of_invitees
            )
            ;
END;
$$

DELIMITER ;

[kaihendry]

Was hoping you would just link to the relevant code, so when it changes we roughly have a reference point.

[kaihendry]

To see the messages in production, check out https://ap-southeast-1.console.aws.amazon.com/cloudwatch/home?region=ap-southeast-1#logEventViewer:group=meteor;filter=Duplicate%20message

Perhaps you can see a pattern.

[franck-boullier]

Was hoping you would just link to the relevant code, so when it changes we roughly have a reference point

I am as certain as one can be that the issue is not in the SQL side of things: if there was a problem the same notification would appear several times in the table ut_notification_case_invited. This is not the case -> the SQL is behaving as expected here

BUT there is the following scenario which will appear as if the same message is sent several times:

• User is using the BZFE to send the invitation.
• User is selecting several user to invite to the case (user 1, 2 and 3)
• User changes several fields in the case (Solution, Deadline, Status)
• User clicks on the 'Submit' button on the BZFE

The following notification will be sent, all at the same time: 1- Notification to user 1 that he was invited to the case 2- Notification to user 2 that he was invited to the case 3- Notification to user 3 that he was invited to the case 4- Notification to users 1, 2, and 3 that the Solution has been updated for this case 5- Notification to users 1, 2, and 3 that the Deadline has been updated for this case 6- Notification to users 1, 2, and 3 that the Status has been updated for this case

This might be the reason why there seem to be duplicate notifications there...

I'm not sure what the ideal solution is here :thinking:

Is it possible to create an "on the fly" compilation when this happens? example: if the timestamp and the case id are the same, then I concat the 4 notification messages into a single notification like "message to invitee x and fields Solution, Deadline, Status have been updated"

Any thought/suggestion?

[kaihendry]

I think it's due to a lambda I should have removed https://github.com/unee-t/sns2mongo Just monitoring to check this is right.