Unable to import space due to duplicate entry primary key constraint
When importing a space XML, you receive an error in the UI similar to:
Import failed. Check your server logs for more information. Hibernate operation: could not insert: com.atlassian.confluence.core.BodyContent#3146738; SQL ; Duplicate entry '3146738' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '3146738' for key 'PRIMARY'"
When checking the
atlasian-confluence.logyou see an error similar to:
2011-08-22 14:29:59,726 INFO [Importing data task] [confluence.importexport.actions.ImportLongRunningTask] runInternal Beginning import by user admin 2011-08-22 14:31:11,052 ERROR [Importing data task] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Duplicate entry 'confluence_ContentEntityObject-238421056-socialbookmarkingurl' for key 'PRIMARY'
This can occur when a space has been imported into Confluence, removed, and imported again (either a newer version of the space or the same XML backup that was initially imported; see below for details). The exception in the UI and the logs will vary. For the UI exception, the affected table and object ID may not match what you see. For the exception in the logs, the portion after 'confluence_ContentEntityObject-' will differ as the issue is not limited to socialbookmarkingurl.
When the space was exported, it was done so either by a user without space admin permissions, or by a space admin that did not select the Pages to Export: All option:
When the space is exported, content properties are included for pages that are not present in the export. When restored, the content properties are added to the
os_propertyentry table. When the space is removed later, the property entries are orphaned as there was no page referencing them, and the deletion process did not remove them. This is being tracked by - CONF-7324Getting issue details... STATUS .
Find all of the lines in the logs that have this message:
Duplicate entry 'confluence_ContentEntityObject-238421056-socialbookmarkingurl' for key 'PRIMARY'
Note the IDs following
confluence_ContentEntityObject-and insert them into the following delete query. You will need to run this query before and after the second space import:
Take a backup of your database before running any delete queries!
DELETE from OS_PROPERTYENTRY WHERE entity_name = 'confluence_ContentEntityObject' AND entity_id in (ID1, ID2, ..., IDN);