Unable to delete a space due to foreign key constraints

Still need help?

The Atlassian Community is here for you.

Ask the community

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-19611 - Getting issue details... STATUS .

Resolution

(info) This issue has been fixed in Confluence 4.1.2.

(warning) The steps below are not applicable for Confluence 5.7 and later.

  1. 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);
  2. Restart Confluence and log in as an administrator.

  3. Navigate to Confluence Admin > Administration > Content Indexing and make sure Confluence isn't currently reindexing.
  4. Remove the space.

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.