Not Able to Restore Confluence XML Backup Data Due to 'Duplicate entry' Error
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;