-
Bug
-
Resolution: Fixed
-
Low
-
7.13.5, 8.5.8
-
19
-
Severity 3 - Minor
-
24
-
Problem
Purging trash for a space fails with an exception due to watchers on an old version of a page.
Steps to Reproduce
- View an old version of a page
- Choose "Watch" from the tools menu
- View the current version of that page
- Remove that page
- Go to space admin and purge the trash
Expected Results
The page should be deleted.
Actual Results
The purge fails due to a foreign key constraint on the notifications table.
The atlassian-confluence.log will show an error similar to the following:
2022-12-22 09:58:36,151 ERROR [http-nio-8090-exec-18] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: update or delete on table "content" violates foreign key constraint "fk_notifications_content" on table "notifications" Detail: Key (contentid)=(1572881) is still referenced from table "notifications". -- space: 131074 | url: /pages/doemptytrash.action | traceId: aa893098bb91568c | userName: admin | referer: https://<BASE_URL>/pages/emptytrash.action?key=TEST | action: doemptytrash 2022-12-22 09:58:36,158 ERROR [http-nio-8090-exec-18] [core.persistence.hibernate.HibernateObjectDao] unIndex Unable to index object: page: Page watcher test v.3 (1572878) -- could not execute statement; SQL [n/a]; constraint [fk_notifications_content]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement -- space: 131074 | url: /pages/doemptytrash.action | traceId: aa893098bb91568c | userName: admin | referer: https://<BASE_URL>/pages/emptytrash.action?key=TEST | action: doemptytrash org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [fk_notifications_content]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:245) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:392) at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:351) at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:486) at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:426) at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.getLatestVersionsOfAttachmentsWithAnyStatus(AbstractHibernateAttachmentDao.java:163)
Workaround
Backup your database before attempting to perform this! (the following removes all notifications from all deleted pages in the space):
- Identify the SPACEID of the space which you are trying to purge all trash from, via this query:
SELECT * FROM SPACES;
- Use the SPACEID in the following query:
DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT WHERE (CONTENT_STATUS = 'deleted' AND SPACEID = <SPACEID>) OR PREVVER IN (SELECT CONTENTID FROM CONTENT WHERE CONTENT_STATUS = 'deleted' AND SPACEID = <SPACEID>));
- Run the query above, then try to purge the trash again
Notes
This issue was previously tracked under CONFSERVER-15964
[CONFSERVER-81514] Unable to purge the trash if notification for old version of a page is present
A fix for this issue is available in Confluence Server and Data Center 8.5.15.
Upgrade now or check out the Release Notes to see what other issues are resolved.
A fix for this issue is available in Confluence Server and Data Center 7.19.27.
Upgrade now or check out the Release Notes to see what other issues are resolved.