Not Able to Restore Confluence XML Backup Data Due to 'Duplicate entry' Error
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
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)Diagnosis
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";Cause
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.
Resolution
- 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;