A space export contains multiple space objects and fails when importing into another Confluence instance
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Importing a space export file into Confluence Server or Cloud instance fails with a "Could not execute statement" or "Duplicate key"
error. The source of the space export could be from either another Confluence Datacenter/Server instance or a Cloud instance.
Environment
Confluence 6.x, 7.x and 8.x (and later)
Diagnosis
From within the atlassian-confluence.log
file, here's an example showing a violation of the space key constraint:
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema.
Cause: SQLServerException: Violation of UNIQUE KEY constraint 'UK_jp1ad5yufsih5r7lqrygakpug'. Cannot insert duplicate key in object 'dbo.SPACES'. The duplicate key value is (DEV).
at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:420)
at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:381)
DataCenter/Server Instances
If the space export was created from a DC/Server instance, then you can use the following query to confirm whether the DC/Server instance's database is affected by this issue:
SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID
UNION ALL
SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID
UNION ALL
SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID;
If the query only returns zeroes, your database is not affected by this particular issue. In this case, please contact Atlassian Support for help troubleshooting the export/import problem. If the query returns non-zero values, your database is affected.
Note that this query should be run against Confluence database from which the space was exported from, not the one you are attempting to import into.
Cloud Instances
When importing a singular space export from a Confluence Cloud instance, the export file should only contain one space object. To determine if it contains more than one space object, run the following command against the entities.xml file - to see this file, extract it out of the space export file:
|
If more than one result returns, then there are extra space objects contained in the export file that will need to be corrected prior to importing into a Confluence instance.
Cause
Different problems have been identified as contributors to this problem.
DataCenter/Server Instances
If the source of the space export was a Confluence DC/Server instance, the issue could have been caused by database inconsistencies as documented in CONFSERVER-45278 - Space import failing with the error message "could not execute statement". Another known issue relates to moving pages. This is fully described in CONFSERVER-77826 - Space id is not updated for custom objects when their containers are moved to another space which also includes queries that you can use to proactively check for and resolve instances of the problem prior to exporting a space.
Despite the previous bug was solved in some Confluence versions, there is still an unresolved bug related to this situation which points as well to database corruption/inconsistencies under: CONFSERVER-79111 - Space export may export multiple spaces
Cloud Instances
In regards to Confluence Cloud instnaces, there are several Cloud bugs which deal with the possibility that a Cloud space export may include content or space meta data from another space:
- CONFCLOUD-62976 - [Priority DoS] - Space export includes more than one space
- CONFCLOUD-64563 - Space export may export multiple spaces
- CONFDEV-62751 - A second follow-up for CONFCLOUD-64563 (Space export may export multiple spaces)
- CONFCLOUD-72415 - Space export may export multiple spaces (regression)
Resolution
For Issues Found within the Source Confluence Database
Once confirmed that the source instance's database is affected by this problem (see Diagnosis above), the inconsistencies will need to be fixed.
- Take a full backup of your database. We strongly recommend applying this fix in a staging or test environment first, before applying it to your production database. We used PostgreSQL and MySQL, but the SQL statements should work for any supported database.
Stop Confluence.
Execute the following SQL statements on your database.
If any non-zero values are returned, repeat this step, and execute the statements again.If only zeros are returned, the issues have been fixed, and you can move to the next step.
- Restart Confluence.
- Go to > General Administration > Cache management and flush the Content Objects cache.
You can now attempt to re-export the space from your Confluence DC/Server site and import it into another Confluence site.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
For Issues Found within the Cloud Space Export
There are a couple of options to mitigate this behavior.
Option 1: Renaming method with SED commands
The first option is aimed at renaming the extra, unwanted space key in the entities.xml
file to a space key that doesn't exist in the destination instance. The admin must be sure to elect a space key that doesn't exist in the destination instance, otherwise it will result in the same behavior. Additionally with this method, once the import completes, the admin will need to clean up the extra space at the conclusion of the operation, so it's especially important to maintain a list of any extra spaces that are imported for removal later.
Note that this example includes sed commands for both uppercase and lowercase values. Be sure to respect the capitalization as noted in the example to ensure non-corruption.
- Create a backup copy of the
entities.xml
file for safe keeping Edit the file and run the following commands replacing the values for OLDKEY and NEWKEY respectively.
sed -i -e
's/\[OLDKEY\]/\[NEWKEY\]/g'
entities.xml
sed -i -e
's/spaceKey=OLDKEY/spaceKey=NEWKEY/g'
entities.xml
sed -i -e
's/\[OLDKEY:/\[NEWKEY:/g'
entities.xml
sed -i -e
's/key=OLDKEY\]/key=NEWKEY\]/g'
entities.xml
sed -i -e
's/<spaceKey>OLDKEY<\/spaceKey>/<spaceKey>NEWKEY<\/spaceKey>/g'
entities.xml
sed -i -e
's/ri:space-key="OLDKEY"/ri:space-key="NEWKEY"/g'
entities.xml
sed -i -e
's/ri:space-key=OLDKEY/ri:space-key=NEWKEY/g'
entities.xml
sed -i -e
's/<ac:parameter ac:name="spaces">OLDKEY<\/ac:parameter>/<ac:parameter ac:name="spaces">NEWKEY<\/ac:parameter>/g'
entities.xml
sed -i -e
's/<ac:parameter ac:name="spaceKey">OLDKEY<\/ac:parameter>/<ac:parameter ac:name="spaceKey">NEWKEY<\/ac:parameter>/g'
entities.xml
sed -i -e
's/<property name="lowerDestinationSpaceKey"><!\[CDATA\[NEWKEY\]\]><\/property>/<property name="lowerDestinationSpaceKey"><!\[CDATA\[newkey\]\]><\/property>/g'
entities.xml
sed -i -e
's/<property name="lowerKey"><!\[CDATA\[NEWKEY\]\]><\/property>/<property name="lowerKey"><!\[CDATA\[newkey\]\]><\/property>/g'
entities.xml
sed -i -e
's/<property name="lowerKey"><!\[CDATA\[oldkey\]\]><\/property>/<property name="lowerKey"><!\[CDATA\[newkey\]\]><\/property>/g'
entities.xml
sed -i -e
's/spaceKey=OLDKEY/spaceKey=NEWKEY/g'
entities.xml
sed -i -e
's/spacekey=oldkey/spacekey=newkey/g'
entities.xml
- Find the file
exportDescriptor.properties
from the Cloud zip export directory and ensure that any renames for the spacekey values are also completed here. - Once the file edits have been completed, re-zip up the entire directory.
- Before retrying the import, be sure to clear up any extra stray data in the database using this documentation: After a failed space import, it's not possible to re-import because of leftover space data
Option 2: Regenerate new export file from a fresh, out of the box Confluence DC/Server install
In this method, rather than clean up the export file itself, we can import the file as is into a clean Confluence instance which will ensure that a duplicate space key error will be not be thrown on import. From there, we can generate a new export of the singular intended space key which will not contain any references to the extra space.
Be sure to stand up a clean instance on the same version of Confluence as the destination instance.
- Stand up a fresh out of the box Confluence instance.
- Import the Cloud export file as is and make sure it completes successfully.
Run the following SQL queries to check if the Confluence DC/Server instance is impacted as well:
SELECT COUNT(*) FROM CONTENT JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID WHERE CONTENT.SPACEID != xpage.SPACEID UNION ALL SELECT COUNT(*) FROM CONTENT JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID WHERE CONTENT.SPACEID != xpage.SPACEID UNION ALL SELECT COUNT(*) FROM CONTENT JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID WHERE CONTENT.SPACEID != xpage.SPACEID;
If any non-zero values are returned, follow resolution detailed under "For Issues Found within the Source Confluence Database"
- Head to Content Tools > Export of the desired space. Select XML > Full Export and create the new export file.
- Before retrying the import in the target instance, be sure to clear up any extra stray data in the database using this documentation: After a failed space import, it's not possible to re-import because of leftover space data
Workaround
If the previous actions did not solve the issue, you can use the workaround outlined in bug CONFSERVER-79111 - Space export may export multiple spaces
The interim workaround to ensure that the Space export contains only the desired Space is to try the following:
- Import the Space XML import into a temporary Confluence instance
- This will result in the multiple spaces imported
- From this Confluence instance, exclude the unnecessary spaces and export the desire space again out as XML
- This should result in just the Unique Space export in the newly created XML export file
- You can then take the newly created XML export file that should contain just the desire space to be imported into your desired Confluence instance