Accessing Confluence space errors out with DataIntegrityViolationException after import
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
You imported a space from another confluence site and you're seeing an error message when trying to load it:
This problem was identified in Confluence server and datacenter version 7.4.3. It could also affect other versions of Confluence.
The error in the UI can be found in the
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.
A duplicated entry either in the entities.xml file or database.
Always backup your data before performing any modifications to the database.
- Shut down Confluence
- Backup your database
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>';
- Change the <spacename> value to the actual space name you imported to list the attachments.
Get the ATTACHMENTID values and update the following query before running it:
delete from IMAGEDETAILS where ATTACHMENTID IN (1237943823, 1237943832, 1237944913);
Commit the changes and restart Confluence
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.