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

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.