Cannot Create or Restore XML Backup Due to Primary Key Constraint
Atlassian recommends disabling the XML backup both for performance and reliability. Setting up a test server and the Production Backup Strategy is better done with an SQL dump. Upgrading Confluence is better done without the XML backup.
The one operation for which an XML backup is required is database migration. For this we recommend a commercial database migration tool. Vote for Cannot Create or Restore XML Backup Due to Primary Key Constraint to add a more robust strategy for large implementation migrations. Atlassian does not support migrating to a new database.
Either during creation or restoration of an XML Backup, the logs contain an error 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'.
There are two possible causes for this issue:
Cause #1: Database collation
You may be moving from a case-sensitive collation database to a case-insensitive collation database. Case-sensitive collation means that 'jsmith' is NOT the same as 'Jsmith.' In this case, a database may allow two rows that have different capitalizations of 'jsmith' into the database.
In case-insensitive collation, 'jsmith' is the same as 'Jsmith', since case doesn't matter. That means if the column where 'jsmith' is stored is a primary key, you'll receive a duplicate key exception if you try to insert 'Jsmith'.
Cause #2: Duplicate entries
This indicates that there are duplicate entries in the OS_PROPERTYENTRY table, despite the existence of the primary key constraint PK_OS_PROPERTYENTRY_314D4EA8 (or possibly, due to a missing primary key). To diagnose that further, run the following diagnostic query:
SELECT entity_name,entity_id,entity_key,COUNT(*) FROM OS_PROPERTYENTRY GROUP BY entity_name,entity_id,entity_key HAVING COUNT(*)>1;
For Cause #1:
Ensure that you're moving to the same database collation.
For Cause #2:
Using the SQL query above, locate the duplicate entries and remove them, which will ensure that the primary key remains unique.
If the primary key is actually missing on the OS_PROPERTYENTRY table, you will need to add it after removing the duplicate entries.
ALTER TABLE os_propertyentry ADD CONSTRAINT os_propertyentry_pkey PRIMARY KEY (entity_name, entity_id, entity_key);
For MS SQL Server:
ALTER TABLE [dbo].[OS_PROPERTYENTRY] ADD PRIMARY KEY CLUSTERED ( [entity_name] ASC, [entity_id] ASC, [entity_key] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO