症状

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;

ソリューション

原因 #1

Ensure that you're moving to the same database collation.

原因 #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.

For MySQL/PostGres/Oracle:

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