Details
-
Bug
-
Resolution: Unresolved
-
Low
-
None
-
7.1.1, 7.3.2
-
28
-
Severity 3 - Minor
-
4
-
Description
Issue Summary
Importing a space backup of Cloud instance fails violating unique constraint "unq_lwr_username"
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username" Detail: Key (lower_username)=(foo@bar.com) already exists.
This is replicable when
- The format of username in the backup is email address. e.g. foo@bar.com
- The instance already has both of following two users which has the same email address (foo@bar.com) in
- lower_user_name column and
- lower_email_address column
This issue also occurs when the target server/datacenter instance has different users with the same email address.
Steps to Reproduce
- Create a space export in Cloud instance (supposing it has a space creator foo@bar.com )
- Create a vanilla Confluence Server instance
- Add following users in Server instance
- which has username foo@bar.com and email address foo@bar.com
- which has username foo and email address foo@bar.com
- Import the Cloud export in step 1 into the Server instance
Expected Results
Space is imported successfully.
Also space creator are expressed as unknown user following Import a space from Confluence Cloud
Actual Results
Space import fails generating "unq_lwr_username" violation error on the screen.
The below exception is thrown in the atlassian-confluence.log file:
2020-03-27 00:28:59,953 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import -- url: /admin/restore.action | referer: http://confluence.atlassian.internal/admin/backup.action | traceId: a8157268b7640867 | userName: admin | action: restore com.atlassian.confluence.importexport.ImportExportException: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: duplicate key value v iolates unique constraint "unq_lwr_username" Detail: Key (lower_username)=(foo@bar.com) already exists. at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:407) at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:368) at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:169) at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:260) at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:36) at com.atlassian.confluence.importexport.xmlimport.RestorePluginStateStoreTransactionCallbackDecorator.doInTransaction(RestorePluginStateStoreTransactionCallbackDecorator.java:47) at com.atlassian.confluence.importexport.xmlimport.RestoreBandanaValuesTransactionCallbackDecorator.doInTransaction(RestoreBandanaValuesTransactionCallbackDecorator.java:50) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:222) at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:73) at com.atlassian.confluence.importexport.DefaultImportExportManager.performImportInternal(DefaultImportExportManager.java:118) at com.atlassian.confluence.importexport.DefaultImportExportManager.doPerformImport(DefaultImportExportManager.java:106) at com.atlassian.confluence.importexport.DefaultImportExportManager.performImport(DefaultImportExportManager.java:101) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy293.performImport(Unknown Source) at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:58) at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26) at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:39) at com.atlassian.confluence.impl.util.concurrent.ConfluenceExecutors$ThreadLocalContextTaskWrapper.lambda$wrap$1(ConfluenceExecutors.java:90) at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$doInRequestContext$0(VCacheRequestContextOperations.java:50) at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContextInternal(VCacheRequestContextManager.java:84) at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:68) at com.atlassian.confluence.vcache.VCacheRequestContextOperations.doInRequestContext(VCacheRequestContextOperations.java:49) at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$withRequestContext$2(VCacheRequestContextOperations.java:66) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username" Detail: Key (lower_username)=(foo@bar.com) already exists. at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImportInternal(DefaultXmlImporter.java:60) at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:42) at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:399) ... 38 more Caused by: org.xml.sax.SAXException: Error while importing backup: could not execute statement; sql=n/a net.sf.hibernate.exception.ConstraintViolationException: could not execute statement; sql=n/a at com.atlassian.confluence.importexport.xmlimport.parser.BackupParser.endElement(BackupParser.java:44) at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.XMLParser.parse(Unknown Source) at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source) at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source) at com.atlassian.security.xml.RestrictedXMLReader.parse(RestrictedXMLReader.java:103) at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.parseBackup(DefaultXmlImporter.java:86) at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImportInternal(DefaultXmlImporter.java:51) ... 40 more Caused by: net.sf.hibernate.exception.ConstraintViolationException: could not execute statement; sql=n/a at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162) at com.atlassian.hibernate.util.SessionHelper.flushAllowNoTransaction(SessionHelper.java:83) at com.atlassian.hibernate.adapter.adapters.session.SessionV2Adapter.flush(SessionV2Adapter.java:135) at com.atlassian.confluence.importexport.xmlimport.ImportProcessorContext.flushIfNeeded(ImportProcessorContext.java:223) at com.atlassian.confluence.importexport.xmlimport.ImportProcessorContext.objectImported(ImportProcessorContext.java:88) at com.atlassian.confluence.importexport.xmlimport.DefaultImportProcessor.persist(DefaultImportProcessor.java:44) at com.atlassian.confluence.importexport.xmlimport.DefaultImportProcessor.processObject(DefaultImportProcessor.java:34) at com.atlassian.confluence.importexport.xmlimport.parser.BackupParser.endElement(BackupParser.java:42) ... 52 more Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username" Detail: Key (lower_username)=(foo@bar.com) already exists. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:462) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3003) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3503) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) at com.atlassian.hibernate.util.SessionHelper.flushAllowNoTransaction(SessionHelper.java:80) ... 58 more
Workaround
Resolve the duplication in the Server instance beforehand.
In the above case, remove or rename the user which has username foo@bar.com