Summary

Purging a space trash throws an error in the UI as the following:

org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute statement

Environment

Confluence Server and Data Center 

Diagnosis

Under all spaces in Confluence, there's the Space Tools option in the sidebar. To clear a space trash, we need access to the space tools as a Space Admin. To clear a space trash in Confluence:

1. Click on the Space Tools option in the sidebar
2. Click on Content Tools
3. On the new page, click on the Trash tab
4. Clicking on Purge all or Purge a specific page throws an error in the UI:

Causeorg.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute statement; sql=n/a; Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`EXTRNLNKS`, CONSTRAINT `FK5v5lw9x88vm27rvubsc130njy` FOREIGN KEY (`CONTENTID`) REFERENCES `CONTENT` (`CONTENTID`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`EXTRNLNKS`, CONSTRAINT `FK5v5lw9x88vm27rvubsc130njy` FOREIGN KEY (`CONTENTID`) REFERENCES `CONTENT` (`CONTENTID`))
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`EXTRNLNKS`, CONSTRAINT `FK5v5lw9x88vm27rvubsc130njy` FOREIGN KEY (`CONTENTID`) REFERENCES `CONTENT` (`CONTENTID`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

The EXTRNLNKS table is related to a deprecated feature in Confluence 7.0.1 – Trackback. With the feature deprecation, the table no longer exists so it doesn't affect Confluence 7.0.1 or later versions. However, we had reports that for some unknown reason at the moment, this table wasn't dropped after an upgrade which may trigger this problem.

Cause

The root cause is a constraint violation in the EXTRNLNKS table with the CONTENT table via foreign key relation. The data integrity protection won't allow the data to be deleted via UI so it requires the removal of the offending entry in the database.

Solution

There are 2 solutions to this issue depending on the Confluence version.

Confluence 7.0.1 or later version

If this error is present in a newer version, the table still exists in the database, with the feature deprecation, this table is no longer necessary so it's possible to clear the table content.

1. Backup the database
2. Shutdown Confluence

3. Run the following query:

   delete from extrnlnks;

4. Restart Confluence
5. Go to the affected space(s) and purge the trash to confirm the issue solution.

All versions before Confluence 7

Due to the constraint violation error, the solution for this version is more delicate as we need to remove just the offending content and not the entire table data as the feature still exists before Confluence 7. It's best to contact Atlassian Support to assist you with this matter.