Confluence 4.0 : Troubleshooting XML backups that fail on restore
This page last changed on Jun 29, 2011 by pwatson.
Seeing an error when creating or importing a site or space backup?
Resolve Errors When Attempting To Restore An XML BackupThe errors may be caused by a slightly corrupt database. You will need to find the XML backup file entry that is violating the DB rules, modify the entry and recreate the XML backup:
Troubleshooting "Duplicate Entry" for key "cp_" or "cps_"If you are encountering an error message such as: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: MySQLIntegrityConstraintViolationException: Duplicate entry '1475804-Edit' for key 'cps_unique_type' This indicates that the XML export came from a version of Confluence with a corrupt permissions database, caused by some 3rd party plugin. This is an issue that was fixed when CONF-22123 was implemented in Confluence 3.5.2. The simplest workaround is to export the space again after upgrading the instance to 3.5.2 or above. If that is not an option, then either the export will need to be edited manually to remove the duplicate permission entries or the source instance will need to have the offending entries removed. The following SQL queries can be used to look for such entries: SELECT * FROM CONTENT_PERM WHERE USERNAME IS NULL AND GROUPNAME IS NULL; SELECT cp.ID, cp.CP_TYPE, cp.USERNAME, cp.GROUPNAME, cp.CPS_ID, cp.CREATOR, cp.CREATIONDATE, cp.LASTMODIFIER, cp.LASTMODDATE FROM CONTENT_PERM cp WHERE cp.USERNAME IS NOT NULL AND cp.GROUPNAME IS NOT NULL; SELECT cps1.ID, cps1.CONTENT_ID, cps1.CONT_PERM_TYPE FROM CONTENT_PERM_SET cps1, CONTENT_PERM_SET cps2 WHERE cps1.ID <> cps2.ID AND cps1.CONTENT_ID = cps2.CONTENT_ID AND cps1.CONT_PERM_TYPE = cps2.CONT_PERM_TYPE ORDER BY cps1.CONTENT_ID, cps1.CONT_PERM_TYPE, cps1.CREATIONDATE ASC; SELECT cp.ID, cp.CP_TYPE, cps.CONTENT_ID, (SELECT scps.ID FROM CONTENT_PERM_SET scps WHERE scps.CONTENT_ID = cps.CONTENT_ID AND scps.CONT_PERM_TYPE = cp.CP_TYPE) AS suggested_cps_id FROM CONTENT_PERM cp, CONTENT_PERM_SET cps WHERE cp.CPS_ID = cps.ID AND cp.CP_TYPE <> cps.CONT_PERM_TYPE; SELECT DISTINCT cp1.ID, cp1.CP_TYPE, cp1.USERNAME, cp1.GROUPNAME, cp1.CPS_ID, cp1.CREATOR, cp1.CREATIONDATE, cp1.LASTMODIFIER, cp1.LASTMODDATE FROM CONTENT_PERM cp1, CONTENT_PERM_SET cps1, CONTENT_PERM cp2, CONTENT_PERM_SET cps2 WHERE cp1.CPS_ID = cps1.ID AND cp2.CPS_ID = cps2.ID AND cp1.ID <> cp2.ID AND cps1.CONTENT_ID = cps2.CONTENT_ID AND cp1.CP_TYPE = cp2.CP_TYPE AND cp1.USERNAME = cp2.USERNAME ORDER BY cp1.CPS_ID, cp1.CP_TYPE, cp1.USERNAME, cp1.CREATIONDATE; SELECT DISTINCT cp1.ID, cp1.CP_TYPE, cp1.USERNAME, cp1.GROUPNAME, cp1.CPS_ID, cp1.CREATOR, cp1.CREATIONDATE, cp1.LASTMODIFIER, cp1.LASTMODDATE FROM CONTENT_PERM cp1, CONTENT_PERM_SET cps1, CONTENT_PERM cp2, CONTENT_PERM_SET cps2 WHERE cp1.CPS_ID = cps1.ID AND cp2.CPS_ID = cps2.ID AND cp1.ID <> cp2.ID AND cps1.CONTENT_ID = cps2.CONTENT_ID AND cp1.CP_TYPE = cp2.CP_TYPE AND cp1.GROUPNAME = cp2.GROUPNAME ORDER BY cp1.CPS_ID, cp1.CP_TYPE, cp1.GROUPNAME, cp1.CREATIONDATE; SELECT * FROM CONTENT_PERM_SET WHERE ID NOT IN (SELECT DISTINCT CPS_ID FROM CONTENT_PERM); Remove all matching entries and perform the export again. Troubleshooting "Duplicate Key" related problemsIf you are encountering an error message such as: could not insert: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@a70067d3]; SQL []; Violation of PRIMARY KEY constraint 'PK_OS_PROPERTYENTRY314D4EA8'. Cannot insert duplicate key in object 'OS_PROPERTYENTRY'.; nested exception is java.sql.SQLException: Violation of PRIMARY KEY constraint 'PKOS_PROPERTYENTRY_314D4EA8'. Cannot insert duplicate key in object 'OS_PROPERTYENTRY'. This indicates that the Primary Key constraint 'PK_OS_PROPERTYENTRY_314D4EA8' has duplicate entries in table 'OS_PROPERTYENTRY'. SELECT ENTITY_NAME,ENTITY_ID,ENTITY_KEY,COUNT(*) FROM OS_PROPERTYENTRY GROUP BY ENTITY_NAME,ENTITY_ID,ENTITY_KEY HAVING COUNT(*)>1 Troubleshooting "net.sf.hibernate.PropertyValueException: not-null" related problemsIf you're receiving a message like: ERROR [Importing data task] [confluence.importexport.impl.ReverseDatabinder] endElement net.sf.hibernate.PropertyValueException: not-null property references a null or transient value: com.atlassian.user.impl.hibernate.DefaultHibernateUser.name This means there's an unexpected null value in a table. In the above example, the error is in the name column in the USERS table. We've also seen them in the ATTACHMENTS table. Remove the row with the null value, redo the xml export, and reimport. To Help Prevent this Issue from Recurring
RELATED TOPICSTroubleshooting failed XML site backups |
![]() |
Document generated by Confluence on Sep 19, 2011 02:38 |