Import MySQL XML backup to Postgres database introduce duplicate constraint error

XMLWordPrintable

    • 9.04
    • 6
    • Severity 3 - Minor
    • 2

      Issue Summary

      Importing MySQL XML backup to Postgres database introduces duplicate constraint errors:

      2024-02-02 13:26:36,187+0000 JiraImportTaskExecutionThread-1 ERROR admin 806x158x1 mlpezv 172.29.190.34,172.50.0.2 /secure/admin/XmlRestore.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 public."AO_319474_MESSAGE" ADD CONSTRAINT "fk_ao_319474_message_queue_id" FOREIGN KEY ("QUEUE_ID") REFERENCES public."AO_319474_QUEUE"("ID")'
	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26)
	at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:55)
...
Caused by: org.postgresql.util.PSQLException: ERROR: constraint "fk_ao_319474_message_queue_id" for relation "AO_319474_MESSAGE" already exists

      In the MySQL backup zip file, we can see the foreignKey constraints exported two duplicate lines for the PSMQ tables below:

        <table name="AO_319474_MESSAGE">
    <column name="CLAIMANT" primaryKey="false" autoIncrement="false" sqlType="12" precision="127"/>
    <column name="CLAIMANT_TIME" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="CLAIM_COUNT" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
    <column name="CONTENT_TYPE" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
    <column name="CREATED_TIME" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="EXPIRY_TIME" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="ID" primaryKey="true" autoIncrement="true" sqlType="-5" precision="19"/>
    <column name="MSG_DATA" primaryKey="false" autoIncrement="false" sqlType="-1" precision="2147483647"/>
    <column name="MSG_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="127"/>
    <column name="MSG_LENGTH" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="PRIORITY" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
    <column name="QUEUE_ID" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
    <column name="VERSION" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
    <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
    <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
  </table>

      This is reproducible on Data Center: (yes)

      Steps to Reproduce

      1. Setup Jira A connecting to the MySQL database.
      2. Install JSM application to the Jira A software instance.
      3. Create a new JSM project and update some issues.
      4. Export data to an XML backup zip file.
      5. Setup Jira B connecting to the Postgres database.
      6. Install JSM application to the Jira B software instance.
      7. Import XML backup from MySQL.

      Expected Results

      • The duplicate foreignkey entries below should be removed: 
            <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
    <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
      • There shouldn't be any issue when user switches MySQL to the Postgres database when importing using XML backup.

      Actual Results

      User are getting the following error message below when try to import XML backup exported from MySQL to the Postgres database:

      2024-02-02 13:26:36,187+0000 JiraImportTaskExecutionThread-1 ERROR admin 806x158x1 mlpezv 172.29.190.34,172.50.0.2 /secure/admin/XmlRestore.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 public."AO_319474_MESSAGE" ADD CONSTRAINT "fk_ao_319474_message_queue_id" FOREIGN KEY ("QUEUE_ID") REFERENCES public."AO_319474_QUEUE"("ID")'
	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26)
	at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:55)
...
Caused by: org.postgresql.util.PSQLException: ERROR: constraint "fk_ao_319474_message_queue_id" for relation "AO_319474_MESSAGE" already exists

      Workaround

      1. Extract two files from the backup zip file (e.g entities.xml and activeobjects.xml)
      2. Open activeobjects.xml file and remove the duplicate foreignkey lines below: 
            <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
    <foreignKey fromTable="AO_319474_MESSAGE" fromColumn="QUEUE_ID" toTable="AO_319474_QUEUE" toColumn="ID"/>
      • Save the changes and zip the two files into a single backup zip file.
      • Import the fix backup zip file.

            Assignee:
            Unassigned
            Reporter:
            John Chin (Inactive)
            Votes:
            7 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: