要約

Restoring a backup fails while restoring ActiveObjects and a "foreign key constraint" message.

The message may contain a reference to table AO_733371_EVENT, AO_733371_EVENT_PARAMETER, AO_733371_EVENT_RECIPIENT or some other, but the cause and resolution should be the same. The examples presented here are from the AO_733371_EVENT and AO_733371_EVENT_PARAMETER table.

See JRASERVER-73298 - Getting issue details... STATUS

環境

Jira Core: All versions 7 and 8.

Jira Software: All versions 7 and 8.

Jira Service Desk: All versions 3 and 4.

Problem Scenario 1

When restoring a backup into Jira, the restore executes for the most part but fails near the end with a message concerning ActiveObjects.

On the logs, we can see a message about violation of foreign key constraint. It's usually about the EVENT tables, with the EVENT_ID varying from instance to instance and from backup file to backup file, but you may find other tables referenced as well:

There was a problem restoring ActiveObjects data for the <unknown plugin> plugin. Caught exception with following message: ERROR: insert or update on table "AO_733371_EVENT_RECIPIENT" violates foreign key constraint "fk_ao_733371_event_recipient_event_id" Detail: Key (EVENT_ID)=(2276078) is not present in table "AO_733371_EVENT". Please check the log for details.

On the example above, it's event #2276078.

Problem Scenario 2

On the logs, we can see a message about violation of foreign key constraint for AO_733371_EVENT_PARAMETER table. 

2023-07-25 05:07:18,108+0000 JiraImportTaskExecutionThread-1 ERROR anonymous 963x314x1 8k4s8a W.X.Y.Z,A.B.C.D /secure/Setu
pImport.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore
com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with <unknown plugin>:Error 
executing update for SQL statement 'ALTER TABLE AO_733371_EVENT_PARAMETER ADD CONSTRAINT fk_ao_733371_event_parameter_event_id FOREIGN K
EY (EVENT_ID) REFERENCES AO_733371_EVENT(ID)'
        at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java
:26)
        at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:46)
        at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:37)
        at com.atlassian.activeobjects.backup.ActiveObjectsForeignKeyCreator.create(ActiveObjectsForeignKeyCreator.java:43)
        at com.atlassian.activeobjects.backup.ForeignKeyAroundImporter.after(ForeignKeyAroundImporter.java:26)
        at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:47)
        at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:70)
        at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:151)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:574)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:749)
        at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:323)
        at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.unsafeCall(DataImportAsyncCommand.java:82)
        at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:64)
        at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:30)
        at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:177)
        at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:149)
        at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)

原因

The backup generation doesn't stop Jira, so data is continually being written and updated by Jira while the backup's running. This leads to race conditions and the backup snapshot might have a partial data: like an event record without its corresponding event attributes.

ソリューション

There are two solutions to this scenario: edit the activeobjects.xml file inside the backup.zip or take a new backup during a time when Jira's under less usage — and less likely to be writing/updating so much data.

Schedule the backup to more quiet hours

This is the preferred solution. If you don't need the most recent backup possible, you may use one generated during a weekend. Or schedule the backup to more quiet hours, if possible.

Try the import again with this new backup and see if the "foreign key constraint" message shows no more.

Solution for Problem Scenario1

Edit the ActiveObjects.XML contained in the backup.zip

This approach is only advised if the Schedule the backup to more quiet hour isn't an option. Mind you may still receive further errors after fixing this one because — as the cause is — the backup was generated during intense activity in Jira.

例:

1. Extract the backup file, this will contain two xml files (activeobjects.xml and entitites.xml).
2. Open the ActiveObjects.xml in a text editor. In case the file is too large then split the file into smaller chunks.
3. Search for the table reference <data tableName="AO_733371_EVENT_RECIPIENT"> in the file.

4. Below the table you can find the rows that contain the data that causes the exception, for example:

   例

   <row>
       <string>mailEventConsumer</string>
       <timestamp>2020-06-05T08:31:20.430Z</timestamp>
       <integer>2276078</integer>
       <integer>1079085</integer>
       <timestamp>2020-06-05T08:40:39.421Z</timestamp>
       <string>NEW</string>
       <timestamp xsi:nil="true"/>
       <string>amanl</string>
   </row>

    The value in the line <integer>2276078</integer> that matches the event_id in the original exception.

5. Remove all rows that reference the event_id and save the xml file. Rows are shown in the xml as displayed in step 4, this entire section from <row> until the closing parenthesis </row> needs to be removed.
   1. You can even remove all rows from this table if needed- you will likely not need old email notifications on the restored Jira instance
6. In case you split the xml file make sure it is one single file again.
7. Zip the folder containing the two xml files.
8. Now try the import again by referring to the documentation on running an import.

Solution for Problem Scenario2

This approach is only advised if the Schedule the backup to more quiet hour isn't an option.

例:

1. Extract the backup file, this will contain two xml files (activeobjects.xml and entitites.xml).
2. Open the ActiveObjects.xml in a text editor. In case the file is too large then split the file into smaller chunks.
3. Search for the table reference <data tableName="AO_733371_EVENT_PARAMETER"> in the file.

4. Remove all entries of <row>... </row> until the closing parenthesis </data> for <data tableName="AO_733371_EVENT_PARAMETER">

5. Once all entries of <row>... </row> are deleted, save file and Zip the folder. 
6. Now try the import again by referring to the documentation on running an import.