Confluence Bulk purge trash for all spaces not working

[Yousaf]

Hello,

the bulk purge trash feature does not work when I select it to go over all spaces. 

The following error appears:

Error: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

The actual error is much longer.

Can someone tell me what the problem here seems to be?

Thanks and best regards

[Shannon S]

Yousaf,

I'm not quite sure which bulk purge feature you're referring to. When a page is deleted in Confluence, it goes to the space's trash, where it can be purged there:

• Delete or Restore a Page

We don't yet have a way to bulk purge pages, but there's a feature request below for bulk-type operations:

• CONFSERVER-1131 Allow for bulk-type operations

Could you let me know if you installed an add-on or User Macro to do this? It'll help us to better understand what the issue is.

It will also help if you can give us more detail surrounding the error message you found. Can you include a few more lines from your logs about that error? Feel free to use Pastebin or something similar to share the rest of the errors.

Regards,

Shannon

[Casey Maynard]

I too am getting an error when I Purge All on a specific Space.  It errors out with

   ' ... 

Cause

org.springframework.orm.hibernate5.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:283)

caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
    at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:67)

Stack Trace:[hide]

org.springframework.orm.hibernate5.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
 at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:283)
 at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:368)
 at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:315)
 at com.atlassian.confluence.content.persistence.hibernate.PluginContentHibernateDao.findAllChildren(PluginContentHibernateDao.java:199)
 at com.atlassian.confluence.content.DefaultCustomContentManager.removeContentEntity(DefaultCustomContentManager.java:276)
 at com.atlassian.confluence.pages.DefaultTrashManager.deleteContentEntity(DefaultTrashManager.java:166)
 at com.atlassian.confluence.pages.DefaultTrashManager.access$100(DefaultTrashManager.java:27)
 at com.atlassian.confluence.pages.DefaultTrashManager$1.doInTransaction(DefaultTrashManager.java:145)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
 at com.atlassian.confluence.pages.DefaultTrashManager.deleteBlock(DefaultTrashManager.java:126)
 at com.atlassian.confluence.pages.DefaultTrashManager.emptyTrash(DefaultTrashManager.java:54)
 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:333)
 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:282)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at com.atlassian.confluence.util.profiling.ConfluenceMonitoringMethodInterceptor.invoke(ConfluenceMonitoringMethodInterceptor.java:34)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
 at com.sun.proxy.$Proxy183.emptyTrash(Unknown Source)

[Shannon S]

Hello Casey,

Happy to help you with this. Thank you so much for sharing your logs. It's very helpful.

I see the error you're encountering is this:

Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

The error indicates that Confluence is trying to delete a record(s) in the database that doesn't exist. This is due to a database integrity issue because there are records in the database that reference another record which is no longer there.

I'm aware of one bug that can cause this:

• CONFSERVER-57823 "Purge All" space trash fails with "org.hibernate.StaleStateException" due to Confluence's handling of CustomContentEntity

There are a few things on that bug that you can look at for troubleshooting. As a workaround, you can try purging each page individually if there aren't too many.

There's another workaround that's a bit more dangerous, but you can try it. Have a look here:

• How to clean programmatically old drafts and purge trash with python for Confluence?

If you're comfortable performing this task, I would just ask that you ensure you've made a full site backup before modifying your instance programmatically. 

If you want to try to solve the issue without deleting the pages individually or programmatically, then you'll want to perform the following tasks to obtain more specific logs:

1. Enable detailed SQL logging in Confluence.
2. Replicate the error by attempting to purge the affected space.
3. After you've replicated the error, disable SQL logging.

Your log files should then have extra information on why exactly the purge is failing. Please feel free to send that along if you wish to proceed in troubleshooting and we can help you from there with the proper SQL queries to use to remove the affected entry.

Let me know if you have any trouble!

Regards,

Shannon

[Casey Maynard]

There are over 2000 pages in one space, only 631 to purge in another, but it happens in all spaces.  I am not comfortable with using the Python option.
I turned on the SQL logging but that did not seem to change the output content.

Where can I send the output details?

Thanks

[Shannon S]

Casey,

Since the bug could be affected by a 3rd party add-on, you might want to try putting your instance in Safe Mode by disabling the add-ons temporarily. There's a chance that you might be able to purge it that way.

If you're not seeing any output after turning SQL logging on and running the error again, then something went wrong. When enabled properly, it will show the output of all of your SQL logging in your atlassian-confluence.log in your Server Logs.

I would recommend raising a ticket with the Support Team if you are eligible for support, if not, please let me know, and I can go ahead and raise that ticket for you.

Regards,

Shannon

[Casey Maynard]

Is there any way to determine which Add-on is creating the error?  We have several add-ons and disabling each one (so that I could determine which one cause the error and notify the Add-on creator) could take awhile.

[Shannon S]

Hi Casey,

You would be able to determine which add-on is causing it by reviewing the server logs, but you were having an issue earlier getting the SQL output to display, so I recommend raising a ticket with the Support Team for that.

If you want to test without reviewing the logs, then you'd want to disable all of the add-ons and enable them one at a time, until the issue occurs again.

It's probably easier to raise the ticket with support, so do let me know if you have any issues with that.

Regards,

Shannon