Unable to Purge Trash, Unable to Successfully Delete Space, Page Watchers Are Not Visible After Upgrade to 5.3.x Because CONTENTID Column Is NULL (PAGEID Data Were Not Migrated To CONTENTID In NOTIFICATIONS Table)

Possibly there is an underlying bug in the upgrade task to 5.3.x when the database in use is MS SQL Server. In 5.3.x, PAGEID column should be replaced with CONTENTID instead in NOTIFICATIONS table, but for this case somehow both columns exist and the value in CONTENTID column is all NULL. Somehow the migration task did not migrate PAGEID data in NOTIFICATIONS table to CONTENTID column and drop the PAGEID column.

This causes three different issues so far:

1. Unable to purge a trash or any trash at all. The issue occurred after an upgrade to 5.3.x and the following appears in the atlassian-confluence.log:

   2013-10-22 13:33:13,681 ERROR [http-8090-2] [sf.hibernate.util.JDBCExceptionReporter] logExceptions The DELETE statement conflicted with the REFERENCE constraint "FK594ACC88C38FBEA". The conflict occurred in database "confluence", table "dbo.NOTIFICATIONS", column 'PAGEID'.
    -- url: /pages/dopurgetrashitem.action | userName: admin | referer: http://confluence:8090/pages/purgetrashitem.action?key=PLAY&contentId=30114153 | action: dopurgetrashitem
   

2. Space deletion seems successful however all users still able to access the deleted space (rebuilding the index didn't make any difference) and the following appears in the atlassian-confluence.log:

   2013-11-27 22:05:32,216 ERROR [Long running task: Space removal long running task] [atlassian.event.internal.AsynchronousAbleEventDispatcher] run There was an exception thrown trying to dispatch event 'com.atlassian.confluence.event.events.space.SpaceRemoveEvent[source=com.atlassian.confluence.spaces.DefaultSpaceManager@4e636bd6]' from the invoker 'SingleParameterMethodListenerInvoker{method=public void com.atlassian.confluence.plugins.createcontent.listeners.RemoveSpaceListener.onSpaceRemoveEvent(com.atlassian.confluence.event.events.space.SpaceRemoveEvent), listener=com.atlassian.confluence.plugins.createcontent.listeners.RemoveSpaceListener@779a9a3f}'.
    -- space: 17596426 | url: /conf/spaces/doremovespace.action | userName: admin | referer: http://localhost:8531/conf/spaces/removespace.action?key=AMP7 | action: doremovespace
   java.lang.RuntimeException: deleted object would be re-saved by cascade (remove deleted object from associations): 17596426, of class: com.atlassian.confluence.spaces.Space; nested exception is net.sf.hibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations): 17596426, of class: com.atlassian.confluence.spaces.Space
       at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:54)
    
   Caused by: net.sf.hibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations): 17596426, of class: com.atlassian.confluence.spaces.Space
       at net.sf.hibernate.impl.SessionImpl.forceFlush(SessionImpl.java:778)
       at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:756)
       at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1401)
       at net.sf.hibernate.engine.Cascades$4.cascade(Cascades.java:114)
    

3. After an upgrade to 5.3.x, you can no longer see the list of page watchers although there were users watching the page before the upgrade. When you look into the NOTIFICATIONS table, the watch notifications were listed there and they were tied to the correct usernames. However they were referencing to PAGEID instead of CONTENTID. CONTENTID is all NULL instead.

Resolution

All of the above can be fixed by implementing the steps below:

1. Stop Confluence
2. Backup Confluence database and Home Directory.
3. Run the following SQL query to copy the PAGEID values into the CONTENTID column

   UPDATE NOTIFICATIONS SET CONTENTID = PAGEID WHERE PAGEID IS NOT NULL;
   

4. Run the following query to drop the PAGEID Foreign Key constraint (double check the actual key name in the NOTIFICATIONS table)

   ALTER TABLE NOTIFICATIONS DROP FK594ACC88C38FBEA;
   

5. Run query to drop the index on the PAGEID column

   drop index NOTIFICATIONS.n_pageid_idx;
   

6. Run this query to drop the actual PAGEID column

   ALTER TABLE NOTIFICATIONS DROP COLUMN PAGEID;
   

7. Start Confluence
8. You should now be able to purge the trash, delete any space, and see a list of page watchers in Watch > Manage Watchers.

Important note

The original bug in the upgrade task has been fixed. This means customers upgrading from Confluence versions lower than 5.3 to 5.4.4+ should no longer be affected by this.

However if you have previously upgraded from an older version of Confluence and are affected by this problem upgrading again to Confluence 5.4.4+ will NOT rerun the upgrade task. Therefore for customers in this situation it is still necessary to run the workaround, since upgrading alone is not sufficient to fix this problem.