Not Able to Restore Confluence XML Backup Data Due to 'Duplicate entry' Error
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
症状
When restoring Confluence site backup from an XML backup data, the data restore process fails due to the com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'xxxxx' for the key 'PRIMARY' error. And the following exceptions are recorded in the atlassian-confluence.log file:
2013-04-10 10:43:31,170 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
-- referer: https://xwiki-tst.ad.xglobal-fra.com/xwikiuat/admin/backup.action | url: /xwikiuat/admin/restore-local-file.action | userName: xgadmin | action: restore-local-file
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.mail.notification.Notification#71532564]; SQL []; Duplicate entry '71532564' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '71532564' for key 'PRIMARY'
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:605)
[.....]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '71532564' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)診断
This problem is known to affect Confluence instances that are running on MySQL database. You can check the character set and collation of your Confluence database with following SQL query (Replace <confluencedb> with your own database name):
Use <confluencedb>
SHOW variables WHERE Variable_name = "character_set_database" or Variable_name = "collation_database";原因
A possible root cause of this problem is incorrect database collation. The problem is reproducible when Confluence database was created using case-insensitive collation, and according to Confluence Database Setup For MySQL, Confluence database needs to be created with utf8_bin collation which is a case-sensitive collation.
ソリューション
- As an attempt to fix the problem, we may create a new Confluence database using utf8_bin collation
The database can be created using the following SQL statement:
create database confluence character set utf8 collate utf8_bin;