How to detect unsaved changes in database

[Peter Scott]

We run a Confluence service with many users from other groups. We want to get produce reports of pages that have unsaved changes. I am trying to tell this from database queries.  We have collaborative editing enabled and are on Confluence Server 7.13.0.

When I create a brand new page and hit Publish, two rows are created in the CONTENT table, with CONTENTTYPE = 'Page' and VERSION=1. One has a null PREVVER and CONTENT_STATUS='current'. The other has PREVVER pointing to the first one and CONTENT_STATUS='draft'.

I do not understand why there is a draft record when no changes have been made; after hitting Publish the page is displayed, it is not bring edited. I have read https://confluence.atlassian.com/confkb/how-do-drafts-work-on-confluence-938043306.html and it does not seem to explain this observation.

I want to write a query that will return all pages with unsaved changes. When I make an unsaved change I can see the HIBERNATEVERSION increase on the draft record, but I can't find anything that tells me the changes are unsaved. I know that once the changes are saved there will be another current record created, but I can't tell whether the draft record has unsaved changes, and it appears that every page has a draft record. I can see that CONTENTPROPERTIES.PROPERTYNAME = 'sync-rev' changes when there's a content change, but that's still not enough. What does that value point to?

[Nic Brough -Adaptavist-]

Welcome to the Community!

I'm afraid you have a premise completely wrong here.  Whilst the database is the "source of truth" for your Confluence data, you absolutely cannot rely on the database as being, well, right.

You can only rely on the database being useful if you have shut down Confluence in an orderly way, allowing it to complete all the stuff it needs to do to get the database up to date.

Basically, stop reading the database.  It is not designed or intended for any of what you're doing with it, and hence you should not be using it this way.

I would want to ask why you want to get reports of pages with unsaved changes.  Of what use is that to your people?

[Peter Scott]

We have issues with users creating pages that cannot be edited; attempts to do so hang. Atlassian advises that this is because they have become too big and/or have unsaved changes and that the only remedy is to reduce page size. We are automating detection of pages larger than a threshold size to generate notifications in advance of possible breakage and I want to add detail of unsaved changes.

It now appears from my tests that the BODYCONTENT.BODY is equal between the latest draft version and the latest current version if there are no unsaved changes. I'm checking this against other pages.

[Nic Brough -Adaptavist-]

Ok, that makes sense, but you won't be able to use the database to do this.  Part of the "it's too large" problem is that the data is held in caches and memory, and hence won't be in the database while it's in this state.

The database will hold committed drafts, but not the ones you're having problems with!  The best you'll be able to do with the broken ones from the database is just know that there is a draft for a page, not what it is.

[Garret Duffy]

I am also looking for help on this item.  Basically I need to query JIRA tickets on whether this flag:is there or not.  Is this possible?  This is very important as I need to find those tickets and "save the changes" properly.

[Nic Brough -Adaptavist-]

Go to the list of drafts, all "unsaved changes" will be on that list, assuming they are over the regular save-time limit (Depending on what version you are on, that could be 30 seconds or 2 minutes by default, but your admins may have changed it)

[Garret Duffy]

To be clear, I am talking about JIRA.  I cannot find such a list.  Is there an attribute that I can query on to get this (in JIRA)?

[Nic Brough -Adaptavist-]

Jira doesn't have drafts or unsaved changes.  It displays that when you are in the middle of editing an issue, once you close the browser, it's gone.

[Garret Duffy]

OK, so it's a local flag only and presumably that's what I can't query on it, gotcha.