Unable to import XML after First Attempt Fails

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

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.

  • Download and run the following script using a parameter the 'entities.xml' file in the import file to generate 'delete' SQL commands that remove the 'trash'.

generate_deletes.sh

  • Run 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

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.