Accessing Confluence space errors out with DataIntegrityViolationException after import
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
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 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.
原因
A duplicated entry either in the entities.xml file or database.
ソリューション
Always backup your data before performing any modifications to the database.
- Confluence をシャットダウンします。
- データベースのバックアップを取得します。
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.