Accessing Confluence space errors out with DataIntegrityViolationException after import

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

 

Summary

You imported a space from another confluence site and you're seeing an error message when trying to load it:

Environment

This problem was identified in Confluence server and datacenter version 7.4.3. It could also affect other versions of Confluence.


Diagnosis

The error in the UI can be found in the atlassian-confluence.log file:

2021-03-31 15:25:27,793 ERROR [http-nio-8090-exec-368] [atlassian.confluence.servlet.ConfluenceServletDispatcher] sendError Could not execute action
 -- url: /confluence/display/TEST | traceId: 7e862b028a2118fa | userName: testuser
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute statement; sql=n/a; ORA-00001: unique constraint (database) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (database) violated

Caused by: Error : 1, Position : 0, Sql = insert into IMAGEDETAILS (HEIGHT, WIDTH, MIMETYPE, ATTACHMENTID) values (:1 , :2 , :3 , :4 ), OriginalSql = insert into IMAGEDETAILS (HEIGHT, WIDTH, MIMETYPE, ATTACHMENTID) values (?, ?, ?, ?), Error Msg = ORA-00001: unique constraint (database) violated

The relevant part of the error is that it tells us the query that hit the constraint error:

insert into IMAGEDETAILS (HEIGHT, WIDTH, MIMETYPE, ATTACHMENTID) values (:1 , :2 , :3 , :4 ), OriginalSql = insert into IMAGEDETAILS (HEIGHT, WIDTH, MIMETYPE, ATTACHMENTID) values (?, ?, ?, ?)

On some occasions, the variables binding didn't happen so we won't know the actual duplicated entry.


Cause

A duplicated entry either in the entities.xml file or database. 

Solution

Always backup your data before performing any modifications to the database.

  1. Shut down Confluence
  2. Backup your database
  3. Run the following query to have the attachments from the IMAGEDETAILS table from the space we just imported:

    select im.attachmentid, c.contenttype, c.title, s.spacename 
    from IMAGEDETAILS im
    join CONTENT c ON im.ATTACHMENTID=c.CONTENTID
    join SPACES s ON c.SPACEID=s.SPACEID
    where SPACENAME = '<spacename>';
  4. Change the <spacename> value to the actual space name you imported to list the attachments.
  5. Get the ATTACHMENTID values and update the following query before running it:

    delete from IMAGEDETAILS where ATTACHMENTID IN (1237943823, 1237943832, 1237944913);
  6. Commit the changes and restart Confluence

  7. Navigate to the space with the error

    Deleting the entries in the IMAGEDETAILS won't break the attachments. However, we do recommend re-uploading the attachments listed in the query to recreate the entries in the respective table. This table holds meta-data for image attachments.


Last modified on Apr 9, 2021

Was this helpful?

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