Unable to import XML after First Attempt Fails
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
Symptoms
After a failed attempt of importing a space in Confluence, if you try to import it again or even maybe another, it may fail yet again for a different reason: duplicate keys in the database. You may see something like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Caused by: net.sf.hibernate.exception.ConstraintViolationException: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=3112997,entityName=confluence_ContentEntityObject,key=create.blueprint.page.draft.request,type=6,boolean=false,string=<null>,text={"spaceKey":"SKEY","contentBlueprintId":"0ad9b54c-41d8-40bf-b60f-b57be266e63c","contentTemplateId":"","contentTemplateKey":"","title":"Setup SipP for Performance Testing","viewPermissionsUsers":"","parentPageId":491567,"moduleCompleteKey":null,"context":{"spaceKey":"SKEY","parentPageId":"491567","title":"Setup SipP for Performance Testing","labelsString":"sipp performance testing","jiraIssuesMacro":"","contentbylabelMacro":"<ac:structured-macro ac:name=\"contentbylabel\"><ac:parameter ac:name=\"showLabels\">false</ac:parameter><ac:parameter ac:name=\"max\">5</ac:parameter><ac:parameter ac:name=\"sort\">modified</ac:parameter><ac:parameter ac:name=\"reverse\">true</ac:parameter><ac:parameter ac:name=\"labels\">sipp performance testing</ac:parameter><ac:parameter ac:name=\"showSpace\">false</ac:parameter><ac:parameter ac:name=\"spaces\"><ri:space ri:space-key=\"SKEY\" /></ac:parameter><ac:parameter ac:name=\"type\">page</ac:parameter></ac:structured-macro>"}},int=0,double=0.0,long=0,date=<null>]]
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:62)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEntityPersister.java:1331)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:472)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2476)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2462)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2419)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:62)
... 32 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "os_propertyentry_pkey"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)This is due to duplicate keys being inserted in the os_propertyentry table in the database. If after Manually Deleting a Space you still have violations in this table, you can follow this KB.
Cause
Entries inserted in the os_propertyentry table in the database by a failed import prevent future imports of the same space.
Resolution
Warning
Please Back up the Database before performing any modifications to the database.
The 'delete' SQL commands generated should work on any database, but were only tested in PostgreSQL.
The bash script command will only run in Linux or Mac.
You don't need to run it on the same machine Confluence is running, though, because it only generates the deletes.
You will need to remove every entry in the os_propertyentry that the XML is trying to insert, so there is no primary key constraints broken. This table has a triple primary key, defined by 3 columns.
First, Manually Delete the Space.
Copy the following into a file and run it using a parameter the 'entities.xml' file in the import file to generate 'delete' SQL commands that remove the 'trash'.
generate_deletes.sh
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41#!/bin/bash # Created by Jaime Kirch da Silveira (Atlassian Cloud Support Engineer in Porto Alegre) on December 18th, 2014 ############################# USAGE ############################# # This script will search the entities.xml file from a Confluence backup for entries in the os_propertyentry table. # For every entry, it will create a 'delete' SQL command to remove this entry from the database # This is used to clean data form a previous failed import preventing this import. # If the previous failed import added entries to this table, some primary key constraints may cause the new import to fail. # More about it in this KB: https://confluence.atlassian.com/display/CONFKB/Unable+to+import+XML+after+First+Attempt+Fails if [ $# -ne 1 ]; then echo "Usage ./generate_deletes.sh <Confluence import entities.xml file>" exit 1 fi cat $1 | egrep '<composite-id><property name=\"entityName\" type=\"string\"><!\[CDATA\[.+\]\]></property>|<property name=\"entityId\" type=\"long\">[0-9]+</property>|<property name=\"key\" type=\"string\"><!\[CDATA\[.+\]\]></property>' | while read line do if [[ $line =~ '<composite-id><property name="entityName" type="string"' ]] then echo -n $line | sed "s/\<composite-id\>\<property name=\"entityName\" type=\"string\"\>\<\!\[CDATA\[/delete from os_propertyentry where entity_name = \'/g" | sed "s/\]\]><\/property>/\' and /g" fi if [[ $line =~ '<property name="entityId" type="long">' ]] then echo -n $line | sed 's/<property name=\"entityId\" type=\"long\">/entity_id = /g' | sed 's/<\/property>/ and /' fi if [[ $line =~ '<property name="key" type="string"' ]] then echo $line | sed "s/\<property name=\"key\" type=\"string\"\>\<\!\[CDATA\[/entity_key = '/g" | sed "s/\]\]\>\<\/property\>/';/g" fi doneRun the generated 'delete' SQL commands on the database to remove exactly the entries in the table that conflict with the ones your import is attempting to insert.
If you can't run the script, you may need to make it an executable first:
chmod a+x generate_deletes.sh
Was this helpful?