Jira table sequences are not created after XML dump restore when the source instance's database is MySQL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Database table sequences are not created after migration using the XML dump restore process. The below KB article explains about only the case when the source database from where the XML dump file is created is using MySQL.
Due to missing sequences, the identity columns of the database tables are not auto incremented and multiple actions on the Jira UI fail due to a unique constraint violation exception.
Environment
8.20.10
Diagnosis
After restoring the XML dump into the target Jira instance and after a restart, if you see the below error messages in the logs, Then verify the steps in the Diagnosis section to see if they match your issue.
When the target Jira instance is using postgres, then the error messages are displayed for multiple tables as below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_8542F1_IFJ_PRG_IN_PRG_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_60DB71_LEXORANK_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_C16815_ALERT_AO_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_60DB71_RAPIDVIEW_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_6FF49D_ANALYTICS_EVENT_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_E8B6CC_SYNC_AUDIT_LOG_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_E8B6CC_ORGANIZATION_MAPPING" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_E8B6CC_SYNC_AUDIT_LOG_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_E8B6CC_ORGANIZATION_MAPPING" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_E8B6CC_SYNC_AUDIT_LOG_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_E8B6CC_ORGANIZATION_MAPPING" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_4789DD_HEALTH_CHECK_STATUS_ID_seq" does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_E8B6CC_SYNC_AUDIT_LOG_ID_seq" does not existVerify the activeobjects.xml file from the source XML backup to check the autoincrement value for the above tables especially for primary key columns.
If you see that the autoincrement is set to false for primary key columns like below, then it confirms that the issue is coming from XML dump file of the source system. F
For the below XML data of the table "AO_4789DD_HEALTH_CHECK_STATUS", we can see that the autoincrement is set to false for column "ID". In ideal scenario the autoIncrement="true" should be set for ID column.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<table name="AO_4789DD_HEALTH_CHECK_STATUS">
<column name="APPLICATION_NAME" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
<column name="COMPLETE_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
<column name="DESCRIPTION" primaryKey="false" autoIncrement="false" sqlType="12" precision="1073741824"/>
<column name="FAILED_DATE" primaryKey="false" autoIncrement="false" sqlType="93" precision="29" scale="6"/>
<column name="FAILURE_REASON" primaryKey="false" autoIncrement="false" sqlType="12" precision="1073741824"/>
<column name="ID" primaryKey="true" autoIncrement="false" sqlType="4" precision="10"/>
<column name="IS_HEALTHY" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
<column name="IS_RESOLVED" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
<column name="NODE_ID" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
<column name="RESOLVED_DATE" primaryKey="false" autoIncrement="false" sqlType="93" precision="29" scale="6"/>
<column name="SEVERITY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
<column name="STATUS_NAME" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
</table>
Cause
If the source Jira instance from where the XML dump is created is using MySQL, check the dbconfig.xml file to verify if it has "useCursorFetch" enabled. When cursor fetch is used, it creates temporary tables in the database. During the XMLexport process, it results in tables that are missing auto-incrementing to be exported
1
<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&characterEncoding=UTF8&useSSL=false&useCursorFetch=true&defaultFetchSize=10000&useStreamingResults=false&allowPublicKeyRetrieval=true</url>Solution
Please make changes to the dbconfig.xml file from the source Jira instance that uses Mysql, to remove the below properties, and change it:
1
<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&characterEncoding=UTF8&useSSL=false&allowPublicKeyRetrieval=true</url>Restart the Jira instance.
Generate a new XML export file and use it to import into the new Jira instance.
Was this helpful?