Page update is failed due to duplicated records in USERCONTENT_RELATION table in Confluence Data Center
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
A specific user will have a problem with updating a specific page with below error message:
atlassian-confluence.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2022-05-31 11:12:33,123 ERROR [http-nio-8080-exec-2] [rest.api.model.ExceptionConverter] convertServiceException No status code found for exception, converting to internal server error :
-- referer: http://127.0.0.1:8080/pages/resumedraft.action?draftId=123456789&draftShareId=cd3c3946-aaaa-bbbb-b9ac-12c34abcf123& | url: /rest/api/content/403199 | traceId: abcd123f4e9d123 | userName: localadmin
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
at org.springframework.dao.support.DataAccessUtils.singleResult(DataAccessUtils.java:54)
at com.atlassian.confluence.internal.relations.dao.hibernate.User2ContentHibernateRelationDao.getRelationEntity(User2ContentHibernateRelationDao.java:58)
at com.atlassian.confluence.internal.relations.dao.hibernate.User2ContentHibernateRelationDao.getRelationEntity(User2ContentHibernateRelationDao.java:45)
at com.atlassian.confluence.internal.relations.DefaultRelationManager.removeRelation(DefaultRelationManager.java:59)
at com.atlassian.confluence.api.impl.service.relation.RelationServiceImpl.lambda$delete$1(RelationServiceImpl.java:152)
at com.atlassian.confluence.api.impl.service.relation.RelationServiceImpl.handlePotentialHibernateException(RelationServiceImpl.java:287)
at com.atlassian.confluence.api.impl.service.relation.RelationServiceImpl.delete(RelationServiceImpl.java:151)
...
at com.atlassian.confluence.relations.touch.TouchRelationSupportImpl.lambda$updateTouchRelation$2(TouchRelationSupportImpl.java:96)
at java.util.Optional.ifPresent(Optional.java:159)
at com.atlassian.confluence.relations.touch.TouchRelationSupportImpl.updateTouchRelation(TouchRelationSupportImpl.java:95)
at com.atlassian.confluence.relations.touch.TouchRelationSupportImpl.handleTouchRelations(TouchRelationSupportImpl.java:55)
at com.atlassian.confluence.api.impl.service.content.ContentServiceImpl.update(ContentServiceImpl.java:170)
...
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy218.update(Unknown Source)
at com.atlassian.confluence.api.impl.service.content.draft.SharedContentDraftServiceImpl.updateContent(SharedContentDraftServiceImpl.java:234)
at com.atlassian.confluence.api.impl.service.content.draft.SharedContentDraftServiceImpl.publishEditDraft(SharedContentDraftServiceImpl.java:163)
at com.atlassian.confluence.api.impl.service.content.draft.ContentDraftServiceImpl.publishEditDraft(ContentDraftServiceImpl.java:40)
...Other users can edit and save the page successfully.
Environment
Confluence 6.13.7
Diagnosis
Diagnosis Method 1 - SQL Logging
By enabling SQL logging, we can confirm the issue is caused by the USERCONTENT_RELATION table with below query:
1
2020-05-11 16:26:03,455 DEBUG [http-nio-8090-exec-65] [org.hibernate.SQL] logStatement select user2conte0_.RELATIONID as RELATIONID1_60_, user2conte0_.TARGETCONTENTID as TARGETCONTENTID2_60_, user2conte0_.SOURCEUSER as SOURCEUSER3_60_, user2conte0_.TARGETTYPE as TARGETTYPE4_60_, user2conte0_.RELATIONNAME as RELATIONNAME5_60_, user2conte0_.CREATIONDATE as CREATIONDATE6_60_, user2conte0_.LASTMODDATE as LASTMODDATE7_60_, user2conte0_.CREATOR as CREATOR8_60_, user2conte0_.LASTMODIFIER as LASTMODIFIER9_60_ from USERCONTENT_RELATION user2conte0_ where user2conte0_.RELATIONNAME=? and user2conte0_.SOURCEUSER=? and user2conte0_.TARGETCONTENTID=?The above query should return a single record for the user and page, but SQL exception occurred due to duplicated records as the table has below unique constraint:
Postgres
1
2
3
# \d+ USERCONTENT_RELATION
"u2c_relation_unique" UNIQUE CONSTRAINT, btree (targetcontentid, sourceuser, relationname)Diagnosis Method 2 - Direct SQL
Alternatively, run this SQL to check if any duplicates exist in the database:
1
2
3
select uc.* from USERCONTENT_RELATION uc inner join (select TARGETCONTENTID, SOURCEUSER, RELATIONNAME from USERCONTENT_RELATION group by TARGETCONTENTID, SOURCEUSER, RELATIONNAME having count(*) > 1) dupe
on uc.TARGETCONTENTID = dupe.TARGETCONTENTID and uc.SOURCEUSER = dupe.SOURCEUSER and uc.RELATIONNAME = dupe.RELATIONNAME
order by uc.TARGETCONTENTID, uc.SOURCEUSER, uc.RELATIONNAME;You are affected by this issue if the above SQL returns any rows.
You are not affected by this issue if the above SQL returns 0 rows.
Cause
How the duplicated records were created is unknown.
Solution
We can fix the issue by deleting the duplicated records.
Shutdown Confluence
⚠️ Backup the Confluence database
Confirm that there are duplicate rows to be deleted if this query returns any rows:
1 2 3select uc.* from USERCONTENT_RELATION uc inner join (select TARGETCONTENTID, SOURCEUSER, RELATIONNAME from USERCONTENT_RELATION group by TARGETCONTENTID, SOURCEUSER, RELATIONNAME having count(*) > 1) dupe on uc.TARGETCONTENTID = dupe.TARGETCONTENTID and uc.SOURCEUSER = dupe.SOURCEUSER and uc.RELATIONNAME = dupe.RELATIONNAME order by uc.TARGETCONTENTID, uc.SOURCEUSER, uc.RELATIONNAME;Run the following SQL to delete only the duplicate rows. Run the SQL that corresponds to your database engine:
For Postgres, MS SQL or Oracle
1delete from USERCONTENT_RELATION where RELATIONID in (select min(RELATIONID) as RELATIONID from USERCONTENT_RELATION group by TARGETCONTENTID, SOURCEUSER, RELATIONNAME having count(*) > 1);For MySQL
1 2 3 4create table TMP_UC_DELETE as select min(RELATIONID) as RELATIONID from USERCONTENT_RELATION group by TARGETCONTENTID, SOURCEUSER, RELATIONNAME having count(*) > 1; create index tu_cd on TMP_UC_DELETE(RELATIONID); delete from USERCONTENT_RELATION where RELATIONID in (select RELATIONID from TMP_UC_DELETE); drop table TMP_UC_DELETE;Check that there are no more duplicates:
1select TARGETCONTENTID, SOURCEUSER, RELATIONNAME, count(*) from USERCONTENT_RELATION group by TARGETCONTENTID, SOURCEUSER, RELATIONNAME having count(*) > 1;If duplicates still show up in the above SQL, repeat the SQL in Step (4) until there are no more duplicates remaining from the above SQL check.
Start Confluence
Try editing the page again
Was this helpful?