Confluence XML import fails on OS_PROPERTYENTRY table.

[invinci]

I have a situation where I am trying to import Confluence XML file generated by V 5.8.13 into my test environment V 5.10.3 and each time I end up getting an error at 49%.

Screenshot of Error:

And the Log file shows the following stack trace;

2018-01-05 04:45:02,174 ERROR [Long running task: Importing data] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Violation of PRIMARY KEY constraint 'PK__OS_PROPE__A5F9290554979C3A'. Cannot insert duplicate key in object 'dbo.OS_PROPERTYENTRY'. The duplicate key value is (confluence_ContentEntityObject, 23171441, confluence.inline.tasks.sequence.last).
 -- url: /admin/restore-local-file.action | referer: http://jiraconfapp31d:8090/admin/backup.action | traceId: a30ee766480ff268 | userName: 90012842 | action: restore-local-file
2018-01-05 04:45:02,174 ERROR [Long running task: Importing data] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
 -- url: /admin/restore-local-file.action | referer: http://jiraconfapp31d:8090/admin/backup.action | traceId: a30ee766480ff268 | userName: 90012842 | action: restore-local-file
2018-01-05 04:45:02,174 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities:
 -- url: /admin/restore-local-file.action | referer: http://jiraconfapp31d:8090/admin/backup.action | traceId: a30ee766480ff268 | userName: 90012842 | action: restore-local-file
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=23171441,entityName=confluence_ContentEntityObject,key=confluence.inline.tasks.sequence.last,type=5,boolean=false,string=29,text=<null>,int=0,double=0.0,long=0,date=<null>]]
 at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:69)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:394)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:363)
 at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:134)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:255)
 at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:34)
 at com.atlassian.confluence.importexport.xmlimport.RestorePluginStateStoreTransactionCallbackDecorator.doInTransaction(RestorePluginStateStoreTransactionCallbackDecorator.java:46)
 at com.atlassian.confluence.importexport.xmlimport.RestoreBandanaValuesTransactionCallbackDecorator.doInTransaction(RestoreBandanaValuesTransactionCallbackDecorator.java:46)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:217)
 at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:71)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.performImportInternal(DefaultImportExportManager.java:94)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.doPerformImport(DefaultImportExportManager.java:85)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.performImport(DefaultImportExportManager.java:80)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
 at com.sun.proxy.$Proxy128.performImport(Unknown Source)
 at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:87)
 at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
 at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:52)
 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:48)
 at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:82)
 at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:72)
 at com.atlassian.confluence.vcache.VCacheRequestContextOperations.doInRequestContext(VCacheRequestContextOperations.java:47)
 at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$withRequestContext$3(VCacheRequestContextOperations.java:64)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at java.lang.Thread.run(Thread.java:745)
Caused by: net.sf.hibernate.exception.ConstraintViolationException: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=23171441,entityName=confluence_ContentEntityObject,key=confluence.inline.tasks.sequence.last,type=5,boolean=false,string=29,text=<null>,int=0,double=0.0,long=0,date=<null>]]
 at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:62)
 at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
 at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEntityPersister.java:1331)
 at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:472)
 at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
 at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
 at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2476)
 at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2462)
 at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2419)
 at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
 at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:55)
 ... 40 more
Caused by: java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK__OS_PROPE__A5F9290554979C3A'. Cannot insert duplicate key in object 'dbo.OS_PROPERTYENTRY'. The duplicate key value is (confluence_ContentEntityObject, 23171441, confluence.inline.tasks.sequence.last).
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
 at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
 at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
 at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
 at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:462)
 ... 47 more
2018-01-05 04:45:03,449 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
 -- url: /admin/restore-local-file.action | referer: http://jiraconfapp31d:8090/admin/backup.action | traceId: a30ee766480ff268 | userName: 90012842 | action: restore-local-file
com.atlassian.confluence.importexport.ImportExportException: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=23171441,entityName=confluence_ContentEntityObject,key=confluence.inline.tasks.sequence.last,type=5,boolean=false,string=29,text=<null>,int=0,double=0.0,long=0,date=<null>]]
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:402)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEverything(BackupImporter.java:363)
 at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:134)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:255)
 at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:34)
 at com.atlassian.confluence.importexport.xmlimport.RestorePluginStateStoreTransactionCallbackDecorator.doInTransaction(RestorePluginStateStoreTransactionCallbackDecorator.java:46)
 at com.atlassian.confluence.importexport.xmlimport.RestoreBandanaValuesTransactionCallbackDecorator.doInTransaction(RestoreBandanaValuesTransactionCallbackDecorator.java:46)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:217)
 at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:71)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.performImportInternal(DefaultImportExportManager.java:94)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.doPerformImport(DefaultImportExportManager.java:85)
 at com.atlassian.confluence.importexport.DefaultImportExportManager.performImport(DefaultImportExportManager.java:80)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
 at com.sun.proxy.$Proxy128.performImport(Unknown Source)
 at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:87)
 at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
 at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:52)
 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:48)
 at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:82)
 at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:72)
 at com.atlassian.confluence.vcache.VCacheRequestContextOperations.doInRequestContext(VCacheRequestContextOperations.java:47)
 at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$withRequestContext$3(VCacheRequestContextOperations.java:64)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at java.lang.Thread.run(Thread.java:745)
Caused by: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=23171441,entityName=confluence_ContentEntityObject,key=confluence.inline.tasks.sequence.last,type=5,boolean=false,string=29,text=<null>,int=0,double=0.0,long=0,date=<null>]]
 at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:69)
 at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:394)
 ... 39 more
Caused by: net.sf.hibernate.exception.ConstraintViolationException: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=23171441,entityName=confluence_ContentEntityObject,key=confluence.inline.tasks.sequence.last,type=5,boolean=false,string=29,text=<null>,int=0,double=0.0,long=0,date=<null>]]
 at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:62)
 at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
 at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEntityPersister.java:1331)
 at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:472)
 at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
 at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
 at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2476)
 at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2462)
 at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2419)
 at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
 at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:55)
 ... 40 more
Caused by: java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK__OS_PROPE__A5F9290554979C3A'. Cannot insert duplicate key in object 'dbo.OS_PROPERTYENTRY'. The duplicate key value is (confluence_ContentEntityObject, 23171441, confluence.inline.tasks.sequence.last).
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
 at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
 at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
 at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
 

I have tried a custom import of the space initially to see if all the pages and attachments are going through and it worked out as expected, but when I tried deleting the space from my TEST env and re-import the space with all content it fails at 49%.

This LINK somewhat relates to the issue that I am facing now. 

I believe that manual deletion of rows related to key=confluence.inline.tasks.sequence.last from the DB is the only feasible solution at this point since there is no other way of finding and deleting them. Please advice!

How would I go ahead and resolve this issue? Any kind of help is appreciated at this point.

Thank You!

[Fabio Racobaldo _Herzum_]

Hi @invinci ,

please take a look to the following troubleshooting article https://confluence.atlassian.com/confkb/cannot-create-or-restore-xml-backup-due-to-primary-key-constraint-180294724.html

Hope this helps,

Fabio