JIRA fails to complete XML backup or restore of AO tables

Summary

As part of JIRA Service Desk now, JIRA is storing the incoming mails in the database (done by the JEPP). Those are stored inside the AO_2C4E5C_MAILITEMCHUNK table.

JIRA is, sometimes, silently failing to import or export the active objects part of the backup.

Steps to Reproduce

1. Install JIRA;
2. Import the backup linked on the private comments.
3. JIRA will stop importing once it reaches 90%. Restart JIRA.
4. Navigate to Administration » System » Plugin Data Storage and verify what has been imported.

Expected Results

All entries in the active objects table would have been imported/exported.

Actual Results

• Exporting: JIRA creates the backup normally, but upon inspecting the activeobjects.xml file (using the grep below) not all entries are in the file.
• Importing: JIRA fails to import the data once it reaches 90%, but after restarting JIRA it seems to have imported correctly. Inspecting the Plugin Data Storage shows most tables as empty, and inspecting the activeobjects file shows all data.

This seems to be happening because JIRA runs out of memory due to import, but fails to log any errors or warnings.

Notes

To verify the data inside the activeobjects.xml backup, run:

grep -E  "data tableName=|<row>" activeobjects.xml | uniq -c

This will show how many rows were exported for each table. For example, when JIRA failed to export correctly, this was shown:

      1   <data tableName="AO_21D670_WHITELIST_RULES">
      3     <row>
      1   <data tableName="AO_2C4E5C_MAILCHANNEL">
      1     <row>
      1   <data tableName="AO_2C4E5C_MAILCONNECTION">
      1     <row>
      1   <data tableName="AO_2C4E5C_MAILGLOBALHANDLER">
      1   <data tableName="AO_2C4E5C_MAILHANDLER">
      5     <row>
      1   <data tableName="AO_2C4E5C_MAILITEM">
   3625     <row>
      1   <data tableName="AO_2C4E5C_MAILITEMAUDIT">
   3625     <row>
      1   <data tableName="AO_2C4E5C_MAILITEMCHUNK">
    691     <row>

It is possible to see what tables were (supposed to be) exported to the backup with:

grep "table name=" activeobjects.xml

Workaround

Follow our Increasing JIRA Memory guide to increase the memory available to JIRA, restart JIRA and try to import/export the backup again. If this doesn't work, please follow the instructions below:

For importing:
It is possible to remove the affected lines from the backup with:

1. Run:

   grep -En  "data tableName=" activeobjects.xml | grep -A1 "MAILITEMCHUNK" | cut -d":" -f1

   This will return two numbers.

2. use vim to open the activeobjects.xml and run:

   :num1,num2s;<string>.*</string>;<string></string>;g
   

   Where num1 and num2 are the numbers returned in the first step. e.g.:

   :45635,58649s;<string>.*</string>;<string></string>;g

3. save and exit vim;
4. zip the backup again and import.

This will return the body of the emails that are stored.

To export:

1. Turn off the Email Puller so that no new mail will be added to the database
   • JIRA Administration > System > Global Mail Settings
2. Wait at least 5 minutes so that the mail processor is able to process mail that is already in the database
3. Stop JIRA;
4. Run the following update in the database to delete the mail items:
   • Optional: backup this table using your database's native tools before running the update

     UPDATE "AO_2C4E5C_MAILITEMCHUNK" SET "MIME_MSG_CHUNK" = NULL;
     

5. Restart JIRA;
6. Export the data again.
7. Turn the Email Puller back on