Hi everyone,
just managed to fix our confluence database after a power outage.
Next to the database backup that runs on an hourly basis we also create regular nightly XML-backups at night, kind of as a last resort. Running through the logs I found out that the backup was failing on a regular basis. The log showed the following:
2018-08-09 02:07:44,550 ERROR [Caesium-1-1] [confluence.importexport.impl.AbstractXmlExporter] backupEntities Couldn't backup database data.
java.lang.RuntimeException: net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.atlassian.confluence.core.ContentEntityObject#34761337]
at com.atlassian.confluence.importexport.impl.HibernateObjectHandleTranslator.handleToObject(HibernateObjectHandleTranslator.java:57)
That made me wonder, why this Object with ID "34761337" was missing and why. I restarted the backup manually to find out why and where in detail it stopped working. Running through the unfinished backup in the /temp/xmlexport-<timestamp> dir I found out by chance that the last object being exported was a usercontent_relation object.
To make a long story short I found out that the entities missing were all created on the date of our power outage. I then recalled that due to other problems the database backup wasn't restored 100% and we had a couple of others of missing content.
Recalling that I figured that the id missing must belong to one of the pages that was created in between the last full backup and the outage. I checked that the appropriate content was actually missing and then checked up for further missing ids. Turned out that a total of 7 blogs / pages where missing and thus the usercontent_relation was pointing no where.
Solution:
I found this page https://confluence.atlassian.com/confkb/how-to-remove-a-page-manually-in-the-database-using-sql-commands-385321460.html on how to remove pages manually and also ran the following sql to get all the elements that needed to be removed:
SELECT * from USERCONTENT_RELATION where TARGETCONTENTID NOT IN (SELECT CONTENTID FROM CONTENT)
Once done the manual backup works now fine again.
As towards Atlassian: I was wondering whether it made sense to add this and probably even more checks to the health check that Confluence runs on a regular basis. This way problems can be tracked down much quicker.
Hope this also helps someone struggeling with the same issue.
