Issue Summary

If there are too many mails listed in the AO_2C4E5C_MAILITEM table with no corresponding data in the AO_2C4E5C_MAILITEMCHUNK table, the Jira Service Management (JSM) Mail Handler might either stop processing any  incoming email or process them with hours/days of delay.

Environment

Any JSM Server/Data Center version 4.0.0 and above.

Detailed Explanation of the bug

The JSM Mail Handler consists of 2 jobs:

• the Mail Puller job which fetches emails from the mailbox and stores them in the Jira Database and mark them as NEW. Such mails are stored in 2 tables:
  • the AO_2C4E5C_MAILITEM table, which lists all the fetched emails along with the status (NEW/PROCESSED/REJECTED/FILTERED)
  • the AO_2C4E5C_MAILITEMCHUNK table, which stores the full mail source (=content) of the fetched emails
• the Mail Processor job which processed the emails stored in the Database and marks them as PROCESSED (or REJECTED OR FILTERED) once they are processed
  • note that the Mail Processor job only processes 20 emails at a time whenever it is scheduled to run (every 1 min by default)

In some cases, the AO_2C4E5C_MAILITEMCHUNK table might not contain any row corresponding to the fetched emails. The exact reason why it might happen is unclear, but we suspect that this might happen when the Mail Puller job fails to successfully fetch the email from the mailbox and store it into the DB, due to some networking issue. This behavior is addressed in the other bug JSDSERVER-11799.

Here is the problem:

• the Mail Processor job can only process 20 emails at a time
• whenever there is a mail which has a row in the AO_2C4E5C_MAILITEM table, but no corresponding row in the table AO_2C4E5C_MAILITEMCHUNK, the Mail Processor job will not do anything with this email, but also it will not mark it as "PROCESSED". As a result, next time the Mail Processor job scans the AO_2C4E5C_MAILITEM table, it will try to process this email again

We might observe any of the 2 scenario below:

Scenario 1

Let's assume that there are 19 emails in the AO_2C4E5C_MAILITEM table marked as "NEW" which content is empty (no grows in AO_2C4E5C_MAILITEMCHUNK). Here is what will happen:

• whenever the mail processor is triggered, it will iterate over the 20 oldest emails in the AO_2C4E5C_MAILITEM table
• since first 19 emails are "empty", it will ignore them, and it will only process the 20th email (and mark it as PROCESSED), and then stop
• then, next time the mail processor is triggered, the same will happen: it will only process 1 email, because the first 19 emails are still marked as "NEW"
• as a result, only 1 single email will be processed during each execution
• because of that, if there are many emails pulled every day from the mail box, such emails might end up being processed with a delay of hours/days

Scenario 2

Now, let's assume that 20 or more emails are AO_2C4E5C_MAILITEM table marked as "NEW" which content is empty (no rows in AO_2C4E5C_MAILITEMCHUNK). Here is what will happen:

• the mail processor job will keep iterating over the same 20 "empty emails" at each execution
• as a result, no new email will ever be processed

Steps to replicate (for Scenario 1)

1. Install Jira Service Management (JSM) on any version 4.0.0 and higher
2. Create a JSM Project
3. Configure a JSM Mail Handler in this project in Project Settings > Email Requests
4. Send 19 emails to the mailbox configured with the mail handler
5. Quickly go to the Jira DB and delete all the rows in the table AO_2C4E5C_MAILITEMCHUNK (make sure to do it less than 1min after sending the emails)
6. Send 20 new emails to the mailbox
7. Wait for these emails to be processed

Expected behavior

Out of the 20 new emails, only 1 get processed whenever the Mail Processor is triggered (instead of having the 20 emails processed altogether).

As a result, processing the 20 emails takes a very long time.

Actual behavior

All the 20 new emails should be processed at once.

Steps to replicate (for Scenario 2)

1. Install Jira Service Management (JSM) on any version 4.0.0 and higher
2. Create a JSM Project
3. Configure a JSM Mail Handler in this project in Project Settings > Email Requests
4. Send 20 emails to the mailbox configured with the mail handler
5. Quickly go to the Jira DB and delete all the rows in the table AO_2C4E5C_MAILITEMCHUNK (make sure to do it less than 1min after sending the emails)
6. Send 20 new emails to the mailbox
7. Wait for these emails to be processed

Expected behavior

Any new incoming email is not processed

Actual behavior

Any new email should be processed.

Workaround

1. Run the following SQL query to fetch the IDs of the Mail Items (from the table AO_2C4E5C_MAILITEM) which have no corresponding rows in the table AO_2C4E5C_MAILITEMCHUNK:

   select * from "AO_2C4E5C_MAILITEM" emailItem LEFT OUTER JOIN "AO_2C4E5C_MAILITEMCHUNK" chunk ON emailItem."ID" = chunk."MAIL_ITEM_ID" WHERE chunk."MAIL_ITEM_ID" is NULL;
   

2. Update all these emails in the AO_2C4E5C_MAILITEM table by setting the STATUS column to PROCESSED in that table. This is to prevent the Mail Processor job from processing these email with null mailchunks. To do that, modify the SQL query below by listing all the IDs found in the previous query, and run it:

   UPDATE "AO_2C4E5C_MAILITEM" SET "STATUS" = 'PROCESSED' where "ID" in (<mailID1>,<mailID2>,<mailID3>,.......);

Note: all these SQL queries have been written and tested with a PostgreSQL database. For any other type of Database, it might be necessary to modify the syntax of these queries.