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

Problem summary

Due to an issue with Confluence's handling of CustomContentEntity within the ContentPropertyService API, using the "Purge All" option in the Space Trash can fail with a org.hibernate.StaleStateException. This can affect spaces which contains a custom entity created by ContentPropertyService. An example is the Numbered Headings plugin, which uses this API.

Environment

1. Confluence 6.13.0 Server
2. Numbered Headings 5.2.2 for Server (link) – this is just an example, there may be other plugins impacted by this bug

Steps to reproduce

1. Create a page
2. In Edit mode, press the "Numbered Headings" button in the tool bar (screenshot: press_this.png)
   • Note: Whenever the "Numbered Headings" button is pressed, a row will be inserted in the CONTENT table which looks like this:

      contentid | hibernateversion | contenttype |         title         |      lowertitle       | version |             creator              |      creationdate       |           lastmodifier           |       lastmoddate       | prevver | content_status | pageid  | spaceid |                                       pluginkey                                       | pluginver |
     -----------+------------------+-------------+-----------------------+-----------------------+---------+----------------------------------+-------------------------+----------------------------------+-------------------------+----------------+---------+----------------+---------+---------+----------------+----------+-----------+---------------------------------------------------------------------------------------+-----------+
        1409026 |                2 | CUSTOM      | nl_avisi_nh           | nl_avisi_nh           |       1 | 2c94b0bb681b718701681b71f8510000 | 2019-01-17 18:05:04.472 | 2c94b0bb681b718701681b71f8510000 | 2019-01-17 18:05:04.472 |         | current        | 1409025 |  491521 | com.atlassian.confluence.plugins.confluence-content-property-storage:content-property | 6.13.0    |
     

3. Save the page (the page content doesn't matter)
4. Delete the page
5. Go to the space trash, and try to "Purge All".

Expected behavior

The space trash is purged without issue

Actual behavior

Stacktrace will be reproduced in the UI and in the logs. Snippet below, and Full stacktrace here: full_stacktrace.txt).

2019-01-17 18:05:46,086 ERROR [http-nio-8090-exec-10] [core.persistence.hibernate.HibernateObjectDao] unIndex Unable to index object: [com.atlassian.confluence.plugins.confluence-content-property-storage:content-property] custom: nl_avisi_nh v.1 (1409026) -- 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
 -- space: 491521 | url: /pages/doemptytrash.action | traceId: ccc8b7a19e645d3c | userName: admin | referer: http://confluence.example.com/pages/emptytrash.action?key=FOO | action: doemptytrash
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.executeWithNativeSession(HibernateTemplate.java:328)
	at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:524)
	at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:400)
	at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.getLatestVersionsOfAttachments(AbstractHibernateAttachmentDao.java:118)
	at com.atlassian.confluence.pages.attachments.CachingAttachmentDao.getLatestVersionsOfAttachments(CachingAttachmentDao.java:131)
	at com.atlassian.confluence.pages.DefaultAttachmentManager.getLatestVersionsOfAttachments(DefaultAttachmentManager.java:280)
	at com.atlassian.confluence.pages.DelegatorAttachmentManager.getLatestVersionsOfAttachments(DelegatorAttachmentManager.java:65)
	at com.atlassian.confluence.pages.CachingAttachmentManager.getLatestVersionsOfAttachments(CachingAttachmentManager.java:57)
...

Workaround

As a workaround, purge the offending page(s) individually from the space trash, which will then allow the "Purge All" option to work again.

Numbered Headings

In the case of Numbered Headings specifically, you can use this query to identify spaces/pages that may be affected:

SELECT s.spacekey, 
       c.contentid, 
       c.title, 
       c.contenttype, 
       c.content_status 
FROM   content c 
JOIN   spaces s 
ON     c.spaceid = s.spaceid 
WHERE  c.contentid IN 
       ( 
              SELECT pageid 
              FROM   content 
              WHERE  contenttype = 'CUSTOM' 
              AND    title = 'nl_avisi_nh' 
              AND    content_status = 'deleted');

Comala Workflows

In the case of Comala Workflows specifically, you can use this query to identify spaces/pages that may be affected:

select * FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted';

and then to manually delete the above pages, we need to first delete out the child dependent pages before we can delete the above pages using these steps:

1. Shutdown Confluence
2. Backup the Confluence database
3. Run these manual SQL to delete the affected pages (and their child pages)

   delete from CONTENTPROPERTIES where CONTENTID in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted');
   
   delete from BODYCONTENT where CONTENTID in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted');
   
   delete from CONTENTPROPERTIES where CONTENTID in (select CONTENTID from CONTENT where PREVVER in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted'));
   
   delete from BODYCONTENT where CONTENTID in (select CONTENTID from CONTENT where PREVVER in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted'));
   
   delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where PREVVER in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted'));
   
   delete from CONTENT where CONTENTID in (select CONTENTID FROM CONTENT where CONTENTTYPE = 'CUSTOM' and LOWERTITLE = 'comalaworkflows' and CONTENT_STATUS = 'deleted');
   

4. Startup Confluence
   • Navigate to Confluence Administration » General Configuration » Content Indexing
     • Rebuild the indexes to realign the Lucene index files from disk with the manual DB changes we made above
5. Navigate to any Space and attempt to purge out the Trash and it should succeed