Confluence daily backup failing

[Martin Boulanger]

I'm getting these nasty errors when a performing a daily backup (running the backup job) in Confluence.

However as soon as I unselect "Backup attachments" the error goes away.  So it must be related to attachement #4522021 (see below).  Is there any way to locate this attachment?

Any help would be appreciated!

2016-11-22 15:23:49,153 ERROR [scheduler_Worker-5] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ERROR: cannot execute UPDATE in a read-only transaction
2016-11-22 15:23:49,154 ERROR [scheduler_Worker-5] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
2016-11-22 15:23:49,154 WARN [scheduler_Worker-5] [persistence.dao.hibernate.AbstractHibernateAttachmentDao$IntraHibernateAttachmentCopier] copy Failed to flush session after copying attachment batch.
net.sf.hibernate.exception.GenericJDBCException: could not update: [com.atlassian.confluence.pages.Attachment#4522021]
at net.sf.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:80)
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
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.update(EntityPersister.java:690)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:630)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:52)
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:2420)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2288)
at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao$IntraHibernateAttachmentCopier.copy(AbstractHibernateAttachmentDao.java:734)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupAttachments(FileXmlExporter.java:162)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupEverything(FileXmlExporter.java:100)
at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.doExport(AbstractXmlExporter.java:88)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExportInternal(FileXmlExporter.java:61)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExport(FileXmlExporter.java:53)
at com.atlassian.confluence.importexport.DefaultImportExportManager.doExport(DefaultImportExportManager.java:124)
at com.atlassian.confluence.importexport.DefaultImportExportManager.exportAs(DefaultImportExportManager.java:94)
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:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy111.exportAs(Unknown Source)
at com.atlassian.confluence.importexport.impl.BackupJob.executeJob(BackupJob.java:72)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.surroundJobExecutionWithLogging(AbstractClusterAwareQuartzJobBean.java:66)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.executeInternal(AbstractClusterAwareQuartzJobBean.java:47)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at com.atlassian.scheduler.quartz1.Quartz1JobFactory$ClassLoaderProtectingWrappedJob.execute(Quartz1JobFactory.java:65)
at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$0(ConfluenceQuartzThreadPool.java:16)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:411)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:673)
... 33 more
2016-11-22 15:23:49,157 WARN [scheduler_Worker-5] [v2.c3p0.impl.NewPooledConnection] log [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2016-11-22 15:23:49,157 WARN [scheduler_Worker-5] [v2.c3p0.impl.NewPooledConnection] log [c3p0] Another error has occurred [ org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block ] which will not be reported to listeners!
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:350)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1048)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:432)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:341)
at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:408)
at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:284)
at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.getByIds(AbstractHibernateAttachmentDao.java:169)
at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao$IntraHibernateAttachmentCopier.copy(AbstractHibernateAttachmentDao.java:701)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupAttachments(FileXmlExporter.java:162)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupEverything(FileXmlExporter.java:100)
at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.doExport(AbstractXmlExporter.java:88)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExportInternal(FileXmlExporter.java:61)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExport(FileXmlExporter.java:53)
at com.atlassian.confluence.importexport.DefaultImportExportManager.doExport(DefaultImportExportManager.java:124)
at com.atlassian.confluence.importexport.DefaultImportExportManager.exportAs(DefaultImportExportManager.java:94)
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:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy111.exportAs(Unknown Source)
at com.atlassian.confluence.importexport.impl.BackupJob.executeJob(BackupJob.java:72)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.surroundJobExecutionWithLogging(AbstractClusterAwareQuartzJobBean.java:66)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.executeInternal(AbstractClusterAwareQuartzJobBean.java:47)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at com.atlassian.scheduler.quartz1.Quartz1JobFactory$ClassLoaderProtectingWrappedJob.execute(Quartz1JobFactory.java:65)
at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$0(ConfluenceQuartzThreadPool.java:16)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
2016-11-22 15:23:49,158 ERROR [scheduler_Worker-5] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-11-22 15:23:49,158 ERROR [scheduler_Worker-5] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ERROR: current transaction is aborted, commands ignored until end of transaction block
2016-11-22 15:23:49,160 ERROR [scheduler_Worker-5] [confluence.importexport.impl.BackupJob] executeJob Error while running the scheduled backup
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute query; uncategorized SQLException for SQL []; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.orm.hibernate.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:364)
at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:351)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:341)
at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:408)
at com.atlassian.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:284)
at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.getByIds(AbstractHibernateAttachmentDao.java:169)
at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao$IntraHibernateAttachmentCopier.copy(AbstractHibernateAttachmentDao.java:701)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupAttachments(FileXmlExporter.java:162)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupEverything(FileXmlExporter.java:100)
at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.doExport(AbstractXmlExporter.java:88)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExportInternal(FileXmlExporter.java:61)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExport(FileXmlExporter.java:53)
at com.atlassian.confluence.importexport.DefaultImportExportManager.doExport(DefaultImportExportManager.java:124)
at com.atlassian.confluence.importexport.DefaultImportExportManager.exportAs(DefaultImportExportManager.java:94)
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:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy111.exportAs(Unknown Source)
at com.atlassian.confluence.importexport.impl.BackupJob.executeJob(BackupJob.java:72)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.surroundJobExecutionWithLogging(AbstractClusterAwareQuartzJobBean.java:66)
at com.atlassian.confluence.setup.quartz.AbstractClusterAwareQuartzJobBean.executeInternal(AbstractClusterAwareQuartzJobBean.java:47)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at com.atlassian.scheduler.quartz1.Quartz1JobFactory$ClassLoaderProtectingWrappedJob.execute(Quartz1JobFactory.java:65)
at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool.lambda$runInThread$0(ConfluenceQuartzThreadPool.java:16)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:350)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1048)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:432)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
... 31 more

[J. Caldwell · Accepted solution]

So...don't rely on the XML backup for Confluence for your disaster recovery or your regular backups. There is too much that can go on and cause the backup to fail or be corrupt. Atlassian recommends separate database backups and filesystem backups. The above example of a problem is a great example of why you shouldn't rely on XML.

That being said...

select * from CONTENT where CONTENTTYPE = 'ATTACHMENT' and CONTENTID = '4522021';

will get you a page ID. From there, go look at the page and see if anything is obvious about the attachment. There's a possibility you have a broken link/orphaned attachment. 

[Martin Boulanger]

Thanks for you help.  This was exactly the problem.  I had about 25 deleted attachments (all gliffy files) that were deleted on the file system but were still referenced in the database.  I deleted these and my backups are working again!

[J. Caldwell]

Cool. But again...if it is production data and you care about it, you need to not rely on the XML backup approach. You need something really reliable and simple for backups. 

https://confluence.atlassian.com/doc/production-backup-strategy-38797389.html - Check out the second section. 

[Martin Boulanger]

Thanks for the link.  I will add this to my todo list.