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.

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
Last modified on Mar 30, 2016

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.