Upgrading Confluence with a wrong collation on Microsoft SQL causes watches to be lost in all pages.

If your Confluence is being upgraded while using a Microsoft SQL Server database configured with the wrong collation, all the page watchers will no longer appear in the UI.

Steps to reproduce:

1. Setup a Confluence under version 5.2.3.
2. Make sure to use the "SQL_Latin1_General_CP1_CI_AS" collation instead of "SQL_Latin1_General_CP1_CS_AS".
3. Once this Confluence is up and running, add a few pages and create a few users.
4. Watch the pages with those new users.
   At this point everything will work fine.
   
5. Upgrade Confluence to Confluence 5.4.3 (or latest).
6. Log on with one of the created users in step 3.
7. If you go to a page you watched you will see that your user is no longer watching that page.
   

Diagnostic

Run the below query against your Confluence MS SQL server database:

SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name <> 'SQL_Latin1_General_CP1_CS_AS'
AND object_name(object_id) NOT LIKE 'sys%' 
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'

If the above return any results means your database is using the wrong collation.

Notes

If the users re add themselves as watchers the function will work normally.
In the UI the user is no longer watching a page after the upgrade, however we are still able to see data in the notifications table:
Before Upgrading:

After Upgrading:

Workaround

1. Restore your pre-upgrade backups
2. Fix the collation in the database by following the steps here
3. While you're there, ensure everything else in this document is set correctly
4. Try the upgrade again

NB: Once the upgrade is done the watches will be lost and there is no way to retrieve them. You must restore your backups from before the upgrade and start the process again.