Duplicate Jira tickets are created from the same incoming email due to incorrect default time stamp

Summary

This article describes a scenario where the Jira Mail Handler (configured in ⚙ > System > Incoming Mail) might create duplicate issues from the same incoming email, how to identify this scenario, and how to fix the issue.

The screenshot shows examples of tickets that are created during a short period of time, with the exact same summary, which corresponds to the subject of an incoming email that was fetched by the Jira Mail Handler:

⚠️ Note that this KB article should not be confused with the other KB article Duplicate Jira tickets are created from the same incoming email due to indexing errors, which addresses a different situation where duplicate tickets might be created from the same incoming email. If you do not find in the Jira logs the error mentioned in this KB article, then please refer to other KB article and check if you can find the error from that one.

Environment

• Jira Server/Data Center on any version from 8.0.0

• Jira is configured with a Mail Handler configured ⚙ > System > Incoming Mail to fetch incoming emails from a mail server and create new issues from them

Diagnosis

To verify that this KB article is relevant and applies to your situation, check the Jira application logs and look for the following error:

If this error is found, this KB article is relevant and you can move on to the Solution section.

Cause

This issue is happening because there is a custom field of date type which contains a default value with an invalid format stored in the Jira Database. In such case, the following will happen:

• Whenever a Jira Mail Handler tries to create a new ticket from an incoming email, the following happens:

  • the ticket is created

  • however, in the middle of the ticket creation, the error related to the invalid date format is thrown, and the mail handler fails to complete its execution and the email is not marked as READ in the mailbox

• Next time the mail handler is executed, since the last mail did not get mark as READ (due to the error), the mail handler processes again the same email, creates a new tickets, and fail to complete its execution

• The same symptoms repeat over and over, and duplicate tickets are created

While it is not entirely clear why some data type custom field might end up with an invalid default value (this has not be reproduced with a fresh vanilla Jira instance), we suspect that it might happened when some data is imported into Jira using 3rd party add-ons such as Configuration Manager for Jira (CMJ).

Solution

To fix this issue and prevent the Jira Mail Handler from creating duplicate tickets from any future incoming email, we need to:

• first, identify the date type custom field(s) which is/are configured with an invalid default value in the database

• then, fix the default value for this/these fields(s)

Identifying the problematic fields

This is the most difficult part of the resolution steps, since there is no easy/straightforward way to identify them.

The custom field default values are stored in the database table genericconfiguration, in the xmlvalue column

• Some example of valid default values are:

  • Example 1 (case where the option Use the current date as the default was selected for the field)

    1 2 3 |id |datatype |datakey|xmlvalue | |-----|------------|-------|----------------------------------------------------| |10200|DefaultValue|10501 |<sql-timestamp>0001-01-01 00:00:00.0</sql-timestamp>|

  • Example 2 (case where a specific date was selected)

    1 2 3 |id |datatype |datakey|xmlvalue | |-----|------------|-------|----------------------------------------------------| |10200|DefaultValue|10501 |<sql-timestamp>2023-01-17 00:00:00.0</sql-timestamp>|

• Some examples of invalid default values are:

  • Example 1 (case where the right tag <sql-timestamp> is there, but the date format is incorrect (the syntax contains the timezone, day, month in letter format, while it should not):

    1 2 3 |id |datatype |datakey|xmlvalue | |-----|------------|-------|-----------------------------------------------------------| |10200|DefaultValue|10501 |<sql-timestamp>Sat Jan 01 00:00:00 CET 2000</sql-timestamp>|

  • Example 2 (case where the date format is correct, but the <sql-timestamp> tag is missing:

    1 2 3 |id |datatype |datakey|xmlvalue | |-----|------------|-------|---------------------| |10200|DefaultValue|10501 |2023-01-17 00:00:00.0|

  • Example 3 (case where the date format is correct, but the tag is incorrect:

    1 2 3 |id |datatype |datakey|xmlvalue | |-----|------------|-------|----------------------------------------------------------------| |10200|DefaultValue|10501 |<someother-timestamp>2023-01-17 00:00:00.0</someother-timestamp>|

The first thing you can do is run the following SQL query, which will return the list of default values which have the <sql-timestamp> tag:

Check the xmlvalue column of the rows returned by this query, and try to identify the ones which don't have the right format.

If you cannot find any suspicious row, then it is possible that the invalid default values don't have the <sql-timestamp> tag, or don't have any tag. In such case, it will be necessary to run the following query to check the entire content of the table genericconfiguration, and then to check each row 1 by 1 to see if you can find a value that looks like a date, but that does not follow the right format:

Fixing the default value

Once you identified a problematic row, here are the steps to fix it.

Let's assume that you found the following problematic row in the table genericconfiguration:

Steps:

• Take the value from the datakey column found in that row, and replace it in the query below. Taking the example above, the value to use in the query is 10501:

  1 select * from fieldconfiguration where id in (10501);

• Check the result from this query, and fetch the fieldid value:

  1 2 3 |id |configname |description |fieldid |customfield| |-----|-----------------------------------|---------------------------------------|-----------------|-----------| |10501|Default Configuration for Some Date|Default configuration generated by JIRA|customfield_10301| |

  • In the example above, the value is customfield_10301, which means that the impacted field has the ID 10301.

• Go to the following Jira URL, after replacing <JIRA_BASE_URL> with the Jira Base URL, and XXXXX with the custom field ID:

  1 <JIRA_BASE_URL>/secure/admin/ConfigureCustomField!default.jspa?fieldId=customfield_XXXXX

  • Taking the example from above, the URL will be:

    1 <JIRA_BASE_URL>/secure/admin/ConfigureCustomField!default.jspa?fieldId=customfield_10301

• For the default value used by each context configured with this custom field, click on Edit Default Value and set a new default value from the Jira UI

  

• Repeat these steps for any other custom field that you've identified

• Send a new email to the mail server used by the Jira Incoming Mail Handler, and verify that:

  • the error found previously in the Jira logs is no longer thrown

  • duplicate tickets are no longer created from the new email