Symptoms

When performing a site XML backup, it doesn't complete and an error like the following appears in the application logs:

2020-06-12 23:01:32,956 ERROR [https-jsse-nio2-443-exec-23] [confluence.importexport.impl.AbstractXmlExporter] backupEntities Couldn't backup database data.
 -- url: /admin/dobackup.action | traceId: 476658cbce6d4360 | userName: admin | referer: https://xyz.com/admin/backup.action | action: dobackup
org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.atlassian.confluence.core.ContentEntityObject#106709098]

Or, another variation:

[DOCPRIV2:ERROR] LazyInitializer - Exception initializing proxy <net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exists: 2535,
of class: com.atlassian.confluence.core.ContentEntityObject>net.sf.hibernate.ObjectNotFoundException:
No row with the given identifier exists: 2535, of class: com.atlassian.confluence.core.ContentEntityObject
 at net.sf.hibernate.ObjectNotFoundException.throwIfNull(ObjectNotFoundException.java:24)
 at net.sf.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:1946)
 at net.sf.hibernate.proxy.LazyInitializer.initialize(LazyInitializer.java:53)
 at net.sf.hibernate.proxy.LazyInitializer.initializeWrapExceptions(LazyInitializer.java:60)
 at net.sf.hibernate.proxy.LazyInitializer.getImplementation(LazyInitializer.java:164)
 at net.sf.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:108)
 at com.atlassian.confluence.core.ContentEntityObject$$EnhancerByCGLIB$$cc2f5557.hashCode(<generated>)
 at java.util.HashMap.hash(HashMap.java:261)
 at java.util.HashMap.containsKey(HashMap.java:339)
 at com.atlassian.confluence.importexport.impl.XMLDatabinder.toGenericXML(XMLDatabinder.java:155)

Cause

One or more records exist in the database in which there's a reference to an ID value that violates a foreign key constraint.  For example, a record in the CONTENT table might include a page ID value for which there is no record with a matching content ID.  This can be caused by a missing table constraint, corrupt internal data or corrupt data imported from a Cloud instance. 

Resolution

Instructions to fix known issues

The following are knowledge base articles that have identified known causes for this error:

• Upgrade Fails Due to Foreign Key Constraint in the PAGETEMPLATES Table
• Upgrade fails due to a constraint violation on BODYCONTENT
• Unique Constraint Violation Due to Value too Large for Column

If the issue is not related to one of the above

If the articles above do not pertain to the issue at hand or resolve it, we'll need to manually identify the cause. To work out where the data corruption exists, our approach will be to:

• increase the information logged during backup
• edit the invalid database entry(ies)

Additionally, please check that the correct transaction isolation level is set if MySQL or SQL Server is in use.

To proceed:

1. Stop Confluence.
2. If you have an external database, use a database administration tool to create a manual database backup.
3. Backup your Confluence home directory as this, with the database backup, can be used to restore the whole site.

4. Open the <Confluence Install>/confluence/WEB-INF/classes/log4j.properties and add these lines to the bottom of the file and save:

   log4j.logger.com.atlassian.hibernate.extras.XMLDatabinder=DEBUG, confluencelog
   log4j.additivity.com.atlassian.hibernate.extras.XMLDatabinder=false

5. (optional) Locate the <confluence-home-folder>/logs/atlassian-confluence.log.  Move or delete all existing Confluence logs to make it easier to find the relevant logging output.
6. Restart Confluence and login.
7. Start a site backup so that the error reoccurs.
8. Now, check the <confluence-home-folder>/logs/atlassian-confluence.log file to find out what object could not be converted into XML format by opening it and scrolling towards the bottom.

9. Prior to the error that causes the backup to fail, there should be a large number of DEBUG messages indicating that objects are being written.

10. Following these, in newer versions of Confluence, there should be a line like the following indicating the error. We'll call this Message Type 1:  

    2020-06-15 15:30:25,981 DEBUG [https-jsse-nio2-443-exec-14] [atlassian.hibernate.extras.XMLDatabinder] writeObject Writing object: com.atlassian.confluence.security.ContentPermissionSet with ID: 106201164 to XML.
    2020-06-15 15:30:27,490 ERROR [https-jsse-nio2-443-exec-14] [confluence.importexport.impl.AbstractXmlExporter] backupEntities Couldn't backup database data.
     -- url: /admin/dobackup.action | traceId: 2b57c89803a43474 | userName: admin | referer: https://xyz.com/admin/backup.action | action: dobackup
    org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.atlassian.confluence.core.ContentEntityObject#106708994]

11. In other versions of Confluence, you may messaging similar to this.  We'll call this Message Type 2:

    2005-08-24 00:00:33,743 DEBUG [DOCPRIV2:confluence.importexport.impl.XMLDatabinder] Writing object:
    com.atlassian.confluence.core.ContentPermission with ID: 5 to XML.
    2005-08-24 00:00:33,743 DEBUG [DOCPRIV2:confluence.importexport.impl.XMLDatabinder] Writing property: type
    2005-08-24 00:00:33,743 DEBUG [DOCPRIV2:confluence.importexport.impl.XMLDatabinder] Writing property: group
    2005-08-24 00:00:33,743 DEBUG [DOCPRIV2:confluence.importexport.impl.XMLDatabinder] Writing property: expiry
    2005-08-24 00:00:33,743 DEBUG [DOCPRIV2:confluence.importexport.impl.XMLDatabinder] Writing property: content
    [DOCPRIV2:ERROR] LazyInitializer - Exception initializing proxy <net.sf.hibernate.ObjectNotFoundException:
    No row with the given identifier exists: 2535,
    of class: com.atlassian.confluence.core.ContentEntityObject>net.sf.hibernate.ObjectNotFoundException:
    No row with the given identifier exists: 2535, of class: com.atlassian.confluence.core.ContentEntityObject
    	at net.sf.hibernate.ObjectNotFoundException.throwIfNull(ObjectNotFoundException.java:24)
    	at net.sf.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:1946)
    	at net.sf.hibernate.proxy.LazyInitializer.initialize(LazyInitializer.java:53)
    	at net.sf.hibernate.proxy.LazyInitializer.initializeWrapExceptions(LazyInitializer.java:60)
    	at net.sf.hibernate.proxy.LazyInitializer.getImplementation(LazyInitializer.java:164)
    	at net.sf.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:108)
    	at com.atlassian.confluence.core.ContentEntityObject$$EnhancerByCGLIB$$cc2f5557.hashCode(<generated>)
    	at java.util.HashMap.hash(HashMap.java:261)
    	at java.util.HashMap.containsKey(HashMap.java:339)
    	at com.atlassian.confluence.importexport.impl.XMLDatabinder.toGenericXML(XMLDatabinder.java:155)

12. From here, our troubleshooting will take somewhat different paths depending on the type of messaging found.

Message Type 1 Troubleshooting

As explained earlier, the value referenced by the ObjectNotFoundException message is a child value without a corresponding parent value.  In the above example message, the value  106708994 doesn't appear to have an associated contentid value in the CONTENT table.  Finding the record(s) in question can be a bit tricky as the messaging doesn't clearly explain which tables are involved. 

To help find these records, execute the following queries against the Confluence database.  While these should help find the record(s) in question, they will also bring to light the extent to which these orphaned child values are a problem:

SELECT * FROM CONTENT c left join CONTENT c1 on c.PAGEID = c1.CONTENTID WHERE c.PAGEID is not null and c1.CONTENTID is null;
SELECT * FROM CONTENT c left join CONTENT c1 on c.PARENTID = c1.CONTENTID WHERE c.PARENTID is not null and c1.CONTENTID is null;
SELECT * FROM CONTENT c left join CONTENT c1 on c.PREVVER = c1.CONTENTID WHERE c.PREVVER is not null and c1.CONTENTID is null;
SELECT * FROM CONTENT c left join CONTENT c1 on c.PARENTCOMMENTID = c1.CONTENTID WHERE c.PARENTCOMMENTID is not null and c1.CONTENTID is null;
SELECT * FROM CONTENT c left join CONTENT c1 on c.PARENTCCID = c1.CONTENTID WHERE c.PARENTCCID is not null and c1.CONTENTID is null;

select * FROM LINKS left join CONTENT on LINKS.CONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM IMAGEDETAILS left join CONTENT on IMAGEDETAILS.ATTACHMENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM USERCONTENT_RELATION left join CONTENT on USERCONTENT_RELATION.TARGETCONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM NOTIFICATIONS left join CONTENT on NOTIFICATIONS.CONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM LIKES left join CONTENT on LIKES.CONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;

select * FROM CONTENT_RELATION left join CONTENT on CONTENT_RELATION.TARGETCONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM CONTENT_RELATION left join CONTENT on CONTENT_RELATION.SOURCECONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;

select * FROM CONTENT_PERM_SET left join CONTENT on CONTENT_PERM_SET.CONTENT_ID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM CONTENT_LABEL left join CONTENT on CONTENT_LABEL.CONTENTID = CONTENT.CONTENTID where CONTENT_LABEL.CONTENTID is not null and CONTENT.CONTENTID is null;

select * FROM CONTENTPROPERTIES left join CONTENT on CONTENTPROPERTIES.CONTENTID = CONTENT.CONTENTID where CONTENTPROPERTIES.CONTENTID is not null and CONTENT.CONTENTID is null;

select * FROM CONFANCESTORS left join CONTENT on CONFANCESTORS.DESCENDENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;
select * FROM CONFANCESTORS left join CONTENT on CONFANCESTORS.ANCESTORID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;

select * FROM BODYCONTENT left join CONTENT on BODYCONTENT.CONTENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;

select * FROM ATTACHMENTDATA left join CONTENT on ATTACHMENTDATA.ATTACHMENTID = CONTENT.CONTENTID where CONTENT.CONTENTID is null;

If a large number of records are returned by these queries, then that could indicate that a table constraint is missing.  In this case, a check of the table constraints would be needed and a support request should be created that includes the latest logs and query results.

Alternatively, if these queries return only one or several records, then the problem is most likely isolated and can be addressed on a one-off basis.  For instance, if the page ID values does not match any content ID in the CONTENT table and the records containing these values aren't accessible from the UI (by way of the page title or pageID, if present), then set the page ID values to NULL and then reattempt the backup.   If there are doubts as to how to proceed, create a support request and include the latest logs and query results.

 If any changes are made, be sure to note what was changed and make sure that a database backup is on hand.

Message Type 2 Troubleshooting

1. Open a DBA tool such as DbVisualizer and connect to your database instance. Scan the table names in the schema. You will have to modify a row in one of these tables.
2. To work out which table, open atlassian-confluence.log, check the first line of the exception. This says there was an error writing the ContentPermission object with id 5 into XML. This translates as the row with primary key 5 in the CONTENTLOCK table needs fixing. To work out what table an object maps to in the database, here's a rough guide:
   • Pages, blogposts, comments, attachments --> CONTENT table
   • More information can be found in the schema documentation
3. Now, find the primary key of the incorrect row in this table. In this case, you can check the first line and see that the row has a primary key of 5.
4. Each property is written to a column, so the last property that was being written has the incorrect value. The row being written to when the exception was thrown was CONTENT (line 5) with a value of 2535 (line 6). Now you know the column and value. This value 2535 is the ID of an entry that no longer exists.
5. Using a database administrative tool, log into the Confluence database.
6. Locate the row in the relevant table and correct the entry. 
7. Check other rows in the table for the default column value, which may be null, 0 or blank. Overwrite the invalid row value with the default.
8. Restart Confluence.
9. Attempt the backup again. If the backup fails and you are stuck, please create a support request with your latest logs.