Accessing Confluence space errors out with DataIntegrityViolationException after import
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
You imported a space from another confluence site and you're seeing an error message when trying to load it:
1
2
3
4
5
6
7
8
System Error
A system error has occurred — our apologies!
Cause
0051: unique constrain. Dataintegrity Violation Exettion: Hibernate operation: could not execute statement; sal-n/a; ORA-00001: unique constraint () violated; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint () violated at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java: 104)
caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint () at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
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 () violated at oracle.idbc.driver.T4CTT|oer11.processError(T4CTTIoer11.java:498)
Stack Trace:[hide]
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute statement: salun/a_0RA-00001: unique constraint XXXXXX violated; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint XXXXXX violatedEnvironment
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:
1
2
3
4
5
6
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) violatedThe relevant part of the error is that it tells us the query that hit the constraint error:
1
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.
Shut down Confluence
Backup your database
Run the following query to have the attachments from the IMAGEDETAILS table from the space we just imported:
1 2 3 4 5select 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:
1delete 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.
Was this helpful?