Import Fails Due to Duplicate Entry Error
Symptoms
- You receive an error in the Confluence UI similar to this:
- The following appears in the
atlassian-confluence.log:
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:
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.
Resolution
- Stop Confluence
- Backup your database
Run the following queries against your database:
create 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.
Last modified on Nov 12, 2018
Powered by Confluence and Scroll Viewport.