Import Fails Due to Duplicate Entry Error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Summary
Symptoms
You receive an error in the Confluence UI similar to this:
The following appears in the
atlassian-confluence.log:
1
2
3
4
5
6
7
8
9
10
11
12
13
2012-04-13 09:44:40,457 ERROR [Long running task: Importing data] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Duplicate entry 'admin' for key 'USERNAME'
-- referer: http://localhost:8090/setup/setup-restore-local.action | url: /setup/setup-restore-local.action | userName: anonymous | action: setup-restore-local
2012-04-13 09:44:40,473 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities:
-- referer: http://localhost:8090/setup/setup-restore-local.action | url: /setup/setup-restore-local.action | userName: anonymous | action: setup-restore-local
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: MySQLIntegrityConstraintViolationException: Duplicate entry 'admin' for key 'USERNAME'
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:67)
at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:335)
at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:303)
...
Caused by: net.sf.hibernate.exception.ConstraintViolationException: could not insert: [com.atlassian.confluence.security.persistence.dao.hibernate.UserLoginInfo#1802246]
at com.atlassian.confluence.importexport.xmlimport.parser.BackupParser.endElement(BackupParser.java:53)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)Diagnosis
Run the following query:
1
select username from logininfo group by username having count(username) > 1;If it returns any rows, you are affected by this issue.
Cause
There are duplicate rows in the logininfo table that are blocking the restore.
Solution
Resolution
Stop Confluence
Backup your database
Run the following queries against your database:
1 2 3create temporary table deleteset as select username from logininfo group by username having count(username) > 1; delete from logininfo where username in (select username from deleteset);We highly recommend that you run any database queries against a test instance/database before attempting to do so on your production data.
Was this helpful?