Unable to delete a space due to foreign key constraints
Problem
When trying to delete a space, errors appear in the atlassian-confluence.log
similar to this:
2012-02-10 16:46:04,700 ERROR [http-80-27] [atlassian.confluence.servlet.ConfluenceServletDispatcher] sendError Could not execute action
-- url: /pages/dopurgetrashitem.action | userName: administrator | referer: http://example.com/pages/purgetrashitem.action?key=storage&contentId=295095
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not delete: [com.atlassian.confluence.pages.Attachment#558627]; SQL []; The DELETE statement conflicted with the REFERENCE constraint "FKA768048734A4917E". The conflict occurred in database "Confluence", table "dbo.IMAGEDETAILS", column 'ATTACHMENTID'.; nested exception is java.sql.SQLException: The DELETE statement conflicted with the REFERENCE constraint "FKA768048734A4917E". The conflict occurred in database "Confluence", table "dbo.IMAGEDETAILS", column 'ATTACHMENTID'.
Caused by: java.sql.SQLException: The DELETE statement conflicted with the REFERENCE constraint "FKA768048734A4917E". The conflict occurred in database "Confluence", table "dbo.IMAGEDETAILS", column 'ATTACHMENTID'.
Cause
The IMAGEDETAILS
table has entries that are preventing Confluence from removing the space. When a space is removed, Confluence flushes the index queue; however, if your site is in the middle of a full reindex, the entries can be added back after Confluence deletes them in order to remove the space. This issue is tracked in
-
CONF-19611Getting issue details...
STATUS
.
Resolution
This issue has been fixed in Confluence 4.1.2.
The steps below are not applicable for Confluence 5.7 and later.
Run the following SQL queries to remove the rows in the IMAGEDETAILS table.
Make sure you take a database backup before running any delete statements against your database. Also ensure that Confluence is not running when you perform these queries.
create temporary table deleteset as SELECT id.ATTACHMENTID FROM SPACES s JOIN CONTENT c ON s.SPACEID = c.SPACEID JOIN ATTACHMENTS a ON c.CONTENTID = a.PAGEID JOIN IMAGEDETAILS id ON a.ATTACHMENTID = id.ATTACHMENTID WHERE s.SPACEKEY = '<your_space_key>'; delete from IMAGEDETAILS where ATTACHMENTID in (select ATTACHMENTID from deleteset);
Restart Confluence and log in as an administrator.
- Navigate to Confluence Admin > Administration > Content Indexing and make sure Confluence isn't currently reindexing.
- Remove the space.